Very bad performance on a good HP DL380 Gen9 Server

Question

Post reply

Very bad performance on a good HP DL380 Gen9 Server

lensmithjr

Mr or Mrs. 500

Points: 583
More actions
October 6, 2016 at 6:56 am

#330332

This issue is something that I haven't had much luck trying to solve via on-line research, and was hoping to find a hit on some forums out there.
I have an HP DL380 server that has 3 physical CPUs, 4 cores each. 32 GB RAM. Four HP MM1000FBFVR drives set up in a RAID 5 configuration. There are 4 1 GB network cards in the back, I'm not sure of the model, but we are using only one, with the jumbo mtu set to 9000, the max.
The OS is Windows Server 2012 R2, and I have SQL Server 2014 Standard edition on it.
I have a set of jobs that I am running on my development machine, a Lenovo with 32 GB of RAM, 1GB network card, and a Blue Western Digital hard drive, 1TB. The OS is Windows 7 Pro.
The problem that I am trying to diagnose is that the same jobs are taking up to 4 times longer on the production machine than on my development box, and that just isn't making sense to me. The network connections are the same speed, and the disc I/O on the production machine is at most 1/2 and often 1/3 of my development box.
I am wondering if it is because it is a standard edition of SQL? I didn't do anything out of the ordinary when I installed it. Could it be the RAID 5? Should I set it up as RAID 10? Any help or tips would be appreciated.

Viewing 15 posts - 1 through 15 (of 18 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

The Guru's Tuning Big 3:

sp_whoisactive

differential file IO stall analysis

differential wait stats analysis

I note that sp_whoisactive can do a differential run itself with a parameter setting. One or more of those will point to why the server is performing poorly.

I also note that 32GB on a server is just silly these days, especially for what you call a "good HP DL380 Gen9" box.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ankur_libra Hall of Fame Points: 3999 More actions · Answer 2

Hi,

Please also monitor following on databases involved:-

1. Index Fragmentation

2. Statistics Update

3. Blocking to ensure that jobs are not being blocked by any oher process

4. I/O & Waitstats (As already suggested by TheSQLGuru)

5. Check SQL Server Error log for any errors or info captured.

Regards,

Ankur

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

It's really down to identifying the pain points. Figure out which query or queries are running slower. Why are they running slower? Fix that. First blush, it sounds like a configuration or settings issue, but really hard to say without a lot more information.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

lensmithjr Mr or Mrs. 500 Points: 583 More actions · Answer 4

Hi Kevin,

Thanks for the ideas. I am going to try some of these out when I get the server back on-line. I have also created a second data RAID with 15K drives to see if that helps performance.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

ankur_libra (10/6/2016)
Hi,
Please also monitor following on databases involved:-
1. Index Fragmentation
2. Statistics Update
3. Blocking to ensure that jobs are not being blocked by any oher process
4. I/O & Waitstats (As already suggested by TheSQLGuru)
5. Check SQL Server Error log for any errors or info captured.
Regards,
Ankur

Item #1 might not be a concern. I've not done any index maintenance to my big server since 17 Jan 2016 and performance has never been better. Part of the reason why I did the experiment was because of the blocking that occurred every Monday morning after the Sunday night index maintenance (your #3 above).

Item #2 is incredibly important. We recently had an episode to prove it. One of our team loaded 200,000 rows into a fairly large table. It wasn't enough auto trigger an automatic update of the stats. His query ran against the table for an hour and never did a single read. I updated stats and it ran correctly in about 2 seconds. And, no, I didn't do anything to the indexes on the table. Just updated stats.

Both Brent Ozar and Kendra Little have written and done videos on the subject of NOT doing index maintenance. I thought they were both drinking bong water and don't know if either of them drank their own Kool-Aid in this area but it worked for me and you could actually see the performance improve over the first two weeks. CPU, File reads, and File writes all decreased substantially. CPU dropped about 10 points across 32 CPUs to an average of 3-5% even during the busiest times of the day. Batch jobs also decreased in duration.

With the server I've done this to weighing in at only 2.2 terabytes, it might not be for everyone but it really worked a treat for my stuff in both the OLTP and the Batch Job areas. It gives me more time on my nightly schedule to rebuild stats that need it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

lensmithjr (10/6/2016)
Hi Kevin,
Thanks for the ideas. I am going to try some of these out when I get the server back on-line. I have also created a second data RAID with 15K drives to see if that helps performance.

I hate to suggest this because it's usually not the problem right after a migration (but will be, day to day) but have you tried rebuilding stats?

Also, has the new machine been setup to use more than just the default amount of memory for SQL Server?

Have they setup any virus scanning packages to ignore MDF and LDF files and SQL Server in general? If not, that needs to be done.

Last but not least (might be the most important, in fact), do they have a "power saving mode" turned on? Contrary to popular belief, it doesn't ramp up as well as most people might think. SQL Servers need to be in the "maximum performance mode".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lensmithjr Mr or Mrs. 500 Points: 583 More actions · Answer 7

Hi Jeff,

I haven't done anything with statistics yet.

I have set the server up to let SQL Server use up to 90% of the memory.

I haven't checked the anti-virus settings yet.

I did make sure that the power saving mode was set to maximum performance.

Thanks for the ideas.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 8

First thing, use a schema comparsion tool to confirm that all the indexes and code changes existing in development have been deployed to production.

Microsoft SQL Server Data Tools

https://msdn.microsoft.com/en-us/library/hh272690(v=vs.103).aspx

Open DBDiff

http://opendbdiff.codeplex.com/

RedGate SQL Compare

https://www.red-gate.com/products/sql-development/sql-compare

Also, run a sample procedure call in SSMS, both in Dev and Production, with 'Include Actual Execution Plan' enabled, so you can determine if there is any significant difference in the execution plan being used.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

lensmithjr Mr or Mrs. 500 Points: 583 More actions · Answer 9

I have been using SQL Compare to deploy the database changes to production, so they are the same schemas.

We have installed a smaller set of 146 GB 15K drives in RAID 10, and the performance is now markedly faster than my development machine. So I think we have found our bottleneck.

PiMané SSChampion Points: 13965 More actions · Answer 10

lensmithjr (10/7/2016)
I have been using SQL Compare to deploy the database changes to production, so they are the same schemas.
We have installed a smaller set of 146 GB 15K drives in RAID 10, and the performance is now markedly faster than my development machine. So I think we have found our bottleneck.

I've had some issues with RAID5 disks...

They have very slow writes and if tempdb is in a RAID5 array then forget it....

It totally kills the IO... Even RAID1 is better since it has no parity check.

If you need to work better, try working less...

vliet SSCommitted Points: 1998 More actions · Answer 11

Check the power settings of your server. We've had a simular issue in the past. Changing the power settings from balanced to high performance nearly doubled its SQL processing speed. Sorry for the short comment, but I'm in a hurry.

wtren SSC Eights! Points: 806 More actions · Answer 12

what's your desktop database version, some time sql developer version have more optimize options than standard version.

lensmithjr Mr or Mrs. 500 Points: 583 More actions · Answer 13

lensmithjr

Mr or Mrs. 500

Points: 583

October 21, 2016 at 10:58 am

#1908029

Both versions are SQL Server 2014 Standard.

Dird SSCertifiable Points: 5081 More actions · Answer 14

TheSQLGuru (10/6/2016)
I also note that 32GB on a server is just silly these days

??

Dird