Thanks Brian and Edward, you have given me some given me some good pointers. I'll let you know what I discover...
Thanks again, Mark From: Ziots, Edward [mailto:[email protected]] Sent: 30 May 2012 16:36 To: NT System Admin Issues Subject: RE: SQL Cluster Disk IO issues +1 for Pal. Other things to think of. 1) Are particular SQL queries causing high disk IO, which could be optimized? (You can use the SQL manager in SQL 2005 to look at the query plans and see which part has the highest IO cost and see to deal with that. 2) What does your buffer pool (memory available to SQL) look like? (Are you getting buffer pool flushes, if so you are memory constrained, which can make your IO go to hell) 3) What is your max page life expectancy (which should be 600 or higher) (Can see that from Performance counters in the SQL object section) 4) How defragged are the disks that you are using for the SQL data? ( High Defragmentation=bad performance). 5) When is the last time your have DBCC commands to perform maintenance on the databases in question? ( DBCC ShrinkDB, DBCC Showcontig) 6) Defintely look to upgrade to SQL 2005/2008/2008R2 since SQL 2000 is basically EOL. Z Edward Ziots CISSP, Security +, Network + Security Engineer Lifespan Organization [email protected] From: Brian Desmond [mailto:[email protected]] Sent: Wednesday, May 30, 2012 10:42 AM To: NT System Admin Issues Subject: RE: SQL Cluster Disk IO issues Have you collected some performance data during the slow downs to see what is bottlenecking? Firmware bugs do happen so this could help, but, it's also possible that you're simply overloading the storage. This tool - http://pal.codeplex.com/ - is very good for getting the right log set built and then analyzing it. Thanks, Brian Desmond [email protected] w - 312.625.1438 | c - 312.731.3132 From: Mark Robinson [mailto:[email protected]] Sent: Wednesday, May 30, 2012 10:31 AM To: NT System Admin Issues Subject: SQL Cluster Disk IO issues Hi all, I'm looking for some advice please. I have an aging SQL 2000 cluster running on 2 x HP DL360's, each with dual fibre HBA's, connected to an MSA 1000 storage array via dual fibre channel switches. Of late database performance is poor, and during these bouts of poor performance, the SQL logs report that "SQL Server has encountered 'X' occurrence(s) of IO requests taking longer than 15 seconds to complete" During Additionally, Perfmon reports very high average disk queues n the disk that hosts the SQL database(s). Having researched this it seems that the most common advice is to focus on the disk subsystem, and to upgrade the firmware of the MSA controllers. I provided our developers with a list of the process ID's that were flagged alongside each of the IO entries in the logs, and I was told that there is no reason why these queries should cause bottlenecks and the issue is most likely with the disk subsystem. I understand the need to keep up to date with firmware releases, however I am failing to understand why the firmware would suddenly be at fault, when up until now there have been no issues. Another suggestion is to migrate resources from the existing MSA to a second MSA to lighten the load. However moving SQL cluster resources from one SAN to another and configuring the SQL cluster so that is still functions as before is a daunting prospect. So I guess my questions are: 1) Have anyone experienced similar issues in the past? 2) Does firmware 'just give up'?! I suspect not but worth asking!! 3) Is there any advice for introducing a second MSA and migrating resources from the existing SAN to the second? I would like to avoid this option if possible - I would much prefer to build up a parallel environment - but time is against me. Any advice very gratefully received. Many thanks, Mark -- Scanned by iCritical. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin -- Scanned by iCritical. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin
