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

Stored Procedure slower on faster newer machine ? Expand / Collapse
Author
Message
Posted Thursday, August 14, 2014 8:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:21 AM
Points: 7, Visits: 22
Greetings.

I'm puzzled.

I have written a stored procedure that I am reasonably happy with. It is run rarely so performance is not a major issue. It takes 2.5 minutes on my i5 4Gb SQL server 2012 11.0.3128.0 It's made up of a couple of recursive loops.

I have just purchase a new MSI all bells and whistles GS60 notebook with 16GB Ram 2 * 128GB SSD Drives an i7 processor and a pretty keyboard.

I installed the same SQL Server software off the same DVD.
Created and restored a backup from my old notebook and sat back expecting fire and brimstone to come out as the query ran. 6 minutes!. A little confused at this point.

Is there anyone out there that could point me in the direction to investigate.

I looked at the performance manager as it ran and it all seems to run on one core.

SQL Server seems to have 5GB RAM allocated . The entire database is less than 100MB

Cheers


Post #1603545
Posted Thursday, August 14, 2014 8:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 2,253, Visits: 6,171
It is obviously the keyboard

There are so many possibilities here that it's hard to say where to start. First thing is probably the laptop's power management, make certain it is running on full throttle.
Post #1603553
Posted Thursday, August 14, 2014 9:19 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 17,843, Visits: 15,792
First thing to check imho would be the power plan.

Next would be to check the various server settings and verify they match the previous.

And the list goes on from there.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1603558
Posted Thursday, August 14, 2014 9:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 1,255, Visits: 1,742
After the power plan and server settings (including any global trace flags), compare the execution plans and IO statistics
Post #1603559
Posted Thursday, August 14, 2014 10:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:21 AM
Points: 7, Visits: 22
I wondered about the power consumption of the keyboard, lots of flashing LEDS.

I took your advice and cranked up the power setting to "Performance" accepting the defaults for that setting.

Lo and behold it dropped down to 2 mins 40 secs. In addition it was now appearing to use all cores for processing

Please accept an elephant stamp.

I then did the obvious and took it back down to "balanced" to confirm that it was indeed a power setting that had done the trick and I still get 2.40.

A quick look at performance manager shows that it is still using most of the cores whereas before it was only using one.

I know performance tuning these beasts is a bit of a black art but ....

Thanks for your help. If you happen to have an explanation for what has switched this to using multiple cores and then staying there I'd appreciate a note.

I've shut SQL Server down completely and it is still utilising multiple cores.

Post #1603566
Posted Thursday, August 14, 2014 10:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:21 AM
Points: 7, Visits: 22
I still have both machines so i will do that over the weekend.

Thanks
Post #1603568
Posted Thursday, August 14, 2014 11:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 2,253, Visits: 6,171
Look into the MAXIMUM DEGREE OF PARALLELISM and COST THRESHOLD FOR PARALLELISM settings.
Post #1603576
Posted Friday, August 15, 2014 12:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:21 AM
Points: 7, Visits: 22
Looks like i have some home work to do

Thanks
Post #1603586
Posted Friday, August 15, 2014 7:49 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 17,843, Visits: 15,792
destino (8/14/2014)
I wondered about the power consumption of the keyboard, lots of flashing LEDS.

I took your advice and cranked up the power setting to "Performance" accepting the defaults for that setting.

Lo and behold it dropped down to 2 mins 40 secs. In addition it was now appearing to use all cores for processing

Please accept an elephant stamp.

I then did the obvious and took it back down to "balanced" to confirm that it was indeed a power setting that had done the trick and I still get 2.40.

A quick look at performance manager shows that it is still using most of the cores whereas before it was only using one.

I know performance tuning these beasts is a bit of a black art but ....

Thanks for your help. If you happen to have an explanation for what has switched this to using multiple cores and then staying there I'd appreciate a note.

I've shut SQL Server down completely and it is still utilising multiple cores.



Either the system feels that full use is warranted (which is a side effect from balanced) or it thinks it is still in high performance.

Is the cpu frequency still at 100% or does it continue to fluctuate (not the % used but the frequency).




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1603711
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse