Post reply

SQL Profiler Reads and Writes Question?

  • February 4, 2010 at 1:13 am

    #210519

    Hi All;

    I am running a SQL Server Profiler Trace and getting below mention result (snapshot and pdf Report attached)

    TextData Reads Writes CPU

    T-SQL Query 12458 25 6580

    SP 600456 600 8000

    As per Reads and Writes operation; can I calculate data transfer from Disk to Memory / or Data Memory to Memory (Buffer Pages) in Bytes or MB as below

    1 Read = 8 K Page

    Total MB Reads = (Reads*8)/1024 is this right?

    Total Kilo Bytes (KB) Writes = (No of Writes * 8) is this right?

    And second question is that if I am using Temp Tables or Table Variable in my T-SQL, or Stored Procedure then those reads/writes related to Temp tables or Table Variable does includes?

    I know I am not explaining well but I guess you understand what I would like to know.

    Thanks in Advance

    Ram
    MSSQL DBA

  • February 5, 2010 at 1:10 am

    #1114218

    profiler includes reasds/writes from all/most internal operations too, this is why the same query run with show io stats in a query window will usually show a different set of values to profiler, this is especially so for more complex queries.

    If you're tuning you should only use one method to track your impact.

    as to the 8kb x io calculation, yes essentially you are correct although reading the same page 1000 times is still 1000 io, same as reading 1000 pages once, but the impact of how much data may have been brought into the buffer cache, if not in cache, is different. You can query the dmvs to see how much of each index/table is sat in cache after a query.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply