SQL Server Performance

What is the best tool to use for I/O counts

Discussion in 'Performance Tuning for DBAs' started by dl@uni-collect.com, Aug 26, 2005.

  1. dl@uni-collect.com New Member

    We just have moved our application to run on SQL. We use a middleware product from ASNA that allows us to target the DB400 or SQL data base. Since there is a layer there, and we do not have a lot of control over the commands issued it makes things difficult. That is my problem, the question for this forum is this. What is the best way I can identify what tables are getting hit, and the number of I/Os that are occuring. I want to identify the correct tool, then start a process that is not performing well and figure out what tables it is hitting, and how many times. Thanks in advance!

    Dan Lewis
    dl@uni-collect.com, Aug 26, 2005
    #2

  2. Chappy New Member

    SQL Profiler will show all procs and statements being issued, along with duration and detailed IO stats. Profiler comes with SQL Server, on your start menu
    Chappy, Aug 27, 2005
    #2

  3. Luis Martin Moderator

    Agree, but no easy. After run profiler, you have to find high reads and writes. Then copy and paste to Query Analyzer those queries to find out tables.


    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.



    Luis Martin, Aug 27, 2005
    #2

  4. ghemant Moderator

    Hi,
    In my opinion Windows Performance Monitor would be gr8 tool for IO counts .



    Regards




    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
    ghemant, Aug 27, 2005
    #2

  5. Luis Martin Moderator

    But with Performance you can´t find witch tables has high I/O.


    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.



    Luis Martin, Aug 28, 2005
    #2

  6. dl@uni-collect.com New Member

    I look at the information profiler gives and it is exactly what I want, but I question what I am reading. For example, one table only has 167 rows, but profiler shows that it did 1,784 reads, when actually it should read one row, but even if it selected all rows because of bad coding that would be 167. I guess I need to better understand what the READ column really means.

    Dan Lewis
    dl@uni-collect.com, Aug 28, 2005
    #2

  7. Luis Martin Moderator

    In profiler, reads means: Logical Disk Reads.

    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.



    Luis Martin, Aug 28, 2005
    #2

  8. ghemant Moderator

    Dear Sir,

    quote:Originally posted by LuisMartin

    But with Performance you can´t find witch tables has high I/O.


    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.
    As you have said i am agree if we need to observe the high read of the table then we have to go using Logical Disk Read. [:I]

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
    ghemant, Aug 28, 2005
    #2

  9. dineshasanka Moderator

    There are several tool you can find
    I have used Coefficient check in the tool spot light section
    dineshasanka, Aug 28, 2005
    #2

  10. FrankKalis Moderator

    Don't spent too much time analyzing IO. Especially logical IO can be misleading at times. Every time SQL Server accesses a page, logical IO is increased. Even if the page is already in memory and therefore doesn't contribute that much to query cost anymore. Personally, I think, the "best" tool to identify poor performing queries is your watch. So, I would rather watch duration and go from there.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, Aug 29, 2005
    #2

Share This Page