Statistics Creation on SQL 2005
in [Server Configuration]
|
From: RG on 3 May 2010 08:16 I am confused. Why, then, after doing reindex, there were no statistics for certain index columns.? Thanks for your help. "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message news:4C348B2D-2EA1-4C99-8147-A32FBAF1D949(a)microsoft.com... >> My db has "auto create statistics" on. For some reason, there are quite >> a >> few indexes that don't have statistics. > > Indexes automatically include statistics (including those that support > primary key and unique constraints) so you don't need to separately create > stats. > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "RG" <RG(a)discussions.microsoft.com> wrote in message > news:B2336A67-BED5-447D-817F-9FDCEFC7887D(a)microsoft.com... >> My db has "auto create statistics" on. For some reason, there are quite >> a >> few indexes that don't have statistics. >> >> What could be the cause? >> >> Thanks in advance >
From: Kalen Delaney on 3 May 2010 08:39 I'm confused. Why was this reposted after I already responded? -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "RG" <nobody(a)nowhere.com> wrote in message news:#6W9Qor6KHA.356(a)TK2MSFTNGP05.phx.gbl... > I am confused. Why, then, after doing reindex, there were no statistics > for > certain index columns.? > > Thanks for your help. > "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message > news:4C348B2D-2EA1-4C99-8147-A32FBAF1D949(a)microsoft.com... >>> My db has "auto create statistics" on. For some reason, there are quite >>> a >>> few indexes that don't have statistics. >> >> Indexes automatically include statistics (including those that support >> primary key and unique constraints) so you don't need to separately >> create >> stats. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> http://weblogs.sqlteam.com/dang/ >> >> "RG" <RG(a)discussions.microsoft.com> wrote in message >> news:B2336A67-BED5-447D-817F-9FDCEFC7887D(a)microsoft.com... >>> My db has "auto create statistics" on. For some reason, there are quite >>> a >>> few indexes that don't have statistics. >>> >>> What could be the cause? >>> >>> Thanks in advance >> >
From: RG on 3 May 2010 08:43 Ah.. So, stats could go on indexes or columns. I thought that stats only go on indexes' underlying columns. Would it be correct to say that for low cardinality columns used in queries, you would want create statistics on columns and not to create index? Is there a reason why sql server allows duplicate statistics for the same column(s)? Thanks for your help "RG" <nobody(a)nowhere.com> wrote in message news:B0AEB0A2-1511-4BBF-9F05-65A26A043964(a)microsoft.com... >I am confused. Why, then, after doing reindex, there were no statistics >for certain index columns.? > > Thanks for your help. > "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message > news:4C348B2D-2EA1-4C99-8147-A32FBAF1D949(a)microsoft.com... >>> My db has "auto create statistics" on. For some reason, there are quite >>> a >>> few indexes that don't have statistics. >> >> Indexes automatically include statistics (including those that support >> primary key and unique constraints) so you don't need to separately >> create stats. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> http://weblogs.sqlteam.com/dang/ >> >> "RG" <RG(a)discussions.microsoft.com> wrote in message >> news:B2336A67-BED5-447D-817F-9FDCEFC7887D(a)microsoft.com... >>> My db has "auto create statistics" on. For some reason, there are quite >>> a >>> few indexes that don't have statistics. >>> >>> What could be the cause? >>> >>> Thanks in advance >> >
From: RG on 3 May 2010 12:03 My post got stuck in the drafts folder. Sorry about that "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message news:edu3w2r6KHA.6052(a)TK2MSFTNGP02.phx.gbl... > I'm confused. Why was this reposted after I already responded? > > -- > HTH > Kalen > ---------------------------------------- > Kalen Delaney > SQL Server MVP > www.SQLServerInternals.com > > "RG" <nobody(a)nowhere.com> wrote in message > news:#6W9Qor6KHA.356(a)TK2MSFTNGP05.phx.gbl... >> I am confused. Why, then, after doing reindex, there were no statistics >> for >> certain index columns.? >> >> Thanks for your help. >> "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message >> news:4C348B2D-2EA1-4C99-8147-A32FBAF1D949(a)microsoft.com... >>>> My db has "auto create statistics" on. For some reason, there are >>>> quite >>>> a >>>> few indexes that don't have statistics. >>> >>> Indexes automatically include statistics (including those that support >>> primary key and unique constraints) so you don't need to separately >>> create >>> stats. >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> http://weblogs.sqlteam.com/dang/ >>> >>> "RG" <RG(a)discussions.microsoft.com> wrote in message >>> news:B2336A67-BED5-447D-817F-9FDCEFC7887D(a)microsoft.com... >>>> My db has "auto create statistics" on. For some reason, there are >>>> quite >>>> a >>>> few indexes that don't have statistics. >>>> >>>> What could be the cause? >>>> >>>> Thanks in advance >>> >>
From: Kalen Delaney on 3 May 2010 14:15 Yes, the option 'auto create statistics' ONLY applies to column stats. Indexes always have stats, there is no way to enable/disable that. Low cardinality columns may not be able to make use of indexes, but I certainly wouldn't make a blanket generalization. Index tuning is a BIG subject that you should read about, starting with Books Online and then searching for blog posts about how to determine the best indexes. SQL Server allows duplicate statistics for the same column(s) probably for the same reason it allows duplicate indexes for the same column(s), which is... just because. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "RG" <nobody(a)nowhere.com> wrote in message news:umL8M3r6KHA.5016(a)TK2MSFTNGP02.phx.gbl... > Ah.. So, stats could go on indexes or columns. I thought that stats only > go on indexes' underlying columns. Would it be correct to say that for > low cardinality columns used in queries, you would want create statistics > on columns and not to create index? > > > > Is there a reason why sql server allows duplicate statistics for the same > column(s)? > > Thanks for your help > "RG" <nobody(a)nowhere.com> wrote in message > news:B0AEB0A2-1511-4BBF-9F05-65A26A043964(a)microsoft.com... >>I am confused. Why, then, after doing reindex, there were no statistics >>for certain index columns.? >> >> Thanks for your help. >> "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message >> news:4C348B2D-2EA1-4C99-8147-A32FBAF1D949(a)microsoft.com... >>>> My db has "auto create statistics" on. For some reason, there are >>>> quite a >>>> few indexes that don't have statistics. >>> >>> Indexes automatically include statistics (including those that support >>> primary key and unique constraints) so you don't need to separately >>> create stats. >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> http://weblogs.sqlteam.com/dang/ >>> >>> "RG" <RG(a)discussions.microsoft.com> wrote in message >>> news:B2336A67-BED5-447D-817F-9FDCEFC7887D(a)microsoft.com... >>>> My db has "auto create statistics" on. For some reason, there are >>>> quite a >>>> few indexes that don't have statistics. >>>> >>>> What could be the cause? >>>> >>>> Thanks in advance >>> >> >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Disk Speed Next: SQL Server 2005 Replication and Third Party Tool |