June 15, 2012 at 5:46 am
Until now, the obvious question has not been raised :
Is your SQLInstance on the latest service pack ? How to obtain ...
Select Serverproperty('ProductVersion') as ProductVersion
, Serverproperty('ProductLevel') as ProductLevel
, Serverproperty('IsClustered') as IsClustered
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 15, 2012 at 5:47 am
ALZDBA (6/15/2012)
Until now, the obvious question has not been raised....
Perhaps it's not that obvious then eh 😉
June 15, 2012 at 7:01 am
I have encouraging news! The CPU powersaving mode was enabled and setting this to High Performance mode did improve performance significantly on the new box.
Rather than running the app each time I have a basic powershell script that selects records in a loop and it was running at ~1400 selects/sec and now has hit just over 2000 /sec. I can't believe servers default to power saving mode!
While this news is encouraging, it is still not beating our 4 year old server which comfortably does 2400 selects /sec.
These tests are all being run locally on the box so network traffic can be ruled out.
I have been monitoring the basic perfmon (CPU, page faults, disk idle, disk queue, cache hit ratio, etc) nothing stands out showing the system is stressed. Profiler traces have a large impact on performance and dont tell me anything I dont know (although I may not be tracing the right thing!)
The app does use a connection string with a SQL login but does use connection pooling so I dont think this is having any tangable impact. Also I am doing my select test in powershell which is using 2008 provider.
Should I see anything in the Wait Types if these are very simple selects looking up on a clustered index, the entire database is in RAM anyway (cache hit ratio 100%) and the server is otherwise idle?
I am open to suggestions of what I need to look at in profiler or perfmon to better get an idea of what is causing the slowdown...
Thanks again for the help so far.
ProductVersion 10.50.2500.0
ProductLevel SP1
IsClustered 0
June 15, 2012 at 7:09 am
OK cool, glad you are getting there. The power saving is a bit of a joke, if people wanted a lower speed machine, surely they'd save the cash & buy a cheaper server as opposed to hamstringing a decent box...
On some server hardware (can't remember which models) you need to check the power saving in the bios as well as windows. The easiset way can be to download cpuz & see what speed the cpu's are running at.
Thanks
Dan
June 15, 2012 at 7:13 am
Good stuff, moving in the right direction.
Have you compared the execution plan for your PowerShell script executions on both servers? Are they the same?
June 15, 2012 at 7:22 am
I would certainly encourage a look at CPU-Z and compare the settings on the old server with the new, hyperthreading being one of them. Additionally, you mentioned faster disk but are you seeing that in the avg disk sec /read performance counter? Lastly, what hardware is the new server on. We recently purchased a higher end HP box that had some funky settings that needed to be adjusted and prior to those settings being made we had less than optimal IO performance on more disks than we had previously.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 15, 2012 at 7:59 am
:blush: regarding HP, I can only tell you should upgrade practically all drivers before throwing it into production.
We learned that the hard way when we were forced to move to that hardware.:sick:
Did you take care of all parallelism settings ?
e.g.
sp_configure 'cost threshold for parallelism' good ref: http://www.sqlservercentral.com/Forums/FindPost1135945.aspx
sp_configure 'max degree of parallelism'
Number of tempdb files and their size settings.
With your typical load, maybe those can help.
ps: Also keep in mind to set max server memory (MB), especially with x64 !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 15, 2012 at 9:21 am
chris-320654 (6/15/2012)
I have encouraging news! The CPU powersaving mode was enabled and setting this to High Performance mode did improve performance significantly on the new box.Rather than running the app each time I have a basic powershell script that selects records in a loop and it was running at ~1400 selects/sec and now has hit just over 2000 /sec. I can't believe servers default to power saving mode!
While this news is encouraging, it is still not beating our 4 year old server which comfortably does 2400 selects /sec.
These tests are all being run locally on the box so network traffic can be ruled out.
I have been monitoring the basic perfmon (CPU, page faults, disk idle, disk queue, cache hit ratio, etc) nothing stands out showing the system is stressed. Profiler traces have a large impact on performance and dont tell me anything I dont know (although I may not be tracing the right thing!)
The app does use a connection string with a SQL login but does use connection pooling so I dont think this is having any tangable impact. Also I am doing my select test in powershell which is using 2008 provider.
Should I see anything in the Wait Types if these are very simple selects looking up on a clustered index, the entire database is in RAM anyway (cache hit ratio 100%) and the server is otherwise idle?
I am open to suggestions of what I need to look at in profiler or perfmon to better get an idea of what is causing the slowdown...
Thanks again for the help so far.
ProductVersion 10.50.2500.0
ProductLevel SP1
IsClustered 0
Late to the party here, but here goes:
1) power plan would have been my first point.
2) assuming you verified ALL server and database settings are IDENTICAL as appropriate, or different where required, that takes a config issue out of the equation (maybe)
3) did someone install 32 bit SQL on new box? or 32 bit OS?
4) did someone install a collation different from old box?
5) you MUST do an aggregate-differential wait stats analysis and file IO stall analysis. without those you are hunting and pecking at best.
6) you are mistaken that profiling is harmful to the box. do it to disk and use Qure (AWESOME free product that wraps a very slick GUI around analytics I have been doing with scripts for 10+ years!!) to see if things are out of whack perf wise.
7) oh, check your entire networking stack to ensure that TCPChimney is disabled too
8) You have been at this for over a day now. I strongly encourage you to engage a good performance tuning consultant to help you out with this. There are SOOOO many things that could be at play here that a pro could zero right in on...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 15, 2012 at 11:58 am
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 18, 2012 at 4:31 am
We have a HP DL360 G7 something like this link:
except with 24Gb RAM and 2 disk Raid1 for the Log drive and 6 disk RAID10 for the Data.
As far as actual disk performance goes - I have done a full set of SQLIO tests to compare performance between the old and new server and the new server is between 2x and 4x faster across all types of operations so this has been ruled out.
The hardware is managed in a data centre so I don't have access to look at the bios - I may put a ticket in to get it looked at though if it is known that HPs have power saving settings in the bios.
The execution plan for the powershell executions are the same on old and new servers.
CPU-Z (and task manager) confirms that hyperthreading is enabled with 24 threads available to the OS.
Changing the parallelism will not have any effect since the query cost for the average query is less than 0.01.
For TheSQLGuru's points
1) Indeed!
2) Yep this was done.
3) No
4) No
5) Can you give me more info on this? I'm not that great with wait stats, do you have a query I can run?
6) 99% of the time I'd agree, but this server's performance centres around to how rapidly it can respond to single queries so I have noticed it does impact performance. Having said that, I will still check out Qure.
7) I did check and disable that - it didnt make any difference to performance though.
8) Over the years this App/DB has been tuned by several DBAs and more recently We had external consultants monitor the app and server only to determine that this application is "suitable for virtualisation". HA! But anyway, that wasn't specifically a tuning consultant, so that could be something we look at.
So, previously I reported that the old server was running the powershell script faster than the new server (which remains true). On Friday I re-ran my application tests and as it turns out the application itself is now faster on the new box by a decent margin. This is the end goal as far as I am concerned as we can now migrate. I will still follow up on any further performance suggestions as they come to light.
Thanks to all who posted!
June 18, 2012 at 7:34 am
chris-320654 (6/18/2012)
CPU-Z (and task manager) confirms that hyperthreading is enabled with 24 threads available to the OS.
I'd be curious how things perform with hyperthreading disabled.
If IO is 2x to 4x faster on the new box then you really need to start focusing on what you are waiting on. Paul Randal's blog post would be a good place to start with this.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 19, 2012 at 2:18 am
I will get hyperthreading disabled today to see how that goes. In the meantime, I cleared the wait stats, ran a few test jobs in the application and the significant wait types it retuned are:
CXPACKET 77%
LATCH_EX 20%
June 19, 2012 at 10:41 am
I re-ran my tests with hyperthreading disabled and there was no benefit at all. If anything times were very slightly slower for the single threaded powershell test, and noticeably slower for the application itself. CXPACKET remained the top wait stat at around 73% and LATCH_EX at 26%.
I know I can eliminate CXPACKET by increasing cost threshold for parallelism but this does affect other parts of the application which would run slower as a result. Is there anything else I can do to reduce this type of wait?
June 19, 2012 at 12:47 pm
chris-320654 (6/19/2012)
I re-ran my tests with hyperthreading disabled and there was no benefit at all. If anything times were very slightly slower for the single threaded powershell test, and noticeably slower for the application itself. CXPACKET remained the top wait stat at around 73% and LATCH_EX at 26%.I know I can eliminate CXPACKET by increasing cost threshold for parallelism but this does affect other parts of the application which would run slower as a result. Is there anything else I can do to reduce this type of wait?
Unless you test it. You'll never know!
chances are the plans you fear that might suffer the theshold being modified may even perform better !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 19, 2012 at 1:00 pm
ALZDBA (6/19/2012)
chances are the plans you fear that might suffer the theshold being modified may even perform better !
+1
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply