When it comes to improving the performance of database systems, there are nearly as many approaches as there are DBA (database administrator) teams. That might sound acceptable at first glance. But, in my opinion, it indicates that database management is not a truly mature professional discipline. Mature disciplines—from medicine to aviation to accounting—also have mature processes that represent the generally accepted workflows and procedures needed for best results, or, at a minimum, to prevent the worst from happening.
However, DBAs do not have an equivalent for the FAA handbooks found in every aircraft cockpit in America—that’s a shame because so many of our DBA workflows and processes could be made easier, more reliable, and more predictable with a bit of planning and foresight.
As a database performance tuning expert, I spend a lot of time with clients who are struggling with the urgency caused by applications and databases that fail to perform as expected or, worse, experience a constant sequence of errors, alerts, and crashes. The tyranny of the urgent is so intense that these teams never have time to work on what is most important. Again, that’s a shame because so much valuable time is wasted on detecting, diagnosing, and fixing problems that have been solved hundreds or thousands of times in the past by others in our profession. And, while I can’t point you to an industry standard for troubleshooting database performance issues, I can share with you the consensus of best practices that I have used personally and encountered frequently when visiting my best managed clients.
In this article, I will describe the wide variety of options for performance information available to you, their value, and how and when to use them. One quick note—I’m going to focus on tools and features with a systemwide capability, thus excluding query tuning-specific features such as Live Query Replay and Graphic Execution Plans and their command-line counterparts.
I will discuss these data sources from newest to oldest, since many of the new sources supplant the older ones:
Query Store (QS): Introduced in SQL Server 2016, QS is similar to an airplane flight recorder with a specific focus on query plans and their performance. QS captures the history of queries, their execution plans, and their runtime statistics. Use the QS when your workloads run on recent versions and are primarily constrained by queries. You can see database usage patterns over time on SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics, within or outside of elastic pools. If you’re using one of the Azure databases, you can take advantage of Query Performance Insight for many additional visualizations about the overall resource consumption of your Azure databases.
Extended Events (XEvents): XEvents was introduced in SQL Server 2008 and then greatly matured over the next few releases. XEvents supersedes the Profiler toolkit and offers a wide and powerful array of performance telemetry, spanning the gamut from wait statistics to locks and deadlocks to TempDB behavior. Since around the time SQL Server 2014 was introduced, there has been very little you could not solve using XEvents, and quite a lot of issues are easier to solve with this excellent tool.
Dynamic Management Objects (DMOs): DMOs are composed of two types of objects—dynamic management views (DMVs) and dynamic management functions (DMFs)— and supersede the use of system tables (although they exist side-by-side) and Performance Monitor (PerfMon) in many cases. DMOs enable you to retrieve server state information using standard SQL queries on just about every performance indicator you could want. Each new release of SQL Server brings new DMOs associated with the newest features in the release. Along with XEvents, DMOs make up the bulk of my troubleshooting and performance-tuning toolkit for any SQL Server instance, version 2014 or greater. For that matter, it’s so comprehensive that there are even DMVs that tell you all about the behind-the-scenes behavior of XEvents. To see DMV use in action, check out my performance troubleshooting series of webcasts at https://sentryone.com/webinars.
SQL Profiler and SQL Trace: These two features are the graphic and textual interface to trace SQL Server performance data by capturing and saving details about a given set of triggering “events.” SQL Profiler causes a lot more overhead than SQL Trace due to its graphical user interface. Once collected, you can analyze the results to diagnose and resolve a variety of performance issues, for example, exception event errors. You can trace individual types of queries and batches, specific event class (such as login and logout) details, and a variety of event category (such as locking) details. And you can filter your traces by a variety of parameters to reduce overhead and focus the scope of your investigation to just the details you need. These tools have been supplanted entirely by other tools on the Azure data platform. But they are still useful for on-prem SQL Server databases from version 2000 to 2012. They are deprecated for on-prem SQL Server but are still supported for instances of SQL Server Analysis Services.
Windows Performance Monitor (PerfMon): I was a beta tester for PerfMon back when it came out with Windows NT 3.1 in the 1990s. It is also known as System Monitor and is a very reliable and useful performance monitoring utility. PerfMon is a tool that retrieves and displays system performance information on a second-by-second basis. Depending on the other services installed, you may also have access to a variety of performance counters for that service, including SQL Server and SQL Server Analysis Services. PerfMon also allows you to store performance data into Data Collector Sets for later analysis. It is still useful to this day, especially when your issue is related to Windows or a hypervisor guest, or when other services on the server act as “noisy neighbors.”
DBCC (Database Console Command) statements: A carry-over from the days when SQL Server was a Sybase product, the DBCC set is now used primarily for preventive maintenance tasks, such as checking for corruption in tables and indexes using the syntax DBCC CHECKALLOC or DBCC CHECKDB. It is still quite useful in certain situations for troubleshooting memory and indexing issues, for example, via the DBCC PROCACHE and DBCC SHOWCONTIG statements, respectively. In addition, there are quite a few other useful DBCC commands in even the most recent releases of SQL Server.
System tables: System tables have been included with SQL Server since its earliest versions. That is because the system tables store the metadata required by the relational database standards for users to see all the information about the database, as well as the objects it contains and their properties. You could, for example, query the sys.objects system table to see all the objects in each database. All system tables start with “sys,” making them easy to identify. The master database houses the greatest number of system tables, although every database on an instance of SQL Server also contains system tables. Note that all system tables are not truly tables. Some are instantiated as views since the data they contain is highly volatile and changes moment to moment, an example of which is sys.syslockinfo. In addition, the ANSI/ISO standard for SQL provides a universal set of metadata called the INFORMATION_SCHEMA database schema. This is especially useful if you need to query the same metadata from multiple relational database platforms, such as SQL Server and PostgreSQL, using a single SQL query.
Error logs: In many situations, error logs are the unsung hero of troubleshooters and performance tuners everywhere. Error logs have existed in SQL Server in essentially the same format from release to release for as long as I can remember. SQL Server also has separate logs for the SQL Server Agent, as well as other components of the data platform, such as SQL Server Analysis Services. In each case, SQL Server keeps seven rolling log files on the server, whose size and location you can adjust from default when needed. SQL Server, and associated components of the data platform which have error logs, also bubbles that information up to the Windows Error Logs. If you’d like to read a SQL Server error log using a SQL statement, check out the syntax for the xp_readerrorlog extended stored procedure.
.