Microsoft SQL Server 2014 Query Tuning & Optimization (Database & ERP - OMG) by Benjamin Nevarez

Author:Benjamin Nevarez [Nevarez, Benjamin]
Language: eng
Format: azw3
Publisher: McGraw-Hill Education
Published: 2014-10-10T04:00:00+00:00

Inspecting Statistics Objects

Let’s look at an example of a statistics object and inspect the data it stores. But first make sure you are using the new cardinality estimator by running the following statement:

Existing statistics for a specific object can be displayed using the sys.stats catalog view, as used in the following query:

An output similar to the following (edited to fit the page) will be shown:

One record for each statistics object is shown. You can use the DBCC SHOW_STATISTICS statement to display the details of a statistics object by specifying the column name or the name of the statistics object.

For example, run the following statement to verify that there are no statistics on the UnitPrice column of the Sales.SalesOrderDetail table:

DBCC SHOW_STATISTICS (‘Sales.SalesOrderDetail’, UnitPrice)

If no statistics object exists, which is the case for a fresh installation of the AdventureWorks2012 database, you will receive the following error message:

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.