September 20, 2005 at 1:59 pm
I'm primarily an Oracle DBA assuming new SQL Server DBA responsibilities. We have a 14 GB SQL database which the business user has recently completed a large data purge. In the Oracle world, I would probably consider rebuilding all the indexes associated with those tables and possibly an export/import of the tables themselves to take care of the fragmentation and reset the high-water mark.
Can anyone recommend a similar follow-up step(s) when this situation occurs in the world of SQL Server?
Thanks in advance!
September 20, 2005 at 2:30 pm
yeah, you would wanna do the same in sql.
perform dbcc showcontig on your tables.
and tables w/ less than optimally compacted indexes.
you can either rebuild the indexes, or perform dbcc dbreindex
This article has some stuff you don't need, but it has definitions of these commands
http://www.sql-server-performance.com/tp_automatic_reindexing.asp
edit: http://www.sql-server-performance.com/rd_index_fragmentation.asp
September 21, 2005 at 7:52 am
Good stuff. Thanks very much!
September 21, 2005 at 2:17 pm
first things first though ... until the other activites are completed you would also do well to execute UPDATE STATISTICS on all tables (or at least the heavily deleted from tables) and then sp_recompile for all tables in the database. This way the optimizer would have access to the latest key/data distributions and rebuild the stored procedure execution plans accordingly. Yes fragmentation may be a 'killer', but out of date statistics and updated query plans will make your fragmentation owes seem like a 'walk in the park' on Sunday ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 21, 2005 at 3:04 pm
Just to confirm - you're saying I should run UPDATE STATISTICS and sp_recompile now? Then, when I have time in the near future, run the DBCC SHOWCONTIG and resolve the fragmentation issue. This makes perfect sense to me since users are still running queries today.
September 21, 2005 at 3:40 pm
Yes ! But in pairs (Update STATISTICS table, sp_recompile table). UPDATE STATISTICS is primarily read only (99%) the update is at the end of the process for each table in a few system tables. The sp_recompile will cause a few milleseconds delay on the first execution after the update statistics has completed.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 22, 2005 at 1:31 pm
Does UPDATE STATISTICS lock the table? I checked Books Online but did not see anything specific to locking.
September 22, 2005 at 1:37 pm
Not the user table. A few rows in a few system tables (to write the statistics for the optimizer) right at the end. It's really quick ... measure it in ms ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 22, 2005 at 2:01 pm
Thanks, Rudy. There's 230 user tables in this database. I'm not really sure which tables had data archived, so I'm going to have to update statistics for all of them. I believe I can just use sp_updatestats to perform this function. Would you agree? Followed by sp_recompile against each user table, of course.
September 22, 2005 at 2:19 pm
yup ... you mught want to even couple it with the 'undocumented' stored procedure sp_MSforeachtable
http://www.databasejournal.com/features/mssql/article.php/3441031
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 10, 2005 at 7:53 am
I performed the steps in this thread in an effort to alleviate the performance issue. The user states the issue is still present.
A couple of things to note:
1. Is there an issue with sp_updatestats not updating statistics on all indexes? If I run STATS_DATE against all sysindexes entries, there are quite a few indexes that have NULL for the date. I'm not concerned about the indexes in the system tables, just the ones in the user tables (probably over 100 are NULL). I can't seem to find anything similar between the indexes which did not have stats updated.
2. While running DBCC DBREINDEX against the indexes which had a scan density of < 95, I received 'torn page' errors on one of the indexes. I ran DBCC CHECKDB against the database received these errors:
Server: Msg 8909, Level 16, State 1, Line 4
Table error: Object ID 3670067, index ID 47739, page ID (1:1880377). The PageId in the page header = (46:3473458).
Server: Msg 8928, Level 16, State 1, Line 4
Object ID 119671474, index ID 255: Page (1:1880376) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 4
Object ID 119671474, index ID 255: Page (1:1880377) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 4
Table error: Object ID 130950064, index ID 2676, page (1:1880376). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 4
Object ID 391672443, index ID 0: Page (1:816398) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 4
Table error: Object ID 391672443, index ID 0, page (1:816398). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 4
Object ID 391672443, index ID 0: Page (1:1089614) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 4
Table error: Object ID 391672443, index ID 0, page (1:1089614). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 3670067)' (object ID 3670067).
I contacted our system administrator and he checked the event logs and we don't seem to have any hardware errors. So, I assume I just have corruption in the index. I'm thinking of just dropping the index and recreating it.
Anyone have any thoughts on the sp_updatestats concern, as well as the index recreation proposal? Is there anything else I should run to fix the performance issue following the large data purge? Thanks in advance.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply