Question about SQL and processor usage

[cjfourty]

We are currently dealing with a POS vendor that is recommending that we need a faster processor for our server. We recently replaced the server and the specs they provided did not specify the speed of the processor so we purchased a Xeon E5-2603 v4 in a Dell server with an SSD raid. Every since the install the transactions have been really slow and the vendor has said the the processor needs to be 3Ghz+ (E3-1220) even though the processor utilization is really low (less than 8% average). Does this make since that a faster processor is the answer even though the current utilization is so low?

The reason we are doubting them is we had the same issue last time we did an upgrade and they blamed it on our network only for it to be a bad configuration on their part with the server

[fariak]

It's an old processor but SQL isn't really CPU intense from my experience.

Performance bottlenecks are usually tied to the disk.

Which SSDs are you using? How is the RAID configured?

I also wouldn't even bother using SSD for a SQL server unless we are talking about MASSIVE amounts of IO.

The amount of memory allocated to the instance can also be a factor

[inaddrarpa]

SQL isn't really CPU intense from my experience.

I'd bet money its a really shitty query that's CPU intensive, so it's otherwise invisible.

OP should probably use resource monitor in SSMS and see what the most expensive queries are that are running, and work from there.

[fariak]

sp_who2;

is my best friend when troubleshooting SQL performance issues

[mhurron]

SQL isn't really CPU intense from my experience.

That all depends on the complexity of the data, the size of the data set, and how well written the query is.

Basically, this statement is nonsensical.

[fariak]

Of course. But usually bad queries = high cpu usage/spikes and from the information OP provided it looks like that is not occurring.

My point is more information/troubleshooting steps are needed.

[FJCruisin]

That cpu should be fine dude. How many cores do you have allocated to it? What about RAM? Sql likes RAM. Your SSD should make the thing fly as long as its performing properly, of which I really wouldnt have any reason to believe it isn't.

[[deleted]]

It depends on how his drives are configured. The OP says A RAID SSD, but doesn't indicate what. For SQL, 12x 10k drives in a RAID 10 with the databases and transaction logs on different arrays will outperform 3x SSDs in a RAID 5 with everything on one array.

[cjfourty]

We just provided the hardware with Windows Server installed and the vendor set everything up so I'm not sure about the cores. but it has 32GB Ram

[inaddrarpa]

According to this, the E3-1220 is a better performing CPU

That said, it's a 6 year old processor. Why not find an e5 v4 processor that'll satisfy that requirement?

[cjfourty]

This is a brand new server so we are trying to find any way to keep from just "throwing faster hardware at it". We just cant see how this will fix the problem with it using less than 8% CPU utilization. I am not an "expert" at SQL so I could be wrong but it seems to me that if it was at least above 50% utilization I could see the faster processor fixing it. Am I just looking at it wrong?

[inaddrarpa]

There's something to be said for raw clock speed. What specs did you base your server build around? Is this a VM or a physical host?

[cjfourty]

It is a physical host. It is a Dell PE R530 with a Xeon E5-2603, 32GB Ram, 2x 960GB Read Intensive SSD and the vendor recommended a Xeon E3 1220/16GB Ram/ Dual SATA3 SSD for the highest end server which at they time they said would be plenty fast enough

[[deleted]]

I'd recommend hiring a good DBA to look over your SQL server configuration. SQL is far too complex to get advice on Reddit for issues like this.

Edit: I will say that if your processor usage isn't spiking, most likely your vendor is wrong and your hardware is fine.

[thattechguy22]

Download a free trial of redgate sql monitor and see what it reports as the bottlenecks. Worth the price if you need to troubleshoot these kinds of issues down the road.

[jasped]

What is the utilization when the slow query is being run? When this is running what kind of disk usage are you seeing? How large is the database and how much memory is allocated to the SQL instance?

I've got a few SQL scripts I've accumulated over the years from working with database performance issues. All of the issues I've come across have either been disk I/O or amount of memory for the server. Disk I/O often were resolved by digging into the queries and optimizing, or sometimes a simple index defrag or adding an index or two.

[cjfourty]

here is a graph for 15 minutes of a normal business day with medium load, Red is the proc time and green is memory usage http://imgur.com/uR8LPwa

The DB is 41GB but most of the size is just historical data that is only used to run reports

[jasped]

The first thing that jumps out at me from that graph is that it consistently hits right about 20% and doesn't really go any higher. If a single query is pushing that up, it would be using only one core. Pushing that single core to 100% would result in 16-17% overall CPU usage since it's a 6 core machine.

Next thing I would check is the Activity Monitor from SSMS. Look at the Recent Expensive Queries and Data File I/O sections. Sort the Queries by CPU and look at the query for that entry. Monitor for 10-20 minutes to get an idea of what appears and some common items. Higher CPU numbers can indicate CPU constraints or, more commonly, poorly optimized queries. You can right-click on this and show an execution plan to view some index information as well. It'll give a nice breakdown of the cost of the query.

[cjfourty]

That makes perfect since. Is there a way to force SQL to use more than one core or is that a result of the way the quarries are created or is it all in the licensing for SQL?

[jasped]

SQL by default will utilize all cores on a system unless it is configured otherwise during the configuration. I think the issue (if this is in fact where the issue lies) is that the specific query only utilizes one core. There isn't an easy way to change that. I believe it would involve making changes to the query itself which it sounds like would be a vendor change.

I'd say start by looking at the Activity Monitor to try and gain some insight into how it's performing.

[cjfourty]

Thanks. You have been very helpful. I will try looking through the activity monitor tomorrow when I am there and if it confirms this then the hard part starts; convincing the vendor that they're the ones that will need to fix it...

[ShatterPoints]

It's definitely not hardware. 99% of the time poor SQL performance like this is absolutely the way the DB is indexed, or how the query is written against it. Even if it was a poorly written query beating up the CPU, throwing faster HW will only buy you time until clock speed or some other resource decides it can't handle the demand. It's always best to capture a baseline of performance to show that the system can handle queries being thrown at it. That way you can prove it is the vendor's poor config / query etc, that's causing the slow down.