Solved

UnderStanding SQL 2008 Activity Monitor Values

Posted on 2012-03-27
21
2,169 Views
Last Modified: 2012-04-06
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
-32GB RAM
-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...
0
Comment
Question by:compdigit44
  • 10
  • 6
  • 3
21 Comments
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
There is no wait type Logging. Do you mean WRITELOG?
0
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
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.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
Here's the link to the MSDN document about best practices to minimize locks and resource waits:

http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc

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.
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
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
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
Any thoughts on this?
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
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.
C:
disk reads/sec: 15.87
disk writes/sec: 4.8

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

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

Page Life Expectancy: 211,098
0
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
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.
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
The AVG DiskQueue Length for my disks is as follows:

C = 0
E = .034
F = 0
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 19

Author Comment

by:compdigit44
Comment Utility
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...
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
Any thoughts on this????

Please :-(
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
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:

http://sqlserverpedia.com/wiki/Index_Maintenance

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?
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
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
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
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.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
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?
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
I just ran you script and got way more results... The avaerge fragmentation on all my index is between 58 -99.9%
0
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
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.
0
 
LVL 19

Author Comment

by:compdigit44
Comment Utility
SO in a nutshell I should run a defrag on index and look into a better disk structure???
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
Comment Utility
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.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now