Sometimes you need to analyze how the queries are behaving and get an understanding of what queries is taking most of the CPU time. The key thought here is to slice the used CPU time per query with the current load. Note that this is not an exact science, but rather a qualified estimate of what is causing the current server load.
There are several ways of doing this, but I prefer to run a trace, parse the trace and import the result set into Excel for further analysis. A complicating factor is whether your load consist of ad-hoc SQL or stored procedures. If your system consists of the latter, then the analysis job is straight forward. However, if your system has ad-hoc sql, then you need to parse and cleanup each sql statement. Itzik Ben-Gan has made a great CLR function for this, but I won’t cover this scenario for now.
Now, simply run a trace where you collect RPC:Completed and SP:Completed. Trace it to file and ensure you collect enough data. You should make a note of the current system load and consider whether it’s a representative load compared with the rest of the day or week. I usually sample for 10 minutes and end up with a file between 100 and 300 Gb.
Note that this may put some extra load on your server, but I have never been able to document a noticeable performance hit while sampling. This may, however, vary from system to system, load to load.
Make sure that you collect the following counters: EventClass, DBName, AppName, NTUserName, CPU, Reads, Writes, Duration, ObjectName, TextData, StartTime and EndTime.
An important question is whether you want to factor in a cost when the reported CPU cost is 0. In my experience this is the right thing to do because you get nothing for free. If you have 1000 queries per second, each with a CPU cost of 0, the sum cost is _not_ zero.
Simply run the query below and copy the result into Excel for further drill down. – VERY EASY-
Note that you need to have the trace files located on a SQL instance.
|
DECLARE @FileName VARCHAR(255) SELECT @Filename = 'C:\baseline traces\18082010_1427.trc' SELECT DATEDIFF([ss], MIN([starttime]), MAX(starttime)) AS [Seconds], COUNT(*) AS [Total], COUNT(*) / DATEDIFF([ss], MIN(starttime), MAX(starttime)) AS s, MIN(starttime) AS [FROM], MAX(starttime) AS [TO] FROM :: FN_TRACE_GETTABLE(@Filename, DEFAULT) ;
WITH a AS ( SELECT CAST(COUNT(*) AS NUMERIC(38, 2)) / ( SELECT COUNT (*) FROM :: FN_TRACE_GETTABLE (@Filename, DEFAULT) ) * 100 AS '%Calls', COUNT(*) AS '#Calls', AVG(duration) * COUNT(*) AS [Weighed AVG Duration], CASE AVG(cpu) WHEN 0 THEN 0.5 * COUNT(*) ELSE AVG(cpu) * COUNT(*) END AS [Weighed AVG CPU], AVG(cpu) AS AvgCPU, MIN(cpu) AS MinCPU, MAX(cpu) AS MaxCPU, ISNULL(AVG(duration) / 1000, 0) AS AvgDurationMS, ISNULL(MIN(duration) / 1000, 0) AS MinDurationMS, ISNULL(MAX(duration) / 1000, 0) AS MaxDurationMS, AVG(READS) AS AvgReads, MAX(READS) AS MaxReads, AVG(writes) AS AvgWrites, MAX(writes) AS MaxWrites, ObjectName FROM :: FN_TRACE_GETTABLE(@Filename, DEFAULT) GROUP BY ObjectName) SELECT CAST([%Calls] AS NUMERIC(38, 2)) AS '%Calls', [#Calls], [Weighed AVG Duration], [Weighed AVG CPU], CAST([Weighed AVG CPU] / ( SELECT SUM (a . [Weighed AVG CPU]) AS [%Weighed AVG CPU] FROM a ) * 100 AS NUMERIC(38, 2)) AS [%Weighed AVG CPU], AvgCPU, MinCPU, MaxCPU, AvgDurationMS, MinDurationMS, MaxDurationMS, AvgReads, MaxReads, AvgWrites, MaxWrites, ObjectName FROM a ORDER BY [%Weighed AVG CPU] DESC |
No comments:
Post a Comment