SQL blocking - Debug

Question

I have a c# application that uses a Sql Server database.

What I am experiencing is that as the size of the database has grown, randomly it will block. Since the database is on another system, and since this occurs randomly (2am, etc) I cannot login and run the reports to see the guilty code. I thought that on my development system, if I put the sql server in blocking, I could run the app in debug and pin point the culprit.

Is there a good way I can manually get the Sql database in blocking to do my debugging?

thanks in advance.

Answer 1

Since you may not be able to replicate blocking on your dev environment as easily as it tends to occur in prod, you could try creating a SQL Server Agent job in prod that executes sp_BlitzWho every 10-15 seconds and outputs the data to a table. This captures active session information to a table for later investigation, allowing you to see what's causing the blocking. Example, implying you have a database named DBATools in which sp_BlitzWho exists and in which you want to save the data:

WHILE 1=1
BEGIN  
EXEC DBATools..sp_BlitzWho 
@OutputDatabaseName = 'DBATools', 
@OutputSchemaName = 'dbo', 
@OutputTableName = 'BlitzWhoOutput';
WAITFOR DELAY '00:00:10';
END;

If the instance does not have SQL Server Agent available or enabled, you can use a startup stored procedure

Answer 2

Not really. You need to get more information on what happens on the actual system. You say "block", which in technical terms means that one process locks some resources, for instance a row or a table, and other processes are waiting to get access to that resource. But with your imprecise use of the term (it is not the database as such that blocks), I am not sure that blocking is what you are seeing.

Whatever, you need to get some monitoring in place. Your description of what's happening is quite vague, but I would suggest that you use my beta_lockinfo to collect information. When you run it, you get a snapshot of what is going on in the instance, including blocking.

Schedule it to run from SQL Server Agent every five minutes to run in archive mode. Set a retention time that is long enough so that you can access the data after a "blocking" incident.

Before you install it in production, play with it at home, so that you get an understanding of how it works and what you see.