Category Archives: T-SQL

SQL Server thread scheduling cycle

Here is a simple diagram illustrating how SQL Server thread scheduling works.

SQL Server Thread cycle

Whenever a new thread arrives it got a slice of CPU. This is shown as Running.

When the thread has to wait for a resource it moves to Suspended list.

When the thread in the Suspended list is notified for the available resources, the thread moves to Runnable queue.

Meanwhile the next thread in Runnable queue will move to Running for it’s share of CPU.

This cycle goes on till the thread finishes the work.

The time taken by a thread between leaving the Running state and again enters Running state is called Wait Time.

The time taken by a thread between leaving the Suspended list and entering the Running state (i.e. time spend in Runnable queue) is called Signal Wait Time.

You can see these states when you query the DMV sys.dm_exec_cached_plans on status column or executing  sp_who

The Wait time is a very important factor in SQL Server troubleshooting. You can read more about in SQL Server 2005 Performance tuning using Waits and Queues.

Leave a comment

August 1, 2014 · 4:03 PM

Transact-SQL: Language of the Year 2013

Disclaimer: The information is based on internet statistics (and not compiled by me! Yes I’m not guilty!)

TIOBE programming community index is a measure of popularity of Turing complete languages based on search engine results. This index is not about the best programming language but about popular programming languages. TIOBE index selected Transact-SQL as the Language of the Year in 2013. Also T-SQL has been ranked 10th in July 2014 TIOBE index. Which language has got first Rank? It’s C (as you may guess). It is Interesting to note that our good-old Visual Basic (not the .NET flavour) is still popular with 6th rank and C# is just behind with the 7th rank.

Go and find yourself where your favourite languages fall in this popularity list. The definition of TIOBE index can be found here.

P.S: TIOBE stands for ‘The Importance Of Being Earnest’. This is also the name of a play written by Oscar Wilde.

Leave a comment

Filed under Fun, T-SQL

A note on behavioral change of IDENTITY property in SQL Server 2012

There can be gaps in the auto incrementing values implemented using IDENTITY property in SQL Server. In most practical situations this column is used as a surrogate key and not visible to the user. However in some business scenarios, the auto generated values have to be made visible to the users. This blog post is relevant to the latter case.

The implementation of IDENTITY property has been changed in SQL Server 2012 compared to the prior versions. In SQL Server 2012, the IDENTITY property is implemented using the new SEQUENCE object. By default the SEQUENCE object has a mechanism called caching, in which consecutive values are kept in memory for further use based on a threshold value. For example, when you insert first row and the current sequence value is 1, SQL Server may keep the next 1000 consecutive values in memory and keep the maximum value (i.e. 1000) in metadata. Once the incrementing value reaches 1000, the cache will create for numbers from 1001 to 2000 (note that these numbers are arbitrary). This is a performance improvement mechanism where the previous SQL Server versions keep the last value in transaction log.

The problem arises when there is a database failure or server restarts. Since the server is keeping only the maximum value, server will create a cache from this value. For example, you have inserted two rows and the current value for the auto increment column is 2. Now the server is restarted. You inserted the next row. Now the value of auto increment column will be 1001. This abrupt jump is not acceptable in some scenarios.

Microsoft has commented on this as ‘by design’ and suggested two workarounds.

  1. Create a SEQUENCE object in place of IDENTITY column using NO CACHE option
  2. Use trace flag 272 to force to use previous version’s IDENTITY property semantics

You can read more on this herehere, and here.

 

Leave a comment

Filed under T-SQL