Stored Procedure slower on faster newer machine ?

Question

Stored Procedure slower on faster newer machine ?

destino

Say Hey Kid

Points: 677
More actions
August 14, 2014 at 8:09 pm

#289050

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

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.

😎

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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[/url]
Learn Extended Events

Andrew G SSChampion Points: 12809 More actions · Answer 3

After the power plan and server settings (including any global trace flags), compare the execution plans and IO statistics

destino Say Hey Kid Points: 677 More actions · Answer 4

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 Say Hey Kid Points: 677 More actions · Answer 5

I still have both machines so i will do that over the weekend.

Thanks

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 6

Look into the MAXIMUM DEGREE OF PARALLELISM and COST THRESHOLD FOR PARALLELISM[/url] settings.

😎

destino Say Hey Kid Points: 677 More actions · Answer 7

destino

Say Hey Kid

Points: 677

August 15, 2014 at 12:52 am

#1738292

Looks like i have some home work to do

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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[/url]
Learn Extended Events