The topic of baselines in SQL Server is one that I’ve had an interest in for a long time. In fact, the very first session I ever gave back in 2011 was on baselines. I still believe they are incredibly important, and most of the data I capture is still the same, but I have tweaked a couple things over the years. I’m in the process of creating a set of baseline scripts that folks can use to automate the capture of this information, in the event that they do not have/cannot afford a third-party monitoring tool (note, a monitoring tool such as SQL Sentry’s Performance Advisor can make life WAY easier, but I know that not every can justify the need to management). For now, I’m starting with links to all relevant posts and then I’ll update this post once I have everything finalized.
These scripts are just a starting point for what to monitor. One thing I like to point in our IEPTO2: Performance Tuning and Optimization course is that there is A LOT of data you can capture related to your SQL Server environment. Your options include Performance Monitor (using Custom Data Collectors), queries via Extended Events or Trace (depending on version), and any data from the DMVs or system views within SQL Server. You have decide what to capture based on
1) What problem you might be trying to solve in your environment, and
2) What information is most important for you to have. Start simple, and then work your way up.
Figure out the one or two most critical things to capture, and start there, and then add on.
If you find there’s something missing from my scripts, let me know and I’ll try to get it added!
Monitoring in general
Disk and I/O