Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!
Course Of The Month
4 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL disk performance
Posted on 2013-01-07
I need to look in to an SQl LUN performance issue, the LUN is served by an EMC CX4-240 SAN.
my issue is that SQl performance is poor, the SQl server is robust enough for the workload i.e. CPU utilization is at about 25% Memory 53%.
however on the storage side when I look at MS counters using prefmon.exe most of the counter like queue, Disk transfer per/sec average disk bytes disk write are all at 100% at all times.
I ran an SQl backup to NULL so I could estimate my storage performance and the the process completed with the speed of 49.5 MB/sec.
I ran the same SQL backup to NULL on a lab system on an ESX server and storage on the same ESX at a much faster speed of 120.3 MB/sec.
how can I pinpoint the source of this performance bottleneck, the SAN people are telling me that the SAN is solid and all I can see on the SQl server side points to disk issue.
any help would be appreciated.
my issue is that SQl performance is poor, the SQl server is robust enough for the workload i.e. CPU utilization is at about 25% Memory 53%.
however on the storage side when I look at MS counters using prefmon.exe most of the counter like queue, Disk transfer per/sec average disk bytes disk write are all at 100% at all times.
I ran an SQl backup to NULL so I could estimate my storage performance and the the process completed with the speed of 49.5 MB/sec.
I ran the same SQL backup to NULL on a lab system on an ESX server and storage on the same ESX at a much faster speed of 120.3 MB/sec.
how can I pinpoint the source of this performance bottleneck, the SAN people are telling me that the SAN is solid and all I can see on the SQl server side points to disk issue.
any help would be appreciated.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
2
7 Comments
Active today
check the EMC perform graph; check with EMC if you are using FAST and what about emc saturation?
based on the EMC stats check what is running on sql (probably sql backups):
if backups try to use backup compression, different times of the backup execution maybe also you can check if your sql server has regularly running DB maints jobs: reindex; checkdb; update stats, etc..
see what queries are running when you got the problems -- you may have some select into (for example) and need to tune tempdb ; maybe you need to put your DBs on fast drives (fiber)..
check with EMC what you have? check the running code that may need optimization, check your sql server configuration
\setup..
and post what you found
based on the EMC stats check what is running on sql (probably sql backups):
if backups try to use backup compression, different times of the backup execution maybe also you can check if your sql server has regularly running DB maints jobs: reindex; checkdb; update stats, etc..
see what queries are running when you got the problems -- you may have some select into (for example) and need to tune tempdb ; maybe you need to put your DBs on fast drives (fiber)..
check with EMC what you have? check the running code that may need optimization, check your sql server configuration
\setup..
and post what you found
SQL is not the issue, the slow or high I/O is basically 24/7 same performance on the drives, the EMC has 4 GB fiber channels. My issue is just I/O we have gone over all aspects of SQL, indexes and optimal query and so on still same issue I/O is maxed out.
Active today
if sql server is not the issue ..not EMC issue .. please clarify what "I/O is maxed out. " in your case?
where do you see it ? how do you know?
maybe drives too slow? are you on RAID 5?
do you have all disks online\no rebuild\etc. ?
maybe you need to add more emc "brains" ? what did emc say? maybe you need upgrade the server firmware? is it VMware or physical server? is it for all servers or just specific? did just start? are you running Antivirus there ? can you check the AV setting?
where do you see it ? how do you know?
maybe drives too slow? are you on RAID 5?
do you have all disks online\no rebuild\etc. ?
maybe you need to add more emc "brains" ? what did emc say? maybe you need upgrade the server firmware? is it VMware or physical server? is it for all servers or just specific? did just start? are you running Antivirus there ? can you check the AV setting?
Sorry, it may be EMC thats what I think and am looking for a way to confirm or perhaps prove us wrong in that assumption. onb the OS looking at harware performance what we see is a slow write hard drive system, I would like to proove this to the SAN techs I dont know what if anything they have looked at but from my perspective that is the issue ( we may not be correct in that assumption) but all we can see points to some disk sytem bottleneck. May be a slow disk as you indicated or busy array or some other cause, i dont know.
I ran this command on 2 equal systems with the exceptiom of hardware and the result was sugnificantly faster on the lesser system.
backup database MyDB to disk = 'NUL:'
BACKUP DATABASE successfully processed 114622090 pages in 18095.621 seconds (49.486 MB/sec).
the result above is on the production sytem showing 49.5 MB/sec on the lab sytem the result was 120 MB/sec.
the production server is hardware the lab system is on ESX i 5.1, my problem is I'm not in the office with issue and dont have direct access to the EMC unit nor do I have expetise to do much on it. I may have to turn it over to the SAn boys but having issues proving it to them!!!
I ran this command on 2 equal systems with the exceptiom of hardware and the result was sugnificantly faster on the lesser system.
backup database MyDB to disk = 'NUL:'
BACKUP DATABASE successfully processed 114622090 pages in 18095.621 seconds (49.486 MB/sec).
the result above is on the production sytem showing 49.5 MB/sec on the lab sytem the result was 120 MB/sec.
the production server is hardware the lab system is on ESX i 5.1, my problem is I'm not in the office with issue and dont have direct access to the EMC unit nor do I have expetise to do much on it. I may have to turn it over to the SAn boys but having issues proving it to them!!!
You've specified the CX4, but haven't indicated:
How many LUNs are supporting SQL?
What is the composition of the RAID group supporting the LUN(s), ie, RAID type, number and type of spindles, additional (competing) LUNs on the group (or the SAN itself).
While the CX4 is 4Gb/s, is the host? And is the switch?
Knowing the details, one can calculate the maximum theoretical performance of (non-cached) storage subsystems.
One other test: in the lab system that does backups >2x faster, does the SQL app perform correspondingly better? The backup test isn't going to use the same workload as the production application: backup is essentially a sequential, large-block read; your app is more likely to be 30-50% random & 20-30% writes of varying access sizes (averaging 64K?).
How many LUNs are supporting SQL?
What is the composition of the RAID group supporting the LUN(s), ie, RAID type, number and type of spindles, additional (competing) LUNs on the group (or the SAN itself).
While the CX4 is 4Gb/s, is the host? And is the switch?
Knowing the details, one can calculate the maximum theoretical performance of (non-cached) storage subsystems.
One other test: in the lab system that does backups >2x faster, does the SQL app perform correspondingly better? The backup test isn't going to use the same workload as the production application: backup is essentially a sequential, large-block read; your app is more likely to be 30-50% random & 20-30% writes of varying access sizes (averaging 64K?).
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Display SQL maintenance plan SQL Code | 3 | 47 | |
| Backup Strategy | 13 | 67 | |
| SQL syntax for max(date) | 3 | 39 | |
| SQL Simple Query Taking a Very Long Time | 11 | 37 |
When we purchase storage, we typically are advertised storage of 500GB, 1TB, 2TB and so on. However, when you actually install it into your computer, your 500GB HDD will actually show up as 465GB. Why?
It has to do with the way people and computers…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month4 days, 21 hours left to enroll
738 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.