Ramesh Meyyappan’s SQL Server Performance Tuning Blog
Archives: July 2012
Plan Caching and Query Memory Part 2: When not to use stored procedure or other plan caching mechanisms like sp_executesql or prepared statement
SQL Server estimates Memory requirement at compile time, when stored procedure or other plan caching mechanisms like sp_executesql or prepared statement are used, the memory requirement is estimated based on first set of execution parameters. This is a common reason for spill over tempdb and hence poor performance. Common memory… Read more
1 comments, 1,636 reads
Posted in Ramesh Meyyappan’s SQL Server Performance Tuning Blog on 18 July 2012
Workspace Memory / Query Memory Tuning – RESOURCE_SEMAPHORE / IO_COMPLETION / SLEEP_TASK Waits
SQL Server is configured to use a lot of memory, but my query is slow and not using all the memory available and it is spilling the sort or the hash match operation to tempdb, how can you tune the configuration and the query?
Memory allocating queries request memory based… Read more
0 comments, 2,138 reads
Posted in Ramesh Meyyappan’s SQL Server Performance Tuning Blog on 18 July 2012
Plan Caching and Query Memory Part 1: When not to use stored procedure or other plan caching mechanisms like sp_executesql or prepared statement
The most common performance mistake SQL Server developers make:
SQL Server estimates memory requirement for queries at compilation time. This mechanism is fine for dynamic queries that need memory, but not for queries that cache the plan. With dynamic queries the plan is not reused for different set of parameters… Read more
0 comments, 2,240 reads
Posted in Ramesh Meyyappan’s SQL Server Performance Tuning Blog on 18 July 2012