asked on
How to improve the speed of a heavily fragmented table?
We have a SQL 2000 database with several tables. During a day these tables have LOTS of SELECT / UPDATE / INSERT commands. Our system is beginning to be VERY slow so I thought i would investigate why this could be. After a bit of reading i researched into table indexes. Worryingly one of the most searched tables had this DBCC SHOWCONTIG info;
ScanDensity 99.898
LogicalFrag 99.9999
ExtentFrag 82.5969
This was AFTER i did a DBCC DBREINDEX on the table, any ideas on how i can improve this?
ScanDensity 99.898
LogicalFrag 99.9999
ExtentFrag 82.5969
This was AFTER i did a DBCC DBREINDEX on the table, any ideas on how i can improve this?
Microsoft SQL ServerDatabases
Last Comment
ale1981
what are the values? are they gnerally strings? if do use ntext as a data type.
ASKER
Hi.. the values are generally a mix of char, datetime and integers.
If there is LOTS of modify activity, you will need to decrease the fillfactor (which increases the freespace) on the table. For example:
DBCC DBREINDEX (tableName, '', 65)
That will leave 35% freespace in every block. It will increase the size of the table on disk, and increase the time to do a full scan of the table, but will dramatically speed up modification activities.
It often takes some trial and error to come up with the optimum fillfactor.
DBCC DBREINDEX (tableName, '', 65)
That will leave 35% freespace in every block. It will increase the size of the table on disk, and increase the time to do a full scan of the table, but will dramatically speed up modification activities.
It often takes some trial and error to come up with the optimum fillfactor.
ASKER
How can I check the current fill factor?
Will reducing the fill factor increase or decrease the SELECT speeds?
Will reducing the fill factor increase or decrease the SELECT speeds?
To check current fillfactor:
SELECT OrigFillFactor
FROM sysindexes
WHERE OBJECT_NAME(id) = 'YourTableName'
AND indid = 1
Depends on how the SELECTs work.
If SELECT pulls a specific matching row from an index, the time should be the same.
If SELECT pulls a range of rows, then it will take longer; the more rows, the longer the increase in time.
SELECT OrigFillFactor
FROM sysindexes
WHERE OBJECT_NAME(id) = 'YourTableName'
AND indid = 1
Depends on how the SELECTs work.
If SELECT pulls a specific matching row from an index, the time should be the same.
If SELECT pulls a range of rows, then it will take longer; the more rows, the longer the increase in time.
ASKER
I see, that SELECT command did not return a result for OrigFillFactor?!
SELECT OrigFillFactor
FROM sysindexes
WHERE OBJECT_NAME(id) = 'SOP30200'
AND indid = 1
SELECT OrigFillFactor
FROM sysindexes
WHERE OBJECT_NAME(id) = 'SOP30200'
AND indid = 1
OK, the table does not have a clus index. Use this to find the other index(es).
SELECT name AS [Name of Index],
CASE indid
WHEN 0 THEN 'Heap (main table, no index)'
WHEN 1 THEN 'Clustered index'
WHEN 255 THEN 'Text/Image data'
ELSE 'Non-clus index' END AS [Type of Index],
OrigFillFactor
FROM sysindexes
WHERE OBJECT_NAME(id) = 'SOP30200'
SELECT name AS [Name of Index],
CASE indid
WHEN 0 THEN 'Heap (main table, no index)'
WHEN 1 THEN 'Clustered index'
WHEN 255 THEN 'Text/Image data'
ELSE 'Non-clus index' END AS [Type of Index],
OrigFillFactor
FROM sysindexes
WHERE OBJECT_NAME(id) = 'SOP30200'
ASKER
Ok, when running that statement i get LOTS of index's, all have a Fill Factor of 90 apart from the first one;
Name of Index Type of Index OrigFillFactor
SOP30200 Heap (main table, no index) 0
Name of Index Type of Index OrigFillFactor
SOP30200 Heap (main table, no index) 0
90 is the default. I would reduce them to 80 for the next rebuild and see if things improve.
Btw, if you use maintenance plans, make sure the option to change freespace is NOT set on [set that option to: leave original freespace] -- in fact, the automatically change fs option should NEVER, EVER be set on, because it could overwrite tuning done on individual tables. Repeat: NEVER set that option on, and anyone who tells you should do so is WRONG, WRONG and more WRONG.
Btw, if you use maintenance plans, make sure the option to change freespace is NOT set on [set that option to: leave original freespace] -- in fact, the automatically change fs option should NEVER, EVER be set on, because it could overwrite tuning done on individual tables. Repeat: NEVER set that option on, and anyone who tells you should do so is WRONG, WRONG and more WRONG.
ASKER
Thanks Scott,
I will reduce them to 80 and get back to you with results.
I will reduce them to 80 and get back to you with results.
ASKER
I have reduced the tables to 80, but it has not seemed to speed up the access / read times.
Any other ideas?
Any other ideas?
So you created a clus index with a ff of 80 and then dropped it?
ASKER
Hi Scott, no I did a DBREINDEX with a ff of 80?
Defragging a non-clus index will compact only the index itself, not the base table data.
To compress the base table, you must create a *clustered* index. You can do it on the same column(s) as the main non-clus index. Then drop the clus index.
This may take time depending on the size of the table and the number of indexes, because all indexes have to be rebuilt when a clus-index is added or removed.
To compress the base table, you must create a *clustered* index. You can do it on the same column(s) as the main non-clus index. Then drop the clus index.
This may take time depending on the size of the table and the number of indexes, because all indexes have to be rebuilt when a clus-index is added or removed.
ASKER
Thanks for the info Scott, how would I go about doing that?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks I will give this a go and get back with my progress!