September 26, 2013 at 7:22 am
Hi,
Everyday I truncate a table of roughly 40 millions rows, populate it and re-create the indexes.
If I restart the SQL server just before, the process take a bit more then 1 hour. After a week, the same process takes 2hours. I can't explain why the time of execution is increasing that much.
I think this it the creation of the indexes that takes more time.
Any idea where I can look at?
Thanks
September 26, 2013 at 7:25 am
Maybe the system is more busy with other tasks when you run the process after a week?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 26, 2013 at 8:15 am
As far as I know, there is no process running at the same time.
September 26, 2013 at 8:23 am
Maybe after a reboot there aren't any users in your system, where without kicking them off they're creating disk i/o contention as well as lock contention during your index creations?
Either way, there's really nothing in a reboot itself that would decrease disk intensive processing time other than stopping/disconnecting other processes. There must be something else going on with the server during slower times...
September 26, 2013 at 9:29 am
This is strange.
September 26, 2013 at 9:31 am
Do the execution plans show any differences? What are the wait types?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2013 at 9:32 am
Rem70Rem (9/26/2013)
I think this it the creation of the indexes that takes more time.
Think? First thing you need to do is time the steps and see for absolute certain which step is taking longer. Otherwise you're guessing and flailing around blindly
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 27, 2013 at 3:43 am
GilaMonster (9/26/2013)
Rem70Rem (9/26/2013)
I think this it the creation of the indexes that takes more time.Think? First thing you need to do is time the steps and see for absolute certain which step is taking longer. Otherwise you're guessing and flailing around blindly
Yep! This. Best thing to do is measure and understand what's literally happening with the system rather than guess. When it's running long, what is waiting on, what blocks are occurring, what resources is it using? Answer all these questions and you'll answer, mostly, how to fix it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 27, 2013 at 4:35 am
Rem70Rem (9/26/2013)
If I restart the SQL server just before, the process take a bit more then 1 hour. After a week, the same process takes 2hours. I can't explain why the time of execution is increasing that much.
Rem70Rem (9/26/2013)
I think this it the creation of the indexes that takes more time.
i dont think above two operations are inter related or linked ... sql server doesnt create or rebuild the indexes unless they are set with startup task (even this is also strange option)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply