SQL Server Performance

Using Performance Monitoring

Discussion in 'Performance Tuning for DBAs' started by brucecmc, Jan 26, 2005.

  1. brucecmc New Member

    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
  2. Luis Martin Moderator

    Memory: Pages/sec.
    Physical disk: Avg. Disk Queue Length.
    Processor: %Processor Time.
    SQLServer: Buffer Manager:Buffer Cache Hit Ratio.
    SQLServer: Memory Manager: Target Server Memory and Total Server Memory.

    Begin with those and let us know your results.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

    Adding more physical RAM to machine and to SQL will have optimum performance affect in this case, and now a days its not expensive to do so.

    Collect the counters specified by Luis for further asessment.

    Also use PROFILER to monitor the slow running queries and take help of index tuning wizard for better performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. brucecmc New Member

    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
  5. Luis Martin Moderator

    No, just post Average of each counter.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  6. brucecmc New Member

    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
  7. Luis Martin Moderator

    I don't see any issue in those counters.
    May you confuse target with total and viceverza?. Because Target is what SQL try to reach, and total server is what SQL actual reach.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  8. brucecmc New Member

    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
  9. brucecmc New Member

    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
  10. satya Moderator

    BTW, confirm the REDINEX schedule on the database and also intermittent execution of UPDATE STATISTICS on volatile table will give more optimum performance, as an interim solution until you get the more memory and try to otpimize the queryies and fine tune them.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. PhilBird New Member

    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 ?
  12. brucecmc New Member

    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

  13. satya Moderator

    Also check the concurrency issues on database (such as read committed) and may be all the users accessing the same table simultaneously, with the bad application design this may contribute slow performance. And during this operation excessive query operations will have stress on SQL resources by consuming most the available memory.

    As explained for starters, run profiler and see what is going on and also check whether any blocking issue persists.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. PhilBird New Member

    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.
  15. brucecmc New Member

    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=':D' />]<br /><br />Bruce

Share This Page