posted 8/14/2012 6:18:08 AM by akash gautam - Views: [2736]
set statistics io on select a.[LoginID],a.BirthDate from [HumanResources].[Employee]as a,[HumanResources].[EmployeeDepartmentHistory] as b where a.BusinessEntityID=b.BusinessEntityID and b.ModifiedDate= (select Max(ModifiedDate) from [HumanResources].[EmployeeDepartmentHistory] as c where a.BusinessEntityID=c.BusinessEntityID)
use AdventureWorks2012 go set statistics io on select v.[LoginID],v.BirthDate from (select a.[LoginID],a.BirthDate,b.ModifiedDate,Max(b.ModifiedDate) over(partition by b.BusinessEntityID ) as M33n@l from [HumanResources].[Employee]as a,[HumanResources].[EmployeeDepartmentHistory] as b where a.BusinessEntityID=b.BusinessEntityID ) as v where v.ModifiedDate=v.M33n@l
akash gautam (Member since: 6/2/2012 3:31:51 AM)
View akash gautam 's profile
Hey Akash,
Nice blog. I had a question here :
The physical read, as you mentioned influences the cost, for the first time the query hits SQL SERVER, assuming the cache doesn't get cleared out, the subsequent hits wil read data from the memory.
Considering the example above, you can clearly see that the physical reads are zero(0) indicating that the data is already present in the cache and the SQL SERVER need not go back to the files to fetch the data.
So I believe we should concentrate on reducing the logical reads instead of the physical ones. This is in my opinion. Is it different in your line of thinking?
Whenever query hits first on a database parsing ,optimization,code generation and execution occurs,to avoid parsing and optimization phase SQL Server stores SQL Server query plan in plan cache.We can retrieve plan by querying sys.dm_exec_cached_plans,if you will see the XML plan,it will tell you the estimated subtree cost,now takes its text from dm_exec_query_text and run the same query including actual execution plan.If statistics of columns has not been changed drastically the estimated cost of the plan present in cache and the actual execution plan will remain same.It means it is using plan present in Plan cache, if stats has been changed by huge amount ,it will create a new plan.
What i want to say is the cost which is coming here is the first time query was compiled so it depends on physical reads and not much on logical reads.hope you understand
Akash,
Thanks for the reply. If I understand your explanation properly, each time a query is executed, the execution plan that the optimizer uses considers that the data is read from the disk and not from memory?
Or am I not understanding the point here?
-Thanks in advance,
Ananthram
Yes
Reading data from Memory is very fast,you can check buffer cache hit ratio,or L1,L2,L3 cache.Cost of query depends on number of free pages present in Memory(Free Memory available) which will directly influence your Logical reads.
Logically, Cost should be independent on the type of resources present in your server ,If I am accessing data from SAN and then from SSD,duration of the query would be different in both the cases,but cost of query will remain same,same goes whether you have AMD processor or INTEL
Leave a comment