cache plan

Question

cache plan

ramyours2003

SSChampion

Points: 12266
More actions
March 19, 2013 at 8:39 am

#275358

what is query cache plan , list the dmv's to trouble shoot poor performance of a query in sql 2005/2008?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

ramyours2003 (3/19/2013)
what is query cache plan , list the dmv's to trouble shoot poor performance of a query in sql 2005/2008?

1. Google "what is query cache plan" - first link in the search results

http://msdn.microsoft.com/en-us/library/bb738562.aspx

2. Google "sql server dmvs list" - second link in the search results

http://social.msdn.microsoft.com/Forums/en-US/searchserverinstallconfigandadminstration/thread/2221d017-8ae1-4940-9895-1b0ad2e357b8/

For some reason, I don't think that my answers will be enough for you to pass through technical interview or exam...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 2

Sound a lot like interview questions, but since this is hours on...

The cache is where the query plans that have been compiled by the query optimizer are stored in order to reuse them. Not all plans are stored there because plans age out of cache and some plans are never stored there (such as queries with a RECOMPILE statement).

Primarily you want to look at dynamic management views that start with sys.dm_exec_*. Those are the ones primarily used for performance monitoring, but you also have sys.dm_os_wait_stats and others.

"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