SQL Clone
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
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 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
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93835 Visits: 20670
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 Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144341 Visits: 18651
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
Learn Extended Events

Andrew G
Andrew G
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7927 Visits: 2345
After the power plan and server settings (including any global trace flags), compare the execution plans and IO statistics
destino
destino
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 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
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

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

Thanks
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93835 Visits: 20670
Look into the MAXIMUM DEGREE OF PARALLELISM and COST THRESHOLD FOR PARALLELISM settings.
Cool
destino
destino
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

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

Thanks
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: General Forum Members
Points: 144341 Visits: 18651
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
Learn Extended Events

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