July 8, 2009 at 10:26 am
I want to verify whether my understanding on SHOWCONTIG is correct or not.
DBCC SHOWCONTIG(269244014,1) and DBCC SHOWCONTIG(269244014) are same as both tell about the fragmentation of clustered index. There is nothing called fragmentation of table.
One more question: There are only four indexes but SELECT * FROM SYSINDEXES WHERE ID = 269244014 shows 7 indexes. From where these new 3 indexes has come.
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
July 9, 2009 at 1:57 am
Any headsup!!
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
July 9, 2009 at 4:41 pm
Those might be statistics. Please post their names.
MJ
July 9, 2009 at 7:21 pm
If the other 3 "indices" are named "_WA_Sys..." then they're auto-generated statistics that SQL Server has created on non-indexed columns.
When the AutoCreateStatistics is turned on for a database the presence of a filter on a non-indexed field (eg. "... where LastName = 'Smith' ...", where LastName is not the first field in any of the indices on that table) will result in SQL Server creating statistics on that field, looking at the selectivity of the data in that field. The definition of this is stored in sys.indexes.
Don't worry about these: they're generally good things as they help SQL Server determine better (ie. more efficient) query plans.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply