Home » 2012 » July

Monthly Archives: July 2012

SQL Server 2008 R2 SP2 Is Now Available !

Service packs are very critical and important. It is very important from product upgrade & bug fixing point of view.

Regular upgrades to your system with latest releases help you in :-

· Continued Support

· Resolves Problems

· New Features

Microsoft has recently released SQL Server 2008 R2 service packs. Please note SQL Server 2008 R2 and SQL Server 2008 are different versions & this release is for SQL Server 2008 R2. It will not work on SQL Server 2008 but will work only on SQL Server 2008 R2. If you are installing fresh SQL Server 2008 R2 or having SQL server 2008 R2 RTM or SQL server 2008 R2 SP1 or any other CUs installed , you just have to download and install SP2 and there is no need to install mediator releases. Service pack SP2 will contain all releases & upgrades before SP2 including SP1 upgrades.

List of Bug Fixes : http://support.microsoft.com/kb/2630458

You can download : http://www.microsoft.com/en-in/download/details.aspx?id=29848

Blocking & Deadlock

Blocking & Deadlock

SQL Server blocking

SQL Server blocking occurs when one connection (user process or application process) places a lock on a table (or a number of rows) and a second connection attempts to read or modify the data under the lock. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

The more blocking happens on the server the less concurrency the system achieves. A certain amount of blocking is unavoidable but too many blocks for longer periods of time can degrade the performance of SQL Server.

SQL Server deadlocks

The combination of two blocked connections where the first is blocking the second and the second is blocking the first is called a deadlock. Since deadlocks are not naturally resolved with time, SQL Server automatically kills one of the connection (Deadlock victim) once it detects a deadlock. This allows the other connection to continue with its transaction.

Although deadlocks can be caused by two short blocks (Fraction of a second), it is often the long blocks that increase the chances of a deadlock to happen.

Blocks escalating to deadlocks

The following diagram shows the sequence of events leading to a deadlock. Consider two applications (A1, A2) accessing two different table (T1, T2):

Event 1: A1 places a lock on T1 inside its transaction and continues to execute other statements

Event 2: A2 places a lock on T2 inside its transaction and continues to execute other statements

Event 3: A1 attempts to place a lock on T2 (Needs to access T2 before it can finish the transaction) but has to wait for A2 to release its lock

At this point, a block is created since A2 is blocking A1

Event 4: While A1 is waiting, A2 attempts to place a lock on T1 (Needs to access T1 before it can finish its own transaction)

A deadlock is created since two connections have blocked one another. SQL Server automatically resolves the deadlock by choosing one of the connections as a deadlock victim and killing it.

Text information from RPC trace events not displayed in Profiler

In SQL Server 2005 and later, the information in the TextData column captured for the RPC:Starting and RPC:Completed events is also available in the BinaryData column. For performance reasons, it is more efficient to include the BinaryData column and omit the TextData column, when adding these events to the trace. If the BinaryData column is included, Profiler will extract the text from that column and display it in the lower event details pane, even if the TextData column was not included for the event.

However, a problem occurs when the trace does not contain ANY events with the TextData column. If such a trace is open in Profiler, the TextData column is not displayed in the grid, and the text information associated with the RPC:Starting and RPC:Completed events is not displayed in the lower event detail pane, even though the information is available in the BinaryData column.

SQL SERVER – SELECT vs. SET Performance Comparison

While testing the performance of the following two scripts in query analyzer, interesting results are discovered.

SET @var1 = 1;
SET @var2 = 2;
SET @var3 = 3;
@var1 = 1,
@var2 = 2,
@var3 = 3;

While comparing their performance in loop SELECT statement gives better performance then SET. In other words, SET is slower than SELECT. The reason is that each SET statement runs individually and updates on values per execution, whereas the entire SELECT statement runs once and update all three values in one execution.

SET is the ANSI standard for variable assignment, SELECT is not.

SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET.

If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned.

When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all keeping the variable unchanged.

Why Can’t I Set a Default SQL Server Schema for My Windows Group Login?

Application doesn’t use the dbo schema and I need to set the users coming into the database to use the correct schema automatically. We’re using Windows groups and whenever I go to set a default schema, SQL Server gives me an error. How can I set the default schema for my users?

The short answer is that if you’re using Windows groups, you can’t. While the best practice is to use Windows groups because that makes security management much easier on the DBA, one area that’s not true is when you need to specify a default schema.

As of SQL Server 2005, users in a database can be set to have a default schema except for the following cases:

  • Windows groups
  • Logins mapped to certificates
  • Logins mapped to asymmetric keys

We’ll focus on Windows groups. If you try to specify a default schema, as in the following example:

You get this error:

This is not a bug; it is the expected behavior. While you can set other default settings, like default database and default language, these correspond to the server level of SQL Server and not an individual database. So why is there a difference? It has to do with the fact that a given Windows user is typically a member of multiple Windows groups. More than one of those Windows groups could have access to the SQL Server and to a specific database, especially in larger environments. For instance, as a DBA you might be a member of the following groups at the domain level (I’ve using generic names as each organization has different naming conventions):

  • Domain User
  • Full Time Employee
  • Information Technology Member
  • Database Administrator

And for a given SQL Server, the groups Database Administrator and Full Time Employee might both access. In the event that both have access, when we’re talking about default database and default language, it’s not a big deal if we have multiple values as long as they are all valid. After all, both can be overridden when connecting to the SQL Server, even if there is an issue. They also can be overridden after the connection is made. However, when it comes to default schema, we have a problem. For instance, imagine if we could do the following:

  • Database Administrator -> Admin
  • Full Time Employee -> Employee

When a DBA connects and tries to execute a stored procedure in the database, does SQL Server execute Admin.SomeStoredProcedure or Employee.SomeStoredProcedure? What if there are multiple groups? How do you know for a particular user which schema should be default? The only way to solve the confusion is to specify per user. And if you’re going to do that, there’s no point specifying the default schema for a Windows group. One could argue that if a particular user is a member of only one group, then a default schema should be specified. However, because of the fact that the association with groups occurs outside of SQL Server, this can lead to unexpected behavior. Also, if a second group is added to the database which the user is a member of, we no longer have the single group situation. Then how should SQL Server react? There’s no simple way to solve this issue. That’s why default schemas aren’t allowed for Windows groups.

SQL has long resolved similar issues for logins that are Windows groups. For instance, if a Windows user is a member of two Windows groups that are in turn created as logins on a SQL Server machine, SQL is able to resolve possible differences in default database, language, etc. So it is beyond any possible justification that Microsoft has yet to in some way resolve this for default schema. Defaulting to the dbo schema in such a situation would certainly be an acceptable solution so long as the user is not automatically granted alter on the dbo schema.

At present, if a user that does not have a default schema by virtue of accessing via a group creates an object without qualifying it (ie: “create table” rather than “create dbo.table”) will automatically cause a new schema to be created named after themselves. This user is then granted full ownership of this new schema. This happens even when the group login/user that they used to get to the database is EXPLICITLY DENIED create schema rights. This is totally unacceptable. A user should not be able to create a schema when they are denied that permission. Microsoft has tried to claim that the user is not actually creating the schema, that SQL Server actually is. While this may technically be true, it in fact always it technically true. Any user that creates a schema does so by submitting a request to SQL Server to create it, and SQL Server then creates is. In either case, the user caused SQL Server to create it so the attempt at justifying this by saying that the user didn’t violate the “deny create schema” fails the test of basic logic

The Workaround

Unfortunately, the only real workaround if users access SQL Server directly is to add users individually to SQL Server and to the database. This doesn’t work if you have many users, but because specifying default schema gets to a per user setting, it’s really the only way to go. There are ways, such as through PowerShell to script the members of a Windows group and then generate the T-SQL to create the logins, then add them to the database and set the default schema, but you’ll be running this regularly to keep everything up to date. However, if you have an application that has a schema other than dbo and users cannot specify the schema, you’re not left with any other choice.

If users are accessing through an application, such as a web service, then using a Windows user account for the web service is an option, which allows you to specify the default schema. This assumes, of course, that the web service or application is handling security outside of SQL Server. If it’s not, then this isn’t a viable option either, and you’re forced back to the workaround. In this situation, resorting to a SQL Server-based login doesn’t work either, because there is no way SQL Server can tell between Mary and John accessing the DB through the application. You’ll have to drop back to specifying the users to be passed through and create them in the database individually.

In SQL 2012 we can have default schema for windows groups as well.

%d bloggers like this: