O'Reilly logo

Writing Stored Procedures for Microsoft SQL Server by Matthew Shepker

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Execution Plans

With the release of SQL Server 7.0, Microsoft changed the way execution plans were used and maintained in memory. With SQL Server 6.5 and earlier, the chances of an execution plan being reused were pretty slim. This caused some extra overhead because SQL Server would have to reevaluate the stored procedure every time it was executed. With SQL Server 7.0 and beyond, execution plans have a much higher chance of being reused.

As with previous versions of SQL Server, after the memory for system data structures is allocated from the overall amount of memory, the remaining memory is divided into two portions:

  • A percentage of the memory is allocated for use as procedure cache, which is used for storing execution plans.

  • The other portion ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required