Thanks for your reply. I was looking for a way other than searching the plan cache.

I was using the same DMVs but your script is a little bit more complete.

from Hard Drive

Physical Reads= data pages read from hard drive

Logical Reads= data pages read from buffer pool (data catch)

you can read the log using "fn_dblog" function

but i didn't understand why u would need that

query the sys.databases "log_reuse_wait_desc" column.It could be for several reasons.

just for the count(*) using set statistics io on

thanks Gila I'm a new DBA in this company and there's lots of bad written queries like this one . The query need to optimized obviously :crazy:

the "MyPlan" is the plan for the whole query and the "NarrowIndexplan" is just the "select count(*) ..." plan with nrrower index being used and also the "largerindex" is the...

the query is not just the "SELECT count(*) FROM PointCurrent WHERE PointTypeID=1" it's an left join query like this "SELECT count(*) FROM PointCurrent WHERE PointTypeID=someOthertable.PointTypeID"...

the cost of seeking the larger index was 77% of the whole Sp execution cost.

for the query I mentioned "select count(*) ..." the logical reads dropped from 1023 to...

set the "optimized for adhoc workload" database option this reduces the size of adhoc plans in your cache

Bhuvnesh (10/12/2012)

So you mean to say here that another ( single...

you're right the fill factor is set to 80%.It's true the smaller index...

SQLSACT (9/19/2012)

pooyan_pdm (9/18/2012)

SQLSACT (9/18/2012)

Thanks

And that would give me an accurate % of plan-reuse in my instance?

What is the...

SQLSACT (9/18/2012)

Thanks

And that would give me an accurate % of plan-reuse in my instance?

What is the Cache Hit...