Database Reference
In-Depth Information
As explained in Chapter 12, you can check the statistics of a table and its indexes using
DBCC SHOW_STATISTICS
.
There are five tables referenced in this query:
Purchasing.PurchaseOrderHeader
,
Purchasing.PurchaseOrderDetail
,
Person.Employee
,
Person.Person
, and
Production.Product
. You must know which indexes are in use by the query
to get the statistics information about them. You can determine this when you look at the execution plan. For now, I'll
check the statistics on the primary key of the
HumanResources.Employee
table since it had the most reads. Now run
the following query:
DBCC SHOW_STATISTICS('HumanResources.Employee',
'PK_Employee_BusinessEntityID');
When the preceding query completes, you'll see the output shown in Figure
25-3
.
Figure 25-3.
SHOW_STATISTICS output for HumanResources.Employee
You can see the selectivity on the index is very high since the density is quite low, as shown in the
All density
column. In this instance, it's doubtful that statistics are likely to be the cause of this query's poor performance. It's
probably a good idea, where possible, to look at the actual execution plan and compare estimated vs. actual rows
there. You can also check the
Updated
column to determine the last time this set of statistics was updated. If it has
been more than a few days since the statistics were updated, then you need to check your statistics maintenance
plan, and you should update these statistics manually. In this case, these statistics could be seriously out of date
considering the data provided.
Analyzing the Need for Defragmentation
As explained in Chapter 13, a fragmented table increases the number of pages to be accessed by a query performing
a scan, which adversely affects performance. However, fragmentation is frequently not an issue for point queries. For
this reason, you should ensure that the database objects referred to in the query are not too fragmented.
You can determine the fragmentation of the five tables accessed by the worst-performing query by running a
query against
sys.dm_db_index_physical_stats
. Begin by running the query against the
HumanResources.Employee
table.
SELECT s.avg_fragmentation_in_percent,
s.fragment_count,
s.page_count,
s.avg_page_space_used_in_percent,
s.record_count,
