8

In order to get an overview and compareable data my current task is to create a performance baseline to get some figures about the different productive SQL Server instances.

My thoughts are:

  • I want to use several DMVs
  • I want to include a profiler trace (incl. Exec. plans)
  • I want to include perfmon data

So what I try to achieve is a general performance monitoring startable and stoppable (also scheduable) that returns:

  1. All information required to identify the success of ongoing performance optimization tasks

  2. Couple of aggregated, simple figures that help to visualize the long-term progress .. esp. for management ;-)

  3. Re-executable execution plans within profiler trace to compare individual queue changes and improvements by index optimization tasks

I found couple of information describing the creation of performance baselines. Most of them are either very complicated or focus only on one of the desired performance indicators (mostly perfmon data).

The most matching sample / description was the following: Creating a Performance Baseline for SQL Server

The question is:

Does anyone have experience creating this kind of performance monitor in a quickly doable manner?

  • 3
    A little information you can get from Brent Ozar Sp_Blitz some more reference links Here and Here – Shanky Aug 17 '15 at 9:17
  • 3
    I think the short answer is: No. You need to take time to outline what it is you need to baseline performance. Based on that assessment, you need to put together the queries to gather the information and create the db objects to store the information. In addition to @Shanky's suggestion you can also use sp_whoisactive to gather useful information – tommyfly Aug 17 '15 at 10:20
  • 6
    You could also consider a 3rd party tool that does baselining (and a whole lot on top of that). What you're proposing is re-inventing a lot of work that others have already done. No, they're not free, but neither is your time (and management will be well aware of that). – Aaron Bertrand Aug 17 '15 at 13:49
  • Hi Aaron,can you please provide a third party tool which does this – TheGameiswar Aug 17 '15 at 15:11
  • 3
    Go for SQLSentry. I did used it in my previous job and its an excellent tool + have excellent support and folks in the community like @AaronBertrand :-) – Kin Shah Aug 17 '15 at 15:31
8

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:

enter image description here enter image description here enter image description here

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:

enter image description here

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/

  • 2
    I am glad you came to the same conclusion, and you are exactly right, your time is much better spent on fixing your actual problems instead of re-inventing the wheel. Your wheel seems particularly shiny though :D – ConstantineK Dec 5 '16 at 15:16
6

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

4

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.

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.

1

To quickly create a performance baseline to get some figures about the different productive sql instances, I would use a free trial of a 3rd party tool like Solarwinds Database Performance Analyser.

1

Read this book!!! SQL Server Tacklebox

It shows you how to use SSIS and TSQL queries to collect monitoring information on all the SQL Server instances in your environment.

0

I was following a similar route to some of the above posters, but then stumbled across something created by Microsoft.

It was mentioned above briefly, but no explicit link was given. The Microsoft Tiger Team developed the 'Tiger Toolbix' which is downloadable from here: https://github.com/Microsoft/tigertoolbox

A Youtube video explains the Tiger Toolbox: https://www.youtube.com/watch?v=bx_NGNEz94k

There is also https://github.com/sqlcollaborative/dbareports

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.