Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC SHOWCONTIG and indid Expand / Collapse
Author
Message
Posted Wednesday, July 8, 2009 10:26 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 3, 2011 4:58 PM
Points: 88, Visits: 191
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.
Post #749473
Posted Thursday, July 9, 2009 1:57 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 3, 2011 4:58 PM
Points: 88, Visits: 191
Any headsup!!

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Post #749964
Posted Thursday, July 9, 2009 4:41 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
Those might be statistics. Please post their names.

MJ
Post #750744
Posted Thursday, July 9, 2009 7:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:38 PM
Points: 2,062, Visits: 867
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.
Post #750794
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse