SQL disk performance
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.
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
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?
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!!!
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.