July 30, 2007 at 7:33 am
We have a large db with a ton of online queries running. We have been looking at the SQL statements for index tuning already. What I want to know is how to use profiler to see if there is i/o disk bottle neck. I have done some research on using profiler for avg. disk queue length and current disk queue length. However, what I don't know is what the thresholds of what is a problem are though. So, I guess my question is what points do I monitor and what is normal/our of normal range.
Our environment is a SQL 2000 Ent. Cluster active/passive on WIn2003 with a 115 gig db.
Any help or website links would be helpful...
July 31, 2007 at 9:32 am
Are you sure you don't mean to use Performance Monitor?? This is the tool to use for I/O investigation I believe. There are some DMVs you can use on SQL 2005, but on SQL 2000 you are limited to a few waitstats that you can find in dbcc sqlperf(waitstats).
For disk queue length, I believe the standard for minimum acceptable performance is 2 PER ACTIVE SPINDLE. So if you have a 5 disk RAID 5 disk system serving data files, you could have 8 average DQL and be ok.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply