• Some users have recently had their accounts hijacked. It seems that the now defunct EVGA forums might have compromised your password there and seems many are using the same PW here. We would suggest you UPDATE YOUR PASSWORD and TURN ON 2FA for your account here to further secure it. None of the compromised accounts had 2FA turned on.
    Once you have enabled 2FA, your account will be updated soon to show a badge, letting other members know that you use 2FA to protect your account. This should be beneficial for everyone that uses FSFT.

determining SQL server bottleneck

T

Thuleman

Supreme [H]ardness
Joined
Apr 13, 2004
Messages
5,833
I am currently running a SQL Server 2008 R2 on Windows Server 2008 R2 in a VM (ESX) which has 4 GB of RAM and 1 CPU with 2 Ghz.

The SQL DB in question is about 4 GB large, retrieving 120,000 records form it takes about 8 seconds. I feel that's way too long, but I don't have a good sense on what the most likely bottle neck could be.

I know that non-optimized queries can really screw things up, but someone else wrote the queries and they are used elsewhere as well so I would assume that they are fairly optimized or someone else would have noticed already. The disk access is on an EQL PS5000E which may be part of the problem, unknown though.

Any pointers on where to start tweaking for better performance would be much appreciated.
 
I would also point you to perfmon and check out your CPU performance
 
CPU most likely, why do you need to run this in a VM?

what is the host computer doing beside just the VM?

what is your harddrive setup?

a single SATA drive will always perform crappy for a database.
 
Permon does show 100% CPU utilization during the time that the query is running. The host has two Xeon E5405, so it is just a matter of giving the VM another vCPU then?

Checking the disk queue is next though I won't be able to do that till next week some time. I don't have SAN HQ set up right now and too much on my plate to monkey with that.

re: why running it on a VM?
Why not? There's no inherent reason why SQL Server can't be virtualized. Most of the time that VM is idle so dedicating a physical server to it would be quite a waste.

I have three Dell 2950 III connected to a single EQL PS5000E, there's no single SATA drive. There are 16 disks which are all on the same array (by design, unfortunately). I don't have disk IO handy, but my guesstimate would be that my total IOPS are less than 200, there is very little load on the cluster.

I have 18 production VMs and a few test VMs running on those three physical hosts. I can easily allocate more resources to the SQL server, I just didn't want to aimlessly starting to change things.

EDIT: I added the average disk queue counter to Perfmon and I am not entirely sure what I am looking at. The values as they are displayed in the fields below the graph are 0.019 for average and 0.222 for max, those are the values that occur when running that 8 second query.

EDIT2: So I gave it 2x vCPUs and the query run time didn't really improve. Here's what the permon cpu graph looks like. CPU is idle, then query starts, and then it goes back to idle at the end of the query.
2vcpusql.gif
 
Last edited:
Disk queue lengths under 1 is very good..no issue there. It may just be a badly written query. What's the CPU load on the ESX host itself when you run the query?
 
CPU load on the host hovers around 4% when the query is not run, even though other VMs are on the same host. Looks like vCenter isn't quick enough to show accurate load, but let's say that I am using 2 vCPUs at 100% that would bump the load up to 12.5% + 12.5% + 4% = 29% total.

I'll try and dig out the query, the app used is open source and it seems like they are basing the query off of jquery even though the app is written in VB. I guess this now becomes more of a SQL issue than a virtualization issue.
 
The resource monitor in R2 gives a real nice overview of system resource usage.
resmon.exe
 
simplesam said:
The resource monitor in R2 gives a real nice overview of system resource usage.
resmon.exe
Click to expand...

This, and SQL Server Management Studio has a specialized version of this which shows any lock, I/O queues, expensive queries etc. Just right click on the server and select activity monitor. Plus - if the database server starts to run low on RAM CPU usage will sky rocket!

Also there is a report "Performance - Object execution statistics" which is useful for checking any stored procedures which are taking their toll.

Further more 120k records in 8 seconds maybe near the best you can get on such an average processor (or WAN?), especially if there are a number of joins, columns and/or SP's. Is it really necessary to pull 120,000 records each time? Plus you could look at creating a cached version of this table for the repeat queries.

That's all that springs to mind right now, hope it helps!
 
Thuleman said:
but someone else wrote the queries and they are used elsewhere as well so I would assume that they are fairly optimized or someone else would have noticed already.
Click to expand...
I wouldn't! Tackle the essence of the problem, not the accidents :)

well, where possible anyway. How do other similar queries run?
 
kencheeto said:
well, where possible anyway. How do other similar queries run?
Click to expand...
I have no idea, currently the .Net app is the only way we are retrieving data and when using it all queries are fairly slow.

The devs had the following to say about that: "While some of the problems with response times we have had may be due to optimization of the SQL query itself, most of the problems we have had are more due to the generation of the queries in .NET. [...] tested some of the same queries directly via the SQL Server Management Studio, and they were quite peppy, but not so much when executed from within a .NET application."

So they are aware of the issue on their end and are working on that. On my end I am looking into an alternate way to retrieve the data and plot it up alas I don't see myself actually working on that in the next few days (weeks?).

For now all I have done is added a second vCPU to that VM which helped to bring the CPU utilization down but didn't noticeably improve the response time on the query. Another issue that I came across after looking into this is that some of the query run time is actually mistakenly assumed to be query run time when in fact the query has already completed and the lag is now caused by the drawing of the plot. For example, when 120k points are retrieved then now all 120k points are drawn. The plotting routine does some sort of sampling magic that draws only every 1,000th point or some such. This takes time and some plotting routines are better than others. I am not sure exactly what the current app is using, probably whatever graphing comes with .Net.

Whenever I get to it I will have a look at how long exactly the query run time is and go from there. This is an issue I do need to solve before the end of September but if it weren't for the last minute nothing would ever get done. ;)
 
You must log in or register to reply here.
Back
Top