Some Transact SQL Queries to Analyze Stored Procedure Performance

1. Remove Gunk (Select into a smaller table)

-- This Moves a Production SQL Profiler Trace into a smaller table and removes a lot of gunk.

select TextData as TxtData, reads, writes, duration, CPU, spid, StartTime, Endtime
into Production_Smaller2
from prod_tracking0911
where TextData like '%exec%'

and EndTime <= '2007-09-11 15:10' - - Make sure you are getting exactly one hour
and StartTime > = '2007-09-11 14:10'
and TextData not like '%exec sp_reset_connection%'
and TextData not like '%get_perf_counters%'
and TextData not like '%sqlagent_log_jobhistory%'
and TextData not like '%sysjobsteps%'
and TextData not like '%sysjob%'
and TextData not like '%isolation level%'
 

2. Get Counts of SP Executions

select
SUBSTRING(textdata, CHARINDEX('x', textdata) + 3, 30) as storedProc,
count(SUBSTRING(textdata, CHARINDEX('x', textdata) + 3, 30)) as [count]
from Production_Smaller2
where (LEFT(Convert(Varchar(10),textData),10) like '%exec%'
or LEFT(Convert(Varchar(100),textData),100) like '%declare%')
and
textdata NOT like '%jobstep%'
and textdata like '%stm%'

--and LEFT(Convert(Varchar(20),textData),20) not like '%rpt%'
group by SUBSTRING(textdata, CHARINDEX('x', textdata) + 3, 30)
order by [count] desc, SUBSTRING(textdata, CHARINDEX('x', textdata) + 3, 30)

3. Get Longest Running SP's

select top 50

LEFT(Convert(Varchar(100),textData),100) as storedProc,

duration,

duration/1000000 as durationInSec,

duration/1000000/60 as durationInMinutes,

StartTime, Endtime, reads, writes, CPU, spid

from Production_Smaller2

where (LEFT(Convert(Varchar(20),textData),20) like '%exec%'

or LEFT(Convert(Varchar(20),textData),20) like '%declare%')

and LEFT(Convert(Varchar(20),textData),20) not like '%rpt%'

order by duration desc, LEFT(Convert(Varchar(20),textData),20)

3. Longest Running SP's

select

LEFT(Convert(Varchar(100),textData),100) as storedProc,

duration,

duration/1000000 as durationInSec,

duration/1000000/60 as durationInMinutes,

StartTime, Endtime, reads, writes, CPU, spid

from Production_Smaller2

where textdata like '%stm.fulSetTicketWorkItem%'

order by duration desc, LEFT(Convert(Varchar(20),textData),20)

4. Breakdown of fulSetTicketWorkItem (Our most Frequently called sproc)

select

--LEFT(Convert(Varchar(800),textData),800) as storedProc,

SUBSTRING(textdata, 26 , 9) as ticketWorkItemId,

-- SUBSTRING(stringToSearch, StartingPos , #CharsToRight)

SUBSTRING(textdata, CHARINDEX('@p_ticketId', textdata) +14 , 5) as ticketId,

SUBSTRING(textdata, CHARINDEX('@p_WorkItemId', textdata) +14 , 6) as WorkItemId,

SUBSTRING(textdata, CHARINDEX('clinician', textdata) +11 , 20) as clinician,

SUBSTRING(textdata, CHARINDEX('@p_performedDate', textdata) +18 , 11) as performedDate,

SUBSTRING(textdata, CHARINDEX('@p_reportedQty', textdata) +15 , 1) as reportedQty,

SUBSTRING(textdata, CHARINDEX('@p_adjustedQty', textdata) +15 , 3) as adjustedQty,

SUBSTRING(textdata, CHARINDEX('@p_serviceQty', textdata) +14 , 2) as serviceQty,

SUBSTRING(textdata, CHARINDEX('@p_adjServiceQty', textdata) +17 , 2) as adjServiceQty,

SUBSTRING(textdata, CHARINDEX('@p_explanation', textdata) +16 , 10) as explanation,

SUBSTRING(textdata, CHARINDEX('@p_resolutionReasons', textdata) +22 , 1) as resolutionReasons,

SUBSTRING(textdata, CHARINDEX('@p_isBillable', textdata) +14 , 1) as isBillable,

SUBSTRING(textdata, CHARINDEX('@p_isComplete', textdata) +14 , 1) as isComplete,

SUBSTRING(textdata, CHARINDEX('@p_modified', textdata) +13 , 19) as modified,

SUBSTRING(textdata, CHARINDEX('@p_modifiedBy', textdata) +14 , 6) as modifiedBy,

duration

--duration/1000000 as durationInSec,

--duration/1000000/60 as durationInMinutes,

--StartTime, Endtime, reads, writes, CPU, spid

from Production_Smaller2

where textdata like '%stm.fulSetTicketWorkItem%'

order by performedDate desc, LEFT(Convert(Varchar(20),textData),20)

5. Finding TicketID's and WordOrder ID's, Date Ranges, etc. Used


 

-- Group by of Ticket ID's

select

SUBSTRING(textdata, CHARINDEX('@p_ticketId',textdata) +14 , 6) as ticketId,

Count(SUBSTRING(textdata, CHARINDEX('@p_ticketId',textdata) +14 , 6)) as fulSetTicketWorkItemCallsMade

from Production_Smaller2

where textdata like '%stm.fulSetTicketWorkItem%'

group by SUBSTRING(textdata, CHARINDEX('@p_ticketId', textdata) +14 , 6)

order by fulSetTicketWorkItemCallsMade desc

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Group by of Work Item ID's

select

SUBSTRING(textdata, CHARINDEX('@p_WorkItemId',textdata) +14 , 6) as WorkItemId,

Count(SUBSTRING(textdata, CHARINDEX('@p_WorkItemId',textdata) +14 , 6)) as fulSetTicketWorkItemCallsMade

from Production_Smaller2

where textdata like '%stm.fulSetTicketWorkItem%'

group by SUBSTRING(textdata, CHARINDEX('@p_WorkItemId',textdata) +14 , 6)

order by fulSetTicketWorkItemCallsMade desc

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Group by of Clinicians

select

SUBSTRING(textdata, CHARINDEX('clinician',textdata) +11 , 20) as clinician,

Count(SUBSTRING(textdata, CHARINDEX('clinician',textdata) +11 , 20)) as fulSetTicketWorkItemCallsMade

from Production_Smaller2

where textdata like '%stm.fulSetTicketWorkItem%'

group by SUBSTRING(textdata, CHARINDEX('clinician',textdata) +11 , 20)

order by fulSetTicketWorkItemCallsMade desc

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- Find Dates Performed

select

SUBSTRING(textdata, CHARINDEX('@p_performedDate', textdata) +18 , 11) as performedDate,

Count(SUBSTRING(textdata, CHARINDEX('@p_performedDate', textdata) +18 , 11)) as fulSetTicketWorkItemCallsMade

from Production_Smaller2

where textdata like '%stm.fulSetTicketWorkItem%'

group by SUBSTRING(textdata, CHARINDEX('@p_performedDate', textdata) +18 , 11)

order by performedDate desc

select distinct SUBSTRING(textdata, CHARINDEX('@p_performedDate', textdata) +18 , 11) as performedDate

from Production_Smaller2

where textdata like '%stm.fulSetTicketWorkItem%'

order by performedDate desc


 

 

 

 

SQL