Too Many Statistics Objects?
in [Server Configuration]
|
Prev: Pink Love-GHD Pink Limited Edition box set with 60% discounts and free shipping
Next: SQL 2005 DB Space Allocation
From: Behzad Sadeghi on 22 Jul 2010 21:25 I just signed up with a client to help them with the performance of their SQL Server 2005 application. A check of sys.indexes on their database revealed one table with some 700 statistics objects and another with over 400. All the statistics objects appear to have been created by the Database Tuning Advisor. I am trying to find references on the maximum number of statistics objects recommended for a table, but I have not been able to come up with anything. Does anybody know if and when having too many statistics starts degrading performance? Thanks.
From: Erland Sommarskog on 23 Jul 2010 09:19 Behzad Sadeghi (behzad.sadeghi(a)gmail.com) writes: > I just signed up with a client to help them with the performance of > their SQL Server 2005 application. A check of sys.indexes on their > database revealed one table with some 700 statistics objects and > another with over 400. All the statistics objects appear to have been > created by the Database Tuning Advisor. I am trying to find references > on the maximum number of statistics objects recommended for a table, > but I have not been able to come up with anything. Does anybody know > if and when having too many statistics starts degrading performance? I'm a little confused here. You talk about statistics objects, but you look in sys.indexes. Statistics are found in sys.stats. 700 statistics objects for the same table sounds a little bit too much, unless the table has 700 columns. SQL Server creates statistics automatically, but they are only one colmn. If on the other hand, there are 700 *indexes* on a single table that is way too many. As a matter of fact, I don't think you can have that many indexes on a table. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Behzad Sadeghi on 23 Jul 2010 11:06 Thank you, Erland, for coming in to help me. > I'm a little confused here. You talk about statistics objects, but you > look in sys.indexes. Statistics are found in sys.stats. Well, I am confused, too. Here is what is going on, and why I called them statistics. These entries all have their is_hypothetical flag set in sys.indexes. The following link explains the meaning of having a 1 in the is_hypothetical columns as such: http://msdn.microsoft.com/en-us/library/ms173760.aspx "1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics." That last bit about these indexes holding column-level statistics is what made me think they are statistics, and not indexes. After your email, I checked sys.stats. These same entries are duplicated in that view. They have the same exact names, too, all starting with _dta_index in both views. A bit more research on the net suggested that these are not real artifacts, but work items left around from previous runs of the DTA, and that I can safely delete them. Is that correct? Behzad > > 700 statistics objects for the same table sounds a little bit too much, > unless the table has 700 columns. SQL Server creates statistics > automatically, but they are only one colmn. > > If on the other hand, there are 700 *indexes* on a single table that is > way too many. As a matter of fact, I don't think you can have that many > indexes on a table. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 23 Jul 2010 17:26 Behzad Sadeghi (behzad.sadeghi(a)gmail.com) writes: > A bit more research on the net suggested that these are not real > artifacts, but work items left around from previous runs of the DTA, > and that I can safely delete them. > > Is that correct? Yes. There is of course the risk that any of these multi-column statistics help the optimizer, but since multi-column stats only have density information for the second and later column, it is not that likely. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Behzad Sadeghi on 23 Jul 2010 17:37
On Jul 24, 1:26 am, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Behzad Sadeghi (behzad.sade...(a)gmail.com) writes: > > A bit more research on the net suggested that these are not real > > artifacts, but work items left around from previous runs of the DTA, > > and that I can safely delete them. > > > Is that correct? > > Yes. There is of course the risk that any of these multi-column statistics > help the optimizer, but since multi-column stats only have density > information for the second and later column, it is not that likely. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thank you, Erland. |