How to migrate SQL Server from one machine to another

I recently was involved in a major migration project which included the move of few database production servers from one location to another. It was a very big project which implied a lot of inner coordination between teams and scheduling, so that the impact for the end-users would be minimal.

In case you are in the position of moving an SQL Server from one machine to another make sure you stick to the below presented checklist. The main purpose of the migration is to create a clone SQL Server with the same configuration, databases up to date, security and SQL Agent jobs in place. For this I always chose to restore both master and msdb along with all user databases. It seemed a lot easier than moving users/passwords, resolving orphaned users, updating default databases, updating system properties (sp_configure), migrating jobs, SQL Agent operators, recreating DB mail profiles, change data capture functionality, restoring partitioning information, and whole other specific database options that where once enabled.

After the migration everything must go smoothly and with no errors, otherwise, the migration didn’t go successfully. You have to make sure you cover all points and when you make the switch in connection strings everything is completed from DBA side of view.

Please if you find there is something to be added to the list below, feel free to comment and I will update this post.

 
I. Steps to do on the current Production server – [Server A]

  1. Check SQL Server properties – These must stay the same on the future server.
  2.  

    use master
    GO
    select SERVERPROPERTY ('Collation')
    select SERVERPROPERTY ('Edition')
    select SERVERPROPERTY ('InstanceName')
    select SERVERPROPERTY ('ProductVersion')
    select SERVERPROPERTY ('ProductLevel')
    select SERVERPROPERTY ('ServerName')
  3. Check global configuration settings – These must stay the same on the future server.
  4.  

    use master
    go
    sp_configure 'show advanced options' , 1
    reconfigure
    go
    sp_configure
    go
  5. Check Databases Integrity
  6.  
    It is necessary in order to be sure that restored backups on migrated server will be valid.

    DBCC CHECKDB ('DATABASE_NAME')
  7. Note down model database growing options.
  8.  
    This setting is important for every new created database. My strong advice is that you never leave the default setting because it can be a big cause for poor performance. The default setting is 1 MB per data file and 10% per log file – imagine what it means for a database to allocate MB after MB if it needs to allocate say 50 MB or grow a log file that is 10 GB in size.

  9. Note down physical path for master, model, msdb, tempdb databases data and log files.
  10.  

     
    use master
    go
    select name, filename from sys.master_files where database_id in (1,2,3,4) order by database_id
    
  11. Note down physical path for mssqlsystemresource data and log files.
  12.  
    SQL 2008: It should be in :\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Binn\
    For SQL 2005: same as master database location.
    Query to run on SQL 2008 and upper versions in order to find the physical location:
     

     
    use master
    go
    select name, filename,dbid from sys.master_files where database_id in (32767) 
  13. Perform Full backup on master and msdb
  14.  

 
II. Steps to do on the future Production server – [Server B]
 

  1. Install same edition/version of SQL Server on the new machine.
  2.  
    Keep in mind the following when installing:
     

    • Make sure the new server has mounted the same drive letters where system databases reside on old SQL Server. Otherwise, after the restore of master SQL will not be able to start because SQL will want to start the rest of system databases on physical paths from [Server A].
    • If you want to restore system databases then you need to keep the same edition (Standard, Entreprise, Business Intelligence) and version (ex: 2008 RTM, 2008 SP1, 2008 R2 RTM etc.). You cannot restore a backup of a system database (master, model, msdb) on a server build that is different from the build on which the backup was originally performed. An workaround would be, to replace manually the system databases files copied from [Server A] to [Server B]. This can be done only if the migrated server can be stopped. If not, then you must follow the procedure with “restore database….”
    • If you want to restore only user databases, then you can install the same or upper edition/version. If you install a lower edition/version than the current Production one, you might not be able to restore some of the databases having higher builds features which won’t be supported on lower builds.
    • Keep the same collation when installing SQL Server.
    • Create a Domain/Windows user account that will be used to start SQL Services.
  3. Backup system databases – master, model, msdb – and put in a safe location… in case you want to recover them.
  4. Stop SQL Server. Copy all system databases files and add them to the safe location.
  5. Create the physical locations for master, model, msdb, tempdb, mssqlsystemresource databases noted down at steps I.5 & I.6
  6. Give to the user running SQL Full security rights on the new folders created.
  7. Copy system databases backups made on [Server A] to [Server B] (step I.7).
  8. Stop SQL Server.
  9. Copy existing model, msdb and mssqlsystemresource database files from the installed location on [SERVER B] to the new created ones (step II.4). Afterwards you will be able to change the location for these databases. For now SQL will need old locations in order to load msdb, model, tempdb and mssqlsystemresource.
  10. Start SQL Server in single user mode. From an elevated cmd (started with administrator rights), access the right folder where sqlservr.exe executable is located and execute below commands. Normally you will find it at “C:\Program Files\Microsoft SQL Server\MSSQL[InstanceName]\MSSQL\Binn”

    There where cases when other services like SQL Agent or SQL Reporting took the single one connection and prohibited the administrator to restore master. This error is given:

    Reason: Server is in single user mode. Only one administrator can connect at this time.
    Before starting the SQL in single user mode, make sure you have stopped every SQL service and there is no one that will make a default connection to it (application, reporting)
     

    cd [drive]:\folder_name 
    sqlservr.exe -c -m
    
  11. Restore master database from the backup copied from [SERVER A] using a NEW cmd line started with administrator permissions or using a DAC (Dedicated Administrator Connection) connection.

    -- connect to SQL
    sqlcmd -SMACHINENAME\SERVERINSTANCE -E 
    -- make sure you are connected to right server: 
    select @@servername 
    GO
    RESTORE DATABASE master FROM DISK = '[Drive]:\Backup_path\MASTER_.bak' WITH REPLACE;
    GO
    
    
  12. Exit the cmd prompt and Start SQL from Services (in multi-user)
  13. Now the system databases (except master) will be loaded from the new created paths. Check new files location for system databases. master will point to the right location. msdb, tempdb and model must be changed. If you will restore msdb you can specify at that time, the new location for the files. So here I will show how to move model and tempdb. For msdb is the same.
     

     
    select * from sys.master_files 
    use master
    go
    
    Alter database tempdb modify file
    (name=tempdev, filename='[drive]:\new_location\tempdb.mdf')
    Alter database tempdb modify file
    (name=tempdev2, filename='[drive]:\new_location\tempdev2.ndf')
    Alter database tempdb modify file
    (name=templog, filename='[drive]:\new_location\templog.ldf')
    
    Alter database model modify file
    (name=modeldev, filename='[drive]:\new_location\model.mdf')
    Alter database model modify file
    (name=modellog, filename='[drive]:\new_location\modellog.ldf')
    
    
  14. Stop SQL Server and move the model files from old location to new location. tempdb will be re-created on the new specified location at every SQL restart so no need to move the files.
  15. Start SQL and make sure that the system database point to the right locations.
     

     
    select * from sys.master_files 
  16. [OPTIONAL] Restore msdb database. I personally encountered problems running Database mail after msdb restore on SQL 2008 R2. I ended up leaving the msdb orginal files and migrated all jobs by scripting, created msdb.dbo.cdc_jobs table (because cdc was in place as well) and re-configured database mail.
  17. Remove folders created at step II.4.
  18. Test some SQL users for connection.
  19. Linked Servers will not work because their credentials where encrypted with Service master key from originated server. In order to fix this you need to backup service master key from [Server A], give permission on the file to your user and copy to [Server B] to restore it.
     

     
    -- on [Server A]
    BACKUP SERVICE MASTER KEY TO FILE  ='[Drive]:\share\master_key' ENCRYPTION BY PASSWORD ='password'
    
    -- right click on file, properties, security, permissions: Give full permissions to your user. 
    
    -- on [Server B]
    RESTORE SERVICE MASTER KEY FROM FILE = '[Drive]:\master_keymaster_key' DECRYPTION BY PASSWORD = 'password' force
    
    
  20. Change the server name in system tables. @@servername variable will point to old server. It must be changed.
     

    -- check servername with ID = 0. The queries will return old server name [SERVER A]. [SERVER B] will not be found.  
    
    SELECT @@servername
    EXEC sp_helpserver 'SERVER B'
    EXEC sp_helpserver 'SERVER A'
    SELECT srvname FROM sysservers where srvid = 0
    
    -- DELETE old references to old servername.
    
    EXEC sp_droplinkedsrvlogin 'SERVER A', null
    EXEC sp_dropserver  'SERVER A'
    
    -- ADD new server name: [SERVER B]
    
    EXEC sp_addserver [SERVER B] , 'local' ,'duplicate_OK' 
    EXEC sp_addlinkedsrvlogin 'SERVER B', 'true'
    
    -- RESTART SQL. [SERVER B] will replace the old server name. 
    
    SELECT @@servername
    EXEC sp_helpserver 'SERVER B'
    SELECT srvname FROM sysservers where srvid = 0
    
    
  21. On versions below SQL 2008, you have to update also the originating server name from sysjobs as well. On higher versions, the reference in sysjobs is made by server_id which is always 0.
     

    SELECT * FROM msdb.dbo.sysjobs
    UPDATE msdb.dbo.sysjobs SET originating_server = @@SERVERNAME WHERE originating_server <> @@SERVERNAME
    
    

    III. Remaining Steps to do on the current Production server – [Server A]

  22. Perform Full backups for all user databases. It is a good idea to use backup compression and backup verification (RESTORE VERIFYONLY)
  23. Perform Differential backups.
  24. Copy all backups on [SERVER B].
  25. Restore Full backups and differential backups on [SERVER B] with ‘NO RECOVERY’ option.
  26. Perform Transactional backups.
  27. Copy all tran backups on [SERVER B] and restore them using with ‘Recovery’ option.
  28. And a last step, after you restore all user databases, keep in mind to change databases owners to ‘sa’ user, otherwise you will receive errors of executing sys schema objects like these ones:

    The EXECUTE permission was denied on the object ‘’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

  29. use Database_Name
    EXEC sp_changedbowner 'sa'
    go
    

msdb.dbo.sp_readrequest;1 – long running process

When monitoring the new installed version of SQL 2008 R2 SP1, I have encountered that Database mail leaves a hung process few minutes. This doesn’t do any blockages or other trouble to your SQL installation, either that it is anoying to see over and over again this long running process. In order to get rid off it, just check the system parameter value for “DatabaseMailExeMinimumLifeTime” running the following query:

Use msdb
GO
exec sysmail_help_configure_sp 'DatabaseMailExeMinimumLifeTime'
GO

The default setting will be 600. You need to change it to a lower period.

Use msdb
GO
exec sysmail_configure_sp 'DatabaseMailExeMinimumLifeTime', 30
GO

That’s all!

 

Away from blogging

Since I received some emails from people wondering why I Didn’t post for some time…. may I excuse myself saying that from january 2012 a little boy is keeping me very busy and I really don’t have time for new posts. I am looking forward to begin writting again, and this will happen at the end of this year.

Thanks for your visits and comments. I really appreciate every one of them!

Table variables or Temporary tables for big tables?

Since Microsoft first introduced table variable in SQL Server, its usage became more and more popular among developers due to its well known benefits: less locking and logging ressources, limited transaction scope and reduced re-compilation when used inside stored procedures.

Considering Microsoft reference of FAQ about table variables, I centralized the main characteristics (advantages and disadvantages) of table variables:

  1. Table variables, such as local variables, have a well defined scope at the end of which they are automatically cleared.
  2. Table variables result in fewer recompilations of a stored procedure as compared to temporary tables.
  3. Transactions that involve table variables last only for the duration of an update on the table variable. Therefore, table variables require less locking and logging resources.
  4. Because table variables have limited scope and are not part of the persistent database, transaction rollbacks do not affect them.
  5. Table variables DO NOT reside only in memory. They are created in the tempdb database, since the table variable could hold more data than could fit into memory. Both table variables and temporary tables will be created and processed in memory if enough memory is available. However, they can both be flushed to disk at anytime.
  6. Non-Clustered indexes which are not Primary or Unique keys, cannot be created on table variables in order to optimize the query execution plan. Only Primary or Unique key can be added at the declare table statement.
  7. Table variables do not maintain statistics like temporary tables can. Therefore, for complex queries on large tables, the lack of statistics may prevent the optimizer to determine the best plan for a query, thus affecting the performance of that query.

There have been over time a lot of discussions whether table variables are better than temporary tables. I am not here to argue any of those, because the reasons are very different for everyone. In my personal view both are very good if used in the right type of queries. Both can perform very good. The problem is that it is hard to know what is the right type of query.

Some time ago, I had to test an SQL query that was developed initially to use table variables. The query used 1 table variable that was populated with data from a join of different standard tables. The result of the insert was small (few rows) but two of the tables involved in the join had 2 and 60 million rows.

This is a part of the query :

declare @temp table 
([...]
)
 INSERT INTO @temp 
        ( [...]
        )
        SELECT  [...] -- columns
        FROM    DWtable2
 		INNER JOIN DWtable DT on [...]
                INNER JOIN SQLTableSent  on [...] 
                INNER JOIN SQLTableCampaign  [...]
                INNER JOIN SQLTableTemplate on [...]
                INNER JOIN SQLTableCulture on [...]
        WHERE   [...] -- apply filters
        GROUP BY [...] 

The duration was extremely long. Following a personal guess, I changed the insert into a table variable (@temp) with an insert into a temporary table (#temp). The query performed 20 times faster, using temporary tables instead of table variables. So, basically with no other improvement, the single use of the temporary table did the trick.
I started to ask why this huge difference in duration.

At first impression:

– The lack of indexes and statistics on the table variable where immediatelly out of the question as it was not the case. An insert does not need any index or statistics on the table populated in order to behave better.
– The select part from the insert was well optimized, because the result would be returned in less than 1 sec.

Next, I started to analyze more deeply. The things that I compared in both cases where:

  • The output from “set statistics io on” when executing the query:
  • I noticed that the insert into table variable had many logical reads for the SQLtableSent object (1640612) and read-ahead reads (919353) compared to 0 for the same object when using temporary table. That table has 60,000,000 rows. It was clear for me that the execution plan was very different because the order of the tables processed and their values where different.

  • The output of “Include Actual Execution Plan” option when executing the query:
  • Comparing those two query execution plans, it hit me – the usage of table variable in modification statements (insert, update, delete) prevents the creation of parallel query processing. An online search confirmed my suspicions. It is written right here at Remarks section:

    “Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. ”

    What it means? During query optimization, SQL Server looks for queries that might benefit from parallel execution on computers that have more than one microprocessor. This is because SQL Server can perform a query in parallel by using several operating system threads and the operation can be completed quickly and efficiently.

    When table variable is used, the query will be processed serially. A serial execution plan, used by a nonparallel query, uses only one thread for its execution and the performance is IN THIS CASE, much worse than in a parallel one. From my experience, I have seen cases when limited parallelism or none, can do wonders on other type of queries.

    In conclusion, it is important to test your query duration/execution plans for both scenarios (table variables and temporary tables), because in some cases non-parallel processing can be better than the parallel one. There are also cases when a non-parallel processing behaves exactly the same as a parallel one. It is a already a subject for another post and will not get into it now, but it is good to keep in mind when working with table variables:

    the key factors that can change the performance:

    1. number of rows manipulated and table sizes
    2. number of tables joined
    3. complexity of filtering columns
    4. complexity of query: group by, having clauses used

    Please check also other usefull links on the subjects:

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx
    http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

SQL Server Partitioning for large production tables

SQL has introduced from SQL 2005 versions, data partitioning. It is a new feature meant to improve the performance of SQL queries, data management, index management and storage management. The greatest benefit of Data partitioning is that you can have a highly available system while extracting, moving, removing information from tables with hundreds of millions of rows without creating blockages.

Due to a wrong estimation of number of rows or no estimation at all when creating a table, the Database Administrator can be confronted at some point with a big problem: the table has too many rows and indexing doesn’t help anymore to retrieve the data in reasonable time accepted by the whole IT structure. Querying very large tables start to cause big performance issues as a direct efect of the big I/O put each time to extract the data. You can be in the situation that old data can’t be even removed from that table without generating long time locks on the whole table.

Also, If you migrated from SQL 2000 system to SQL2005, it is time to consider partitioning for your biggest tables.

So let’s get into the facts and add partitions to a new table that will replace the un-partitioned one. Of course, I don’t suggest that this can be done easy and with no downtime for the Production environment, but with good planning ahead, it is possible and worth it. I had to deal with a table that had over 150 milllions of rows, measuring 60 GB in data+index size. The actual downtime was 1 hour. Because the physical storage of a table is set at create table statement, you can not add partitioning without re-creating the table. The downtime I was speaking about, refers to the duration of moving the data from the old un-partitioned table to the new partitioned table.

Pre-Requistites

For the demonstration purpose, the table in question will have the following structure:

1. Create the table that does not have any partitions

CREATE TABLE [dbo].[Table_without_partitions](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](255) NULL,
	[CreatedDate] [datetime] NOT NULL,
	 CONSTRAINT [PK_Table_without_partitions] PRIMARY KEY CLUSTERED 
([Id] ASC) 
)

2. Populate with values – Insert rows for 4 days

INSERT INTO [DB_Test].[dbo].[Table_without_partitions] ([Username],[CreatedDate])
VALUES      ('USER 1',GETDATE ())
GO 20 

INSERT INTO [DB_Test].[dbo].[Table_without_partitions] ([Username],[CreatedDate])
VALUES      ('USER 2',GETDATE ()+1)
GO 20 

INSERT INTO [DB_Test].[dbo].[Table_without_partitions] ([Username],[CreatedDate])
VALUES      ('USER 3',GETDATE ()+2)
GO 20

INSERT INTO [DB_Test].[dbo].[Table_without_partitions] ([Username],[CreatedDate])
VALUES      ('USER 4',GETDATE ()+3)
GO 20

You need to select a partitioning column that would serve as the limiting interval for a certain partition. This would be the most used column as “filter” in your queries and the most logical to separate various portions of data. In my scenario, [CreatedDate] column will be the partitioning column. Partitioning column can be also any other data type. For example price for a product can be a partitioning column. You will have in that case different partitions for products with the price from: 1 to 1000, 1000 to 2000, 2000 to 3000 etc.

Create the partition function and the partition schema

Method 1:

First I have to create the partition Function. It defines the method SQL Server will use on HOW to split the data inside the table.
In this method I will create a Partition Function that will contain all 4 boundaries, meaning 4 partitions for the 4 days inside our table.

a) Find out what will be the function boundaries:

 select distinct convert(varchar, CreatedDate, 112) from table_without_partitions

------------------------------
20111129
20111130
20111201
20111202

b) Create the Partition Function that will contain all partition boundaries:


CREATE PARTITION FUNCTION [Table_with_partitions_PartFunc](bigint) AS RANGE LEFT FOR VALUES 
(20111129,20111130,20111201,20111202)
GO

c) Create a partition scheme that specifies a ‘NEXT USED’ filegroup:

The partition schema is the second step in preparing the new table for partitioning because it will let SQL Server know WHERE you want to partition the data. You can select a different filegroup from the PRIMARY one. The big improvement will be in the SPEED of data access. To achieve this, you can add more filegroups to the database that will contain secondary data files (.ndf) located on different disks than the primary data file (.mdf) – possibly much faster. Once the filegroups are created these can be assigned to different partition boundaries, considering the most accessed partitions.
In my example though, I will use the default filegroup for all boundaries: PRIMARY.

You will notice that there are 5 filegroups specified instead of 4 – the number of partitions. This is because I specified the next file group that will be used by the next partition added.

CREATE PARTITION SCHEME [Table_with_partitions_PartScheme] 
AS PARTITION [Table_with_partitions_PartFunc] TO ([PRIMARY], [PRIMARY], [PRIMARY],[PRIMARY],[PRIMARY])
GO

ALTER PARTITION SCHEME [Table_with_partitions_PartScheme] 
              NEXT USED [PRIMARY]

Method 2:

Create the Partition Function for a single boundry – the lowest, and then alter the partition function in order to add the next needed boundaries, using an SQL script that will generate the partitions values based on your table values. I used this method while doing the partitioning in Production environment, as there where over 200 boundaries and it was not very fun to write down a list of boundaries for the partition function.

a) Find out the lowest boundry:

 

select min(convert(varchar, CreatedDate, 112)) from table_without_partitions

------------------------------
20111129
 

b) Create the partition function that will contain only the lowest boundry:

CREATE PARTITION FUNCTION [Table_with_partitions_PartFunc](bigint) AS RANGE LEFT FOR VALUES 
(20111129)
GO

c) Create the partiton schema that specifies a ‘NEXT USED’ filegroup:

CREATE PARTITION SCHEME [Table_with_partitions_PartScheme] 
AS PARTITION [Table_with_partitions_PartFunc] TO ([PRIMARY], [PRIMARY])
GO

ALTER PARTITION SCHEME [Table_with_partitions_PartScheme] 
              NEXT USED [PRIMARY]

d) Alter the Partition Function and add the next needed boundaries:

The following script will generate and execute the SQL statements for the next 3 partition boundaries.

Declare @StartDate datetime
Declare @EndDate datetime
Declare @Script nvarchar (4000)
DECLARE @PartValue BIGINT

Set @StartDate = (select min(CreatedDate)+1 from table_without_partitions)
Set @EndDate = (SELECT max(CreatedDate) from table_without_partitions)

WHILE @StartDate <= @EndDate 
Begin	
		SET @PartValue = Convert(bigint,Convert(varchar(8),@StartDate,112))				
		Set @Script = 'ALTER PARTITION FUNCTION [Table_with_partitions_PartFunc] () split RANGE ('+ Convert(VARCHAR,@PartValue) +')
		ALTER PARTITION SCHEME [Table_with_partitions_PartScheme]
					  NEXT USED [PRIMARY] '
		print @Script   		   
		exec sp_executesql @statement = @Script   
		Set @StartDate = @StartDate +1   
End

/*Results:
ALTER PARTITION FUNCTION [Table_with_partitions_PartFunc] () split RANGE (20111130)
ALTER PARTITION SCHEME [Table_with_partitions_PartScheme] NEXT USED [PRIMARY] 

ALTER PARTITION FUNCTION [Table_with_partitions_PartFunc] () split RANGE (20111201)
ALTER PARTITION SCHEME [Table_with_partitions_PartScheme] NEXT USED [PRIMARY] 

ALTER PARTITION FUNCTION [Table_with_partitions_PartFunc] () split RANGE (20111202)
ALTER PARTITION SCHEME [Table_with_partitions_PartScheme] NEXT USED [PRIMARY] 
*/

In both methods you will end up having one partition function for all 4 boundry values and one partition schema created.

Partition a table

1. Create the new table that will replace the old one without partitions

The new table will be created ON the new partition schema, which will point to the partition function.
The table will have a new BIGINT computed column named [Partition_column] - based on the existing [CreatedDate].

CREATE TABLE [dbo].[Table_with_partitions](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Username] [varchar](255) NULL,
	[CreatedDate] [datetime] NOT NULL,
	[Partition_column]  AS (CONVERT([bigint],CONVERT([varchar],[CreatedDate],(112)),0)) PERSISTED,
 CONSTRAINT [PK_Table_with_partitions] PRIMARY KEY CLUSTERED  ([Id] ASC, [Partition_column] ASC)
 ) ON Table_with_partitions_PartScheme ([Partition_column])

Go to table properties and check that the table is partitioned:

Partitioned table

Partitioned table

Migrate rows from the un-partitioned table to the partitioned table

1. Check the distribution of rows per each partition from the [Table_with_partitions]

SELECT partition_number, sum(rows) FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('Table_with_partitions') group by partition_number order by partition_number

partition_number SUM_ROWS
---------------- --------------------
1                0
2                0
3                0
4                0
5                0

2. Insert all the rows from [Table_without_partitions] to [Table_with_partitions]

This can be done by various methods:

  • SQL Server Export/Import Wizard - you may need to check the "Enable Identity Insert" option from "Edit Mappings" window.
  • SQL statement: Insert into [...] Select from [...]
    
    SET IDENTITY_INSERT [Table_with_partitions] On
    GO
    insert into [Table_with_partitions] ([Id],[Username],[CreatedDate])
    select [Id], [Username], [CreatedDate]
    from [Table_without_partitions] 
    SET IDENTITY_INSERT [Table_with_partitions] Off
    
  • BCP utility to export and BULK INSERT to import. I noticed that BULK INSERT is faster than BCP utility in import operations.
    EXEC master..xp_cmdshell 'BCP "SELECT [Id], [Username], [CreatedDate], ''1111'' as [PARTITION_COLUMN] FROM [DATABASE].[dbo].[Table_without_partitions]" queryout D:\BCP_export_file.TXT  -w -t\t -r\n  -SSERVERNAME -T'
    
    BULK INSERT [DATABASE].[dbo].Table_with_partitions FROM 'D:\BCP_export_file.txt' WITH (KEEPIDENTITY, FIELDTERMINATOR = '\t', ROWTERMINATOR ='\n') 
    
    

You are free to select the one that suits you best considering the amount of rows needed to be migrated. From my experience first two methods are efficient while handling up to 5-10 millions of rows. When we are speaking about tens or hundreds of millions I strongly suggest you use the BCP method. It is the fastest and the safest for your SQL Server because it doesn't put any locks.

3. Check again the distribution of rows per each partition from the [Table_with_partitions]

SELECT partition_number, sum(rows) FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('Table_with_partitions') group by partition_number order by partition_number

partition_number SUM_ROWS
---------------- --------------------
1                20
2                20
3                20
4                20
5                0

Now, all rows have been distributed per each created partition and the 5'th one is empty waiting to be populated with the next partition data.

Deploy in Production

1. Add the next partition

You should create a SQL Job that will run daily and add the partition for the next day, so that the rows inserted for 2011/12/03 will be assigned to the right partition. Of course you can create more partitions ahead and schedule the job to run weekly or monthly.

ALTER PARTITION FUNCTION [Table_with_partitions_PartFunc] () split RANGE (20111203)
ALTER PARTITION SCHEME [Table_with_partitions_PartScheme]  NEXT USED [PRIMARY]

2. Testing

Insert 10 rows for the next day into the new partitioned table [Table_with_partitions].

INSERT INTO [DB_Test].[dbo].[Table_with_partitions] ([Username],[CreatedDate])
VALUES      ('USER 5',GETDATE ()+4)
GO 10

3. Checking

The new inserted 10 rows are assigned to the 5'th partition.

SELECT partition_number, sum(rows) FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('Table_with_partitions') group by partition_number order by partition_number

partition_number sum_rows
---------------- --------------------
1                20
2                20
3                20
4                20
5                10
6                0

That's everything you have to do in order to get rid off the old un-partitioned table.

[UPDATE....]

A small update is in place here, because I missed one detail about creating clustered indexes on partitioned tables.

Create Indexes on partitioned table

Creating Primary Key/Unique Key on partitioned table

When speaking about clustered indexes, I am referring to either the Primary keys or the Unique keys. For a partitioned table the basic behavior is different from a non-partitoned table. Unlike the non-partitioned table that can have a primary key composed of one column that will uniquely identify a certain row in the table, the partitioned table Primary Key must contain the partition column as well which would be a subset of the index key. The default behavior of clustered indexes built on partitioned tables is to align the index with the partitioned table on the same scheme.

That is the reason the Primary Key from my example above is composed of [Id] and [Partition_column]. It ensures that the Index will also be partitioned.

Whereas this is available for Clustered indexes, Non-clustered indexes can be PARTITIONED or NON-PARTITIONED indexes. You define this by specifying the ON clause when creating the index. If you don't specify anything, by default it will be created as partitioned index on the partitioning scheme. To create an non-partitioned index you need to specify the ON PRIMARY clause instead.

I found a very good article written by Michelle Ufford, and she explains very nice the performance impact of having both partitioned and non-partitioned indexes.

Creating Foreign Key referencing a partitioned table

Since the Primary Key on a partitioned table contains also the partionining column, when you try to create a foreign key that will reference a column from the partitioned table that is part of the Primary Key (other than partitoning column) you will receive the below error:

There are no primary or candidate keys in the referenced table ' ' that match the referencing column list in the foreign key ' '.

To overcome this issue, you will need first to create a Unique Non-Clustered Index on the referenced column from the partitioned table. A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

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.