ASP.Net Site Performance: Speeding up Database Access

ASP.NET Site Performance Secrets

Simple and proven techniques to quickly speed up your ASP.NET website

• Speed up your ASP.NET website by identifying performance bottlenecks that hold back your site’s performance and fixing them
• Tips and tricks for writing faster code and pinpointing those areas in the code that matter most, thus saving time and energy
• Drastically reduce page load times
• Configure and improve compression – the single most important way to improve your site’s performance
• Written in a simple problem-solving manner – with a practical hands-on approach and just the right amount of theory you need to make sense of it all.

The reader can benefit from the previous articles on Pinpointing bottlenecks for better Database Access in ASP.Net and Fixing bottlenecks for better Database Access in ASP.Net.

Locking

In this section, you’ll see how to determine which queries are involved in excessive locking delays, and how to prevent those delays from happening.

Gathering detailed locking information

You can find out which queries are involved in excessive locking delays by tracing the event “Blocked process report” in SQL Server Profiler.

This event fires when the lock wait time for a query exceeds the “blocked process threshold”. To set this threshold to, for example, 30 seconds, run the following lines in a query window in SSMS (locking.sql in the downloaded code bundle):

DBCC TRACEON(1222,-1)

Then, start the trace in Profiler:

1. Start SQL Profiler. Click on Start | Programs | Microsoft SQL Server 2008 | Performance Tools | SQL Server Profiler.
2. In SQL Profiler, click on File | New Trace.
3. Click on the Events Selection tab.
4. Select Show all events checkbox to see all events. Also select Show all columns to see all the data columns.
5. In the main window, expand Errors and Warnings and select the Blocked process report event. Make sure the checkbox in the TextData column is checked—scroll horizontally if needed to find it.
6. If you need to investigate deadlocks, also expand Locks and select the Deadlock graph event. To get additional information about deadlocks, have SQL Server write information about each deadlock event to its error log, by executing the following from an SSMS query window:

   ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK
     IMMEDIATE;
   ALTER DATABASE mydatabase SET READ_COMMITTED_SNAPSHOT ON;
   ALTER DATABASE mydatabase SET MULTI_USER;

7. Uncheck all the other events, unless you are interested in them.
8. Click on Run to start the trace.
9. Save the template, so that you don’t have to recreate it the next time. Click on File | Save As | Trace Template. Fill in a descriptive name and click on OK. Next time you create a new trace by clicking on File | New Trace, you can retrieve the template from the Use the template drop-down.
10. Once you have captured a representative sample, click File | Save to save the trace to a trace file for later analysis. You can load a trace file by clicking on File | Open.

    

    (Move the mouse over the image to enlarge.)

When you click a Blocked process report event in Profiler, you’ll find information about the event in the lower pane, including the blocking query and the blocked query. You can get details about Deadlock graph events the same way.

To check the SQL Server error log for deadlock events:

1. In SSMS expand the database server, expand Management and expand SQL Server Logs. Then double-click on a log.

   

2. In the Log File Viewer, click on Search near the top of the window and search for “deadlock-list”. In the lines that chronologically come after the deadlock-list event, you’ll find much more information about the queries involved in the deadlock.

Reducing blocking

Now that you identified the queries involved in locking delays, it’s time to reduce those delays. The most effective way to do this is to reduce the length of time locks are held as follows:

• Optimize queries. The lesser time your queries take, the lesser time they hold locks.
• Use stored procedures rather than ad hoc queries. This reduces time spent compiling execution plans and time spent sending individual queries over the network.
• If you really have to use cursors, commit updates frequently. Cursor processing is much slower than set-based processing.
• Do not process lengthy operations while locks are held, such as sending e-mails. Do not wait for user input while keeping a transaction open. Instead, use optimistic locking, as described in:
  • Optimistic Locking in SQL Server using the ROWVERSION Data Type http://www.mssqltips.com/tip.asp?tip=1501

A second way to reduce lock wait times is to reduce the number of resources being locked:

• Do not put a clustered index on frequently updated columns. This requires a lock on both the clustered index and all non-clustered indexes, because their row locator contains the value you are updating.
• Consider including a column in a non-clustered index. This would prevent a query from having to read the table record, so it won’t block another query that needs to update an unrelated column in the same record.
• Consider row versioning. This SQL Server feature prevents queries that read a table row from blocking queries that update the same row and vice versa. Queries that need to update the same row still block each other.Read versioning works by storing rows in a temporary area (in tempdb) before they are updated, so that reading queries can access the stored version while the update is taking place. This does create an overhead in maintaining the row versions—test this solution before taking it live. Also, in case you set the isolation level of transactions, row versioning only works with the Read Committed isolation mode, which is the default isolation mode.To implement row versioning, set the READ_COMMITTED_SNAPSHOT option as shown in the following code (locking.sql in the downloaded code bundle). When doing this, you can have only one connection open—the one used to set the option. You can make that happen by switching the database to single user mode; warn your users first. Be careful when applying this to a production database, because your website won’t be able to connect to the database while you are carrying out this operation.

  select is_read_committed_snapshot_on
  from sys.databases
  where name='mydatabase'

  To check whether row versioning is in use for a database, run:

  SET LOCK_TIMEOUT 5000

  Finally, you can set a lock timeout. For example, to abort statements that have been waiting for over five seconds (or 5000 milliseconds), issue the following command:

  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
  BEGIN TRAN
  SELECT Title FROM dbo.Book
  UPDATE dbo.Book SET Author=’Charles Dickens’
  WHERE Title=’Oliver Twist’
  COMMIT

  Use 1 to wait indefinitely. Use 0 to not wait at all.

Reducing deadlocks

Deadlock is a situation where two transactions are waiting for each other to release a lock. In a typical case, transaction 1 has a lock on resource A and is trying to get a lock on resource B, while transaction 2 has a lock on resource B and is trying to get a lock on resource A. Neither transaction can now move forward, as shown below:

One way to reduce deadlocks is to reduce lock delays in general, as shown in the last section. That reduces the time window in which deadlocks can occur.

A second way is suggested by the diagram—always lock resources in the same order. If, as shown in the diagram, you get transaction 2 to lock the resources in the same order as transaction 1 (first A, then B), then transaction 2 won’t lock resource B before it starts waiting for resource A. Hence, it doesn’t block transaction 1.

Finally, watch out for deadlocks caused by the use of HOLDLOCK or Repeatable Read or Serializable Read isolation levels. Take for example the following code:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
  SELECT Title FROM dbo.Book WITH(UPDLOCK)
  UPDATE dbo.Book SET Author='Charles Dickens'
  WHERE Title='Oliver Twist'
COMMIT

Imagine two transactions running this code at the same time. Both acquire a Select lock on the rows in the Book table when they execute the SELECT. They hold onto the lock because of the Repeatable Read isolation level. Now, both try to acquire an Update lock on a row in the Book table to execute the UPDATE. Each transaction is now blocked by the Select lock the other transaction is still holding.

To prevent this from happening, use the UPDLOCK hint on the SELECT statement. This causes the SELECT to acquire an Update lock, so that only one transaction can execute the SELECT. The transaction that did get the lock can then execute its UPDATE and free the locks, after which the other transaction comes through. The code is as follows:

SELECT b.Title, a.AuthorName
FROM dbo.Book b JOIN dbo.Author a ON b.LeadAuthorId=a.Authorid
WHERE BookId=5