Home     RSSRSS

Posts Tagged ‘Database Bottlenecks.Deadlocks’

Short note on using Indexes- MS SQL 2005

August 18, 2012 by kiranbadi1991 | 1 Comment | Filed in Database, Development, Environment, Performance Engineering, Testing

Most often whenever Performance Engineer detects some issues  with regard to response time, the first thing the application team thinks as a fix is to check and add some more indexes to the columns in the table.Adding indexes is low hanging fruit which every development team tries and should try  before reviewing their code base for fix.I certainly agree and go with most development team on this.However these concepts are now misused and now people often try to add indexes as band aid than do real time fix for their insufficient design. I recently had one such case, and to everyone’s surprise it failed  and they had rework on their queries.Don’t get me wrong, designs often get outdated with technologies.They don’t stay in pace with growth of technologies.Today I may use Model 2 MVC, but 3 years down the line, Model 2 MVC will be outdated.However these  should not impact low level basics used in the design.If I am storing zip codes, then probably I should be using char or int with limited bytes range depending on zip code format.This according to me is low level basics and the same applies to the basics about indexes.

So this post is all about indexes and my thoughts on it.

Indexes helps in reducing IO when we use it efficiently.Indexes work in the similar manner the way indexes in the book works.Lets say if we have book with around 1000 pages in it and you need to find a definition of some single word and you know for sure that definition exists somewhere in the book but not sure which page,how are you going to find out ?.Just go to the index page of the book and then search of the word what you require and then go to that page number.No need to flip over thousand pages to get the information.That’s the power of index when correctly implemented.Lets assume what happens when there is no index on any column involving the query, MSSQL database engine has to read each row of the table for getting the information and this results in the full table scan.So whenever you have large amount of rows and full table scan happens, the first thing that gets impacted is the response time of the application and to some extent resource usage of the database server.Your Database box is going to get very busy even to fetch 1 row of record.

Indexes are categorized as  clustered and non clustered Indexes.Clustered indexes are the one which often gives the better results and is default index type whenever we create the primary key constraint on the table in MSSQL Database.Clustered indexes are often created first than non clustered indexes since row locators of the non clustered indexes can be pointed toward the index keys of the clustered index.Clustered indexes also helps in sorting of the data and in cases where large range of rows needs to be retrieved.Non clustered index is useful when we are retrieving a very small number of rows.

Then there are also certain situations where in adding the indexes in fact degrade the performance of the application.Indexes often adds overhead costs in terms of more memory consumption and can also impact response time of the applications.The Insert/update/Delete  often gets longer whenever we have indexes on the tables where these operations takes place.Lets take earlier example, If I am deleting 25 pages from 1000 pages book, then the order of the all the pages changes and this change can lead to serious response time issues depending on the frequency of the change.So this is reason as why we should be cautious in using indexes for use cases where in we do high number of these DML operation.

Some of the main factors to be considered while designing indexes are,

  • Where clause and Joins to be used in queries:If the queries returns less rows, better the benefits we get due to index.We have to keep in mind the whole purpose of Where clause is to get exact required information from the table and if Where clause column is already indexed and the benefits due to the index almost gets doubled.
  • Column Data types : Columns with data type char,varchar,nchar etc of String family gains less due to indexes.Less the number of bytes they occupy,more the gain we can have due to indexes.
  • Columns which has duplicate data in large numbers are bad choice for indexes.Boolean columns where in we store the flag status with only 2 options Y and N.The reason these are bad is that it defeats the uniqueness of the Where clause if used.Column which has large number of unique values gains most due to the indexes.

Information about the indexes and their associated costs can also be found out using Data management View (DMV’s) of MS SQL Servers.The DMV sys.dm_db_index_operational stats can help us to get  low-level activity, such as locks and I/O, on an index that is in use. The DMV sys.dm_db_index_usage_stats can give us the  counts of the various index operations that have occurred to an index over time.

These are the key factors to be considered while designing the indexes on the table.Indexes often helps in fixing performance issues but if the design of the table or query is bad, then adding indexes to it may worsen the problem than resolving it.

Tags: , , , , ,

Deadlocks in MSSQL 2005 Explained

May 1, 2012 by kiranbadi1991 | Comments Off on Deadlocks in MSSQL 2005 Explained | Filed in Development, Environment, Performance Engineering, Testing

Have you ever seen the below message during your load testing assignment working in the Microsoft .Net world involving ASP.NET and MSSQL Server,

Msg 1205, Level 13, State 51, Line 1

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

If yes, then probably you need to read on this complete post because either you are testing with limited set of data or you are really seeing locking issues in your database,so I am hoping  that you will find it interesting to correlate deadlocks or locking contention issues with system performance or response time of the applications,

So let’s start with Microsoft definition of deadlocks which is excellent material to refer,

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. The following graph presents a high level view of a deadlock state where:

  • Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).
  • Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).

Diagram showing tasks in a deadlock state

Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

The SQL Server Database Engine automatically detects deadlock cycles within SQL Server. The Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

Deadlocks often add to the wait time stats and most of the time becomes a silent performance bottlenecks which are hard to identify and troubleshoot, I call them as silent bottlenecks because you never know from the front end or from your load testing scripts as why a simple form insert or search is taking so much time to retrieve a data in spite of giving sufficient filter parameters in the search.

There also exist performance counters which can help to identify whether deadlocks are happening during the test run assuming that you see slowness but not any kind of errors during your test execution.

Below are some of the counters I often see in case its required to monitor for locks,


  • Lock wait time (ms) – Total time spend to acquire a lock for all DB Transactions.
  • Lock waits/sec – Number of times user has to wait to acquire a lock. Ideally value of 0 is expected here.
  • Average Wait Time (ms) – Wait time each lock request as to wait. More than 500ms often indicates blocking issues.
  • Lock requests/sec – The number of lock request per sec. High value often indicates that lot of rows are being accessed. So this indicates the application is heavily dependent on DB health. There exists some optimization scope to reduce DB calls.
  • Lock Timeout/sec – Time out that occurs due to locking issues. Sometimes these timeouts manifests as application errors. If you see frequent timeouts in applications during load tests, then probably you need to monitor this counter.
  • Number of deadlocks/Sec: These are pure play deadlocks where in process with low priority is rolled back. Error message shown earlier belongs to this category of locks. If your application handles all types of exceptions, then probably you will not see the above message other than some custom error message saying something went wrong with the application.

The above counters are extremely helpful in identifying the performance issues with regard to Database. In case if you have access to application database, then probably you can also go ahead and fire the below queries in SSMS,





When you execute the above queries in SSMS, it will give you status whether trace 1204/1222 trace flags are on for your database, these are 2 trace flags which needs to turned on for identifying and fixing the root cause of deadlocks.

In order to enable these flags, we can fire the below queries in SSMS,

DBCC TRACEON (1204, -1);


DBCC TRACEON (1222, -1);

By turning on both the trace flags ,we get valuable information with regard to resources/spid which are locked and reasons/queries which are locking or creating deadlocks.

As per Microsoft SQL Team,the importance and difference between these flags are when deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

In addition to above methods, I feel SQL Profiler also contains events that can be used to identify deadlocks quite easily and in fact does show right away the sql queries involved in deadlocks.I vividly remember that SQL Server 2000 profiler had this capability to detect deadlocks.

Now the big question,are deadlocks high priority performance defects, I personally feel that deadlocks are not high priority defects unless they are not memory locking contention issues or thread contention issues on the smaller scale application.However they can have big impact if your application has large user base and your application makes lot of DB Calls.Deadlocks are often associated with high wait times which directly contributes to the high latency on the front end side of the application.

Tags: , , , ,