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