Visualizing Statistics in SSMS
While it may not be fun, running DBCC SHOW_STATISTICS can tell you a lot about how the query optimizer might use a given index, or why certain queries are more susceptible to parameter sniffing.
If you are the visual type like myself you have probably taken the output from this command and pasted it into Excel to create a bar chart. This can be an extremely easy way to get a quick look at the statistics to see how evenly the column values are distributed in the B-Tree, but who wants to waste time opening Excel?
With the following script you can use SSMS’s built-in drawing capabilities to create a histogram you can view right inside SSMS:
To use the above query simply input the table and statistics you are interested in seeing and execute it, the graphical histogram will appear in the “Spatial Results” tab. For this example we are going to use the AdventureWorks2012 database and take a look at the statistics for the IX_SalesOrderDetail_ProductID on the Sales.SalesOrderDetail table:
In the image above you can see the “Spatial Results” tab at the top, and the graphical view of the histogram in the results area. Each line in the chart represents a step in the histogram. Using this quick visual tool you can eaily see how “balanced” your histogram is. In cases where it is highly unbalanced, like in the example, queries written that use compare a parameter value to the value in this column could be highly susceptible to poor parameter sniffing.
So how does this all work?
The Spatial Results tab shows geometric (spatial) data. Spatial data is great for storing things like map data, but in the end it’s just a collection of connected points on a plane. What the above script is doing is simply plotting out a line for each step in the histogram and drawing it as a narrow box. When all the boxes are arranged next to eachother we end up with a bar chart.
Statistics are an interesting and important component of SQL Server, below are some resources to help you better understand statistics as well as learn more about displaying spatial data in SSMS.
Inside the Statistics Histogram & Density Vector @ www.sqlpassion.at/
Statistics in SQL Server @ www.simple-talk.com
Generating Charts and Drawings in SQL Server Management Studio @ www.sqlmag.com
The Elephant and the Mouse, or, Parameter Sniffing in SQL Server @ www.brentozar.com