Understanding DBCC Show_Statistics with MSSQL
Microsoft SQL Server uses statistics for finding out the distribution of data in columns and indexes. These statistics are not used to create indexes, they are just used for creating the all important execution plans used for the queries.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
What are the statistics?
You get the listing of all the statistics name with the columns involved by running sp_helpstats.
SP_HELPSTATS 'person.contact','all';
go
go
statistics_name statistics_keys ---------------------------- --------------------- _WA_Sys_00000004_1273C1CD FirstName _WA_Sys_00000006_1273C1CD LastName AK_Contact_rowguid rowguid IX_Contact_EmailAddress EmailAddress PK_Contact_ContactID ContactID
What is the data distribution?
You will need to query each statistics name, one at a time, by using DBCC SHOW_STATISTICS.
DBCC SHOW_STATISTICS ('person.contact','_WA_Sys_00000004_1273C1CD');
go
go
Name Updated Rows Rows Sampled Steps Density Average key length String Index ------------------------------ -------------------- ---------- -------------- ------ ------------- ------------------ ------------ _WA_Sys_00000004_1273C1CD Jul 22 2007 8:14PM 19972 19972 200 0.08022754 11.80753 YES (1 row(s) affected) All density Average Length Columns ------------- -------------- ------------ 0.0009823183 11.80753 FirstName (1 row(s) affected) RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS -------------------------------------------------- ------------- ------------- -------------------- -------------- A. 0 2 0 1 Aaron 1 56 1 1 Abigail 21 76 3 7 Adam 1 53 1 1 Aidan 64 25 4 16 Alan 22 30 2 11 Alex 94 51 4 23.5 Alexandra 46 93 2 23 Alexis 67 47 3 22.33333 Alisha 48 48 4 12 Allison 44 46 2 22 Alyssa 47 67 3 15.66667 Amanda 0 69 0 1 Ana 56 22 3 18.66667 Andrea 23 46 3 7.666667 Angel 78 44 5 15.6 Angela 0 50 0 1 Anna 52 71 4 13 Antonio 51 24 5 10.2 Arianna 42 45 2 21 Arthur 26 46 4 6.5 Ashley 71 51 4 17.75 Austin 26 50 2 13 Barbara 67 51 3 22.33333 Benjamin 34 59 7 4.857143 Bethany 28 23 2 14 Blake 53 71 11 4.818182 Bonnie 25 28 2 12.5 Brandon 72 54 3 24 Brendan 49 21 3 16.33333 Brian 44 36 2 22 Brianna 19 68 1 19 Brittney 49 23 5 9.8 Bryan 70 23 4 17.5 Caleb 82 52 4 20.5 Cameron 22 51 1 22 Carina 64 1 4 16 Carlos 47 51 2 23.5 Carol 40 28 3 13.33333 Carolyn 28 43 4 7 Casey 43 47 2 21.5 Cassidy 23 25 1 23 Cedric 49 41 7 7 Charles 47 71 5 9.4 Chloe 73 88 3 24.33333 Christian 12 54 1 12 Christy 71 40 5 14.2 Clarence 32 42 5 6.4 Clayton 22 41 2 11 Cole 62 24 3 20.66667 Colleen 46 46 1 46 Connor 5 51 1 5 Cristina 93 20 10 9.3 Cynthia 43 28 2 21.5 Dalton 59 93 4 14.75 Daniel 71 26 4 17.75 Darren 74 45 4 18.5 David 25 87 2 12.5 Deanna 46 48 1 46 Derek 93 21 12 7.75 Destiny 40 69 4 10 Devin 0 87 0 1 Dominic 72 23 6 12 Donna 53 21 4 13.25 Duane 62 1 6 10.33333 Dylan 44 54 2 22 Eduardo 93 90 6 15.5 Edward 0 72 0 1 Elijah 48 50 3 16 Elizabeth 0 52 0 1 Emma 53 68 5 10.6 Eric 21 63 2 10.5 Erika 67 20 3 22.33333 Ethan 51 53 3 17 Evan 32 48 5 6.4 Fernando 84 65 4 21 Frank 56 37 9 6.222222 Gabriel 66 54 5 13.2 Gabrielle 48 66 2 24 Gary 37 32 3 12.33333 Gerald 74 44 5 14.8 Gilbert 4 43 4 1 Grace 77 69 9 8.555555 Hailey 73 64 10 7.3 Hannah 60 45 1 60 Heather 28 22 7 4 Henry 53 25 4 13.25 Hunter 26 70 6 4.333333 Ian 0 89 0 1 Isabella 77 92 9 8.555555 Jack 89 59 5 17.8 Jaclyn 54 45 2 27 Jacqueline 20 47 1 20 Jaime 73 50 4 18.25 James 23 97 1 23 Janet 91 36 6 15.16667 Jasmine 55 62 5 11 Jay 68 52 3 22.66667 Jennifer 73 96 7 10.42857 Jeremy 93 47 2 46.5 Jesse 65 47 3 21.66667 Jessie 73 42 1 73 Jill 25 34 3 8.333333 Jo 63 5 4 15.75 Joe 89 46 5 17.8 John 21 58 1 21 Jonathan 95 78 5 19 Jordan 17 77 1 17 Jose 47 75 2 23.5 Joseph 9 31 1 9 Juan 58 32 7 8.285714 Julia 17 89 4 4.25 Justin 74 52 5 14.8 Kaitlyn 24 90 4 6 Kari 61 42 4 15.25 Katelyn 69 48 5 13.8 Katherine 0 99 0 1 Kayla 95 48 7 13.57143 Kelli 74 46 2 37 Kelvin 68 45 3 22.66667 Kevin 54 60 7 7.714286 Kristi 87 44 9 9.666667 Kristopher 63 21 3 21 Kurt 45 22 2 22.5 Lacey 52 45 2 26 Larry 26 25 3 8.666667 Lauren 73 69 3 24.33333 Leslie 93 23 8 11.625 Linda 23 38 4 5.75 Logan 77 73 7 11 Lucas 80 93 8 10 Luke 57 53 3 19 Madison 95 49 6 15.83333 Mandy 49 25 6 8.166667 Marcus 74 97 7 10.57143 Maria 32 64 3 10.66667 Marie 48 46 3 16 Marshall 54 43 5 10.8 Marvin 48 23 3 16 Mason 45 44 4 11.25 Maurice 49 24 3 16.33333 Megan 64 70 5 12.8 Melanie 22 48 1 22 Melody 62 21 2 31 Michael 94 54 6 15.66667 Michele 20 64 3 6.666667 Miguel 28 73 3 9.333333 Miranda 45 24 7 6.428571 Molly 49 21 5 9.8 Morgan 40 92 2 20 Natalie 71 91 5 14.2 Nathan 25 75 2 12.5 Nelson 47 22 4 11.75 Nicole 66 72 5 13.2 Noah 29 73 8 3.625 Olivia 10 67 6 1.666667 Paige 95 48 7 13.57143 Patrick 55 28 6 9.166667 Pedro 37 42 5 7.4 Phillip 60 23 8 7.5 Rachel 77 71 11 7 Ramon 57 21 8 7.125 Raul 66 20 6 11 Rebekah 48 42 2 24 Richard 94 103 9 10.44444 Robert 69 90 3 23 Rodrigo 76 1 5 15.2 Ronnie 79 20 4 19.75 Ross 27 40 3 9 Ruben 44 44 3 14.66667 Ryan 57 60 5 11.4 Samuel 75 73 7 10.71429 Sara 33 50 2 16.5 Savannah 48 47 2 24 Sean 17 53 2 8.5 Seth 45 92 3 15 Shannon 27 42 4 6.75 Shawna 76 22 3 25.33333 Sierra 85 19 13 6.538462 Stacy 82 23 8 10.25 Stephanie 46 70 5 9.2 Sunil 92 6 8 11.5 Sydney 63 90 5 12.6 Tamara 53 41 7 7.571429 Taylor 91 71 6 15.16667 Tete 95 1 8 11.875 Thomas 44 85 3 14.66667 Tina 75 24 3 25 Tony 74 25 7 10.57143 Trisha 90 23 6 15 Tyrone 74 22 6 12.33333 Victor 80 23 7 11.42857 Victoria 0 69 0 1 Warren 75 43 8 9.375 William 92 30 6 15.33333 Wyatt 45 70 3 15 Xavier 0 88 0 1 Zachary 37 51 10 3.7 Zheng 1 3 1 1 Zoe 0 23 0 1 (200 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
- You will need to scroll down for the horizontal scroll bar.
- The listing will tell you:
- The date and time of the last updates of these statistics: Is this the date and time that you expect?
- Rows vs. Rows Sampled: If it's different by a significant amount, then maybe it's time to update the statistics. [And the keyword is maybe, do you really need to update that stats on a whole bunch of almost identical data.]
- Density: Is how selective an index is. The higher the density, the more selective the index. The closest to 1 the better. A unique index has a density of 1.
- The EQ_Rows should be a close to 1 as possible. All the primary keys should have a value of 1.
- The key length should be the smallest possible. The narrower the index, the more of rows fit into a page therefore less reads for the select.
- Then you will get the values stored in the statistics.