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, 2011 at 10:38 PM in Default

avatar image

434 33 33 38

(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, 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

more ▼

answered May 15, 2011 at 10:58 PM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

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, 2011 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, 2011 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, 2011 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, 2011 at 08:44 AM ThomasRushton ♦♦

I mean to say Central Managment Servers. How did you run the script against the multi-server?

May 16, 2011 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.

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, 2011 at 10:38 PM

Seen: 2072 times

Last Updated: May 15, 2011 at 10:38 PM

Copyright 2018 Redgate Software. Privacy Policy