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 |
|
hitman
Starting Member
23 Posts |
 Posted - 2008-01-04 : 06:24:36
|
| Is somehow possible to see a cached execution plan? I have one problematic stored procedure and often get a timeout. So every time I have to recompile it.I suppose it is out-of-date statistics problem.Thanks in advance-----------------------http://www.sqltips.info----------------------- |
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-04 : 07:26:23
|
| Sounds more like parameter sniffing. The SET SHOWPLAN_XML statement is useful for this as it tells you what parameter values the plan has been cached for, as well as the usual plan information. Not very readable, but save the output with a .sqlplan extension and you can open it graphically in SSMS.HTH |
 |
|
|
hitman
Starting Member
23 Posts |
Posted - 2008-01-04 : 07:50:26
|
| Is it possible that only SET SHOWPLAN_... ON forces server to recompile the execution plan? Because right now the procedure is ok (no timeout).I checked also statistics and it is really out-of-date.But despite that fact, proc is running ok now.I don't understand it :-(-----------------------http://www.sqltips.info----------------------- |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-04 : 08:36:05
|
quote:
Originally posted by hitman
Is it possible that only SET SHOWPLAN_... ON forces server to recompile the execution plan? Because right now the procedure is ok (no timeout).I checked also statistics and it is really out-of-date.But despite that fact, proc is running ok now.I don't understand it :-(-----------------------http://www.sqltips.info-----------------------
Are you certain the proc ran? quote:
From BoLSET SHOWPLAN_XML ONThis statement causes SQL Server not to execute Transact-SQL statements. Instead, Microsoft SQL Server returns execution plan information about how the statements are going to be executed in a well-formed XML document. For more information, see SET SHOWPLAN_XML (Transact-SQL).
|
|
|
|
hitman
Starting Member
23 Posts |
Posted - 2008-01-04 : 08:52:38
|
| My mistake, you're right. Still timeout.I've created a new proc with a same code and compared their executions plans. Of course they are different.I also updated statistics but probably I have to recompile procedure as well.Thanks-----------------------http://www.sqltips.info----------------------- |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-04 : 09:17:49
|
| hitman - google about "Parameter Sniffing" - it is likely that this is your problem. The greater the possible variance in the number of rows returned by this sproc the more likely parameter sniffing is catching you out. Otherwise post your sproc code and we can see if we can help turn things round. |
|
|
|
hitman
Starting Member
23 Posts |
Posted - 2008-01-04 : 09:31:55
|
| I do. Thanks for help.-----------------------http://www.sqltips.info----------------------- |
|
|
|
|
|
|
Query execution plan