Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrsaif
Starting Member
43 Posts |
 Posted - 2006-09-24 : 21:58:33
|
| i want to see the execution plans that the sql server 2k stored for its T-sql or stroed procedures. What is the query to retrive all execution plans that are stored in Sql server and How to cleare these execution plans from sql server.Muhammad Saifullah |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-25 : 01:35:44
|
| [code]-- Clear cache (for level playing field -- - only if wanting to check PHYSICAL performance rather than LOGICAL performance)-- Do **NOT** run these on a Live serverDBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE-- Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats-- SET SHOWPLAN_TEXT ONGO-- SET STATISTICS IO ON; SET STATISTICS TIME ON-- ... put query here - e.g.:SELECT * FROM Northwind.dbo.ProductsSET STATISTICS IO OFF; SET STATISTICS TIME OFFGOSET SET SHOWPLAN_TEXT OFFGO[/code]See also: See http://www.sql-server-performance.com/statistics_io_time.asp for more detailsKristen |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-26 : 03:43:35
|
| also check out Select * From master..syscacheobjectsChirag |
|
|
|
|
|
|
where execution Plans are stored?