Chapter 18. Monitoring and analyzing performance
This is one of the most important chapters in this book. Aside from index maintenance and monitoring, performance monitoring is one of the most impactful things you can do as a SQL Server administrator. In this chapter, I’ll focus on some of the easiest ways to monitor SQL Server performance, particularly from the perspective of SQL Server 2012 and later.
This chapter is, I’ll admit, a wee bit on the long side. You may need a couple of days’ lunches to go through everything.
Windows’ built-in Performance Monitor tool remains one of the best ways to get a quick look at performance, and for compiling long-term performance information. It isn’t an enterprise monitoring system as System Center Operations Manager, but it does let you dig in to see a snapshot at what’s happening.
When using Performance Monitor, always run it on a machine other than the one you’re analyzing. The tool works fine remotely and runs on all Windows computers. It can display information in a real-time chart, or save performance information to a log, called a data collector set. Those logs can be opened later and viewed in a non-real-time graph.
Tip
To run Performance Monitor on nearly any Windows computer, hold the Windows key and press R. Then, type Perfmon and hit Enter.