March 27, 2008 at 7:39 am
can anyone give me a hand I have very busy drives and I'm trying to profile it out to see whats going on but I'm still pretty green when it comes to profiler.
SQL Server 2005
RAID 5 (3 phy disks)
3.5GB RAM
When I run profiler I can see LanDesk is very read intensive I created a job for LanDesk to reorganize to indexes every night and rebuild them once a week per LanDesk support. This helped but the servers drives are still 100% busy, 90% of the time. There are about 44 db on this box and most are pretty idle. Any ideas?
March 27, 2008 at 7:50 am
As a first suggestion, run profiler and capture the T-SQL Batchcompleted and StoredProc:RPC completed events. Run it for an hour or so during one of your busy periods. You can run it on another machine and save the trace to a file
Make sure you capture the reads and writes columns. Once the profile's finished, have a look at the top 20 queries in terms of IO.
Generally, to reduce reads requirs either changing the code or changing the indexes. I'm not familiar with LanDesk. Vendor product?
How much memory is SQL server set to use? If you run perfmon, what are the average values of the following counters?
SQL Memory manager: Buffer cache hit ratio
SQL Memory manager: Page life expectency
Memory: Pages/sec
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
March 27, 2008 at 8:07 am
oops this is a sql 2000 box
I started the trace but couldnt check the counters since its sql2k. What counters should I check for? and how do I check to see how much memory sql is using?
thnks
March 27, 2008 at 8:14 am
Nothing I posted was SQL 2005 specific.
For the counters, use performance monitor. They're not within profiler.
Check the server's max and min memory settings. Check to see if AWE is enabled (both in server properties)
Check to see in /3GB or /PAE are in the boot.ini file
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
March 27, 2008 at 8:34 am
ok, the counters were just in a different spot
SQL Buffer manager: Buffer cache hit ratio
SQL Buffer manager: Page life expectency
this is also sql 2000 standard edition so is AWE part of standard? I read it was only in Enterprise and Developer Editions. Also the /3GB or /PAE are not in the boot.ini file
March 27, 2008 at 8:35 am
SQL Buffer manager: Buffer cache hit ratio = 99.62 avg
SQL Buffer manager: Page life expectency = 99.88 avg
March 27, 2008 at 8:45 am
Memory: Pages/sec = 0.081 avg
March 27, 2008 at 8:50 am
SQL Buffer manager: Page life expectency = 4063 avg
March 27, 2008 at 11:00 am
Are the drives busy constantly or just at night when LanDesk is running? Id' expect an index rebuild to spin them as you are moving data around.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 27, 2008 at 11:10 am
the disks busy all day. LanDesk is very read intensive (all day) and there are other dbs on the same box that also do a lot of reads.
March 27, 2008 at 11:18 am
DO you have proper indexes in place for the databases?
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
March 27, 2008 at 11:31 am
There's a SQL Server Tuning and Performance guide for Intel LanDesk here:
http://community.landesk.com/support/docs/DOC-2356
you might care to spend some time in there. According to it - you have an "IO bottleneck".
How many machines are you inventorying?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 27, 2008 at 2:03 pm
Those IO stats don't look too bad. 99.6% of all reads are done from memory not from disk. There's virtually no swapping occurring. Not really what I would expect if SQL was driving the disks to 100%
Is there anything else other than SQL on that box?
If you look at the following perfmon counters broken down for each disk, what do they look like?
Physical disk - transfers/sec
Physical disk - average disk queue length
Physical disk - % idle time
Physical disk - avg sec/read
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply