Category Archives: MSSQL

Restore SQL 2008 R2 Database on SQL 2008 SP1 instance

It has been a long time since I wrote a new post, but now I ‘m back after a very busy period and I would like to share with you a recent problem I had with a restore from a higher version of SQL Server – SQL 2008 R2 to a lower version – SQL 2008 SP1.

If you search the internet, you will be sad to find out that:

  1. The restore from a database backup made on a higher SQL version will fail on a lower SQL version because the SQL is not up-level compatible.
  2. The attaching of data and log files created on a higher SQL version will fail on a lower SQL version because the SQL is not up-level compatible.
  3. Scripting entire database (SQL 2008 R2: Tasks –> generate scripts –> script entire database –> advanced: select to script schema and data) and executing on destination server can get you what you need but it works only if the database is not very big and the output of the sql file is in reasonable size parameters.
  4. Another scripting method is to do it step by step (object by object): create database, export tables with export/import tool (easier because you don’t have to deal with constraints), create primary keys, foreign keys, default and other check constraints, indexes, views, stored procedures, migrate users security, cdc feature, triggers… etc. As you can see it is time consuming and you can’t do a single mistake. If the database was as big as I had to deal with (over 60 tables) then it can become a nightmare.

Gladly, there is one single possibility of “restoring” the database and having all of needed objects inside in a very elegant way ūüôā
The duration depends on the database size. If you have do it relatively urgent like I did and can’t afford to go through the above alternative steps (3 and 4), please read further. By the courtesy of one of my readers, as he pointed out: very important to know is also that this method will work if the database migrated doesn’t have encrypted procedures. To use this method, you need to remove encrypted procedures and recreate them after the migration. I couldn’t find anywhere this tip even if this is an SQL feature and I thought many can find it usefull. On MSDN, the solution presented below that works for a database downgrade is treated only as an database upgrade option. (Using the Copy Database Wizard).

Things that are good to now

The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to. The database version number does not equal the SQL Server version. For example, doing the following:

SELECT @@version;
GO

will display information about the SQL Server installation build, meaning the whole engine. The database version however is not the same.
Running this will get you the database version:

USE master
GO
SELECT DatabaseProperty ('DATABASE NAME', 'version');
GO

Database compatibility level determines how certain database behaviors work. This is set on database level. You can have a lower database compatibility level than the default one. On 2008 instances the default compatibility level is 100, and it can be 90 or 80 if you need the database to be compatible with a lower SQL Server edition. Setting a lower compatibility version does not allow the database to be restored on a lower SQL Server version.

In the following example, I will detail how you can use the Copy Database Wizard to migrate a database created on SQL 2008 R2 to SQL 2008 SP1, meaning to downgrade it from database version 661 to 655.

As a note to this, and by the courtesy of one of my readers I have to add that this option is only available if you don’t have encrypted stored procedures inside your database. If you do you will receive an error and will need to escalate this problem: either drop the procedures and re-create them after the copy database process described below, either un-encrypt the procedures before the copy database process and re-encrypt after.

Copy Database method

  1. Step 1 – Select source and destination servers
  2. Start by selecting the database that you want to downgrade. Right click on it and go to: Tasks –> Copy Database… Press Next. In “Select Source Server” window specify the source server name and the authentication type. In my example I used Windows. If you use SQL authentication, it should be with a user that has sysadmin role assigned. Both Windows and SQL need sysadmin role.

    Press Next and in following screen, select the destination server with same considerations on authentication type as on previous screen.

  3. Step 2 – Select Transfer method
  4. There are 2 options: Use detach and attach method and Use SQL Management Object method.
    If I would select the detach/attach method, the database would be detached, the data and log files would be copied to the destination server and finally attached to the destination server. This method would fail because the attach method is not supported on lower SQL versions.

    This is why you need to select the second option: SMO method.

    Select Transfer Method

    Select Transfer Method


     
     
     
     
     
     
     
     
     
     
     
     

  5. Step 3 – Select Database
  6. Select Databases

    Select Databases


     
     
     
     
     
     
     
     
     
     

  7. Step 4 – Select Database file location
  8. Select the location on disk for the future database files:

    Configure Destination Database (1 of 1)

    Configure Destination Database (1 of 1)


     
     
     
     
     
     
     
     
     
     
     
     
     
     
     

  9. Step 5 – Select Server objects
  10. You can migrate Logins, SQL Agent Jobs and other objects. I selected only the logins for the migrated database.

    Select Server Objects

    Select Server Objects


     
     
     
     
     
     
     
     
     
     
     

  11. Step 6 – Configure the Package
  12. The SSIS package created will be integrated into a new SQL Agent job with the same name as the package but you can change it. This can be executed immediately or scheduled. In this example I will execute it immediately. Also, you can store the log file and to check afterwards what exactly happened beyond this wizard.

    Configure the Package

    Configure the Package


    Schedule the Package

    Schedule the Package


     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     

  13. Step 7 – Finalize the migration
  14. Review that all the information displayed in below screen are valid.

    Complete Wizard

    Complete Wizard


     
     
     
     
     
     
     
     
     
     
     
     
     
     
     

At last if someone is curious of what exactly this SSIS package is doing, I thought it would be useful to post the package log from both types of methods: Detach/Attach & SMO. You will see the difference between them starting the moment when the data and log files are created. In SMO, these are created not copied.

Detach/Attach
 

Transferring database DB_2008_R2 from SERVER2008R2 server as DB_2008_R2 to SERVER2008SP1 server
Dropping all connections to DB_2008_R2
All active connections to DB_2008_R2 are dropped
Detaching database DB_2008_R2 from source SQL Server
Database DB_2008_R2 detached from source SQL Server
Copying file D:\SERVER2008SP1\MSSQL\DATA\DB_2008_R2.mdf to G:\SERVER2008R2\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2.mdf overwrite set to False
Copying file D:\SERVER2008SP1\MSSQL\DATA\DB_2008_R2_log.ldf to G:\SERVER2008R2\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2_log.ldf overwrite set to False
Attaching database DB_2008_R2 to destination SQL Server as database DB_2008_R2
Attaching database DB_2008_R2 to SERVER2008SP1, SetBrokerState=True, AttachOptions=None
OnError,SERVER2008R2,DOMAIN\SQLUSER,SERVER2008R2_SERVER2008SP1_Transfer Objects Task,10/21/2011 12:00:32 PM,10/21/2011 12:00:32 PM,0,0x,Attach database failed for Server ‘SERVER2008R2’.
[…]
InnerException–>The database ‘DB_2008_R2’ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database ‘DB_2008_R2’. CREATE DATABASE is aborted.

SMO
 

Transferring database DB_2008_R2 from backenddc server as DB_2008_R2 to backenddc\LCS server
Added data file DB_2008_R2 in path G:\LCS_INSTANCE_2008_SP1\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2.mdf to file group [PRIMARY]
Added log file DB_2008_R2_log in path G:\LCS_INSTANCE_2008_SP1\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2_log.ldf
Transferring data to database DB_2008_R2 from DB_2008_R2
Transferred data to DB_2008_R2
Transferring server objects to server backenddc\LCS from backenddc
Database transfer is complete.
Transfer objects finished execution.

How to force SQL to use ad-hoc created Indexes on #temp tables

Inside a stored procedure, one simple and common way of querying/modifying some production data and avoiding unnecessary locks on production tables is to use temporary tables. These tables can be queried/modified and joined with base tables without creating blockages; resulting information can be manipulated easier and faster.

There is a problem though, when working with temporary tables. I am speaking about tables created with # in front which are removed at each session’s closure and only those that are part of a stored procedure code.

If you use a certain temporary table in a join operation with another table, and you have many rows implicated – than you would probably see a performance issue, since you don’t have an index covering both joining columns.
This behavior is also available in order by statements or filter clauses (where involved).

You will see table scans on #(temporary) table and the performance will be very poor. The next logical step is to create the proper indexes right after the table creation.

You are absolutely right to do so, but SQL will ignore this index and exclude it from the Execution Plan, because the Execution Plan was already created when the index didn’t exist. When a stored procedure is called, it is first compiled and Query Optimizer creates an Execution Plan for it. It does not take into consideration the indexes created on temporary tables.

In order to achieve this you can do this by following one of the below methods:

  • Create Primary/Unique keys when creating the temporary table. – this is only efficient if you have unique columns involved in order by or filtering options.
  • Specify the table hint: with (index(name_of_the_index)) right after mentioning the table in a select statement.
     

    Example: Select name from #table with (index (index_id)) where id>1000

RangeX-X Key-Range Locks in SERIALIZABLE isolation level (part 3)

I will continue in this part describing other types of Key – Range locks: RangeX-X combined with RangeS-U locks.

As a reference I will use the same table created in part 1 – range_lock.

RangeX-X Locks

RangeX-X locks are exclusive resource locks acquired on Index key values from a specific range when the transaction modifies a key value from the Index. The locks are exclusive locks meaning, no other type of transaction (Select/Update/Insert/Delete) can be executed until the first transaction is completed/commited on the range key values modified.

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
begin transaction
go

update range_lock set rname ='ana' where rname = 'anna' 


Analyze the Query Execution Plan

RangeX-X Execution Plan

RangeX-X Execution Plan

 
 
 
 
 
 
You will see a Clustered Index Update, and an Index seek on ix_rname (rname column) – this defines the range of the key values to seek in.
At first look, the execution plan looks exactly as it did on RangeS-U locks – with a slight difference on the operator cost (%): 75% on RangeS-U and 86% on RangeX-X. Why? If I mouse over the Clustered Index Update operation I see 2 indexes listed for Update operation: Primary Key and ix_rname index.

RangeX-X Execution plan - highlight Index Update operation

RangeX-X Execution plan - highlight Index Update operation


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Both Indexes are planned for update because:

  • PK_range_locks – in this case is a Clustered Index that orders the data rows in the table based on its (Primary Key) key values. The update is made on another column than the defined Primary key column. All data rows from a table are called the leaf level of the index. The Primary Key values contain pointers to all data rows from the table. Any update on another column than the primary key will result to an update on the leaf level of the Clustered Index.
  • ix_rname – Update is made on a key value from the Index, so the index should be updated with a different value.

Check Held Locks

Execute:

sp_lock 55

Here is what you would get:

RangeX-X for equality operation

RangeX-X for equality operation

I see that one Exclusive lock (X) is held on IndId=1 (Primary Key) which is needed for the Index update, and I see that RangeX-X lock is acquired on IndId = 2 (ix_rname) on one key value. Also RangeS-U is acquired on IndId = 2 (ix_rname) on one key value. If I go back in part 1, and take a look on table rows and their order, I will see that after “anna” is “antony” row. I can take a smal guess that the RangeS-U lock is on the next key value after “anna” – “antony”. How can I prove that?
 
 
 
Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

begin transaction 

select rname from range_lock where 	
rname = 'antony' 

This statement will work fine. RangeS-U locks allows Select statements to be executed. Rollback this transaction.

Inside the same session execute:

begin transaction 

update range_lock set rname = 'antoni' where
rname = 'antony' 

You will see that this transaction will be blocked by the first one.

Session 58 is blocked by session 55

Session 58 is blocked by session 55


 
 
 
 

Check Locked resources on both transactions

To figure out why the update operation on rname column for the “antony” row is blocked by the first update on rname column for the “anna” rows check this out:

Execute:

sp_lock 55 
go
sp_lock 58
go
RangeX-X for equality operation

RangeX-X for equality operation

I see one WAIT type of lock for the second transaction on same resource the first transaction has put RangeS-U lock. It is clear that we are speaking about same row: “antony”.

Microsoft explains WAIT locks as:

WAIT – Lock is blocked by another process.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
The conclusion I get is: RangeX-X locks will only be acquired on those columns that are actually modified from the range. Even if the range contains the “next” value from the table list of rows, RangeS-U locks will be held on that value (s), in order to maintain the integrity of data until the transaction is commited.

 

RangeS-U – Key-Range Locks in SERIALIZABLE isolation level – (part 2)

I will continue in this part describing other types of Key – Range locks.

As a reference I will use the same table created in part 1 – range_lock.

RangeS-U Locks

RangeS-U locks are locks acquired on Index key values from a specific range when the following conditions are met:

  • The transaction contains an UPDATE statement
  • WHERE clause is used to specify a condition that limit the rows that are updated. The condition will include at least one column from the Index. This will define the range of key value(s) from the Index. In this case, locks are needed on defining key value(s) that refer to the rows updated.
  • The UPDATE modifies a column(s) which is(are) not contained in the Index definition or:
  • The UPDATE modifies a column(s) which is(are) contained in the Index definition.

I hope from the below example the explanation will more straightforward. I will use a larger range of values in this example, meaning I will use an inequality operation. The equality operation will have the same types of locks.

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
begin transaction
go

update range_lock set sname ='surname' where rname between 'anna' and 'arlen'

Analyze the Query Execution Plan

RangesS-U - Execution Plan

RangesS-U - Execution Plan

 
 
 
 
 
 
You will see a Clustered Index Update, and an Index seek on ix_rname (rname column) – this defines the range of the key values to seek in.

The update on PK_range_locks happens because in this case the PK is a Clustered Index that orders the data rows in the table based on its (Primary Key) key values. The update is made on another column than the defined Primary key column. All data rows from a table are called the leaf level of the index. The Primary Key values contain pointers to all data rows from the table. Any update on another column than the primary key will result to an update on the leaf level of the Clustered Index.

Check Held Locks

Execute:

sp_lock 55

Here is what you would get:

RangeS-U for inequality operation

RangeS-U for inequality operation

I see 3 Exclusive locks (X) are held on IndId=1 (Primary Key) which are needed for the Clustered Index update, and I see that RangeS-U locks are acquired on IndId = 2 (ix_rname) on four key values. If I go back in part 1, and take a look on table rows and their order, I will see that between “anna” and “arlen” are 3 rows, not 4. The fourth lock is on the next key value after “arlen” – “barry”.
 
 
 
 
 
 
 
 
As the name suggests Shared – Update locks mean that:

  • Other transactions that need to Select the same rows inside the range will be permitted.
  • Other transactions that perform an update on any of the key values from the range – will be blocked by the first one until the first one is commited or rollbacked.

Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

begin transaction 

select rname from range_lock where 	
rname = 'barry' 

This statement will work fine. Rollback this transaction.

Inside the same session execute:

begin transaction 

update range_lock set rname = 'barri' where
rname = 'barry'  

You will see that this transaction will be blocked by the first one.

Session 58 is blocked by session 55

Session 58 is blocked by session 55


 
 
 
 

Check Locked resources on both transactions

To figure out why the update operation on rname column for the “barry” row is blocked by the first update on sname column for the rows between “anna” and “arlen” check this out:

Execute:

sp_lock 55 
go
sp_lock 58
go
RangeS-U for inequality operation

RangeS-U for inequality operation

I see one WAIT type of lock for the second transaction on same resource the first transaction has put RangeS-U lock. It is clear that we are speaking about same row: “barry”.

Microsoft explains WAIT locks as:

WAIT – Lock is blocked by another process.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
This is all about RangeS-U locks and further in part 3, I will detail RangeX-X locks and RangeX-X combined with Range S-U locks.

RangeS-S – Key-Range Locks in SERIALIZABLE isolation level (part 1)

Overview
 

Locking mechanism in SQL is by far one of the most complex mechanism and hard to explain. Blockages and Locks are a DBA’s daily problems when confronting with concurrent processes which access same records of the same table. In order to have a high – performance system, a very important aspect to consider is isolation of concurrent operations. In high-activity systems like web-sites, ERP’s or online shops there is a huge amount of concurrent transactions that select data from same tables, modify data and insert new data. In order to isolate some processes, the isolation level of transactions must be changed. The Transaction Isolation Level controls the default transaction locking behavior for all SQL statements issued by a connection.

READ COMMITTED is the default level of isolation which is also the most recommended for most processes. But this level is not always enough in order to have an ACID transaction because the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. In order to isolate completely a Transaction which has a BEGIN TRANSACTION in front – you can SET TRANSACTION ISOLATION LEVEL to SERIALIZABLE inside a stored procedure. This does not affect other transactions and their isolation level.
Also, If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control, the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

SERIALIZABLE isolation level is equivalent HOLDLOCK hint and has the same behavior when dealing with locks.

When SERIALIZABLE isolation level is set – a range lock is placed on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. And also… it is the less explained on WWW. This is mainly the reason I want to write about the Locking types inside SERIALIZABLE transaction.

Key-Range Lock Modes
 

In serializable isolation level Key – Range locks are used. Serializability implies that any query executed inside a transaction must obtain the same set of data from the beginning until the end of the transaction. The set of data locked inside the serializable transaction is based on a range of key values of the index that covers the rows extracted. It is assured that the values from the range of values locked will not be modified or new values will not be inserted for the same range of values by other concurrent transactions. Any attempt of a second transaction to insert, update, or delete any row within the same range as the first one requires a modification to the index. Thus, the second transaction is blocked until the first transaction completes because key-range locks cover the index entries.

There are 4 types of locks:

  1. RangeS-S – Shared range, shared resource lock; serializable range scan.
  2. RangeS-U – Shared range, update resource lock; serializable update scan.
  3. RangeX-X – Exclusive range, exclusive resource lock; used when updating a key in a range.
  4. RangeI-N – Insert range, null resource lock; used to test ranges before inserting a new key into an index.

There are couple of basic things to know about key-range locking which are not mentioned in MSDN documentation:

  1. In equality operations (“=”, “IN”) – Key-Range locks are not held on key values that are found inside an Unique Index even if the transaction is issued in Serializable isolation level.

    Example:
    If the ix_range index created on range_lock table would be Unique, the following statement would not require Key-Range locks.

    Shared locks will be enough.

    select rname from range_locks where rname = 'anna' 
    
  2. In equality operations (“=”, “IN”) – Key-Range locks are held on “next” key values that are not found inside an Unique or Non-Unique Index.

    Example:
    Whether ix_range index created on range_lock table is Unique or Non-Unique, the following statement will hold Key-Range locks on the “next” value of the key value from the equality operation that was not found inside the index.

    In this example “annabella” field does not exist so, Key-Range locks will be put on next value from the list: “antony”.

    select rname from range_locks where rname = 'annabella' 
    
  3. In in-equality operations (“>”, “<", BETWEEN, LIKE, "<>“) – Key-Range locks are held on all key values (found) from the range specified and the “next” value. This is true for both Unique or Non-Unique Indexes.

    Example:
    Whether ix_range index created on range_lock table is Unique or Non-Unique, the following statement will hold Key – Range locks on all key values in the range when using “BETWEEN” and on the “next” value of the key value that is outside the range.

    In below example RangeS-S locks will be acquired on all values between “antony” and “barry”.
    “annabella” is missing – so the starting value of the range will be considered the next value in alphabetical order after “annabella”. Also an additional RangeS-S lock will be put on the value after “barry”: “benedict”.

    select rname from range_locks where rname between 'annabella' and 'barry'
    

RangeS-S Locks
 

I will explain through examples each type of locking. In this part I will go through Key – Range Shared – Shared locks. In order to not repeat on following parts I will write only in this part the prerequisites for my tests.
I need to create a table and insert few rows into it. The table will have a Primary Clustered Key and an Index Non-Clustered on one of the three columns.

Here you go:

create table range_lock 
(rid int NOT NULL IDENTITY (1, 1),
rname nvarchar (20),
sname nvarchar (20))

ALTER TABLE range_lock ADD PRIMARY KEY (rid);

CREATE NONCLUSTERED INDEX [ix_rname] ON [dbo].[range_lock] 
(
[rname] ASC
)

Populate the table with names. The rows will be displayed in alphabetical order due to the ix_rname index.

INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('anna')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('antony')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('angel')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('ARLEN')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BARRY')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BENEDICT')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BILL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BRYCE')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CAROL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CEDRIC')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CLINT')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('DARELL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('DAVID')

A simple select in the range_lock table will show the inserted values ordered by rname column ascending.

Rows ordered in ascending order by the rname column.


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
Begin Transaction
go
select rname from range_lock where rname = 'anna'

Analyze the Query Execution Plan

RangeS-S locks - Execution Plan

RangeS-S locks - Execution Plan


 
 
 
 
 
 
 
 
You will see an Index Seek on ix_rname index because the ix_rname contains the rname column needed in the SELECT. Index Seek only touches only the rows that qualify.

IMPORTANT!!!

If I would issue a SELECT on all table columns, then the Query Optimizer will perform an Index Scan on the Primary Key in order to retrieve rid and sname values position from the Clustered Index. This is not optimal and must be avoided. So to avoid this, the rid and sname columns should be added as included columns inside ix_rname index.

Check Held Locks

Execute:

sp_lock 55

Here is what you would get:

RangeS-S for equality operation - Non-Unique Index

It is obvious that RangeS-S locks are acquired on IndId = 2 (ix_rname) for two key values.

Remember what I wrote earlier about equality operations and Unique Indexes – that Range locks are not held? Well, here we deal with a Non-unique Index so, it is quite normal that RangeS-S locks are held. The question that pops in, is why two RangeS-S locks are acquired when the statement only retrieves data about “anna”?
That is because Range locks are acquired on the key value from the “=” operator and on the next value. This is to ensure that no row can be inserted updated or deleted between the requested key and the one after that. It means that “anna” and “antony” rows would become locked if some other transactions tried to modify the fields. Let’s see that.

If you look above in the range_lock table content you will see that after “anna” comes “antony”. Further I will try to delete the “antony” row inside another transaction from the default isolation level and see what happens.

Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

begin transaction
go
delete from range_lock where rname ='antony'

The second transaction will wait for the first one to be committed.
 
Execute sp_who2 active to make sure session 58 is blocked by session 55.

58 blocked by 55


 
 
 
 

Check Locked resources on both transactions

To figure out why the delete operation on “antony” row is blocked by the first select on “anna” row check this out:

Execute:

sp_lock 55 
go
sp_lock 58
go

RangeS-S and CNVT locks


 

I see one CNVT type of lock for the second transaction on same resource the first transaction has put RangeS-S lock. It is clear that we are speaking about same row: “antony”.

Microsoft explains CNVT locks as:

CNVT – Lock is being converted to another lock. A lock being converted to another lock is held in one mode but is waiting to acquire a stronger lock mode (for example, update to exclusive). When diagnosing blocking issues, a CNVT can be considered similar to a WAIT.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
This is all about RangeS-S locks and further in part 2 and 3, I will detail more complex locks such as RangeS-U and RangeX-X.

Configure MSDTC for Distributed Transactions

Few days ago, inside a Software project, I had to enable and configure MSDTC (Distributed Transaction Coordinator) between 2 machines: a Web Server using NServiceBus to process messages in a transaction and a SQL Server.
I encountered some issues and I would like to detail the order of few configuration steps as well as testing methods that should be done/checked before MSDTC in fully functional.

 
Step 1: Network connectivity

Make sure both servers can ping each other by the machine name, cause MSDTC uses netBIOS to establish a connection.

Start a command prompt window and execute on both servers:

  • ping [SERVER_NAME1]
  • ping [SERVER_NAME2]

Step 2: Enable MSDTC on both servers.

There are pictures for Windows 2008 Server and Windows 2003 Server because MSDTC location is changed from one WIndows edition to another. The options to check are the same though.

  1. Open Component Services:
  2. Component Services

  3. Access DTC Properties
  4. Windows 2003 - MSDTC location

    Windows 2003 - MSDTC location

    Windows 2003 - MSDTC location

    Windows 2003 - MSDTC location

    Windows 2008 - MSDTC location

    Windows 2008 - MSDTC location

     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     

  5. Enable MSDTC according to below options selected.
    Check only the red check boxes and click Apply.
     
  6. Windows 2008 - MSDTC properties

     
     
     
     
     
     
     
     
     
     
     
     

    A warning message will be popped in to inform that the Distribution Transaction Coordinator Windows service will be started (or restarted).
    MSDTC restart

  7. Set the startup type of Distribution Transaction Coordinator Windows service to Automatic.

If you don’t have Firewalls that prohibit external access to machine’s ports than you can stop here with the MSDTC configuration.
MSDTC will function great with basic configuration (Step 1 & Step 2) done only when there is no Firewall involved in network external communication. What do you do when there is a Firewall afterwards? In most Network configurations it is mandatory for securing the external access. If you follow all the steps detailed below, you should be able to make the MSDTC work without problems. Take a look at next steps.

Step 3: Restrict MSRPC dynamic port allocation.
 
MSDTC service depends on RPC protocol which stands in front of every Windows process that uses RPC. When you deal with MSDTC, you also have to consider the RPC access points. When MSRPC protocol is left with its default setting, all depending services including MSDTC is free to use a dynamically allocated port in the port range 1024-65535. Basically, it means that each re-start of MSDTC will result in a different port number. Fortunately you can restrict this port range which means that, instead of creating a rule in Firewall that opens all the ports from 1024 – 65535 you only need to insert the range of ports restricted in RPC setting.

There is one thing to be considered though:
 
There can be up to 100 services that depend on RPC and will be affected by this change. Make it not too small… not to big. Doing a little reading on the internet I saw that 50 – 100 ports would be a minimum – maximum for RPC depending services to function, but again it depends on each machine and how many processes depend on RPC. If you want to find out which are these look at RPC service at Dependencies tab and count the active ones.

RPC_Service

Perform these steps on both machines in order to configure a different port range. The port range does not have to be the same on both machines.

  1. Open Component Services properties windows
  2. Component Services Properties

  3. Access Default Protocols tab and insert a new port range.
  4. Change Port Range

Next, in order to be able to start a Distributed Transaction through MSDTC service – both participating servers must be configured to trust each other by allowing access to the each other’s port used by MSDTC service.

Step 4: Add Firewall Inbound rules

on SERVER 1: to allow inbound requests to the port range configured.
on SERVER 2: to allow inbound requests to the port range configured.
This will enable the communication between SERVER 1 and SERVER 2 through MSDTC service.

SERVER 1 will be able to access MSDTC allocated port from SERVER 2 and SERVER 2 will be able to access MSDTC allocated port from SERVER1.

Step 5: Restart both machines

You need to restart both machines where the port range was changed in order for these modifications to take effect.

 
Step 4: Testing MSDTC connectivity
 
After you had done all of the above, you might want to test first if a Distributed Transaction can be initialized and committed. There are 2 important Microsoft troubleshooting tools that I used and worked for me: DTCping and DTCtester. I will not detail the testing steps because all of them are covered in this post at step 2 and step 4.

Top 5 Steps To Determine Bad Performance of Queries

I will talk today about Performance of SQL queries. The performance of queries is a task which always has multiple causes and seen from different angles gives different solutions. Personally, I have sort-of a rule when confronting with bad queries: before coming up with o solution to improve execution of a specific query, I look at every possible angle of the problem and make sure all is covered according to performance best practices.

First, when a performance problem occurs? Basically there are few hints:

  • Application users make complaints about slow running processes.
  • Developers report bad performance for some queries and need DBA analyze before going in Production with the code.
  • Any of following¬†resources¬†is often triggered with high usage: CPU, Disk, Memory
  • Frequent blockages due to long-running or high I/O queries .

Next I will go through most important steps to do when handling a bad query

1. Analyze Execution Query Plan

Checking the Execution Plan of a query is the first thing to look at because it will give you a lot of information about how the statements are executed and provide estimates of the resources requirements for the statements as well at their order. It will outline the query plan chosen for a specific query. Do it in two ways:

  • From SQL Server Management Studio: at runtime click on “Include Actual Execution Plan” ¬†or without executing the query click on “Display Extimated Execution Plan”
  • Issue¬†SET SHOWPLAN_ALL ON at runtime.¬†When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it. This will detail the exact order of each statement.

Next, look inside Query Plan for statements which have Query Cost relative to batch higher than the rest of statements.

Pay attention to following events:

  • Index / Table Scan – Shows the need for an Index update or Index Creation. Check that every join or filtering (=, >, <) operation has an index on the column(s) used to filter data. For better performance include the columns from the select statement in the Index structure as included columns. It will avoid Primary Key Lookups.¬†Sometimes correct Index is being used but still Index scan is performed: It can happen if the amount of rows returned is bigger than the half of total rows from one table. SQL will choose to make a scan than an Index seek.
  • Index Create hints – Double¬†Check the Index hint syntax and make sure you don’t have already an index that can be updated with additional columns.
  • Use of Convert_Implicit function in Index Scan operations – in compare, equality or inequality operations use same type of datatype. Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. SQL will use this function to transform the value to another datatype before performing the actual operation which can take a lot of unnecessary I/O. I saw many Stored Procedures with declared variables with varchar datatype which were being assigned to nvarchar table columns. Same with int / bigint. This is not needed and is a frequent programming error that has to be fixed.
  • Index Update – A Clustered Index Update when an Index key is modified is normal but Non-Clustered Index updates should be avoided since every Index update puts an extra load on SQL. Having too much Indexes leads to many Index updates. Check the current Indexes from the specific table and decide whether you really all Indexes. Remember that Indexes are good for reading data, and bad for data ¬†modifications.
  • Nested Loops and Key Lookups – shows the need for¬†covering Indexes.
  • Sorts – Try to use Indexes instead which can avoid the sort operation.

2. Analyze the query with Tunning Advisor

This is an effortless double check measure for a query after the execution plan is analyzed. Create an .sql file with the Query code and load it to Tunning Advisor. SQL will create a report if it considers that there are missing indexes or statistics that can improve the performance. Tunning Advisor will report hints along with their syntax to create Indexes and show an improvement percent if the modifications suggested are done.

3. Use Profiler to track query compilations/re-compilations frequency.

Use Profiler to see if your Query has many compilations or re-compilations that can delay the query execution. I wrote a special post about this particular step that can be read at this link.

4. Avoid Parameter Sniffing

This applies to Stored Procedures with input parameters only. Parameter sniffing is the process whereby  SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed. When a Stored Procedure is executed for the very first time, SQL Query Optimizer will compile the query and create an optimal Execution Plan that suites best the passed parameters. In order to save re-compilation time, The Execution Plan will be used at subsequent executions of  the procedure if this is still is the cache.

In theory, there is no worry if each subsequent procedure call will be done using the exact number of parameters and values. The difference is when each call of the procedure is not the same as the compiled one in terms of the number of parameters passed as well as of the number of rows returned.

So, if you have a Stored Procedure that runs slowly, first thing to do is to check whether the execution plan is optimal for the result set returned.

  • Tune the procedure’s body script in SSMS (SQL Server Management Studio)

Execute the Procedure body script inside SSMS with a set of predefined variable values – same ones encountered at the slow execution of the stored procedure with parameters. At runtime press on “Include Actual Execution Plan” so that you can see the plan generated and have a compare start point. If the duration is considerably smaller than previous executions, ¬†than you should spend some time in analyzing this behavior.

  • Tune the long running procedure execution with Profiler.

Inside a Profiler trace add these events:

Profiler Events to display Execution Plan

 

 

 

 
 

Filter by Database Name to find easier the procedure call and click on Run. After you have caught some results check the Execution plan at Showplan All event for the SPID displaying your procedure call. Compare this Execution Plan to the one from the SSMS.

  • Avoid Parameter Sniffing

To avoid parameter sniffing the stored procedure can be written in a different way with identical output. Instead of using the calling parameters that are passed at execution Рreplace them with local variables which will receive the value of the calling parameters passed. This little correction will lead the Query Optimizer to keep track of the statistics and create an optimal Query Plan based on a average of the real values passed as parameters.

A second option would be to use WITH RECOMPILE in Procedure body. SQL will not cache the query plan for this procedure, forcing it to be compiled each time it is executed.

Or you could execute the procedure with this hint:

EXEC Procedure 'value' WITH RECOMPILE

The Compilation will be done only this time for this procedure only. The new execution plan created will apply only this time to the called procedure.

  • Check this example of handling Parameter Sniffing on AdventureWorks database:

Procedure containing parameter sniffing:

CREATE PROCEDURE [DBO].[PROCEDURE_PARAM_SNIFFING] (@FIRST int)
AS
select name, [ProductSubcategoryID] from AdventureWorks.production.product where [ProductSubcategoryID] = @FIRST
GO

Execute from 2 different sessions:

EXECUTE [PROCEDURE_PARAM_SNIFFING] 2
EXECUTE [PROCEDURE_PARAM_SNIFFING] 7

The call of [PROCEDURE_PARAM_SNIFFING] with different parameters will have the same Execution plan and scan a range of 43 rows even if second execution returns only one row.

 

Execution Plan ‚Äď Parameter Sniffing

 

 

 

 

 

 

 

 

 

 

 

 

 

Procedure re-written to avoid parameter sniffing:

CREATE PROCEDURE [PROCEDURE_WITHOUT_PARAM_SNIFF] (@FIRST int)
AS
DECLARE @LOC_FIRST int = @FIRST
select name, [ProductSubcategoryID] from AdventureWorks.production.product where [ProductSubcategoryID] = @LOC_FIRST
go

Execute from 2 different sessions:

EXECUTE [PROCEDURE_WITHOUT_PARAM_SNIFF] 2
EXECUTE [PROCEDURE_WITHOUT_PARAM_SNIFF] 7

The call of [PROCEDURE_WITHOUT_PARAM_SNIFF] with different parameters will still have the same Execution plan, but the scan range will represent an average (7.97) on all values grouped by¬†ProductSubcategoryID.The estimated number of rows is not related to first compilation of this procedure, rather it’s is calculated according to tables statistics.

 

Execution Plan ‚Äď Avoid Parameter Sniffing

 

 

 

 

 

 

5. Check Query Parallelism

This applies to multi processor servers. Quoting msdn: SQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Because SQL Server can perform a query or index operation in parallel by using several operating system threads, the operation can be completed quickly and efficiently. During query optimization, SQL Server looks for queries or index operations that might benefit from parallel execution.

The SQL Server configuration option that manages the number of CPU’s for sqlserver process is called: max degree of parallelism and is set at server level. With this option enabled (sp_configure), SQL Engine will use multiple threads to manage the so-cold¬†parallel-query execution plan. It can have big performance improving when speaking about complex – transactions which need to be processed all at once in multiple threads – taking advantage of the¬†powerful¬†CPU in place. But sometimes, when running a simple query, which does not needs to be divided into threads takes longer with the¬†degree of parallelism enabled than without it. This is all about testing and figuring out which option is best.

The Degree of parallelism can be¬†overwritten in singular query executions if MAXDOP hint is used. MAXDOP can be used to reduce the number of CPU’s used in multi-processing. I found a nice explanation of Pinal Dave about MAXDOP – that can help on starting with the analyze: SQL Server Maxdop settings.

To conclude this post, top 5 steps in analyzing a query’s bad performance before heading to fast solutions are:

1. Analyze Execution Query Plan
2. Analyze the query with Tunning Advisor
3. Use Profiler to track query compilations/re-compilations frequency.
4. Avoid Parameter Sniffing
5. Check Query Parallelism

Check Tables & Indexes size for your Database

Before accepting that your database is big because it needs all the data inside it and buy extra storage to overcome the potential space problem, you can find out if the existing space can be reduced by removing unused objects, indexes.

First find out what are the biggest tables:

 


DECLARE @query varchar(100)
    DECLARE @schema as sysname
    DECLARE @objectname as sysname
    
    IF (object_id('tempdb..#Table_Size') IS NOT Null)
    drop table [dbo].[#Table_Size]
    CREATE TABLE #Table_Size 
      ( TableName varchar(50),
        [RowCount] int,
        Table_Size varchar(50),
        Data_Space_Used varchar(50),
        Index_Space_Used varchar(50),
        Unused_Space varchar(50)
     )
    
    DECLARE Object_Cursor CURSOR FOR
    
    SELECT s.name, o.name  FROM sysobjects o inner join sys.schemas s ON o.uid = s.schema_id
    WHERE xtype='U' 
    
    OPEN Object_Cursor
    FETCH NEXT FROM Object_Cursor 
    INTO  @schema, @objectname
    
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
    
    set @query = 'sp_spaceused ''[' + @schema + '].[' + @objectname + ']'''
    
    INSERT INTO #Table_Size EXEC(@query)
    
    FETCH NEXT FROM Object_Cursor 
    INTO   @schema, @objectname
    
    END
    
    CLOSE Object_Cursor
    
    DEALLOCATE Object_Cursor
    
    select TableName,[RowCount],
    
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Table_Size)),len(ltrim(rtrim(Table_Size)))-2))/1024 [Table_Size (MB)],
    
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Data_Space_Used)),len(ltrim(rtrim(Data_Space_Used)))-2))/1024 [Data_Space_Used (MB)],
    
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Index_Space_Used)),len(ltrim(rtrim(Index_Space_Used)))-2))/1024 [Index_Space_Used (MB)],
    
    convert(NUMERIC(12,2) ,left(ltrim(rtrim(Unused_Space)),len(ltrim(rtrim(Unused_Space)))-2))/1024 [Unused_Space (MB)]
    
    from #Table_Size
    
    order by convert(int ,left(ltrim(rtrim(Table_Size)),len(ltrim(rtrim(Table_Size)))-2))  desc

After you have a list of biggest tables, check their Indexes. During my DBA work I have found many times unused indexes, doubled with same definition which not only occupied the disk space for nothing but also made worse the performance of any DML query as these should have been updated. So for a single Insert imagine updating 4 indexes. Sometimes these can be re-designed.

 

DECLARE @query varchar(100)
    DECLARE @index_id INT
    DECLARE @index_size BIGINT SET @index_size = 0
    DECLARE @name as NVARCHAR(500)
    DECLARE @indextype as NVARCHAR(256)
    DECLARE @TableName AS NVARCHAR(256)
    
    SET @TableName = 'TABLENAME'
    
    
    IF (object_id('tempdb..#Index_Table') IS NOT Null)
    drop table [dbo].[#Index_Table]
    
    CREATE TABLE #Index_Table 
     (	Table_Name		 varchar(250),
    	Index_ID		 varchar(250),
     	Index_Name		 varchar(250),
     	IndexType		 varchar(250),	
     	Index_Size_MB	 INT
     )
    
    DECLARE Index_Table CURSOR FOR
    
    SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) 
    
    OPEN Index_Table
    FETCH NEXT FROM Index_Table 
    INTO  @index_id, @name
    WHILE @@FETCH_STATUS = 0
    
    BEGIN
    
    SELECT @index_size = @index_size + (avg_record_size_in_bytes * record_count)
    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')
    
    set @index_size = @index_size / 1048576
    select @indextype = (index_type_desc) 
    from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED')
    
    insert into #Index_Table values (@TableName,  @index_id,  @name, @indextype, convert(int ,@index_size))
    
    FETCH NEXT FROM Index_Table
    INTO   @index_id, @name
    END
    CLOSE Index_Table
    
    DEALLOCATE Index_Table
    
    select * from  #Index_Table order by Index_Size_MB desc

Trace DDL & DCL operations in SQL Server Log – fn_trace_gettable

Did you know that starting with SQL 2005 all future versions of SQL includes the default trace of all DDL or DCL modifications done on all DB objects?

I was inspired by Jonathan Kehayias blog to dig deeper and check other useful output from the default trace.

Let me remind you what are Data Definition Language (DDL) and Data Control language (DCL) statements:
 
DDL statements: are used to define the database structure or schema. Some examples:

  • CREATE – to create objects in the database
  • ALTER – alters the structure of the database
  • DROP – delete objects from the database
  • TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT – add comments to the data dictionary
  • RENAME – rename an object

 
DCL statements : are used to manage users permissions. Some examples:

  • GRANT – gives user’s access privileges to database
  • REVOKE – withdraw access privileges given with the GRANT command

For example, I am pretty sure that you wanted at least once to find out if somebody with access on Production databases altered a certain Procedure (by create and drop), Deleted an Object or Created without letting you know or even by mistake? As well, granting rights to Logins just because some people can do that is a very bad¬†habit with which ¬†maybe you had to deal at some point. Windows Administrators or DEV team leaders can take advantage of their high rights and escalate the DBA’s ¬†in some urgent matter.

Asking¬†around¬†didn’t bring you any answer right? Perhaps the person who did that doesn’t even know, or maybe it was part of a faulty deployment?

The modification may have caused some errors, or lost in permissions… At last, it is very¬†useful¬†to know right away if this kind of modification took place and you or your team didn’t do that.

To see what Events are logged Рquery the fn_trace_geteventinfo (trace_id) function by joining it with sys.trace_categories and sys.trace_events tables:

SELECT DISTINCT tge.eventid, tc.name AS CategoryName,te.name AS EventName 
FROM fn_trace_geteventinfo(1) AS tge INNER JOIN sys.trace_events AS te ON tge.eventid = te.trace_event_id 
INNER JOIN sys.trace_categories AS tc ON te.category_id = tc.category_id
ORDER BY CategoryName, EventName

If you are interested in collecting all information in the last week displaying all Created/Deleted/Altered Objects as well as db_roles or server roles assigned to new Logins not by you, than you can issue following SQL code Рtested only on SQL 2008 :

SET ANSI_NULLS ON 
SET ANSI_WARNINGS ON
SET NOCOUNT ON

GO

---
IF (object_id('tempdb.dbo.#ddl_stmt') IS NOT Null)
   DROP TABLE tempdb.dbo.#ddl_stmt
 
CREATE TABLE   tempdb.dbo.#ddl_stmt
 (	
	   HostName nvarchar(250) null, 
       ApplicationName  nvarchar(250) null, 
       NTUserName  nvarchar(250) null, 
       NTDomainName  nvarchar(250) null, 
       LoginName  nvarchar(250) null, 
       SPID int null, 
       EventClass int null, 
       Name  nvarchar(250) null,
       EventSubClass int null,      
       TEXTData  nvarchar(250) null, 
       StartTime datetime, 
       ObjectName  nvarchar(250) null, 
       DatabaseName  nvarchar(250) null, 
       TargetLoginName nvarchar(250) null,
       TargetUserName nvarchar(250) null )
	
DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

DECLARE @starttime datetime = getdate () -7

INSERT INTO tempdb.dbo.#ddl_stmt ( [HostName]
      ,[ApplicationName]
      ,[NTUserName]
      ,[NTDomainName]
      ,[LoginName]
      ,[SPID]
      ,[EventClass]
      ,[Name]
      ,[EventSubClass]
      ,[TEXTData]
      ,[StartTime]
      ,[ObjectName]
      ,[DatabaseName]
      ,[TargetLoginName]
      ,[TargetUserName])
SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.TargetLoginName,
       gt.TargetUserName
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN master.sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (164, 46,47,108, 110, 152) and gt.StartTime > = @starttime
and gt.LoginName not in ('NT AUTHORITY\NETWORK SERVICE') 
ORDER BY StartTime DESC; 

select * from #ddl_stmt