-
Re: Monitoring Orion for Performance?
bshoppOct 6, 2009 3:11 PM (in response to ecornwell)
I would start with Disk Queue Length for DB performance monitoring using the APM SQL Server template
-
Re: Monitoring Orion for Performance?
jeff.stewartOct 6, 2009 4:12 PM (in response to bshopp)
Ecornwell, I just added this counter to watch out DBs in APM. Let me know if you have any questions about it.
-
Re: Monitoring Orion for Performance?
ecornwell Oct 7, 2009 7:22 AM (in response to jeff.stewart)Thank you both for the response. Here is what I'm currently using.
SELECT AvgDiskQueueLength FROM Win32_PerfFormattedData_PerfDisk_PhysicalDisk WHERE Name="_Total"
Is this what I'm looking for or do I need to break it out more. Also, what is considered a "bad" value. I'd like to set the warning and critical states. The high for today was about 330 and it looks like it was during the DB maintenance.
-
-
Re: Monitoring Orion for Performance?
bshoppOct 7, 2009 9:19 AM (in response to jeff.stewart)
I think that is correct, here is a good article Dev gave me, see here, but less than '2' is good number for disk queue length.
-
Re: Monitoring Orion for Performance?
ecornwell Oct 7, 2009 3:34 PM (in response to bshopp)Thank you for your help. I've added the performance monitors and I'll keep track of them.
-
Re: Monitoring Orion for Performance?
ecornwell Oct 8, 2009 8:57 AM (in response to ecornwell)If I could get APM to be stable, I might be able to get some data. I checked before I left and it was collecting data then when I checked this morning it hadn't collected in 12 hours. I checked and both the Job services weren't running. I restarted them and got 1 collection and now it's been over 50 minutes since the last.
Yes... I opened (another) ticket....
Both NPM and APM have seemed extremely unstable recently. I had our DBA's put in something so it emails me when the NPM service stops putting data into the database. We took down the servers the other day to move the Database server from local storage to the SAN. It hasn't alerted since then but the APM service seems to be the one that is giving me fits now...
-
-
Re: Monitoring Orion for Performance?
bshoppOct 8, 2009 1:01 PM (in response to jeff.stewart)
At this point I would recommend a support call to get more research done
-
Re: Monitoring Orion for Performance?
Congo Oct 8, 2009 2:13 PM (in response to jeff.stewart)Ouch jeff.stewert
My database is the other way around, that is what my Write performance looks like(average 169), while my read performance averages .02
I am still attempting to convince my management that solid state is necessary.
-
Re: Monitoring Orion for Performance?
jeff.stewartOct 8, 2009 2:23 PM (in response to Congo)
We had problems with Write performance for a long time, but that is now under control. The Read performance seems pretty odd to me. I'll post what we find.
-
Re: Monitoring Orion for Performance?
warbird Oct 8, 2009 2:53 PM (in response to jeff.stewart)Jeff, do you have a very high number of people hitting your web front end?
-
Re: Monitoring Orion for Performance?
jeff.stewartOct 8, 2009 2:57 PM (in response to warbird)
We have 5 people on our network team using Orion.
-
-
-
-
-
Re: Monitoring Orion for Performance?
warbird Oct 8, 2009 1:34 PM (in response to ecornwell)To get an idea of what your avg disk queue length is doing, during a busy part of the day for your network, log into your SQL server, click start, run, type perfmon, and hit enter. One of the default counters already in place is the Avg Disk Queue Length.
Click the little light bulb button to highlight the counter you have selected, then click on the Avg. Disk Queue Length counter. The line on the graph representing disk queue length will be highlighted. Regardless of the highlighting, clicking on the avg disk queue length will show you the relative numbers (last count, min and max, and the average over the last 1.4 minutes). Watch this for a while. Keep a close eye on that average number. Per the Orion admin guide, that number should remain below twice the number of spindles that the db is stored on.
This is what I used to determine the disks on my old SQL server were not fast enough. I was getting insanely high average queue length (400 - 800). We were also running RAID5, which was half the problem. The other half was the disks were simply not fast enough. I took many screen shots of the perfmon window and a copy of the pertinent section of the Admin quide to my boss.
If the avg disk queue length ends up being your problem, let us know. It was with the advise from other folks around here that I solved it for my organization. Ended up having to do a couple of different things.
-
Re: Monitoring Orion for Performance?
warbird Oct 8, 2009 1:37 PM (in response to ecornwell)BTW: I just saw you said you moved your db to a SAN. Is your SQL server connected to it via fiber? How many spindles? Is your SQL server 32 bit Windows?
Just moving my db to a very high end SAN wasn't good enough. I also had to implement AWE to allot SQL more memory.
-
Re: Monitoring Orion for Performance?
patriot Oct 8, 2009 1:52 PM (in response to warbird)Sorry, but I have a dumb question. When I look at the average value for Avg Disk Que Length in Perfmon, I see numbers like 0.050. Is that really 0.05, or should I read that as 50? When you say a desirable limit is 2, do you mean 0.002 on the counter, or actually 2?
-
Re: Monitoring Orion for Performance?
warbird Oct 8, 2009 2:07 PM (in response to patriot)That is actually .02 and that is a very good number to see. If it were "50", it would actually read something like 50.04 or whatever. When I first inherited our Orion implementation, I was seeing numbers between 400 - 800. No joke. Right now, I am looking at a single spike within the measurement window such that the maximum number read 50.964 but the average (which is the important number) is still very low.
The admin guide indicates the average number should remain below 2 times the number of actual hard drives in the system that your db resides on.
-
-
-
-
-
-
-
-
-