Partager via

An ASP.NET & SQL Server Performance Issue

  • Article

I have worked a few high profile performance related issues in the last six months which all resulted in the same root cause. Since most of my customers use SQL server and stored procedures to access data from their ASP.NET web sites I thought I would share with you my findings related to these cases and what you can do to prevent the issue from effecting you or your customers.

Typically with a web application if SQL server or some other external data source is causing a bottleneck these issues will come into Microsoft support as a web application/ASP.NET issue. One of the most common SQL slow downs is 'blocking'. Blocking is seen when locks are held to perform some action and other waiters or threads have to block or stop execution until that lock is released. For example a compiler lock is one that is held during the time a stored procedure is compiled. Holding locks for long periods of time or constantly needing to acquire a lock can severely effect throughput of both SQL and heavily dependent ASP.NET applications. With web applications most users use stored procedures as a method of interacting with the data layer. Not fully qualifying the stored procedure with the owner's name is one of the primary causes of compile locks contentions. This occurs when the following conditions are met:

- The user that runs the stored procedure is not the owner of the procedure.
- The stored procedure name is not fully qualified with the object owner's name.

For example, if user "dbo" owns object dbo.mystoredproc, and another user "Harry" runs this stored procedure with the command "exec mystoredproc," the initial cache lookup by object name fails because the object is not owner-qualified. (It is not yet known whether another stored procedure named Harry.mystoredproc exists, so SQL cannot be sure that the cached plan for dbo.mystoredproc is the right one to execute.) SQL Server then acquires an exclusive compile lock on the procedure and makes preparations to compile the procedure, including resolving the object name to an object ID. Before it compiles the plan, SQL Server uses this object ID to perform a more precise search of the procedure cache and is able to locate a previously compiled plan even without the owner qualification. For more information, see the following article: 263889 SQL blocking due to COMPILE locks ( https://support.microsoft.com/?id=263889 )

ASP.NET SQL Session State too hit this issue and needed to be corrected ( https://support.microsoft.com/?id=843400 ). Anyone running SQL Server Session State needs to install this fix. The fix involved changing our const strings that represented the various stored procs by prepending them with "dbo.". It was a very simple and non destructive change that many of our large customers have implemented with no side effect.

If you suspect you or your customer may be running into this issue or you want to determine if they are at risk and you have a dump of the process that is accessing the SQL server run the following command to dump out each commandString in each SQLCommand object.

Debugger Command: .foreach ( obj {!dumpheap -type System.Data.SqlClient.SqlCommand -short} ) {!duclean c+ poi( obj + 10) }

To run the command above you will need to load sos.dll and mdacexts.dll (not sure if this is public but you can substitute "!duclean" for "du" and it should work).

By analyzing the stored procedure names you can determine if you might be hitting this issue.

Here is a sample of what I found yesterday on one of my customers dump files:

0:000> .foreach ( obj {!dumpheap -type System.Data.SqlClient.SqlCommand -short} ) {!duclean c+ poi( obj + 10) }

BCC_GetSingle_SuperUsers
BCC_GetSingle_CurrentUserInput
BCC_GetSingle_Records
BCC_GetAll_Supplement
BCC_GetAllInvestigatorApprovers
BCC_GetAllCustomerApprovers
BCC_GetAllPrincipalInvestigators
BCC_GetAllProjectInitiators
BCC_GetAll_ResponsibleCostCenter
BCC_GetAll_ResponsibleCCGroupFromCC
BCC_GetAll_Customers
BCC_GetAll_CustomersWorkUnit
BCC_GetAllSysCreatedBy
BCC_GetUser_FilterCriteria
BCC_Add_FilterCriteria
BCC_Count_InvestigatorApprovers
BCC_Count_CustomerApprovers
BCC_Add_AttachedFiles

Once they prepend these stored procedure names (which you will find are typically implemented as const strings hopefully in a single or very few locations ) with "dbo." or another owner name the performance problem was no more.

Comments