In a job where you’re responsible for an application and how it performs, the worst thing that can happen is waking up to an email chain from management, detailing how the application is sluggish, unresponsive and in short, broken. You are to be blamed; finger pointing is running at an all-time high and you’ve just caught up to your 20th email.

Has this ever happened to you? It’s pretty much an engineer’s worst nightmare. Things happen unfortunately, and you need to track it down.

A situation like that arose recently, and it was narrowed down to it being database related. However, in an environment where there's thousands of stored procedures, countless amount of indexes and an application where there's millions of lines of code; identifying the underlining issue is somewhat of a daunting task.

I’ve done these level of diagnosis a handful of times, and normally I would load up SQL Server Profiler (on production) to try to capture the events that are happening real time. Well over a half-hour or an hours’ worth of data is then dumped into a table and quickly gleam over it, sorting it by duration. However, being that I only have a basic understanding of this, I needed more insight. So I came across a few posts which takes it further and really breaks down how to best go about that tool and really identify the meat of the data you've just traced:

Finding the Causes of Poor Performance in SQL Server, Part 1

Finding the Causes of Poor Performance in SQL Server, Part 2

Referencing Part 1, I had to add the “procedurename” column into that trace table since the trace program does not create it for you out of the gate.

The textdata column is an ntext type, so it needs to be converted to a varchar(max) column type in order for the update procedure to work.

UPDATE prod11192010_b
   SET ProcedureName = 
   LEFT(
      RIGHT(TextData, LEN(TextData) - CHARINDEX(' ',TextData, CHARINDEX('Exec',TextData))),
      CHARINDEX(' ', RIGHT(TextData, LEN(TextData) - CHARINDEX(' ',TextData, CHARINDEX('Exec',TextData))) + ' ')
   )
where TextData like '%exec%'

and the best part… good data.

select top(10) procedurename, sum(duration) as timeimpact, sum(reads) as IOImpact, sum(cpu) as cpuimpact,
count(*) as executioncount
from prod11192010_b
group by procedurename
order by timeimpact desc

to be continued.