Using Performance Monitoring

Hi folks,

new to the forum and new to MS SQL.

I am running a web application that utilizes an SQL DB backend.

There has been some excessive amounts of time when a user uses the web app to query the db (ie, clicks the link that displays the products received in the past 30 days).

I would like to rule out SQL as being the issue and am not sure what PERFMON Performance Objects or Counters to use...

Any good ones that you can suggest???

I'm running both the web app and the db on the same server on the same drive (not ideal i know)..

Dual xeon 866's with 1.9 gb ram (thinking maybe a ram issue)

I have looked at the page file counters and memory counters and it doesnt seem to be that though...

thanks in advance

Bruce

Luis and Satya,

Thank you for the replies...

I agree on the increase in RAM, should certainly help.

I just wanted to rule out the possibility of anything else...

I suppose i'm being a dork, i set up a log counter for the objects you specified..

Am i to post the reslts from the .csv file here?

Bruce

this is a sample over a period of roughly an hour, with at least 5 connections to the database via the web application during that time period.

Memory: Pages/sec 1.336
physical disk: avg disk queue length: .037
sql server: buffer: buffer cache hit ration: 99.933
processor: %processor time: 2.052
sql memory: target server memory: 1367061
sql memory: total server memory: 179938.640

thanks guys.

Bruce

thanks luis...

No, i understand the difference in the 2...i was just looking for some counters to use to help me determine if i was having a memory problem...

and as you said, it doesnt look like that is an issue...

Yet, the web app seems to take what seems like a long time to access the data...

I thought maybe memory, or maybe disk access was causing the problem...

I moved the detached and reattached to the db on a different drive and on the drive in which the web app is running on with the same results...slow access...

from the local machine, access the data takes roughly 8 - 15 seconds.... when coming in from the web to the application, it takes roughly 25 - 40 seconds to access...

Other than network bottleneck somewhere, do ya have other thoughts?

Bruce

hey luis,

thanks for the replies earlier...have ya had any more chance to think on my issue...

didnt mean to sound like a smart ass in my last reply...after i read it again, it kinda sounded that way...sorry....

unfortunately, i dont have additional ram to jam in this guy to see if that helps any...but would you agree, based on that performance average, that memory may not be the problem with slow access to the data via the web app?

bruce

brucecmc,

I say your post and thought I might be helpful,

When you say it takes 8- 15 seconds from the local machine, what exactly does that mean?

a) the web application running on the same server as the database
b) the same query running in query analyzer
c) something else

Have you monitored the query from profiler i.e. the duration ?
how many rows do you expect the query to return ?

hi philbird,

thanks for the reply...what i mean by the local machine is this:

I am on the server in which the web application is running...This server also houses the
SQL DB...when running on the "local machine", accessing the data when using the application by using one of the "query's" for, say, how many pieces of equipment have been recieved by a certain company in the past month, it takes 8 - 15 seconds.

If I do this same query from a client machine outside of the network in which the web app/DB server is in, it takes that same query takes 25 - 40 secs...

I have not looked at this using query analyzer or profiler as yet...i was ruling out sql's use of memory and i think we did that...quite honestly, as i am new to sql, i'll have to read up on how to use both of these tools for any useful information...

If you have any pointers, please feel free to provide them...I'm wide open for suggestions...

Bruce

brucecmc,

Satya has a good suggestion, Here's how I'd recommend you proceed.

we need to eliminate the webserver from the analisys, when you observe the query taking 8-15 seconds I belive what you are really observing is how long it takes for the webserver to:

receive the request,
process it,
run the query,
produce a response

we need to determine if the query is really the slow performer here or the webserver.

I'd recommend running profiler using the 'SQLProfilerStandard' template.
then run the web app, this will capture each sql command it executes.
then looking at the results, you need to make a judgement, is a paticular command very slow ? (remember duration is in milliseconds) look for 800 or above, or is the problem related to volume of commands?

if you are able to identify a couple of slow running queries as the problem, click on one,
the full text of the query will be displayed in the lower grey area.
copy the entire query text into query analyzer and execute it from there.

the status bar of query analyzer will record the execution time, though not with the same precission. if the result is simular to what was recorded in profiler and simular to what you experiance from the web app, then you need to begin reviewing the query plan and checking index placement etc.

if the query performs well from query analyzer and poorly when recorded in profiler the problem may be poor web app performance or other parallel queries, then I'd look at issues of concurrency and blocking.

if you only have a hand full of queryies and cannot identify a query with a duration > 800 then the database is not your problem.

let me know how this goes and we'll asses from there.

thanks for the help folks...<br /><br />we are presently moving the web app/db to another server (not my choice/decision, just how the cards fell out here)...<br /><br />I will run the commands you recommend on the old server (as it is still running), but at this time, my "crisis" has passed (heheh)...<br /><br />thanks again for the help and i'll let ya'll know what happens when i'm finished with the analysis...<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Bruce