Monthly Archives: April 2011

Transfagarashan – a magical road across the mountains

I will start the travelling category with a unique place located here where I live: in Romania. A beautiful and breath taking panorama situated at 2042 m altitude.

In order to reach this place you don’t have to be a trained mountain climber. Pack the bags, with a few friends, find a car and start the adventure. There is an actual road, a paved road that goes across the mountain peak from one side to another like a meander. Many tourists from all over the world come to see this place each year in the summer time. It is worth spending at least 3 days here and enjoying the sides.

It is called: Transfagarashan. It lies two historical romanian regions: Muntenia and Transilvania.

The road is not opened entirely to tourists all the year because of the bad weather and snow. This is reachable by car only in July – October period each year. At the top you will find the “Balea Lac” a lake inside the heart of the mountains with a splendid view over the Fagaras mountains – the highest mountain chain from Romania which is part of Meridional Carpatians mountains.

In winter time, basically between 1 november and 30 June the sector between km 104 (“Piscul Negru”) and 130 km (“cabana Balea Cascada”), is closed to road traffic and you can there only by cable car from “cabana Balea Cascada”. Near “Balea” Lake in the south -est there is another lake “Capra” to which one can get only following a mountain route.

Portion of the road closed in winter time


Glacial Lake "Capra" at 2230 m altitude

A view on the meander


Lake "Balea" at the top of "Transfagarashan" road - at 2000 m altitude





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,  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
    set @query = 'sp_spaceused ''[' + @schema + '].[' + @objectname + ']'''
    INSERT INTO #Table_Size EXEC(@query)
    FETCH NEXT FROM Object_Cursor 
    INTO   @schema, @objectname
    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
    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
    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
    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, AS CategoryName, 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 :



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]
SELECT gt.HostName, 
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') 

select * from #ddl_stmt

How to find out which Queries have non-reusable Query Plans?

Following a post on Stack Exchange asking how to check if a Query Plan of an SQL statement was compiled/re-compiled or cached for the first time, I was driven to write a more detailed explanation on this.

This query will update 1000 rows.

Update t1 set c1 = 10

If I execute it for the first time, SQL needs to create first a Query Plan for it, Compile it and store it in the SQL plan cache for the first time. This will be used for next executions. Plan re-use is desirable for OLTP workloads because re-creating the same plan (for similar or identical transactions) is a waste of CPU resources. What if, the Plan is not cached long enough and each time we execute the Update SQL creates the same Query Plan and compiles it over and over again? This is a performance problem that must be analysed. How we find these queries with many Query Plans compilations?

Profiler is my best friend, along with Performance Monitor when analyzing Poor Queries.

To find out how many compilations SQL Server is doing, you can monitor the SQLServer: SQL Statistics: SQL Compilations/Sec counter. This counter measures how many compilations are performed by SQL Server per second. To have a better undarstanding monitor also SQLServer: SQL Statistics: Batch requests/Sec and SQLServer: SQL ReCompilations/Sec counters.

Performance Monitor – SQL Compilations counters

A general acceptance is that if SQL Compilations/Sec counter measures over 100 compilations per second, then you may be experiencing  unnecessary compilation overhead.

Some of the reasons when Query Compilation/Re-compilation occurs:

– Changes in Object Schema

– Index rebuild, drop, create

– Update Statistics

– Stored Procedure alter – can’t use the old Query Plan.

As well Batch requests/Sec should be compared with the SQL Compilations/Sec. Say I have 500 Batches per sec and my Compilation rate is at 200/sec. It is close to 50% of all batches. So it is good to have a compilation rate as low as possible.

So, the rate only shows that the system is high on Compilations but how to know which queries are not cached?

Profiler can be used to find this out. I will explain here how to extract data from Profiler testing the Performance of a known SQL Statement but you will probably wish to find what query is getting compiled over and over again. The results can be overwhelming and hard to filter. I suggest you store your results into a table to a different database (I use a different SQL instance as well) than the one you are monitoring. It will allow you to select all the Events for a single SPID. The SPID will be easy to extract from Profiler.

Below are described the steps to catch the Poor Queries for which the Query Plan is Compiled more often that it needs to.


To check out the IO and Execution time enable statistics output:

set statistics time on
set statistics io on


Start a Profiler new trace and include following events:

  • Performance
    • Performance Statistics – Indicates that a compiled plan was cached for the first time, re-compiled or evicted from plan cache.
  • Stored Procedures
    • RPC:Completed
    • SP:CacheHit – Indicates when a procedure is found in cache.
    • SP:CacheInsert – Indicates when an item is inserted into procedure cache.
    • SP:CacheMiss – Indicates when a Stored Procedure is not found in the procedure cache.
    • SP:CacheRemove – Indicates when an item is removed from procedure cache.
    • SP:Recompile – Indicates that the Stored Procedure is recompiling.
  • TSQL
    • SQL:BacthCompleted
    • SQL:BatchStarting

Check to Show All Columns and select each column for Performance Statistics event. Click Run.


To achieve this post goal I removed all elements from plan cache for testing purposes (!!! never do this on Production !!!)

dbcc freeproccache
dbcc dropcleanbuffers

After Starting Profiler –> I will run below query:

set statistics time on
set statistics io on
Update t1 set c1 = 10


Let it run for some time (10 min) and then identify the SPID of the rows where Performance Statistics, SP:CacheMiss and SP:CacheInsert are displayed. Filter those SPID’s that contain all of three Events.

Now you identified basically those Processes SPID for which the Query Plan was not found in the cache, compiled and inserted in the cache.


Execution Output:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 90 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
Table 't1'. Scan count 1, logical reads 17738, physical reads 3, read-ahead reads 1000, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 2454 ms.

(1000 row(s) affected)

Profiler Output:

Profiler – Stored Procedure not in cache

First indication that the query is not cached is given by first occurence of Performance Statistics event class, that has a duration of 86 ms used to compile the statement within an adhoc query (EventSubClass: 2 – Batch:Plan). To see all types of EventSubClass descriptions, look here at msdn documentation. Afterwards, SQL checks whether the statement exists in procedure cache – SP:CacheMiss shows that it is not there. The statement is inserted into the procedure into procedure cache : SP:CacheInsert. The actual duration of the statement is 2544 ms. In SQL statistics we can see a difference between the time reported for compilation 90 ms (IO statistics) against 86 ms (Profiler)


I will execute again the Update after it was cached to see the difference.

Profiler – Stored Procedure found in cache

I no longer have Performance Statistics event class occurrence – so the compiled plan was found in the cache. Moreover I see a new event SP:CacheHit which confirms that the Query Optimizer found the Statement in procedure cache. It is obvious also the difference in Duration: 1223 ms. versus 2544 ms. Same reads and writes which is normal. CPU usage: 171 versus 234.

So the SQL physical resources are less used when query plan does not have to be compiled again. It is an important fact when designing web applications as well as writing SQL code. The DBA should be able to report excessive SQL Compilations and work with the DEV team to try to cache as much as possible.

Some good documents on this subject can be found also here:

Handling SQL Deadlocks – One Resource Conflict

From a DBA point of view, Deadlocks occurred in Production environment are probably the trickiest and time consuming problems to fix. Every time I came across this, it took me a while to understand where it is coming from, why, and how to avoid a particular Deadlock. This blog will go through some types of deadlocks and how to deal with them.

Here I will analyze a Deadlock caused by an After Insert trigger that acts like a constraint.

Usually people see the Deadlock error after the Deadlock already happened – thrown by client applications or DB Monitoring tools (ex: SQL Diagnostic Manager – Idera).

1. Provoke Deadlock:

a) Create a table with a Primary Key and a Trigger on it that check that same Security Number is not inserted for a new client.

CREATE TABLE [dbo].[Clients]
([clientid] [int] NOT NULL,[name] [nvarchar](50) NULL,[SecurityNR] [int] NULL,
([clientid] ASC)) ON [PRIMARY]
CREATE TRIGGER [dbo].[SecurityNR] ON [dbo].[Clients]
SET @SecNR  = (select SecurityNR from Inserted)
           FROM Clients c
           where c.SecurityNR = @SecNR having count (1) >1)
RAISERROR ('This client''s SecurityNR is assigned to another Client', 16, 1);

Let’s assume that there are many concurrent transactions running on the Server that insert new clients inside one transaction.

b) Start a Transaction and Insert one client and do not commit it.

INSERT INTO [test2].[dbo].[clients]       
VALUES (3,'Chris',12345680)

c) Start a second Transaction and Insert one client and do not commit it.

INSERT INTO [test2].[dbo].[clients]       
VALUES (4,'Lya',12345682)

At this point the second Transaction will be blocked by the first one.

d) Return to first Transaction and insert another client and commit transaction

INSERT INTO [test2].[dbo].[clients]       
VALUES (5,'Mary',12345681)

Now, the Deadlock will occur.

Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

2. Start Debugging:

Look at the error you caught. If you have a DB monitoring tool which captures deadlocks (I have worked with Idera and I recommend it), than you will probably have already some informations about the deadlocked process.

Alert displayed in IDERA:

A deadlock occurred on server [SERVER NAME]. 

The deadlock victim was spid 58 with application name '[APPLICATION NAME]' 
by user '[USER NAME]' on host '[MACHINE NAME]'.
Last command issued:
Proc [Database Id = 13 Object Id = 1157579162]

Whether you have or not a monitoring tool, next step is to activate the Deadlock traceflag and analyse its output from SQL Server Log after you encounter another deadlock:

-- SQL 2005 - 2008: 
DBCC TRACEON (1222, -1)

-- SQL 2000 :
DBCC TRACEON (1204, -1)

3. Collect Deadlock Trace Output

Here is the important info that must be extracted from the Log file.

A: deadlock-list

  1. deadlock victim=process1816da088

B: process-list

    1. process id = process1816da088 waitresource = KEY: 7:72057594040483840 (03000d8f0ecc) lockMode = S spid = 53 clientapp = Microsoft SQL Server Management Studio – Query loginname = irina-VAIOirina


    1. frame procname = test2.dbo.SecurityNR


IF EXISTS (SELECT count (1) FROM Clients c where c.SecurityNR = @SecNR
having count (1) >1)
    1. frame procname = adhoc


INSERT INTO [test2].[dbo].[clients]([clientid],[name],[SecurityNR])
    1. process id = process1816da508 waitresource = KEY: 7:72057594040483840 (0400b4b7d951) lockMode = S spid = 54 clientapp = Microsoft SQL Server Management Studio – Query loginname = irina-VAIOirina


    1. frame procname = test2.dbo.SecurityNR


IF EXISTS (SELECT count (1) FROM Clients c where c.SecurityNR = @SecNR
having count (1) >1)
    1. frame procname = adhoc


INSERT INTO [test2].[dbo].[clients]([clientid],[name],[SecurityNR])

C: resource-list

  1. keylock dbid = 7 objectname = test2.dbo.Clients indexname = PK_Clients mode = X
  2. owner id = process1816da508 mode = X
  3. waiter id= process1816da088 mode = S requestType = wait
  4. keylock dbid = 7objectname = test2.dbo.Clients indexname = PK_Clients mode = X
  5. owner id = process1816da088 mode = X
  6. waiter id = process1816da508 mode = S requestType = wait

3. Analyze Deadlock Trace Output

At A: deadlock-list I see the deadlocked process number – process1816da088.
Lower at B: process-list, I can see the SQL statements of the 2 processes caught in the deadlock:

    • process1816da088 – spid 53 – executed:


INSERT INTO [test2].[dbo].[clients]([clientid],[name],[SecurityNR]) values(4,'Lya',12345682)
IF EXISTS (SELECT count (1) FROM Clients c where c.SecurityNR = @SecNR having count (1) >1)
    • process1816da508 – spid 54 – executed:


INSERT INTO [test2].[dbo].[clients]([clientid],[name],[SecurityNR]) values(5,'Mary',12345681)
IF EXISTS (SELECT count (1) FROM Clients c where c.SecurityNR = @SecNR having count (1) >1)

Lower at C:resource-list:

process1816da508  has X (exclusive) lock on PK_Clients
process1816da088 waits for S (Shared) lock on PK_Clients
From these lines I can get the name of the first resource demanded by both processes: primary key PK_Clients from Clients table.

The second resource demanded by both processes seems to be also the primary key PK_Clients of Clients table. It can be extracted from the other 2 types of locks held:

process1816da088 has X (exclusive) lock on PK_Clients
process1816da508 waits for S (Shared) lock on PK_Clients

How can this be possible? In all documentation the deadlock is described as a conflict between at least 2 different resources.

Now, I have the same resource on which each one of the concurrent processes has X locks and claims S lock. The single logical explanation refers to the fact that SQL would put Row Locks (default Locking level) if the Clustered Index didn’t exist. In Row Lock mechanism transactions are not blocked because each one is modifying a different row from a table. The presence of PK makes a little different but not completely.

After each Insert SQL will have to make a Clustered Index Insert with a new value inserted on ClientID column. So it will request Exclusive Key Lock on  PK_Clients pointing a different row from the Clustered Index. This allows other concurrent transactions to update the Index. So, first conclusion is that the Deadlock problem happens inside the trigger.

After each Insert, the trigger is fired – the select from trigger will do an Index Scan on PK_Clients, in this way claiming Shared locks. How do I know all this? Looking at the execution Plan for Trigger code – I see a Clustered Index Scan to extract the SecurityNR value.

Shared locks read only commited data. It means that the second Transaction will wait after the first row has been commited from the First Transaction. But the first Transaction has one more Insert coming which will have the exact behavior – only now it will claim Shared Lock on PK_Clients that is already locked by second Transaction – inserted row from second Transaction.

This is how the Locking occured to my example:

Deadlock schema


4. Fix the Problem

There are more ways to fix this problem:

Avoid Index Scan on PK_Clients when trigger is fired –> Create Index on SecurityNR column which will avoid the PK_Clients index. Instead the trigger will perform an Index Seek on new NonClustered Index.

Putting No_Lock hints at the Select from trigger will fix the deadlock as well, but this is very risky because you can have dirty data reads and corrupt data.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE – option set per session/transaction. This is the highest Isolation Level and will prevent concurrent Inserts, causing a lot of blockages. More on this here.