February 12, 2004 at 9:24 am
In our PeopleSoft DB, we have a view that involves four tables, the largest of which has 1.5 Million records. When joined, the number of records in the view they comprise is over 100 Million. The table with 1.5 Million records in it is replaced every night by a transfer from our data warehouse. It does the replacement by using a Delete, then an Insert, so we can log the transaction.
I have a job that runs on the weekends that does a reindex of any index on any table that is over 5% fragmented. This runs, and has run successfully over the past weeks, but queries using this view have gotten slower. Checking the log for the reindex job, I saw only one table, of the four, that was fragmented more than 5%, the 1.5 Million record table, which was 50% fregmented.
Executing DBCC DBREINDEX ('PS_LA_OPR_DEPT',PS_LA_OPR_DEPT) - fragmentation currently 50% [SQLSTATE 01000]
This table is a 2 field table, containing voucher numbers and alpha-numbers Used IDs of 8 characters. There is one index on the table and that index is clustered.
I decided to rebuild all indexes on all of the four involved tables by dropping them and recreating them. This soultion, rather than the reindex, helps the speed issues by 500% or better. Queries that took over 3 minutes now ran in between 15 and 25 seconds.
I don't know why this drop and create worked better, since that's what the reindex is supposed to do, if I understand it correctly.
Anyone want to explain or speculate what's going on here?
Thanks,
Chris
February 13, 2004 at 8:48 am
Is it possible that the fillfactor changed when you did manual drop/re-create of the the indexes?
I hope we see ultimately see resolution on this post because otherwise I'll have to doubt my dbreindex-based maintenance, and it seems that only way you could tell the difference was perceived performance change (more difficult to assess than, say, reported fragmentation level).
February 17, 2004 at 7:41 am
Mike,
I don't think so. I scripted out the index from the table to use it in the job for recreation. I changed nothing. Here it is:
CREATE UNIQUE CLUSTERED INDEX [PS_LA_OPR_DEPT] ON [dbo].[PS_LA_OPR_DEPT]([OPRID], [DEPTID]) WITH FILLFACTOR = 90 ON [PRIMARY]
Thanks,
Chris
February 17, 2004 at 11:29 am
Last idea: maybe you used to have statistics that were out-of-date & inducing the optimizer to choose bad query plan, but these statistics were dropped when you dropped indexes manually?
My coworker, Wei, says he read an article on this in SQL Server Pro magazine a couple years ago. For statistics to that bad I think you'd either have to have autoupdate stats disabled, or else it the autoupdate stats was not working right.
At first I did not believe it would be possible for presence of statistics to hurt performance, but I guess I'm now convinced that it's possible. Do you know if you dropped the statistics "indexes" when you dropped & recreated indexes?
If no resolution to this, and you revert to old plan of using DBREINDEX, and then notice performance start to decay, how about some capturing query plans before and then again after your manual reindexing, might show you something?
Regardless, this is very interesting to me, and I use DBREINDEX for everything--so I'd like to hear what comes of this if you don't mind: mgood@spheris.com
February 17, 2004 at 12:37 pm
There isn't much wiggle room for me to test this out right now. The query that uses this view is running terribly, even after starting this index drop and recreate. That helped for a couple of days, but it is starting to seem like we are polishing a turd here.
Before you go changing you way of doing things, I must add another wrinkle to this problem. I didn't mention this before because I didn't, and still don't, think it should matter. The 1.5 million row table gets replaced every night. It is done by Delete and Insert so we have a log of the proceedings. I figure, since the records are deleted, then the index essentially gets rebuilt, in order, since it is clustered, every night.
Before the Index recreate job, on Thursday afternoon (Reindex job runs on Sunday)
DBCC SHOWCONTIG scanning 'PS_LA_OPR_DEPT' table...
Table: 'PS_LA_OPR_DEPT' (588138182); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 9955
- Extents Scanned..............................: 1262
- Extent Switches..............................: 1550
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 80.27% [1245:1551]
- Logical Scan Fragmentation ..................: 99.60%
- Extent Scan Fragmentation ...................: 14.42%
- Avg. Bytes Free per Page.....................: 795.3
- Avg. Page Density (full).....................: 90.17%
Morning of the next day, after the Drop and create index jobs ran. (the data delete and insert ran at 22:30 that night)
DBCC SHOWCONTIG scanning 'PS_LA_OPR_DEPT' table...
Table: 'PS_LA_OPR_DEPT' (588138182); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 9972
- Extents Scanned..............................: 1260
- Extent Switches..............................: 1259
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.97% [1247:1260]
- Logical Scan Fragmentation ..................: 0.07%
- Extent Scan Fragmentation ...................: 18.81%
- Avg. Bytes Free per Page.....................: 797.9
- Avg. Page Density (full).....................: 90.14%
I added Update Usage and Update Stats steps to this job, to be run after the index recreate. I will see if that helps in the morning.
Thanks,
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply