What are the different ways of finding out the SQL server instance peak hour in 24 hour period? Please let me know. Thanks.
(comments are locked)
|
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
Or you could look at the appropriate DMVs, for example those particularly related to the SQL Server OS, or the I/O subsystems. EDIT 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:
I then use the following query against that table to form the basis of any reports:
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
and store and manipulate that. Bear in mind that these stats only get reset when the server restarts, (unlike 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)
|