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:
- Start -> Run
- Type perfmon
- Press Enter
- In the left tree pane of the perfmon window, open "Performance Logs and
Alerts"
- Right click on "Counter Logs" and click on "New Log Settings From..."
- Select c:\whatever\whatever.html file (from above)
- In the ensuing "New Log Settings" dialogue box type "whatever" (without
quotes)
- Click Ok
- Select the "Log Files" tab
- For "Log File type" select "Text File (Tab delimited)
- Click on "Configure"
- for "Location:" type c:\whatever
- for "File name:" type whatever.tsv
- Uncheck the "End file names with" checkbox
- 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"
- Click on "Apply" and "Ok"
- In the right pane of perfmon window, right click on the item you just
created and select "Start".
- After a while you could right click again and Select "Stop".
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.
