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 |
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2005-11-11 : 10:46:54
|
This:DBCC SHOWCONTIG (myTable) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS Produces:ObjectName ObjectId IndexName IndexId Level Pages ExtentSwitces ScanDensity BestCount ActualCount LogicalFragmentation-------------- -------------- ---------------------- ------ ------ ------ -------------- ---------------------- -------------- -------------- ----------------------myTable 1415676091 PK__myTable__48BAC3E5 1 0 436872 55298 98.75223783431889 54609 55299 4.0515299886465073E-2 myTable 1415676091 col1_myTable 2 0 41712 5287 98.600605143721637 5214 5288 0.05514000728726387 myTable 1415676091 col2_myTable 3 0 41649 5291 98.393801965230537 5207 5292 7.2030544281005859E-2 myTable 1415676091 col3_myTable 4 0 72749 58940 15.428988310344243 9094 58941 41.612941741943359 myTable 1415676091 col4_myTable 5 0 82110 43084 23.822676105373102 10264 43085 30.924369812011719 myTable 1415676091 col5_myTable 6 0 64022 8120 98.546976973279158 8003 8121 4.0611039847135544E-2 myTable 1415676091 col6_myTable 7 0 63179 8024 98.417445482866043 7898 8025 5.2232544869184494E-2 myTable 1415676091 col7_myTable 8 0 48329 6111 98.854712041884824 6042 6112 1.2414906173944473E-2 myTable 1415676091 col8_myTable 9 0 108628 67448 20.132248068911327 13579 67449 35.917076110839844 myTable 1415676091 IX_myTable_col9 10 0 141927 68674 25.833272661084823 17741 68675 31.067378997802734 myTable 1415676091 IX_myTable_col10 84 0 78456 43156 22.724007692842413 9807 43157 27.853828430175781 myTable 1415676091 IX_myTable_col11 85 0 63004 42664 18.460096097503808 7876 42665 34.367660522460937 Which I am reasonably confident means bad things.It took over a minute to isolate records created on a given day (only 144 records) in the most basic select statement.This table has records added to it throughout the day.We run an index defrag everynight. But this doesn't seem to cut it. Will removing and rebuilding the indexes help? Its a big table, so I don't want to get into that if I can help itCorey Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."  |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-11-11 : 10:52:59
|
| The fragmentation looks good (which you would expect from a nightly rebuild of the indexes). What does the query plan look like? I assume the SARGs are all index-friendly? Is a minute normal for that query, or does it run faster at other times of day?That's one big table. |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-11-11 : 11:10:27
|
| Here's the rest of the story:we have a query that runs on this table (looking at a createddate column), when it is run for a single day it works, but when you run over a week or a month, it takes ten forevers.I would expect that it would be slower for a month period, but not exponentially slower...are the ones in red not a problem??CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-11 : 12:11:59
|
| "We run an index defrag everynight. But this doesn't seem to cut it"Might it be bailing out because the table [or large swathes of it] is in use?You run it "regardless" every night? You might want to move to a policy of only running it where the DBCC SHOWCONTIG indicates it needs (FWIW we log DBCC SHOWCONTIG to a "logging table", and run REBUILD on small tables and DEFRAG on the big ones, where the fragmentation is over some "threshold". We then have the "logging table" to refer to to see how often and which tables are getting hammered, and how their fragmentation improves/deteriorates with time.One thing I've come across which is a nuisance is if the database has been backed up with maintenance Plan at any time in its life. The default option, in the Maintenance Wizard, is to defrag the "free space" at 10%, rather than leaving it at the initial index build setting. So all your intentional 100% Fill indexes get reset to 90%, and for clustered indexes on an IDENTITY column that's a bit of a waste of time & space! and leads to DEFRAG / REINDEX moving shed loads more stuff around than is needed at 100% I expect.Kristen |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-11-11 : 13:23:01
|
| Which index(es) does this query use? |
|
|
|
|
|
|
|
|
Its SO SLOW!