October 19, 2008 at 8:30 pm
Can you wonderful folks help a relative newbie with interpreting a perfmon trace result?
I read somewhere to trace the counters below during an typical work day, sampled at 1 minute intervals. The results below are a 12 hour period (8am to 8pm).
This server is a Windows 2003 R2 Enterprise sp1, SQL 2000 Enterprise sp4. Dual 3GHz CPUs, 8gb ram, with 7gb allocated to SQL. The main user database is approx 110gb. Performance is slow.
I don't have info handy as to disk configuration but I do know that all mdf and ldf are on same array.
Any obvious issues with the couter results? Suggestions on what else to trace?
Counter Avgerage Minimum Maximum
Memory\Available Bytes434687982.000319942656.000477491200.000
Memory\Page Faults/sec85.21118.1342129.870
PhysicalDisk(_Total)\Avg. Disk Read Queue Length0.8530.00045.384
PhysicalDisk(_Total)\Avg. Disk sec/Read0.0110.0000.149
PhysicalDisk(_Total)\Avg. Disk sec/Write0.0150.0000.330
PhysicalDisk(_Total)\Avg. Disk Write Queue Length0.8250.00025.255
PhysicalDisk(_Total)\Disk Reads/sec68.9720.1173462.953
PhysicalDisk(_Total)\Disk Writes/sec46.5751.067370.480
Processor(_Total)\% Processor Time29.6190.67295.345
Processor(_Total)\DPCs Queued/sec1656.58360.1866188.815
Server\Server Sessions19732
SQLServer:Cache Manager(_Total)\Cache Hit Ratio0012
Thanks a bunch.
October 20, 2008 at 12:15 am
Well, the averages don't look bad, but the max numbers would be a bit slow. However, it's impossible to tell if the Max numbers are relevant or just spurious.
What would help us is:
1) Collect at 5 min intervals, instead of 1 min: this makes the Max more relevant
2) Collect only during the peroids when you are having performance problems. 8am to 8pm is usually too long as it typically includes a lot of time with no problems in the averages.
3) Also, include Disk Idle % for each disk
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 1:09 am
Also include paging information, 7 GB for sqlserver leafs only 1GB for windows and that seems a bit low to me.
Have you granted the lock pages in memory right to sql server ?
your diskdrives seems to do lots of reads and writes, are youre data partitions formated in 64K sectors, and are they sector aligned?
what raid set are they ? raid 5 or 1 ? how many disks are there in the set
edit : just read that youre MDF and LDF are on the same drive?
that would be the first thing to fix.
October 20, 2008 at 9:50 am
1) you almost certainly have IO stall issues given the TOTAL averages you have. Specify individual drives that have mdfs and ldfs on them for monitoring in the future.
2) use fn_virtualfilestats to check for IO stalls on database files.
3) 7GB is definitely too much out of 8GB box. I would reduce to 6gb and then monitor for excessive paging and reduce further if necessary.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply