April 23, 2009 at 3:16 am
I have a table that has over a million rows. Every night this table is truncated and repopulated.
It has a primary key constraint on an id and a non clustered index on a product field.
Now these indexes show these results the morning after the table has been butchered -
- Pages Scanned................................: 248209
- Extents Scanned..............................: 31138
- Extent Switches..............................: 207463
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 14.96% [31027:207464]
- Logical Scan Fragmentation ..................: 30.42%
- Extent Scan Fragmentation ...................: 2.59%
- Avg. Bytes Free per Page.....................: 3468.6
- Avg. Page Density (full).....................: 57.15%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
- Pages Scanned................................: 4213
- Extents Scanned..............................: 531
- Extent Switches..............................: 2488
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 21.17% [527:2489]
- Logical Scan Fragmentation ..................: 19.89%
- Extent Scan Fragmentation ...................: 32.96%
- Avg. Bytes Free per Page.....................: 2197.4
- Avg. Page Density (full).....................: 72.85%
The table is reindexed every night before the job runs. I have tried a defrag that improves the scan density by a few percent but this is mainly based on removing unused pages.
I have tried dropping and recreating the indexes at the start of the job.
Any other ideas? Maybe drop and recreate the table rather than truncating?!
All in all I'm trying to improve the speed of this overnight job and I don't think the indexes are helping!
April 23, 2009 at 7:24 am
Shark Energy (4/23/2009)
The table is reindexed every night before the job runs....
I have tried dropping and recreating the indexes at the start of the job.
Re-indexing BEFORE you truncate and re-load the table is not going to provide any benefit. Neither is dropping and re-creating the indexes at the start of the job, although you are close here. You want to drop the indexes at the start of the job and then re-create them AFTER re-loading the table. Try that and see if it helps. If not add to this thread.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 23, 2009 at 7:52 am
Good call!
*slaps forehead*
April 24, 2009 at 3:24 am
Made change. The process Drops indexes. Truncates table. Populates table. Adds indexes.....THEN updates table (hence I want the indexes in) but then populates it with MORE data. Maybe I should try and juggle that around better...
Anyway latest figures -
- Pages Scanned................................: 248379
- Extents Scanned..............................: 31164
- Extent Switches..............................: 207321
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 14.98% [31048:207322]
- Logical Scan Fragmentation ..................: 29.89%
- Extent Scan Fragmentation ...................: 2.85%
- Avg. Bytes Free per Page.....................: 3469.2
- Avg. Page Density (full).....................: 57.14%
- Pages Scanned................................: 4717
- Extents Scanned..............................: 596
- Extent Switches..............................: 2518
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 23.42% [590:2519]
- Logical Scan Fragmentation ..................: 17.64%
- Extent Scan Fragmentation ...................: 27.18%
- Avg. Bytes Free per Page.....................: 2824.7
- Avg. Page Density (full).....................: 65.10%
Hmmmmm....
April 30, 2009 at 1:19 pm
What fill factor are you using?
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
April 30, 2009 at 1:25 pm
If there are too many changes in those tables then may be u can consider having fill factor of 75 to 80, any number that best suites ur purpose.
September 30, 2009 at 11:26 pm
how can i reduce the scan density on my tables..pls help me out any command for that
February 2, 2010 at 12:16 pm
Follow these steps please.
The process Drops indexes.
Truncates table.
Populates table.
updates table
populates it with MORE data.
Finally Adds indexes.
Mean you should create/build indexes in the last step.
Thanks
Irfan
February 2, 2010 at 3:18 pm
Good point - but surely better to put the indexes back in before the updates so that SQL can take advantage of the indexes in the main table when joining with the tables it updates from?
February 2, 2010 at 3:47 pm
No,It will slow down the update process if you will put indexes before update.
If tables are small then ok otherwise always try to drop the indexes if you are going to make big update and then re-create them again.
Thanks
Imran
February 3, 2010 at 2:27 am
Can you explain your reasoning? My way of thinking is that if the process is updating based on joins to other tables, and the relevant fields in the update table are indexed, then it will be faster. If there are no indexes, then the joins will slow it down dramatically. If you think otherwise let me know why and then I will test it! Thanks
February 3, 2010 at 7:19 am
I think we have some misunderstanding.
Can you please make it clear that for example there are two tables abc and xyz and they have joins.
here i am not clear...
table abc needs to update or table xyz?
which table you think should have indexes before update?
Do you mean if abc is getting update and abc should have indexes or you mean xyz table joing column should have index which you are using just for joining?
February 3, 2010 at 7:53 am
We have a table - Lets call it "SalesData"
We remove the indexes
We populate this table with data from tables A,B and C
We then add the indexes back on to fields 1, 2 and 3
We then update SalesData with data from D, E and F (that are already indexed and we are joining to) based on a where clause using fields 1, 2 and 3.
Does that make more sense?
I'm thinking, without the indexes we would have
Remove indexes
Populate SalesData
Update SalesData with data from D, E and F using where clause on fields 1, 2 and 3 but with no indexes we get table scans for those statements.
We then add indexes on.
February 3, 2010 at 9:14 am
So, I believe you have something like:
A) Truncate
B) Insert
C) Update
D) More Insert
E) More Update
The most important thing is to _try things_ and benchmark how long they take, and look at your indexes after. Maybe it's better to create the indexes after the last step that modifies that field, and maybe it's better to create the indexes with one FILLFACTOR before the first step using them in a JOIN or WHERE, allow them to be fragmented by updates/inserts, and then DBREINDEX/ALTER INDEX REBUILD them with a different FILLFACTOR after their last usage.
You should probably make sure that B) inserts in the order of the clustered key (in which case it probably doesn't matter much if the clustered key is put on before or after). Unless D) inserts and sequential and contiguous and at the end, you're going to fragment there.
All fields used in JOINs and WHEREs for C through E should probably be indexed prior to the statement that uses them.
Consider (and benchmark) creating early with FILLFACTORs based on the rest of this routine, and then rebuild with a different FILLFACTOR based on estimated usage until the next time this routine is done.
Also, depending on your system as a whole, consider popping up SQL Server Profiler and letting it run through your whole routine, so you can see how many Reads, CPU, Writes, duration, or whatever's your bottleneck you save, or don't save, for each option.
February 3, 2010 at 12:35 pm
As Nadrek said create indexes on last step is correct way.
He gave good suggestion.
Thanks
Irfan
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply