UnderStanding SQL 2008 Activity Monitor Values

I have one SQL 2008 server running on a Windows 2008 R2 platform using local storage. The stats on the server are as follows
-HP DL385 G5P
-2x AMD 2382 CPU's
-P400 controller wi battery wite cache
-OS and SQL application are installed on different logical volumes
-Physical Memory usage averages around ~95%
-Server host 68 DB's

Every now and then users have complained of sometime slow access from the DB server. I'm not a DBA but I'm trying to use Activity Monitor and unsure if the following values are good or bad or what they mean...

All values are for Cumulative Wait Time (SEC)
Network I/O - 44883
Logging - 70002
Latch - 13883
Buffer Latch - 553
Memory - 2
Buffer I/O - 9983

Thanks in advance for the help...
LVL 21
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

There is no wait type Logging. Do you mean WRITELOG?
Sorry I missed information that you are reading from 2008 Resource Waits cumulative values.

Please run this for deap insight to waits:
select * from sys.dm_os_wait_stats order by wait_time_ms desc

Open in new window

and post few from top.
Ryan McCauleyEnterprise Analytics ManagerCommented:
Here's the link to the MSDN document about best practices to minimize locks and resource waits:


It looks like "Typical resource bottlenecks" starts on page 15 (as printed on the document pages, not actual pages). It doesn't cover these values specifically, but it gives an overall feeling for the types of waits you experience, their cause, and what you can do about them.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

compdigit44Author Commented:
Here are the results for the query you posted.. Are these values good or bad???????

LAZYWRITER_SLEEP      1829095      1877687457      2030805      260800
DISPATCHER_QUEUE_SEMAPHORE      569770      966289195      73523234      137636
REQUEST_FOR_DEADLOCK_SEARCH      188995      939472025      5166      939472025
XE_TIMER_EVENT      31316      939454494      30130      939452523
LOGMGR_QUEUE      8783036      938600791      18341      1648268
SQLTRACE_BUFFER_FLUSH      234612      938580181      4159      343
BROKER_EVENTHANDLER      2230      938309012      14576960      3357
XE_DISPATCHER_WAIT      1015      937684326      73742214      0
FT_IFTS_SCHEDULER_IDLE_WAIT      15528      931637988      60171      1390
CHECKPOINT_QUEUE      19970      930643238      1853538      11113
ONDEMAND_TASK_QUEUE      11      761125890      265385104      25
SLEEP_TASK      38710225      474595239      2750      2461446
BROKER_TO_FLUSH      458335      469467389      1344      23908
BROKER_TASK_STOP      69534      336930215      11208      44860
CXPACKET      72228782      302913026      269395      26825855
BROKER_RECEIVE_WAITFOR      803      126909377      599922      240
WRITELOG      8141954      76969497      20963      1001045
ASYNC_NETWORK_IO      7328511      50216901      9624      291795
LATCH_EX      5373692      16269733      2501      2556014
LCK_M_IX      407      8893881      59263      2965
LCK_M_S      29928      8680805      9219      8271
SLEEP_BPOOL_FLUSH      789488      7770103      523      171232
SOS_SCHEDULER_YIELD      31164690      7015730      1426      6973858
BACKUPBUFFER      1125517      5774309      735      285608
ASYNC_IO_COMPLETION      3418      5293581      62617      1995
BACKUPIO      1292420      5201867      6072      76137
PREEMPTIVE_OS_AUTHENTICATIONOPS      2930819      1709071      188      0
LCK_M_U      6202      1474754      39847      1459
IO_COMPLETION      576130      1086561      716      9692
LCK_M_IS      1829      1015050      58705      2141
LOGBUFFER      108485      937371      281      5828
LCK_M_X      8285      703255      23989      4493
PREEMPTIVE_OS_LOOKUPACCOUNTSID      1021363      588793      374      0
PAGELATCH_EX      33291097      499739      1104      332052
LATCH_SH      270195      475896      3759      125609
PAGEIOLATCH_EX      41673      463460      2216      1136
PREEMPTIVE_OS_WAITFORSINGLEOBJECT      91201      402305      967      0
PAGELATCH_UP      401892      386756      2759      54765
PAGEIOLATCH_UP      22266      340427      3485      583
compdigit44Author Commented:
Any thoughts on this?
compdigit44Author Commented:
I've been going through the document and think I may have a ""Latency caused by disk to memory tranfers slow down..

My PageIOLatch vales on my SQL 2008 server are as follows:

PAGEIOLATCH_UP      22266      340427      3485      583

ALso I've gather some disk and sql stats using Perfmon as suggestion in the MS KB article you posted. Please note my SQL server has the volumes: C, E & F (C is for the OS, E is where SQL is install and where the DB's & log files reside and F is for backups.  Please let me know if you think these numbers are good of bad..

All numbers are averages.
disk reads/sec: 15.87
disk writes/sec: 4.8

disk reads/sec: 0
disk writes/sec: 10.98

disk reads/sec: 0
disk writes/sec: 0

Page Life Expectancy: 211,098
It really looks that it should be disk problem. For better measure please add performance counters "disk queue length" for E:
If you can change hardware configuration it is better to separate transaction log files to another physical disc.
compdigit44Author Commented:
The AVG DiskQueue Length for my disks is as follows:

C = 0
E = .034
F = 0
Ryan McCauleyEnterprise Analytics ManagerCommented:
Those values all look pretty reasonable - I don't get worried about disk queues until the values are closer to 1. Are you sure you ran the perfmon monitor during a period of high load/poor performance, or is the poor performance pretty much constant?

Also, you provided the averages - were there any large spikes in there? You could have really poor performance for short periods of time so that the averages look fine but periodically it's performs terribly.
compdigit44Author Commented:
The slow peerformance is sporatic...

what are you thoughts on my sql latch values? From eveerything that i have read this is very high?? Is it possilbe fragmented index could be a problem or to many table scanns??

please advise this why out of my league on this...
compdigit44Author Commented:
Any thoughts on this????

Please :-(
Ryan McCauleyEnterprise Analytics ManagerCommented:
Do you ever do index maintenance? You can easily run a script (after-hours, especially if you have Standard Edition) - this is a good one:


It's the second script on that page - running it (the default options are already set) will rebuild all indexes that are more than 30% fragmented and reorganize all indexes between 5 and 29% fragmented. It's a good place to start if you've never done this type of maintenance before, and if you're having quite a bit of disk thrashing as your do large reads, it can help immensely.

Latches are very short duration locks, while SQL Server is transferring or actively modifying data - not like a transaction where you're performing multiple operations as a unit, but while it's physically acting on data (like reading it from disk or waiting for the disk head to update a value). A large latch wait time could be the result of disk thrashing or page splitting, but it may indicate other performance problems with your disk. I'd start with the index maintenance script and see if that gains you anything - it won't hurt and may help resolve the problem. It may be the underlying disk performance of your server, though - what kind of disks are you using, how many, and what's their RAID configuration?
compdigit44Author Commented:
First off I wanted to say that you to everyone for the help!!!!

I have accesss to an older version of IDera's SQL Admin Tool Set and ran a Index Analyzer scan:  The % Updates to Total Access on about 60% of my tables is 98% -100% not sure what this value means.

I found two maintenace plans on the server which I did not build one is called index rebuild which run maybe twice a week and a static update which runs daily

Physical Disk Structure: Two P400 controllers three 72GB 15k SAS drive in a Raid 5 and the other   P400 host eight 146GB 15K SAS drives in a Raid 5.
C:\ is for the OS and where the system managed page file resides
E:\ is for the the SQL program, DB , Log and BAK files
F:\ archived bak files
compdigit44Author Commented:
I found the following TSQL script online to list index fragmentation on all indexes. My Sql 2008 server has 60+ DB's the query only returned 7 rows two of which only should data for to specific indexes. One was for an avg fragment of 55% and the other was for 83%. PLease let me know what you think.
Ryan McCauleyEnterprise Analytics ManagerCommented:
The script I provided need to be run on each database on the server to give the fragmented indexes for each. Are you saying that you ran it on each database and you only had one database with two fragmented indexes, or did you only run it on one database?
compdigit44Author Commented:
I just ran you script and got way more results... The avaerge fragmentation on all my index is between 58 -99.9%
Ryan McCauleyEnterprise Analytics ManagerCommented:
The 99.9% ones are really, really bad, and even the 58% can slow things down quite a bit. Numbers like that can definitely lead to slow disk access times and a lot more thrashing than is necessary.

By default, the script only displays the commands you'd use to clean things up - near the top, there's a variable called @ExecuteSQL that's set to 0 - set that to 1 instead to have it actually run the commands and clean up the indexes.

Once you've actually executed the commands, run the script in display mode again and see what the difference looks like - I'll bet it's significant, and even if it doesn't improve the latch times you're seeing, it will improve overall performance pretty significantly, especially for queries that pull wider data sets.
compdigit44Author Commented:
SO in a nutshell I should run a defrag on index and look into a better disk structure???
Ryan McCauleyEnterprise Analytics ManagerCommented:
I'd start with the indexes, since that's an easy fix and it will only improve things - it seems like the largest impact for the time, and is an easy place to start.

I'm a bit concerned about the disk layout , though - you mentioned that you have two RAID-5 arrays and didn't specify which drive letters rest on which array, but given your disk sizes, I'm assuming array 1 (3x72GB) hosts your OS (drive C) and array 2 (8x146GB) is divided between drives E and F. You've separated the OS/Pagefile and the SQL Data, which is good, but all the SQL files are on a single array - data, logs, and tempdb. Also, it's RAID-5 - while 15K drives SAS are really fast, and RAID-5 generally supports pretty quick reads, it can result in slower writes to disk. Especially because a SQL commit involves both data and log activity (and sometimes tempdb as well), the existing array is likely stressed because all of those things are happening at the same time on different areas of the disk.

We usually use RAID-10 to support our SQL Data files - it's more expensive in terms of parity (you lose 50% of the volume), but it supports read/write operations at close to the speed of RAID-0, while still supporting drive failures. Also, you should investigate whether you can separate your data from your logs/tempdb - not sure if you can manage something like this or what the size of your individual drive letters currently is, but maybe:

Drive C (3x72GB) - OS
Drive E (4x146GB, Raid-10, 292GB effective space) - SQL  Program, MDB files (Database data files)
Drive F (4x146GB, Raid-10, 292GB effective space) - LDB files (Database logs), TempDB data and log, BAK files

The archived BAK files could go on either drive, as could the actual BAK files if you need to adjust the space a bit. That way, the data and the logs/tempdb can operate independently, and commits that work with both are less constrained. I know re-configuring a RAID array is not a lightweight task, which is why you should start with the indexes to see what kind of improvement you get there first - it may be enough to mitigate the problems you're seeing. Also, since the array supporting drives E and F is the same physically (they're carved from the same RAID-5 array), moving the logs to drive F from E isn't going to get you any advantages - just wanted to put that out there.

Let me know if you have any other questions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.