Database Reference
In-Depth Information
To further investigate queries producing these problems, use Extended Events to identify the queries (I will cover
this tool in the next chapter). Queries with missing indexes, too many rows requested, or badly formed T-SQL will
have a large number of logical reads, caused by scanning the entire table or entire index, and an increased CPU time.
Be aware that full scans may be performed for the temporary tables used in a stored procedure because most of
the time you will not have indexes (or you will not need indexes) on temporary tables. Still, adding this counter to the
baseline helps identify the possible increase in the use of temporary tables, which, when used inappropriately, can be
bad for performance.
Dynamic Management Objects
Another way to check for missing indexes is to query the dynamic management view sys.dm_db_missing_index_details.
This management view returns information that can suggest candidates for indexes based on the execution plans of
the queries being run against the database. The view sys.dm_db_missing_index_details is part of a series of DMVs
collectively referred to as the
missing indexes feature.
These DMVs are based on data generated from execution
plans stored in the cache. You can query directly against this view to gather data to decide whether you want to build
indexes based on the information available from within the view. Missing indexes will also be shown within the
XML execution plan for a given query, but I'll cover that more in the next chapter. While these views are useful for
suggesting possible indexes, since they can't be linked to a particular query, it can be unclear which of these indexes
is most useful. You'll be better off using the techniques I show in the next chapter to associate a missing index with a
particular query. For all the missing index suggestions, you must test them prior to implementing any suggestion on
your systems.
The opposite problem to a missing index is one that is never used. The DMV sys.dm_db_index_usage_stats shows
which indexes have been used, at least since the last restart of the SQL Server instance. Unfortunately, there are a number
of ways that counters within this DMV get reset or removed, so you can't completely rely on it for a 100 percent accurate
view of index use. You can also view the indexes in use with a lower-level DMV, sys.dm_db_index_operational_stats.
It will help to show where indexes are slowing down because of contention or I/O. I'll cover these both in more detail
in Chapter 20. You may also find that the suggestions from the Database Tuning Advisor (covered in Chapter 10) may
be able to help you with specific indexes for specific queries.
Database Concurrency
To analyze the impact of database blocking on the performance of SQL Server, you can use the counters shown
in Table
4-5
.
Table 4-5.
Performance Monitor Counters to Analyze SQL Server Locking
Object(lnstance[,lnstanceN])
Counter
SQLServer:Latches
Total Latch Wait Time (ms)
SQLServer:Locks(_Total)
Lock Timeouts/sec
Lock Wait Time (ms)
Number of Deadlocks/sec
Total Latch Wait Time (Ms)
Latches are used internally by SQL Server to protect the integrity of internal structures, such as a table row, and are
not directly controlled by users. This counter monitors total latch wait time (in milliseconds) for latch requests that
had to wait in the last second. A high value for this counter can indicate that SQL Server is spending too much time
waiting on its internal synchronization mechanism.
