lockingperformancesql server
I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use
EXECUTE sp_configure 'blocked process threshold', 5
In combination with a server side trace to collect a Blocked Process Report.
I understand that this activity will incur a performance hit, I'd like to find some way of quantifying what this will be.
A LCK_M_S is a blocking problem not a deadlocking problem. So you are just waiting on something else. You need to determine what process is blocking you.
So, "it suddenly disappeared" because that block either completed or failed and rolled back. This is normal. What sounds abnormal from your notes or your perspective is the length of the block.
In addition to finding the blocking process you could also consider using a different transaction isolation level. For example, although not without their own problems, you might consider using "READ COMMITTED SNAPSHOT" or "SNAPSHOT" isolation levels for your report. This would allow it to read the data as of the start of the transaction and would avoid most blocking situations.
The latest version of the RML Utilities (09.04.0051 as at today) support Extended Events tracing, so maybe you could work with your DBAs to do some controlled tracing. A screenshot from the help file:
They do however have a point because the default trace template provided with RML includes statement-level events (SQL:StmtStarting, SQL:StmtCompleted, SP:StmtStarting, SP:StmtCompleted), ie every statement in every proc. This could easily bring a server to its knees under the wrong conditions, eg scalar function in a SELECT statement running across a big table.
Extended Events (XE) were a bit limited in versions prior to 2012, but it is more lightweight. You still need to be careful as SQL statement-level events will still incur overhead for very intensive workloads (sqlserver.sp_statement_starting, sqlserver.sp_statement_completed, sqlserver.sql_statement_starting, sqlserver.sql_statement_completed). Execution plan capture (sqlserver.query_post_execution_showplan) can also be intensive. Consider using the XETraceCaptureDefLightWeight.sql template which is provided and includes less events.
In summary I suggest moving to XE and using the lightweight XE definition provided with the latest versions of RML.
Best Answer
Minimal hit. The cost of evaluating the blocked process list is already there since this is how deadlocks are detected. The cost of generating and writing an event in the server side log is quite low and any server IO should be able to handle an extra write into LOG/*.trc every 5 seconds (At worst). But 5 is a quite low setting, you may be flooded by reports. Is your server server blocking so well behaved as to consider any 5 second block an incident to investigate?
Make sure you monitor the generated traces and make sure you don't cause an self-induced outage due to disk space exhaustion...