Unexplained SQL Server Timeouts and Intermittent Blocking

Unexplained SQL Server Timeouts and Intermittent Blocking

by JBrooks 24. February 2009 10:00

We had a problem where our users would timeout for apparently no reason.  I monitored the SQL Server for a while and found that every once in a while there would be a lot of blocking going on.  So I need to find the cause of this and fix it.

If there was blocking going on, than there must have been exclusive locks somewhere in the chain of stored proc calls…. Right?

I walked thru the full list of stored procs that were called, and all of the subsequent stored procs, functions and views.  Sometimes this hierarchy was deep and even recursive.  

I was looking for any UPDATE or INSERT statements…. There weren’t any (except on temporary tables that only had the scope of the stored proc so they didn’t count.)

On further research I found the locking is caused by the following:

A.     If you use a SELECT INTO to create your temp table then SQL Sever places locks on system objects.  The following was in our getUserPrivileges proc:

                --get all permissions for the specified user
                select   permissionLocationId,
                    contactDescr as contactName,
                    l.locationId, description, siteNodeId, roleId
                into #tmpPLoc
                from vw_PermissionLocationUsers vplu
                    inner join vw_ContactAllTypes vcat on vplu.contactId = vcat.contactId
                    inner join Location l on vplu.locationId = l.locationId
                where  isSelected = 1 and
                    contactStatusId = 1 and
                    vplu.contactId = @contactId

The getUserPrivileges proc is called with every page request (it is in the base pages.)   It was not cached like you might expect.   It doesn’t look like it, but the SQL above references 23 tables in the FROM or JOIN clauses.  None of these table have the “with(nolock)” hint on it so it is taking longer than it should.   If I remove the WHERE clause to get an idea of the number of rows involved it returns 159,710 rows and takes 3 to 5 seconds to run (after hours with no one else on the server.)

So if this stored proc can only be run one-at-a-time because of the lock, and it is being called once per page, and it holds the locks on the system tables for the duration of the select and temp table creation, you can see how it might be affecting the performance of the whole application.

  The fix for this would be:
1.    Use session level caching so this is only called once per session.
2.    Replace the SELECT INTO with code that creates the table using standard Transact-SQL DDL statements, and then use INSERT INTO to populate the table.
3.    Put “with(nolock)”  on everything involved with this call.

B.    If the stored proc getUserPrivileges didn’t have enough problems for you, then let me add:  it probably gets recompiled on each call.   So SQL Server acquires a COMPILE lock on each call.  
The reason it gets recompiled is because the temp table gets created and then a lot of rows are deleted from it (if a @locationId or @permissionLocationId are passed in).    This will cause the stored proc to be recompiled on the SELECT that follows (yes, in the middle of running the stored proc.)     In other procs I’ve noticed a DECLARE CURSOR statement whose SELECT statement references a temporary table – this will force a recompile too.

For more info on recompilation see:

The fix for this would be:
1.    Again, hit this stored proc far fewer times by using caching.
2.    Have the @locationId or @permissionLocationId filtering applied in the WHERE clause while the table is being created.
3.    Replace the temp tables with table variables – they result in fewer recompilations.

If things don’t work like you expect them to then you can spend a lot of time staring at something without every figuring out what is wrong.