Lists details for all indexes on one or more tables / schemas, including row count and size. The non-pivot version also gives data type and other details for the columns.  Read more...

Reports stats on what the query optimizer records in the DMVs as missing indexes and what it says the cost savings will be if they were present. Can limit by table / schema name patterns.  Read more...

The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005 that became available shortly after the release of Service Pack 2 for SQL Server 2005.  Read more...

SQL 2008 has some key improvements over SQL 2005. This article deals with backup compression and faster backups in SQL 2008  Read more...

This article illustrates how to query dynamic management views to find the TOP queries based on average CPU and average I/O.  Read more...

The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005.  Read more...

How should you approach indexing your database? Do you have a good strategy for deciding which indicies to create and on which columns? Aaron Ingold brings us a great article discussing these very topics that is likely to become a must read for many DBAs.   Read more...

Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system.   Read more...

SQL Server performance can be tracked and monitored by using performance counters. For SQL Server 2005 performance counters can be displayed by using the “sys.os_exec_performance_counters” Dynamic Management View (DMV).  Read more...

Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.  Read more...

It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem.  Read more...

As part of your performance audit, you will need to examine each database located on each of your SQL Server instances and examine some basic database settings.  Read more...

In a previous article I showed how to look at what query plans are in SQL Server's memory. In this article I'll look at how they got there and how and when they get reused. This is commonly called compiling a query plan. More specifically I'll look at how and when SQL Server can parameterize dynamic SQL   Read more...

SQL Server memory is primarily used to store data (buffer) and query plans (procedure cache). In this article I'll show how much memory is allocated to the procedure cache (RAM). I'll explain how to determine what plans are in the cache and how often they're used.  Read more...

If you are using SSMS and SQL Server 2005 SP2, You've probably tried out the database reports that are available in the Performance Dashboard, and decided how useful they are, but did you know that you can create your own dashboard reports?   Read more...

SQL Server memory is primarily used to store data (buffer) and query plans (cache). In this article I'll show how much memory is allocated to the data buffer (or RAM). I'll explain how to determine what tables and indexes are in the buffer memory of your server.  Read more...

The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005 that became available shortly after the release of Service Pack 2 for SQL Server 2005.  Read more...

Full scan statistics update can be slow and expensive on a large system, and a single update statistics job for a whole database can easily exceed an overnight maintenance window. This article looks at jobs that could update statistics on the system in parallel.  Read more...

This article introduces some techniques that will allow you to pinpoint exactly where the performance issues are in your system, so you'll know exactly where to spend your time (and money) in solving them.   Read more...

Using hints in a query is something that most DBAs don't ever seem to bother with, but when they do, NOLOCK seems to be their hint of choice. Wayne Fillis brings us a detailed examination of how this particular hint actually affects the performance of your system.   Read more...