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

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!

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

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

[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

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

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

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."

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

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.

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

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

[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.

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

[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

[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?

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 []

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.

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[]

try to apply these calculations with your case
So, go ahead with DMV stats

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