Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Analysis of SQL Server Performance Data

By Jambu Krishnamurthy,

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.

Total article views: 11124 | Views in the last 30 days: 12
 
Related Articles
FORUM

Converting Float into Date

Converting Float into Date

FORUM

Error converting data type varchar to float

Error converting data type varchar to float.

FORUM

Problem in converting float to varchar

Problem in converting float to varchar

FORUM

Error converting data type nvarchar to float

Error converting data type nvarchar to float

FORUM

Exe SQL Task - Convert float to varchar

Exe SQL Task - Convert float to varchar

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones