July 2012. Volume 1 - Issue 4
By Tom Romeo

The goal of this article is to help developers design applications with consideration for the importance of proper database interaction for maximum performance.

To Profile or not to Profile

In our previous article we demonstrated how Disk or CPU can be the bottle neck if an index is not properly constructed or if a query is not formed well to use the desired index.

To recap, we illustrated how Select * is a bad practice. Not only did it bloat the result set, it caused the query plan to perform table scans instead of index seeks.

After we have tackled all of the items on our list of obvious offenders, that is queries that have a high value for max_elapsed_time as well as min_elapsed_time, and last_elapsed_time, we may now look deeper at queries that normally run reasonably fast, but occasionally run very slowly. These queries may slow down sometimes due to resource contention, or because locks and or deadlocks are occurring. "SQL Server Profiler" may become your knight in shining armor when this occurs.

SQL Server applies locks via complex algorithms and usually does a very good job of it. Occasionally, nolock or rowlock hints can be used to help, but these hints should be used sparingly, and rowlock, for example is not always acknowledged or obeyed. Actually nolock is among the group of settings that has been deprecated.

Typically I set these SQL trace flags

• dbcc traceon (1224, -1) -- Disables Lock escalation unless memory pressure forces it
• dbcc traceon (1204, -1) -- Returns resources and types of locks
• dbcc traceon (1222, -1) -- Returns deadlock information

MSDN - DBCC Trace Flags

To guarantee that they are always set, I create a stored procedure in the master database that sets the flags and then use "exec sp_procoption", setting startup true. This way if SQL Server is restarted or if the server is rebooted, the flags are re-set.

MSDN - sp_Procoption - setting SQL startup processes

if you are a bit squeamish about using the SQL Server Profiler, you could try this approach to find out which objects are locking out others. If however you do not find satisfaction here, it is time to dig in.

MSDN - Determine Which Queries Are Holding Locks

Starting a SQL Profile Session

From your start menu where you would normally open SQL Management Studio, there is a Performance Tools menu. The SQL Server Profiler is listed under the Performance Tools menu.

Open the SQL Server Profiler.

If you will be performing a lot of SQL traces then making your own trace template will save you time.

From the File menu, create a new trace. After entering database connection credentials you will see a screen like the following:



Enter a trace name. Microsoft provides several templates with preselected options. If you wish to re-run your test over and over you could start with the TSQL_Replay template, but you may also need to have a good backup of your starting point so you can restore before running again.

Select the save to file check box and select a location for the file to be created. It really depends on how much data you will be collecting, but I usually set my maximum file size to 200MB and enable file rollover. Notice I selected a blank template because I have a specific set of data that I want to collect.



Click the Events Selection tab. Generally I select every error because if any errors are occurring within my application I would like to correct them.



I usually select every type of lock event as well. Notice when you select the deadlock graph event a new tab entitled "Events Extraction Settings" appears.



We will also collect:

• Stored Procedures
  • SP:Completed
  • SP:Starting
  • SP:StmtCompleted
  • SP:StmtStarting
• TSQL
  • SQL:BatchCompleted
  • SQL:BatchStarting
  • SQL:StmtCompleted
  • SQL:StmtStarting
• Performance
  • Show Plan All
  • Show Plan XML
• Transactions
  • SQL Transaction

On the events extraction settings you can select XML Showplan and Deadlock XML. It is best to capture each deadlock and each query plan into a separate file because it is easier to separate each plan as it is opened via SQL Management Studio and also, if the plan file is very large it can cause an out of memory exception in SQL Management Studio.

Please note that many SQL plans will be generated so it is best to put them in their own directory. Or you can bypass SQL plans all together as you can always see the plan by running each suspect query on its own.



You can use the column filters to single out a single database or several. If there are many Databases on your SQL server it is best to single out your database as the SQL trace will be more verbose than you probably expect.

Run the trace until your error condition occurs.

To illustrate, I ran a test such that I inserted data from two different web browsers at the same time.

When I began the test the database was effectively empty as it was a fresh installation of the software. I opened two instances of the web client software, one chrome, and one Mozilla Fire Fox. I logged in to the system from each browser as two different users. I opened the section to add data of a particular type. I clicked save from both browsers as simultaneously as I could. After about 30 seconds a deadlock occurred. The deadlock graph shows the two transactions that were in contention and the deadlock victim. The trace also shows the time to lock acquisition and deadlock exception. By tracing the transaction identifiers from start to finish it is clear that each transaction executed six separate SQL batches, between the begin transaction and the commit / rollback transaction. This means an ADO.NET begin transaction was issued by each save button click event followed by six separate SQL batches before an ADO.NET commit / rollback transaction was issued. Each transaction consisted of eight distinct round trip calls to the database.

Having worked on systems that can accommodate hundreds of concurrent users, I was shocked to discover that I could cause such an error so easily with just two users.

All of this is pictured below.

This illustrates one of the important points made in our first article entitled, "A Guide to properly constructing database transactions".

DO NOT issue a begin transaction in a single batch and proceed to perform multiple additional SQL batches followed by a commit at the end. This will impose unnecessarily long locks causing others to wait for database resources. For example DO NOT DO THIS:

1. Open DB Connection
2. Execute ADO.NET "Begin Transaction"
3. Select * from tblFooBar"
4. Execute "Update tblFoo"
5. Execute "Update tblBar"
6. Execute "Delete tblFooAgain where ..."
7. Execute ADO.NET "Commit Transaction"
8. Close DB Connection

Each execution is a round trip to the database server. It is far better to send everything including and between the Open DB Connection and Close DB Connection in one round trip to the database server.

Deadlock Chain Discovered



Deadlock victim Discovered



Transaction ID's for the deadlocked pair with time to lock acquisition





Click here to see other articles in the series.