February 24, 2005 at 3:07 pm
I used the data import feature of EM to transfer SQL objects from one DB to another. The new DB shrunk from 4.5 GB to 850mb - which surprised me since I run DBCC INDEXDEFRAG each weekend on the source DB. This indicates fragmenation of some sort, right?
Anyway, I was using the table search in the task pad view (in EM) and noticed that a table with only 3 defined indexes shows up as having 34 additional indexes with names starting "_WA_Sys_*". The new DB only shows the 3 defined indexes.
Anyone know what those are??? Also, if anyone has a query that will compare table & indexes names/sizes between two DB's, that would be helpful too.
TIA
February 24, 2005 at 3:10 pm
When SQL Server generates statistics, it uses sysindexes to hold entries, and these are named "_WA_Sys_*". The 'status' column should distinguish them from 'normal' indexes.
February 24, 2005 at 4:17 pm
Ahh yes, that makes sense. Thanks for the quick response. So, assuming that all data was transfered would you expect that extreme table fragmentation in the source DB might account for such a large difference in DB size? This DB is part of a third party application - so I have no say over its design. That being said, I also noticed while looking through indexes today that they do NOT use a clustered index on most of the ~700 tables in the database. Couldn't that lead to increased table (heap) fragmentation?
February 25, 2005 at 1:26 am
The system statistical indexes are usuallya good starting point for potential additional indexes. See what SQL has created and these may indicate that the database in uder indexed.
Tables without clustered indexes - I hate them, currently dealing with one with 1211 tables and not a clustered index in sight. Fragmentation would depend more on fill factors and pad_index, but the performance of any range retrieval (where x > y and x < z or a between clause) using an index would really suffer.
Hope this helps
February 25, 2005 at 7:17 am
I've seen big space usage drops when just going through tables and changiong "unique" indexes into clustered ones because of the removal of a leaf level
Creating a "unique" index in teh EM GUI is not the same as creating a PK by default.
DBCC INDEXDEFRAG is not as good as DBCC DBREINDEX generally but it's not usually and on-line operation
February 25, 2005 at 8:04 am
GBN, can you ellaborate? This is from BOL:
"Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns in the table. There are no significant differences between creating a UNIQUE constraint and creating a unique index manually. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or created manually. A unique index and a UNIQUE constraint cannot be created if there duplicate key values exist."
While a unique constraint and a PK are not the same, I expected that the unique non-clustered index automatically created over each would be handled the same way. Is that wrong?
February 25, 2005 at 8:20 am
From BOL "CREATE TABLE"
...
If CLUSTERED or NONCLUSTERED is not specified for a PRIMARY KEY constraint, CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.
...
If CLUSTERED or NONCLUSTERED is not specified for a UNIQUE constraint, NONCLUSTERED is used by default.
Correct: "There are no significant differences between creating a UNIQUE constraint and creating a unique index manually"
But declaring a PK is not the same declaring a UQ (constraint or index).
I have seen quite a few instances of this: yes a table may have a unique index but it may not have a clustered one.
I'd add a UQ after adding a PK if the design needed it, but I'd always have a PK. I may change the PK to non-clustered if it made sense
For example, our developers here click the "Unique" check box in the EM GUI to create an index, but not the key symbol after selecting columns.
If they also select "clustered" then you have a unique, clustered index... oh, a primary key by default.
I have save 20% disk space, decreased maintenance/backup windows and speeded application perforamnce by replacing lots of UQs with PKs - simply because of the default clustering
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply