Analysis of SQL Server Performance Data

,

There have been many writeups about handling performance data from SQL Server on this website and in many other places. Here is a little twist with an implementable example of how to do this. First, let us define the situation:

Let us assume, we want to analyze 5 perfmon counters (I am not discussing

which counters are best for monitoring SQL Server performance in this writeup.

There are numerous lists on the web for that). For this demo I am randomly

picking five counters.

  • adql - Average Disk Queue Length

  • drt - Disk Read Time

  • dwt - Disk Write Time

  • pql - Processor Queue Length

  • pt - Processor Time

We want to monitor these 5 counters once a week on some predetermined day of the week, say for 12 hours. (Is Wednesday 6 am to 6 pm a nice day/time?)

At any given point in time, we want to see the average, minimum and maximum values of all these 5 counters for a specific day. A sample report is given below:

counter date avg min max

======= ====================== === === ===

adql 10/11/2006 12:24:31 PM 1.0 0.0 1.0

drt 10/11/2006 12:24:31 PM 1.0 0.0 1.0

dwt 10/11/2006 12:24:31 PM 1.0 1.0 1.0

pql 10/11/2006 12:24:31 PM 3.0 2.0 4.0

pt 10/11/2006 12:24:31 PM 10.0 1.0 100.0

We also want a report of the average values across a period of time, say for the last 10 collections. My sample report below shows for the last three periods, for example. A similar report for min and max is nothing but an extension of this.

counter P1 P2 P3

======= ==== === ===

drt 1.0 1.0 1.0

dwt 1.0 1.0 1.0

adql 1.0 1.0 1.0

pt 10.0 10.0 10.0

pql 3.0 3.0 3.0

Now with the requirements clear, let us see the steps to achieve this. This was tested on Windows XP Professional and SQL Server 2000

Step-1

Cut paste the following in a file called c:\whatever\whatever.html. Note that

you have to change \\YOURSERVERNAME to the name of your machine.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML>

<HEAD>

<META NAME="GENERATOR" Content="Microsoft System Monitor">

</HEAD><BODY>

<OBJECT ID="DISystemMonitor1" WIDTH="100%" HEIGHT="100%"

CLASSID="CLSID:C4D2D8E0-D1DD-11CE-940F-008029004347">

<PARAM NAME="_Version" VALUE="196611">

<PARAM NAME="LogName" VALUE="jk_1">

<PARAM NAME="Comment" VALUE="">

<PARAM NAME="LogType" VALUE="0">

<PARAM NAME="CurrentState" VALUE="0">

<PARAM NAME="RealTimeDataSource" VALUE="1">

<PARAM NAME="LogFileMaxSize" VALUE="-1">

<PARAM NAME="DataStoreAttributes" VALUE="33">

<PARAM NAME="LogFileBaseName" VALUE="jk_1">

<PARAM NAME="LogFileSerialNumber" VALUE="1">

<PARAM NAME="LogFileFolder" VALUE="C:\PerfLogs">

<PARAM NAME="Sql Log Base Name" VALUE="SQL:!jk_1">

<PARAM NAME="LogFileAutoFormat" VALUE="-1">

<PARAM NAME="LogFileType" VALUE="1">

<PARAM NAME="StartMode" VALUE="0">

<PARAM NAME="StopMode" VALUE="0">

<PARAM NAME="RestartMode" VALUE="0">

<PARAM NAME="LogFileName" VALUE="C:\whatever\whatever.tsv">

<PARAM NAME="EOFCommandFile" VALUE="">

<PARAM NAME="Counter00001.Path" VALUE="\\YOURSERVERNAME\PhysicalDisk(_Total)\% Disk Read Time">

<PARAM NAME="Counter00002.Path" VALUE="\\YOURSERVERNAME\PhysicalDisk(_Total)\% Disk Write Time">

<PARAM NAME="Counter00003.Path" VALUE="\\YOURSERVERNAME\PhysicalDisk(_Total)\Avg. Disk Read Queue Length">

<PARAM NAME="Counter00004.Path" VALUE="\\YOURSERVERNAME\Processor(_Total)\% Processor Time">

<PARAM NAME="Counter00005.Path" VALUE="\\YOURSERVERNAME\System\Processor Queue Length">

<PARAM NAME="CounterCount" VALUE="5">

<PARAM NAME="UpdateInterval" VALUE="15">

<PARAM NAME="SampleIntervalUnitType" VALUE="1">

<PARAM NAME="SampleIntervalValue" VALUE="15">

</OBJECT>

</BODY>

</HTML>

Step-2

Perform the following steps to schedule a perfmon log:

  1. Start -> Run

  2. Type perfmon

  3. Press Enter

  4. In the left tree pane of the perfmon window, open "Performance Logs and

    Alerts"

  5. Right click on "Counter Logs" and click on "New Log Settings From..."

  6. Select c:\whatever\whatever.html file (from above)

  7. In the ensuing "New Log Settings" dialogue box type "whatever" (without

    quotes)

  8. Click Ok

  9. Select the "Log Files" tab

  10. For "Log File type" select "Text File (Tab delimited)

  11. Click on "Configure"

  12. for "Location:" type c:\whatever

  13. for "File name:" type whatever.tsv

  14. Uncheck the "End file names with" checkbox

  15. On the Schedule tab. Select the schedule you wish. (For the purposes of

    this demo you could select the "Manually..." radio buttons for start and stop"

  16. Click on "Apply" and "Ok"

  17. In the right pane of perfmon window, right click on the item you just

    created and select "Start".

  18. After a while you could right click again and Select "Stop".
The

above is one simple way to create and run perfmon logs. You could create one log

for every week, or just change the schedule after every run, so that it is ready

for next week. It is beyond the scope of this write up to discuss the additional

features of perfmon's logging capabilities.

This step should have created the c:\whatever\whatever.tsv file

Step-3

Now DTS this text file (c:\whatever\whatever.tsv) into a SQL Server table

named 'whatever' Again it is not in the scope of this write up to explain

DTS/SSIS, but believe me it will take 5 minutes to do this, for anyone who is

comfortable with DTS. Do not do any transformation. Just move the raw data, as

is.

Please note that every week perfmon will be overwriting

c:\whatever\whatever.tsv with new data. So it is essential to run the DTS

package, before the new data is written to c:\whatever\whatever.tsv (You could

schedule the DTS as a job too). However, DTS will insert the data into the table

every time, you run the package, which is what we need, data across periods.

While setting up the dts package, make the destination columns in the table

like this for easy reference/readability.

time
adql
drt
dwt
pql
pt

Step-4

At this stage you only need to worry about the 'whatever' table in the

database, which has just the 6 columns listed above. Here is the query for the

first report in the spec, given at the beginning of this write up: Once you get

the idea, you can tweak the 'where' clause to get the required data

select * from (

select 'drt' as counter, time,

ceiling(avg(convert(float, drt))) as avg,

ceiling(min(convert(float, drt))) as min,

ceiling(max(convert(float, drt))) as max

FROM

[whatever]

group by time

UNION all

select 'dwt' as counter,time,

ceiling(avg(convert(float, dwt))) as avg,

ceiling(min(convert(float, dwt))) as min,

ceiling(max(convert(float, dwt))) as max

FROM

[whatever] group by time

UNION all

select 'adql' as counter,time,

ceiling(avg(convert(float, adql))) as avg,

ceiling(min(convert(float, adql))) as min,

ceiling(max(convert(float, adql))) as max

FROM

[whatever] group by time

UNION all

select 'pt' as counter,time,

ceiling(avg(convert(float, pt))) as avg,

ceiling(min(convert(float, pt))) as min,

ceiling(max(convert(float, pt))) as max

FROM

[whatever] group by time

UNION all

select 'pql' as counter,time,

ceiling(avg(convert(float, pql))) as avg,

ceiling(min(convert(float, pql))) as min,

ceiling(max(convert(float, pql))) as max

FROM

[whatever] group by time

) as dummy

where time < getdate()

order by counter

The query above is nothing but a UNION of a bunch of similar queries. For each counter I am picking the average, minimum and maximum values grouped on the time field. And restricting the set to a particular date, in the above case to the current date. To add more counters you need to just add another stub to the above query.

Step-5

And here is a complex query for the second report:

--==================================================================

select * from

(select 'drt' as counter,

ceiling(avg(convert(float, drt))) as Period1

FROM

[whatever]

group by time

having time < getdate()) as dummy1,

(select

ceiling(avg(convert(float, drt))) as Period2

FROM

[whatever]

group by time

having time > getdate() and time < getdate() + 1) as dummy2,

(select

ceiling(avg(convert(float, drt))) as Period3

FROM

[whatever]

group by time

having time > getdate() + 1 and time < getdate() + 2) as dummy3

--==================================================================

union all

--==================================================================

select * from

(select 'dwt' as counter,

ceiling(avg(convert(float, dwt))) as Period1

FROM

[whatever]

group by time

having time < getdate()) as dummy1,

(select

ceiling(avg(convert(float, dwt))) as Period2

FROM

[whatever]

group by time

having time > getdate() and time < getdate() + 1) as dummy2,

(select

ceiling(avg(convert(float, dwt))) as Period3

FROM

[whatever]

group by time

having time > getdate() + 1 and time < getdate() + 2) as dummy3

--==================================================================

union all

--==================================================================

select * from

(select 'adql' as counter,

ceiling(avg(convert(float, adql))) as Period1

FROM

[whatever]

group by time

having time < getdate()) as dummy1,

(select

ceiling(avg(convert(float, adql))) as Period2

FROM

[whatever]

group by time

having time > getdate() and time < getdate() + 1) as dummy2,

(select

ceiling(avg(convert(float, adql))) as Period3

FROM

[whatever]

group by time

having time > getdate() + 1 and time < getdate() + 2) as dummy3

--==================================================================

union all

--==================================================================

select * from

(select 'pt' as counter,

ceiling(avg(convert(float, pt))) as Period1

FROM

[whatever]

group by time

having time < getdate()) as dummy1,

(select

ceiling(avg(convert(float, pt))) as Period2

FROM

[whatever]

group by time

having time > getdate() and time < getdate() + 1) as dummy2,

(select

ceiling(avg(convert(float, pt))) as Period3

FROM

[whatever]

group by time

having time > getdate() + 1 and time < getdate() + 2) as dummy3

--==================================================================

union all

--==================================================================

select * from

(select 'pql' as counter,

ceiling(avg(convert(float, pql))) as Period1

FROM

[whatever]

group by time

having time < getdate()) as dummy1,

(select

ceiling(avg(convert(float, pql))) as Period2

FROM

[whatever]

group by time

having time > getdate() and time < getdate() + 1) as dummy2,

(select

ceiling(avg(convert(float, pql))) as Period3

FROM

[whatever]

group by time

having time > getdate() + 1 and time < getdate() + 2) as dummy3

As in the earlier query, this query is also a bunch of UNIONs, but the query is a bit more

involved. The query is building a pivot out of the [whatever] table, using three derived tables

in each UNION. If the query is looking unduly complicated, start looking only at one of the derived table parts of one of the query in one of the UNIONs. For example if you can understand this, the entire query is nothing but a bunch of these small queries put together.

select 'pql' as counter,

ceiling(avg(convert(float, pql))) as Period1

FROM

[whatever]

group by time

having time < getdate()) as dummy1

Nothing comes without a price!!! It works, but simplifying it is an

exercise for you. Please note that these queries are nice leads to move towards

understanding pivoting, cube'ing and Analysis Services. Ah! big words huh?

Hope I made sense. I know many eyebrows are raising!!! I agree that a lot of

the above could be automated. However for the sake of clarity, I decided to show

the steps clearly.

Thankz for reading!

Acknowledgements: For a portion of this article, I would like to give credit to my previous company Arrowsight Inc.

Rate

Share

Share

Rate