Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored Procedure slower on faster newer machine ?


Stored Procedure slower on faster newer machine ?

Author
Message
destino
destino
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 57
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.Hehe

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
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6820 Visits: 17766
It is obviously the keyboard:-P

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.
Cool
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21143 Visits: 18259
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

Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 2230
After the power plan and server settings (including any global trace flags), compare the execution plans and IO statistics
destino
destino
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 57
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.:-D

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.
destino
destino
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 57
I still have both machines so i will do that over the weekend.

Thanks
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6820 Visits: 17766
Look into the MAXIMUM DEGREE OF PARALLELISM and COST THRESHOLD FOR PARALLELISM settings.
Cool
destino
destino
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 57
Looks like i have some home work to do

Thanks
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21143 Visits: 18259
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.:-D

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search