Avg. Disk Read / Queue Length counter on SQL Server machine jumps to 40-50

Question

Avg. Disk Read / Queue Length counter on SQL Server machine jumps to 40-50

zbakhramov

Mr or Mrs. 500

Points: 559
More actions
March 13, 2009 at 11:25 am

#155412

Hi!
For couple of weeks we notice that the performance counter Avg. Disk Read / Queue Length on our SQL Server machine jumps to 40/50 sometimes even more. At this time it's almost impossible to work with the databases and users have to wait for a few minutes when it drops to normal 1-2. We have a windows server 2003 with sql server 2000 running in the vmware ESX server and SAN.
I will anyway try defragmenting from within windows the disk where resides the database file but I wanted to know if there could be any other major issues for this problem. Could this be due to a SAN?
Thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

If it is "shared" disk then it could be something external of the server / instance that you are looking at for sure. You should be able to get with the ESX admin to have them look at where the IO requests are coming from during the times that the counter is high.

I would also suggest that you set up a trace that you can log some of the high read queries to a file so that you can start to review them. It is entirely possible, and very probable that a new query has crept in, some indexes are missing, bad plans due to stats, etc and therefore your reads have increased. Finding those queries and addressing them is going to be key in getting those numbers back down.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

zbakhramov (3/13/2009)
We have a windows server 2003 with sql server 2000 running in the vmware ESX server and SAN.

Disk queue length is a near-impossible to interpret properly on a SAN. Has to do with all the layers between the OS and the disks. What do the following perfmn counters look like?

Physical Disk: Avg sec/read

Physical Disk: Avg sec/write

Physical Disk: % idle time

Break them down per disk.

What's your drive layout? How many LUNS, what RAID level and what's on them?

Are the drives in the LUNs shared or dedicated?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zbakhramov Mr or Mrs. 500 Points: 559 More actions · Answer 3

We run Navision 3.70.

We have a production database which is 123 Gb size and a test database which is a copy of production database.

We have run a Table Optimizer from Navision on test database and we've noticed that the database size increased and became 127 Gb after optimizing only one table: Item Ledger Entry.

Before Optimizing 'Item Ledger Entry' table was:

Nr. of records: 4592120

Record Dim.: 5582

Dimension (KB): 25031520

After optimization it became:

Nr. of records: 4592120

Record Dim.: 2.131

Dimension (KB): 9556144

Also we have noticed the performance decrease as the tables take more time to open.

For a few weeks we've suffering of a general performance issue even if we regularly run a maintenance plan:

--Step 1:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0F31AD1C-FC96-468D-ACEE-62AF37D991CD -Rpt "d:\Microsoft SQL Server\MSSQL\LOG\Mantenance ME_Prod - FT_Prod - F_Prod2.txt" -DelTxtRpt 4WEEKS -WriteHistory -CkDB '

-- Step 2:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0F31AD1C-FC96-468D-ACEE-62AF37D991CD -Rpt "d:\Microsoft SQL Server\MSSQL\LOG\Mantenance ME_Prod - FT_Prod - F_Prod0.txt" -DelTxtRpt 4WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

Perfomance counter Avg. Disk Read/Queue Length bounces to a value of 50,000-60,000 every now and then...

Since we have not changed anything in the server configuration it really seems strange that the performance has so dramatically decreased.

I have two questions:

1) Why did the database grow so much after Optimizing only one table and what if happens if we Optimize others?

2) What could be the reason of such a performance decrease and how may we track it back.

Looking forward to your comments!

Thank you!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

What did you set the fill factor for when you reindexed? (If that's what the maint plans are doing. Impossible to tell from the commands you postd)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

IMO these are very interesting articles on this Disk related subject ...

- Playing with Disk Alignment

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/11/19/playing-with-disk-alignment.aspx

- SAN Performance Tuning with SQLIO

http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me