Unsolved
This post is more than 5 years old
30 Posts
0
19399
January 21st, 2014 11:00
Issues with SQL on PS
We are being told by our Dev team that the SQL on the PS group is acting worse than on local dev machines. Here is what we have. We have an EQ6100 and a EQ6500. We are running SQL 2012 on a windows 2012 cluster. The cluster consists of a physical box and virtual box. Both boxes have 2 iscsi NICs dedicated for iSCSI and the iSCSI traffic is on a separate VLAN (everything on the hardware side is set to best practices, Flow control, switch configuration, etc.) For benchmarks we ran a large query and it took over 2 minutes to run no matter which server was hosting the SQL in the cluster. We originally had the SQL running on the 6500 in raid 6. The dev team said that the limitation was the SAN. We then brought up the 6100, which was slated for DR, and load balanced the SQL across the members. No change in the time to run. We then forced the SQL volumes to run on the 6100 with raid 10. Still same result. The IOPS on the SAN are nominal for the volumes. The network usage on the ports on the SAN is nominal. The virtual server is Citrix but that shouldn't matter as the issue happens whether ran on a physical box or virtual box. What else can I do to troubleshoot? I wanted to put the SQL on a physical box with physical drives to show that the issue is probably with the cluster or SQL but I don't know how to fool the cluster in to using local drives. I want no doubt that the issue is not with the SAN. Help me Obi Wan you're my only hope!!!!
No Events found!
AMSEAMANS
30 Posts
0
January 21st, 2014 11:00
Already did. They are not showing anything is wrong with the SAN hardware itself. I am trying to approach this from the software side and SQL side versus the hardware. We have told DEV what Dell has said and they don't believe it. So I am trying to find a way to isolate the root cause.
mirko.schnellba
25 Posts
0
January 21st, 2014 12:00
Use SQLIO or iometer to measure actual LUN performance, then use the same test to measure disk performance on a developer workstation.
SQL server performance tuning is quite complex, but if a query is mostly CPU bound it can run faster on a modern desktop PC (which tend to have processors with few cores and high clock frequencies) compared to a typical virtualization server (which typically has many cores / processors with low to medium clock speeds).
SQL Profiler can help you examine what bottlenecks that query, if your systems are correctly set up (and Dell seems to says they are) and performing within the usual range, it might be time for the devs to find a way to query smarter.
AMSEAMANS
30 Posts
0
January 21st, 2014 12:00
"it might be time for the devs to find a way to query smarter." There is a LOT of bad coding. It has always been the policy to throw more hardware at it. Well we can't do that anymore. I am just trying to eliminate the excuses we are seeing and blaming the hardware for bad DB design and poor application coding.
What is weird is that the query that we are using as a benchmark runs the same on a physical server as a virtual server. The only common thing with those is the SAN LUNs and the config of SQL and the cluster.
AMSEAMANS
30 Posts
0
January 21st, 2014 12:00
well iometer is pre 2012 and sqlio is also pre 2012. I am unsure of their usefulness. :)
bealdrid
1 Rookie
•
56 Posts
0
January 21st, 2014 13:00
I don't know the hardware specifics of your dev servers, but lots of times there is no substitute for a local RAID array with a battery backed write cache. Maybe that is what you have on your dev servers? Is your query that is suffering a write query? For us, write SQL performance is much better with a local RAID controller with write cache configuration than via iSCSI to our arrays. We have 10Gig and everything. I think its just a matter of latency. i.e. its faster for the OS to commit changes to a local RAID array with a battery backed write cache than it is on the EqualLogic array, where you must go through the overhead of iSCSI protocol, Ethernet, cables, switches, latency, etc.
Don't get me wrong EqualLogic SANs are awesome, its just that there are some situations where a SAN of any vendor will not solve a I/O performance problem, and in our experience, this is one of them.
AMSEAMANS
30 Posts
0
January 21st, 2014 13:00
Thanks. unfortunately it is a read query. I am just trying to prove the fact that the issue is with the SQl DB and not with the hardware backend. I agree with what you said. No SAN from any vendor will fix the issue of bad code and SQl configuration.