Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance issues SQL 2012 Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 7:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:43 PM
Points: 215, Visits: 695
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
Post #1498828
Posted Thursday, September 26, 2013 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 13,279, Visits: 10,153
Maybe the system is more busy with other tasks when you run the process after a week?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1498829
Posted Thursday, September 26, 2013 8:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:43 PM
Points: 215, Visits: 695
As far as I know, there is no process running at the same time.
Post #1498875
Posted Thursday, September 26, 2013 8:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2014 12:32 PM
Points: 22, Visits: 143
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...
Post #1498881
Posted Thursday, September 26, 2013 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 1:43 PM
Points: 215, Visits: 695
This is strange.
Post #1498926
Posted Thursday, September 26, 2013 9:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 42,460, Visits: 35,520
Do the execution plans show any differences? What are the wait types?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1498927
Posted Thursday, September 26, 2013 9:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 42,460, Visits: 35,520
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1498929
Posted Friday, September 27, 2013 3:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1499281
Posted Friday, September 27, 2013 4:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #1499302
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse