September 14, 2011 at 2:58 pm
Hi all
This is a bit of a general question but I am wanting to start collecting data for monitoring SQL server such that I can create baselines, and from those identify potential bottle necks and hence start to tune my SQL Server. I have basically collected a set of perf counters I want to use and also have gathered a set of queries that run against DMVs which I am thinking of automating such that they gather snapshots of the instance performance to try and trend it.
My question is two fold really, have I missed anything out (in general terms) when gathering information. Secondly in light of the fact we don't have any 3rd party monitoring software how do people best trend and analyse this information. Is excel the best way to do this, or maybe I should be looking at SSRS to display information I have stored?
I would appreciate any comments.
Thanks
September 14, 2011 at 4:49 pm
Hi,
Creating SQL traces and importing the data into a table is the based way to track performance over time. This is all the monitoring software does and you can create it yourself. All of that functionality is provided for free by Microsoft (you just have to set it up).
Here is a link to get you started: SQL Trace
The important part is getting the data from the file into the database, which is described at the end. The article also has links to Microsoft's site which provides a super detailed article on setting everything up.
Edit: Added the correct link (sorry!). Also, it sounds like you are doing a lot of the right things for tracking the data. Automating that tracking is your next step.
Jason
Webmaster at SQL Optimizations School
September 16, 2011 at 1:14 pm
Kwisatz78 (9/14/2011)
Hi allThis is a bit of a general question but I am wanting to start collecting data for monitoring SQL server such that I can create baselines, and from those identify potential bottle necks and hence start to tune my SQL Server. I have basically collected a set of perf counters I want to use and also have gathered a set of queries that run against DMVs which I am thinking of automating such that they gather snapshots of the instance performance to try and trend it.
My question is two fold really, have I missed anything out (in general terms) when gathering information. Secondly in light of the fact we don't have any 3rd party monitoring software how do people best trend and analyse this information. Is excel the best way to do this, or maybe I should be looking at SSRS to display information I have stored?
I would appreciate any comments.
Thanks
Have you taken a look at the Data Collector and the Managment Data Warehouse- introduced in SQL Server 2008? This is a pretty robust solution. For 2005 I was doing almost exactly what you describe- caputruing DMV values and perfmon counters in to custom tables, and then outputting to excel for analysis/trending. The Data Collector is basically providing this same functionality out of the box, and comes with some pretty snazzy looking reporting.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply