SQL server peak hour

What are the different ways of finding out the SQL server instance peak hour in 24 hour period? Please let me know. Thanks.
more ▼

asked May 15 '11 at 10:38 PM in Default

ebzm gravatar image

423 27 33 35

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You need to monitor and then report against those monitors.

"It depends". What exactly do you mean by "peak"? Peak number of queries? Peak number of reads/writes? of bytes read/written? of CPU usage?

For example, you could take regular snapshots of the results of sp_monitor, and use that to identify the busiest periods. Or, if you're more interested in particular databases, log and report against the output from

select * from ::fn_virtualfilestats(-1,-1)

Or you could look at the appropriate [DMVs][2], for example those particularly related to the SQL Server OS, or the I/O subsystems.


OK, so you've indicated you're interested in the file stats. What I do (because I'm monitoring multiple servers on multiple versions of SQL), is capture at a central database the output from the following query, run every 10 minutes:

SELECT convert(varchar(128), Serverproperty('servername')) AS Server,
   convert(varchar(128), Db_name(dbid)) AS DatabaseName,
FROM   ::fn_virtualfilestats(-1, -1)

I then use the following query against that table to form the basis of any reports:

WITH vfs
 AS (SELECT Row_number() OVER (ORDER BY server, databasename, fileid, datechecked) AS ID,
            Server, DatabaseName, FileID, Timestamp, NumberReads, NumberWrites, BytesRead, BytesWritten, IOStallMS, DateChecked
     FROM   dbo.virtualfilestats),
 vfs1 AS (SELECT * from vfs),
 vfs2 AS (SELECT * FROM vfs)
SELECT vfs1.Server,
   vfs1.DateChecked                      AS StartDate,
   vfs2.DateChecked                      AS EndDate,
   CASE WHEN vfs2.NumberReads > vfs1.NumberReads THEN
   vfs2.NumberReads - vfs1.NumberReads   ELSE 0 END AS Reads,
   CASE WHEN vfs2.NumberWrites > vfs1.NumberWrites THEN
   vfs2.NumberWrites - vfs1.NumberWrites ELSE 0 END AS Writes,
   CASE WHEN vfs2.BytesRead > vfs1.BytesRead Then
   vfs2.BytesRead - vfs1.BytesRead       ELSE 0 END AS BytesRead,
   CASE WHEN vfs2.BytesWritten > vfs1.BytesWritten THEN
   vfs2.BytesWritten - vfs1.BytesWritten ELSE 0 END AS BytesWritten,
   CASE WHEN vfs2.IOStallMS > vfs1.IOStallMS THEN
   vfs2.IOStallMS - vfs1.IOStallMS       ELSE 0 END AS IOStallMS
FROM   vfs1
   JOIN vfs2
     ON vfs1.server = vfs2.server
        AND vfs1.databasename = vfs2.databasename
        AND vfs1.fileid = vfs2.fileid
        AND vfs1.id = vfs2.id - 1
        AND vfs2.DateChecked IS NOT NULL
[2]: http://msdn.microsoft.com/en-us/library/ms188754(v=SQL.100).aspx
more ▼

answered May 15 '11 at 10:58 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 13 20 44

The purpose is to grab IO statistics for SQL Server databases and data files over a specified time interval which is a peak time.
May 15 '11 at 11:03 PM ebzm

OK, so you'll be wanting to regularly grab the output of

select * from ::fn_virtualfilestats(-1,-1)

and store and manipulate that. Bear in mind that these stats only get reset when the server restarts, (unlike sp_monitor, which resets its stats whenever it's executed)

I've got a query somewhere that'll help. Give me time to grab a coffee, and I'll dig it out.
May 15 '11 at 11:06 PM ThomasRushton ♦
Thank you Thomas. Any guidance on how to automate the script to run every 10 min using CMS?
May 16 '11 at 04:37 AM ebzm
CMS? Not specifically. I wrapped that little lot up in a SSIS package, and scheduled that using the SQLAgent.
May 16 '11 at 08:44 AM ThomasRushton ♦
I mean to say Central Managment Servers. How did you run the script against the multi-server?
May 16 '11 at 11:33 AM ebzm
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 15 '11 at 10:38 PM

Seen: 1321 times

Last Updated: May 15 '11 at 10:38 PM