suhasini.m (3/27/2009)
Thanks for the detailed reply.I am running both the quries at the same time and also doing DBCC DROPCLEANBUFFER and DBCC CLEARPROCCACHE before for both the queries.
If this is a table or index scan howz the execution cost of 2nd query is less than the first one. Ultimately a table or index scan is costlier than an index seek on this table as it has some millions of rows. I have checked the query plan and there was no index or table scan on this table. I will try and post the plan.
You just have to remember that those are estimated costs, not actual costs. They're useful as a general measure, but not as a real determining factor. Check out the situation in this blog post[/url] to see why.
Is there a way to check the number of bytes of data that is being fetched by a sproc per execution if not a query?
Try checking out sys.dm_exec_query_stats. It's an aggregate, but it'll tell you what you want to know.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning