Creating SQL Server Performance Baseline Monitoring
More than one year later I want to let everyone know my experience and the final result of this question / topic.
I started out creating things on my own. Initially I followed the Article Collect and store historical SQL Server performance counter data with CMVs by Tim Ford to get something up and extended this with whatever Data I wanted to collect. So once a per day I run several stored procedures on each Sql Server that collect some specific information from DMVs and stores the results on the local Server inside of a database. This includes index usage, missing indexes, specific log entries like autogrow, server settings, application database settings, fragmentation, job execution, transaction log info, file information, wait stats and more.
Additionally I added Brent Ozar's sp_blitz regulary execution results to this repository to collect additional valuable indications to work, improve and report.
All data is lateron collected from there into a dedicated monitoring Sql Server and this way I create a bundeled store for performance relevant information about all my servers and use this as a base for investigation and reporting.
Then I created excel sheets and also reports using reporting services to analyze and interpret. Some samples:
Also I configured some performance counters monitoring using TYPEPERF inspired by the article "Collecting Performance Data into a SQL Server Table" by Fedor Georgiev.
From my SQL Monitoring instance I trigger typeperf to run and collect a configurable number of samples with a configurable sampleinterval and store the results in my central monitoring db.
This allows me to observer longterm performance values, sample:
After a while of using this to gather baseline information, it tunred out that it is quite a lot of maintenance work that has to be spent on looking at failed jobs, de-buggin procedures (for example in case a DB was taken offline, some scripts failed), maintaining settings after a server was replaced...
Also the database collecting all the records tiself needs maintenance and performance tuning, so additional work comes up to keep the data useful...
What is finally completely missing is the ability to look at things that happen live. In Best Case, I will be able to tell what was possibly going on the next day after the data collectors have ran. Also all the details are missing. I do not have access to deadlock graphs, I can't look at query plans of queries that were running in a suspicious timeframe....
All of that made me going to charge management to spend money for a prefessional solution I am not able to create on my own.
The final choice was to buy SentryOne because in comparison to others it is convincing and delivers a lot of information required to identify our pain points.
As a final conclusion I would advice anyone looking for answers to similar question not to try creating things on their own as long as you don't have a small and basically healthy environment in place. If you have couple of systems and a lot of problems, better immedeately go for a professional solution and use the assistence of the vendor on your problems instead of spending a lot of time and money to create somehing less useful. However, this route was still very interesting and made me learn a lot I dont want to miss.
I hope you find this useful once you ran into this question thread.
EDIT April 20, 2017: Brent Ozar recently posted the following article on facebook that is kind of a simiar approach taken by the SQL Tiger Team: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-baselining-reports-unleashed-for-enterprise-monitoring/
Here are some good articles with some practical examples that you can find here:
How to detect SQL Server performance issues using baselines – Part 1 – Introduction
How to detect SQL Server performance issues using baselines – Part 2 – Collecting metrics and reporting
How to detect SQL Server performance issues using baselines – Part 3
While Part 1 will provide you some basic knowledge about what the baseline is, in Part 2 you can find info how to do that on your own using "poor man" method (it's free and good for learning)
Part 3 provides some examples on how you can establish baselines and how to use baselines in troubleshooting some issues via ApexSQL Monitor
As a fairly newly minted DBA under the gun, I have run the gamut of free tools and done some experimentation in the paid space (DPA, SQL Sentry, and Foglight) and it really depends on what you want the tool for.
In my experience the most important thing was not just communicating performance baselines (management vastly didn't care unless there was someone to yell at), but produce something in an easy to consume format that made the priorities clear and was able to track down performance issues in production.
You can absolutely build up your skills by going the free route, and the tools for SQL Server are great.
- Ozar First Responder Kit https://www.brentozar.com/responder/
- sp_whoisactive http://whoisactive.com/ (included in the first responder kit)
- #sqlhelp on twitter is one of the most friendly and useful SQL Server related resources on the internet
- PSSDiag and SQLDiag http://diagmanager.codeplex.com/
- SQLNexus http://sqlnexus.codeplex.com/
With these and some additional databases/tables and jobs and time you can build out a basic monitoring system (but it isn't pretty) these are tools for DBAs; unless you are good at BI stuff you will struggle to find time to produce useful business friendly stuff from it, though the Ozar sp_blitz app is pretty dang cool.
After spending around a year doing the free thing and resolving plenty of issues (but not getting much buy in) I was able to make it clear, after a major issue, that perf monitoring software was a priority, and we were going to buy it come hell or high water.
After demoing the previously mentioned clients, I chose DPA because management could easily consume the results, though I definitely have client licenses for SQL Sentry Plan Explorer Pro (1000% worth the money) and really liked using the server version, it just didnt grab them the same way.
I also tried getting SQLNexus working at one point but I ended up working a lot than I was interested in, it may suit your needs.