April 5, 2010 at 2:32 pm
Hi,
With Sys.dm_os_wait_stats being cleared out with a reboot, would it be advisable to capture this data at regular intervals between server reboots to help monitor the wait times on the server? For example if I captured it into a database once an hour then run DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); after each capture, is this advisable? I wasn't sure if anything else hinged on this table.
We're not seeing server latency per say, but I'm working on adding some data collection processes so I can get some statistics on our servers during various times of the day and week in hopes that I can catch trends and better optimize processes.
Also along these same lines do you guys suggest running SQL Profiler and Perfmon periodically to also collect info to analyze and possibly find bottlenecks? I've been reading through some of Brent Ozar[/url]'s articles on collecting info from SQL and Windows to find problems early on, but I wasn't sure how often others suggest running these apps to collect server and trace info, and when running how long to collect date (hours, days, weeks, 24/7, ?)
Thanks for your thoughts ...
Sam
April 5, 2010 at 2:48 pm
Querying that data hourly might not be a bad idea. Dump it into a warehouse where you can use Analysis Services on it.
I run a couple of traces on every server I administer, capturing usage and performance data. I use that to know what to tune.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2010 at 6:09 am
Yes, I definately recommend running server side traces to capture query calls for performance metrics. But be careful here. Don't run the SQL Profiler GUI against your production systems. You can use the GUI to generate the trace script and you can use the GUI to browse the data output from the script, but the GUI itself can cause performance problems when run against a system.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply