Definite Disk I/O issues

[mebenz]

I ran the perf mon for a 24 hour period on the physical disks and have the following results for our drives where our SQL Data resides (logs are separated).

Avg Disk Read Queue Length AVG 0.373, MAX 98.021
Ave DIsk sec/Read AVG 0.032 MAX 0.468

I am not sure of the total number of spindles on our RAID 5 array where the data resides, but I have been told that these drives are performing poorly from these results.

What I would like to run next is just to monitor the queries using the Disks. I want to see over a period of time, if it is specific to one database or is it just across the board (which from what our users say is the case as all our apps seem to be lagging).

Is there any recommended columns and events to look at that will just focus on Disk I/O?

 

[mrdenny]

Watch the Batch Completes. If you have SQL 2005 run profiler and perfmon at the same time. You can then open the perfmon counter data in profiler and see which queries are causing the the counters.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mebenz]

Could I ask you one other question? We have SQL Server 2000 Enteprise Edition, which uses a max of 3GB of RAM on Windows 2003. If we wanted to add more RAM to our server (which is also a clustered server for SQL), can we get SQL to use say 4Gb of RAM?

 

[SQLWilts]

Only using AWE - which is "sort of".

There is a mathematical limit to the amount of memory that any 32 bit application can use - which equates to 3GB (its down to physical address space - sorry, late at night here - plenty on google to explain).

You can enable AWE, which will handle memory in a totally different manner. There are reams written about this out there too - I can answer specific questions, but I suggest you research microsoft KBs on the subject before you play with this.

Really, the only true way to take advantage of lots of memory is to move to a 64 bit environment and ramp up that ram.

 

[mrdenny]

Yes you can use more than 3 Gigs of RAM. With SQL accessing over 2 Gigs of RAM already, you should already have AWE enabled. If you SQL Server is only using 2 Gigs you may need to enable AWE.

AWE allows SQL to access more than 2 Gigs of RAM and use this additional memory for the buffer cache.

You will also need to add the /PAE and /3GB switches in the boot.ini of both nodes.

You can read up on how SQL decideds how much buffer cache to use here

http://itknowledgeexchange.techtarg...dure-cache-and-how-much-buffer-cache-to-use/.

SQL Enterprise Edition on Windows 2003 Enterprise can use pretty much as much RAM as will fit into the server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog