SQL Server Performance

Different query costs returned by SET STATISTICS IO ON and sys.dm_exec_query_stats

Discussion in 'SQL Server 2005 General DBA Questions' started by fabi, Jan 7, 2010.

  1. fabi New Member

    This is my first message here so firstly i would like to say 'Hello'.

    I have a problem i hope that you will help me because i am stuck.

    1. At the beginning i cleaned the buffer(data cache) executing DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS

    2. Then i run SET STATISTICS IO ON to have a statistics after query execution.

    3. I run select * from [table] and i've got following message in sql serv management studio in Message tab :
    10000 row (s) affected)
    Table 'person'. Scan count 1, logical reads 318, physical reads 0, read -
    ahead reads 332, lob logical reads 0, lob physical reads 0, lob read -
    ahead reads 0


    In this point i have a question. Why i have physical reads 0 although this was a first execution of query?? What's more i cleaned buffers so i expected that i will be have many physical reads! This is what i dont understand at all.

    4. I found in the internet that i can check also costs of executed query in sys.dm_exec_query_stats table so i found a row which stored costs of "select * from [table]" and compare result with the results from point3. What's occured.
    In the table sys.dm_exec_query_stats i found following data [column - value]:
    last_physical_reads - 13 ( in point 3 i have 0)
    last_logical_reads - 322 (in point 3 i have 318 )

    So the most important question of this topic is why i have these differences and which data are correct. These from point 3 or this from sys.dm_exec_query_stats??

    I hope you understand me correctly and sorry for my mistakes i don't speak english very well :)

    Regards!

  2. moh_hassan20 New Member

    Welcome to the forum
    DROPCLEANBUFFERS remove all database pages from the memory , and it should be physical read after that statement
    I simulated your case , as you described , and found , as expected , phsical read

  3. fabi New Member

    weird because i also run DBCC DROPCLEANBUFFERS so i also had clean memory before executing my query.
    Please tell me in comparision to my steps. Where did you get physical reads ?? in point nr 3 or 4 or both?? could you compare your results(from step3/this from Message tab) with results from sys.dm_exec_query_stats ? are they the same? ( it means that physical reads from Message tab is the same as last_physical_reads from table and the same with logical?)
    Thanks for help
  4. moh_hassan20 New Member

    [quote user="fabi"]Where did you get physical reads ?? in point nr 3 or 4 or both?? [/quote]
    at point 3
    here is my steps
    1) DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    2) SET STATISTICS IO ON
    3) select * from HumanResources.Employee
    physical read = 2
    4)
    select ST.text , last_physical_reads ,last_logical_reads , last_execution_time
    FROM sys.dm_exec_query_stats AS QS
    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
    order by QS.last_execution_time desc
    review the ouput that match the query text
    5) select * from HumanResources.Employee
    physical read =0
    i guess, that you read 0 for the physical read that match the query of sys.dm_exec_query_stats , not your query select * from person
    let me know your discoveries by executing my steps


  5. fabi New Member

    hmmm it looks that i am doing something wrong. Now i am on holiday so i am not able to retest it again according to your steps but i will do it on Wednesday when i back and let you know about my results. Thanks for reply.
    Cheers
  6. fabi New Member

    Mohan
    i've just retested again according to your steps.
    after first execution of select* from osoba
    in step 3 i got "(5000 row(s) affected)
    Table 'osoba'. Scan count 1, logical reads 186, physical reads 1, read-ahead reads 183, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0."
    in step nr 4 the columns last_physical_reads = 5 and last_logical_reads = 186 i used exactly your query.

    So generally information displayed in "Messages" tab is different than data from sys.dm_exec_query_stats table.Logical reads are the same but not physical . Any idea why??
    Maybe i will show you how my table looks like :
    /****** Object: Table [dbo].[osoba] Script Date: 01/13/2010 23:34:21 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[osoba](
    [id_osoby] [int] NOT NULL,
    [imie] [varchar](20) NOT NULL,
    [nazwisko] [varchar](20) NOT NULL,
    [login] [varchar](20) NOT NULL,
    [haslo] [varchar](20) NOT NULL,
    [ulica] [varchar](100) NOT NULL,
    [numer_domu] [varchar](100) NOT NULL,
    [nr_mieszkania] [varchar](100) NOT NULL,
    [kod_pocztowy] [varchar](100) NOT NULL,
    [miasto] [varchar](100) NOT NULL,
    [telefon] [varchar](100) NOT NULL,
    CONSTRAINT [PK_OSOBA] PRIMARY KEY CLUSTERED
    (
    [id_osoby] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

  7. Adriaan New Member

    BOL has a note for this DMV -
    "An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query."
  8. fabi New Member

    but there is no workload. i am using locally hosted database so there is no other users using this database. i've started thinking that i will never solve this issue [:(] but on the other hand i can not do my task without solving this problem so generally i am stuck
  9. Adriaan New Member

    Well, they are statistics, so they will only make sense after a solid number of executions.
    I'd concentrate on the IO results, not the DMV.
  10. moh_hassan20 New Member

    I agree with Adriaan , use SET STATISTICS IO , because it is the official command that used for analysing IO performance.
    You can post that bug to MIcrosoft
  11. fabi New Member

    Adriaan. But DMV after first execution of query with clean buffers shows more physical reads than IO so it seems that DMV stats are more corrected than IO. when i run second time then DMV shows zero physical reads as supposed. The most important question why IO and DMV shows me different physical reads after first execution of query. ( of course with clean buffers so memory is empty). If i find answer i will be in heaven :)
  12. moh_hassan20 New Member

    [quote user="fabi"]when i run second time then DMV shows zero physical reads as supposed[/quote]
    kindly , zero physical reads is for the query of dmv , not your query , try using my script that i posted before hand.
  13. fabi New Member

    Moh_hassan20
    I am using your query i am finding executed query not DMV it means select * from osoba so i am pretty sure that i am looking on good row.
    With 5000 rows in table i have:
    First time execution: DMV displayed 5 physical reads IO 1
    Second time execution: DMV displayed 0 and IO also 0 . Everything was moved to the memory so both shows true.
    But why after first execution i have difference ??
    Moh you said few posts ago that you don't have such differences on your environment. Could you provide me a CREATE script of your table? Maybe i will try retest using your table and my problem will disappear. Please tell me also how many rows i should load to this table. If you want we can switch on private channel :)
  14. moh_hassan20 New Member

    [quote user="fabi"]Moh you said few posts ago that you don't have such differences on your environment. Could you provide me a CREATE script of your table? Maybe i will try retest using your table and my problem will disappear[/quote]
    i didn't say that " don't have such differences" ,but there is was a physical read after first execution not zero value.
    i used adventureworks ,HumanResources.Employee table

    The phsical read is 2 using statistics IO , and 4 (not the same value) using DMV, It is duplicated
    but , generaly , we use DMV for statiscs, so it may be inaccurate

  15. fabi New Member

    [quote user="moh_hassan20"]
    The phsical read is 2 using statistics IO , and 4 (not the same value) using DMV, It is duplicated
    but , generaly , we use DMV for statiscs, so it may be inaccurate

    [/quote]
    oh i see. sorry ! We had misundestanding :) So you have the same problem as me. Why do you think that statistics IO are more reliable than DMV? i have feeling that IO is less. i will tell you why. Because when my table contained low amount of rows for example 1000rows IO showed 0 physical reads( what in my opionion is nonsense because memory was clean so after first execution of query we have to have any physical reads!) and DMV showed in the same time 5 physical reads and it has sense. Do you agree with me?
  16. moh_hassan20 New Member

    one of advices, when tuning queries using Statistics IO , is to care to logical read , and don't care for physical read , because it will be cached sooner or latter.
    The less logical you get , the better performance when applying suitable indexes in tuning queries.
    Have a look for http://www.sqlprof.com/blogs/sqlserver/archive/2008/03/23/understanding-set-statistics-io-and-set-statistics-time.aspx
    [quote user="fabi"] Do you agree with me?[/quote]
    I prefer using Set statistics IO [:)]
  17. fabi New Member

    oh i see but my task is to check also physical reads after first execution of query when the buffers are cleaned. So i see good resolution. I will be take physical reads after first execution from DMV [:)] and next stats i can take from IO when the only logical reads are important [:)]
    Thanks a lot for helping.
  18. moh_hassan20 New Member

    let us make some math for query:
    show execution plan for query, get the following data:
    no of rows =290 row
    size of record = 398 byte
    total size of table ~= 290 * 398 = 115420 byte
    as page = 8 K
    total size of table in pages = 115420 /(8*1024) ~= 14 Page

    from statistics
    logical read = 9 pages
    so phsical read should be = 14 - 9 = 5 pages
    it seems that DMV is reasonable[:D]

    try to apply these calculations with your case
    So, go ahead with DMV stats
  19. fabi New Member

    With my numbers these calculations are not equal each other.
    I took the number from graphic execution plan from 'this yellow table' when you put the cursor on.
    Estimated number of rows: 5000
    Estimated row size: 374
    as page 8 K
    total size of table = 5000 * 374 = 1 870 000
    so 1870000 / (8*1024) = 228 pages
    DMV shows physical reads 5, logical reads 186
    228-186= 42
    according to your math there should be 42 physical reads but DMV shows 5 :)

Share This Page