April 22, 2011 at 2:31 pm
Greetings all.
I have a weekly process that updates statistics and rebuilds indexes on on my tables just to make sure everything is fresh and up to date (this is a datawarehouse SQL Server 2008 64 bit). I noticed after a while there were significant performance issues with my nightly ETL after doing this however. I turned this off a few weeks ago and noticed my performance is not taking hits anymore.
One in particular is our sales order table. Through indexes and better programming I got the ETL to run in 3 minutes vs 15 minutes (yes all data has been validated on a nightly basis to be loading correctly). Today, I moved the clustered index to a different filegroup and back (was testing something else) to the original. That was ALL that was done. Now I notice the ETL is taking 10 minutes to load instead of the 3 minutes.
My question is, what am I missing? Why, if I do anything with the indexes, does everything become a problem?
Link to my blog http://notyelf.com/
April 22, 2011 at 3:52 pm
So I solved my own problem by rebuilding the other non clustered index on the table. Not sure why that was needed I just did it on a hunch 🙂
My guess since non clustered indexes point to pages on the clustered index, is if the clustered index is modified then the non clustered index needs to be updated.
Link to my blog http://notyelf.com/
April 22, 2011 at 10:23 pm
Every index should be rebuild or reorganize as per requirement,you can check the avg_frag_percentage in a dynamic management function
SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID('DATABASE NAME'),,NULL,NULL,NULL,NULL);
When we Rebuild indexes when avg_frag_percentage > 30
When we Reorganize indexes when avg_frag_percentage < 30
These are only practices
Rebuild as shown in the meaining full word REBUILD its mean rebuild a index
Reorganize as only organize the data in the pages
when DML operation perform like update,insert,delete on a particular table then indexes are fragmented of this table then there is a need of rebuild or reorganize these fragmented pages
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA |OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply