SQL Server: Some Quirks in the Cache, Part II
Sep 27,
2017
Some Surprising Traits of SQL Server Query Caching In the previous section<link SQL Server: Some Quirks In the Cache Part 1>, we saw that it is easy to observe query execution plans being entered into the procedure cache by SQL Server. Now that the tools are in place, we will observe some unexpected behavior of […]
SQL Server: Some Quirks in the Cache, Part I
Sep 26,
2017
Monitoring the Procedure Cache Getting the best performance from SQL Server is a complex task involving virtually every aspect of the system. Memory management is one of the most important aspects, of course. The primary concern is memory for data itself, but that doesn’t mean we can ignore the procedure cache. Procedure cache is the […]
LINQ: The Good, The Bad, and The Poorly Optimized (Part 2)
Sep 20,
2017
Part 2: Controlling Parameter Optimization with Plan Guides For Part 1 of this series, see LINQ: The Good, The Bad, and The Poorly Optimized. T-SQL supports a variety of “hints”. They are hints in the same sense that your mother used to hint that it’s time to wash up and come to dinner. In other […]
LINQ: The Good, The Bad, and The Poorly Optimized (Part 1)
Sep 12,
2017
Part I: LINQ and sp_executesql Numerous questions raised in numerous forums mention that LINQ to SQL wraps queries in a call to the stored procedure sp_executesql. There seems to be agreement that this is done to ensure the caching of the query and query parameterization (if, indeed, parameters are supplied). Some advantages to sp_executesql, like […]
Viewing the SQL Server Procedure Cache with DMVs
Sep 6,
2017
In the previous blog, we took a quick look at query plan caching using the classic but still serviceable syscacheobjects view. A serious journey of exploration into the SQL procedure cache must, however, begin with the dynamic management view sys.dm_exec_cached_plans. While this view does provide us with some interesting information in its own right, the […]