February 3, 2010 at 10:44 pm
I have a DTS Package which normally used to run for 30 min now takes 7hours to run, when I had a look at the fragmentation of the table they were badly fragmented, The Scan Density was below 30%, I reindexed all the table Indexes with the command : DBCC DBREINDEX (table_name)
Which brought the scan density to 100% or 99. %. This reduced the time limit of the package drastically brought it back down to 30 min. But 4 days after the table was fragmented again, this was never the case before could someone please assist and explain whats going on, I need to explain thin in an SGA meeting soon and I'm not sure whats going on.
February 3, 2010 at 10:53 pm
Faiyaaz (2/3/2010)
I have a DTS Package which normally used to run for 30 min now takes 7hours to run, when I had a look at the fragmentation of the table they were badly fragmented, The Scan Density was below 30%, I reindexed all the table Indexes with the command : DBCC DBREINDEX (table_name)Which brought the scan density to 100% or 99. %. This reduced the time limit of the package drastically brought it back down to 30 min. But 4 days after the table was fragmented again, this was never the case before could someone please assist and explain whats going on, I need to explain thin in an SGA meeting soon and I'm not sure whats going on.
What is the DTS package doing? Is it loading a lot of data into a table. Which table is getting fragmented, the source or target? Generally, it is a good practice to drop indexes on the target table(s) load the data and then once the data is loaded recreate the indexes.
February 3, 2010 at 11:02 pm
If this hasn't happened in the past, what has changed?
Also, what is the table structure and what is the clustered index?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 11:34 pm
This table is getting data loaded into t from a DB2 database, yes its a target table
February 3, 2010 at 11:40 pm
Is the table wiped out, or is the DB2 data just added to the existing records in the destination table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2010 at 11:56 pm
Yes it does delete records, but it deletes records for the current month, and then reloads it
Amount of records deleted for current month = +- 110000 records
February 4, 2010 at 12:01 am
Are the records being deleted sequential? And the records being loaded, are they sequential?
The delete and reload is fragmenting your clustered index and other indexes. As the table grows, the impact will become more noticeable. Some of this might be less of an impact if the clustered index is changed. It would depend on where your clustered index resides and the datatype of that column. There will still be an impact.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 12:28 am
Thanks I actually told them that, but how is it possible that 4 days later after a defragged the fragmentation occurs again. Does defragmentation have to be done daily which is im-possible considering the aount of time it takes to defrag indexes esp on large tables
February 4, 2010 at 6:58 am
How much data is there? You could try creating a clustered index on date/month column and then when you load the data for the current month, ensure that the data Order By on the date/month column in the source query.
Since you are deleting the current month's data, i think the clustered index would help.
February 4, 2010 at 8:49 am
vstitte (2/4/2010)
How much data is there? You could try creating a clustered index on date/month column and then when you load the data for the current month, ensure that the data Order By on the date/month column in the source query.Since you are deleting the current month's data, i think the clustered index would help.
This is the same direction I was headed - wanted to know the clustered index first though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 4, 2010 at 4:15 pm
Drop Index on destination table then rebuild index then load data, Rebuilding Index takes lesser time than defrag. If you want you can do rebuild index online so that it doesn't take lock on the tables, you cannot perform Index rebuild only if the index on the columns are Large object data type columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml . Best way is create a job for this DTS package with
Step 1 Rebuild Index,
Step 2 Load Data,
Keep the fill factor 60 - 70% if you are updating table often, which is another option to reduce fragmentation on the tables.
Enjoy!
February 4, 2010 at 10:16 pm
Thanks alot fo everyones response I really appreciate it, just one last thing to increase my knowledge, You said add the fill factor to 60 -70, can you please let me know wht is the fill factor used for.
February 5, 2010 at 11:15 am
The fill factor is for the pages. How much data should be placed on the page before creating a new page. If you fill every page 100%, you will likely see more page splits and quicker fragmentation. The flip side is that if you don't fill your pages very much, you will increase the number of pages and thus potentially decrease performance.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply