3

I need to optimize a legacy stored procedure, with over 2.000 lines, but I'm unable to view the execution plan, when I try to do so, the client crashes.

The procedure executes over 200 queries during the execution, if I mark the option to generate the actual execution plan, but I don't click on the execution plan tab, it finishes without errors, but when I try to see the execution plan itself, the client crashes, it doesn't display any errors, simply closes and opens again, the system messages also do not display any error message.

Is there another way to view the execution plan, or any other option, except rewrite the legacy code, that I am not familiar with? thank you.

Thiago Perotto Zocoli
  • 99
  • 1
  • 5
  • What is the version of SQL Server? And what is the version of SQL Server Management Studio (SSMS)? – miroxlav Jun 21 '16 at 20:15
  • Worth a read? http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – P.Salmon Jun 21 '16 at 20:25
  • An execution plan that large is going to inhale system resources to display the visual representation. To be honest a stored procedure with over 200 queries spanning 2,000 lines is not in need of optimization, it is in need a complete re-architecture. That sounds like a procedure that has lots and lots of embedded business logic. – Sean Lange Jun 21 '16 at 20:35
  • You can try to use a free version of [SQL Sentry Plan Explorer](http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view) instead of SSMS. – Vladimir Baranov Jun 22 '16 at 01:21
  • SQL Server 2012, SSMS version 11.0.5058.0 @ Miroxlav – Thiago Perotto Zocoli Jun 22 '16 at 12:47
  • @P.Salmon, I'll read the article, thank you. – Thiago Perotto Zocoli Jun 22 '16 at 12:52
  • @Sean Lange, I am aware of the issue, and I completely agree with you that the ideal scenario would be a comple re-architecture, but I do not have the necessary resources neither time available to perform such action, that is why I was aiming for the execution Plan – Thiago Perotto Zocoli Jun 22 '16 at 12:52
  • @Vladimir Baranov, I'll take a look at this software, thank you. And thank you all for the comments and sugestions, I really appreciate the imput. – Thiago Perotto Zocoli Jun 22 '16 at 12:52
  • My concern is with a procedure that large any performance enhancing is going to time wasted. It is such a massive amount of code that it is going to be incredibly difficult to make major improvements in performance. I hope you are able to find a solution though. – Sean Lange Jun 22 '16 at 13:22
  • I know this is old, but a procedure using a loop to process millions of records in batches does cause my SSMS 18.6 to be slow and then crash if the execution plans are included. If SSMS can't handle it, it should gracefully respond. Perhaps there should be a limit to the number of plans collected by SSMS. – Randy in Marin Mar 17 '21 at 20:00

0 Answers0