O'Reilly logo

Microsoft SQL Server 2012 Performance Tuning Cookbook by Bihag Thaker, Ritesh Shah

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

Configuring "Optimize for Ad hoc Workloads"

Execution of any query or stored procedure for the first time creates an execution plan, which is stored in SQL Server 's procedure cache memory. It happens many times that we execute a simple query once, which is not even going to be used again anytime soon and it may never run again in future too even execution plan generated for that query will consume space in procedure cache. You may run out of cache sometimes, due to lack of memory, which affects performance. This was really a big issue till SQL Server 2005. In order to remedy this, Microsoft introduced "Optimize for Ad hoc Workloads" in SQL Server 2008, and it is still available in SQL Server 2012. This setting is instance-wide in SQL Server. ...

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