SQL Server audit and Lock request time out period exceeded. (.Net SqlClient Data Provider) error

Have you ever noticed "PREEMPTIVE_XE_CALLBACKEXECUTE" wait type on one of your SPID which is not letting you perform any operation at SQL Server instance???

I have seen this wait type  when using SQL Server auditing and it seems that transaction is stuck and blocking meta deta. The exclusive lock was placed on sysclsobj in master database

If you try to see corresponding OS thread(KPID), you would notice that thread  actually belongs to sql server agent task.

Below are some additional findings :

 SPID for server audit is blocking all other instance level operation
 Stopping and restarting sql server agent job does nothing
 "PREEMPTIVE_XE_CALLBACKEXECUTE" reflects that sql thread is waiting on OS level resource
 Eventually there is no wait resource

This is a bug in SQL Server 2008 R2 SP1 and is fixed in CU 6 : ): )
Puneet

Update statistics some key points!

As a DBA, we all know that update stats is one of the key, which let SQL optimizer to decide the best (best means fast :)) way to retrieve the query results.

Now we also know that the best practice is to not just let statistics get updated automatically but also to place some kind of manual plan.

Although, you can choose a best time to manually update the statistics on every server in your farm, but realistically, you don’t have control over the time of modification of data into the tables as its control by application/users/business.

So what you can do your best is to make sure that statistics are updated most of the time!

You can leverage "sys.dm_db_stats_properties" to monitor the stats on your objects, especially large objects.

For smaller to medium table, it’s not a big concern as auto stats with manual plans can take care of most of the situation but with Large size tables(millions of rows), it can be bit tricky!

I would not rely on default automatic update of statistics for large objects.

Tables with millions of rows mean, you have to wait longer before it reaches 20% of threshold value.

Thankfully, SQL Server 2008 R2 released trace flag 2371, which controls the update dynamically.

What this mean is larger the number of rows exposed to modification, smaller the threshold get set and quicker the automatic update can happen.

Having said all this, make sure you test this trace flag  properly for your application and then make a decision.