Many a times a DBA wishes for the below information, during performance troubleshooting,

  1. Can I get to know what was being executed 30 minutes back.
  2. Can I get to know which were the most long running sessions, spid(s), during the last 30 minutes back.
  3. Can I get know on who was blocking whom during the last 30 minutes back.
  4. If blocking was present, did the blocking escalate to a chain reaction with multiple spid(s) being blocked out.
  5. If there was no blocking, was my SQL instance being pressurized due to particular wait types.
  6. Were any particular spid(s), running 30 minutes back, resource intensive with high reads and writes
  7. TEMPDB size has suddenly grown huge during the last 30 minutes. Though stabilized now, want to know which spid, from which application was the cause.
  8. etc.

It is not necessary that on a LIVE production SQL instance, a performance issue persists for a duration sufficient enough for the DBA to log on, run profiler or perfmon traces to trace out the root cause.

But on a LIVE production SQL instance, if a performance issue was logged as a complaint and the issue seems to rear up at anytime but for short bursts of duration, then it becomes imperative for the DBA to collect all facts possible by looking back in time. The facts collected helps the DBA along with other log entries and traces to arrive at the root cause of an issue.

Many a times DBA(s) would get request from users stating the DB application is running poorly. Sometimes the complaint starts pouring in and the DBA is left stunned, because nothing has changed on the server. A very stable system. By the time he logs in and starts checking, things get back to normal. Getting back to the users, even they confirm yes, things are ok now, but half an hour before the performance was really poor.

This is where session capture plays an important role in understanding what was being executed on the SQL instance 30 minutes back.

The below code running continuously [every 3 second gap] captures and stores session information on the SQL Server instance. Some of the most important information(s) that can be queried by the DBA are on

  1. “session start time”, “session end time”, “session text”,
  2. “login name”, “application name”,
  3. “wait type”, “wait duration”,
  4. “total reads”, “total writes”, “tempdb usage”,
  5. “blocking spid”, “blocking text”, etc.

Note: on a personal front, I recommend that the session information be stored for not more than 48 hours. Meaning along with the code to capture and store session, you also need a purge code, to delete information collected older than 48 hours. This is to manage the DB size, which hosts the table for storing the session capture data.



Vasudev Menon

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook