Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
 Posted - 2009-07-21 : 05:32:33
|
| I know this is the exact opposite of everything we do but:What's the best (quickest) way to completely trash an index on a particular table?I'm trying to test some different SQL under conditions where the statistics are not up to date with a highly fragmented clustered index.My current approach is to put some brand new indexes in place to test the best case and then shrink the db. That give me nice amounts of fragmentation (99%+ fragmentation) but it takes absolutely forever for bigger databases (for me that's about 12G so small potatoes for most of you I'm sure)Does anyone know a better / more selective way to fragment a clustered index that doesn't involve deleting or inserting data into the table?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-07-21 : 07:39:18
|
| Why don't you stick the index onto a small filegroup (along with a filler table that you can drop before the shrink) and then you just have to shrink that file instead of the entire DB. Unless the index in question is a significant portion of that 12 GB, should be faster than shrinking the entire DB.--Gail ShawSQL Server MVP |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-07-21 : 07:47:53
|
| Oh -- hadn't thought of that.Great idea.Thanks.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
Best (quickest) way to destroy an index