Subscribe to this blog
Ramesh Meyyappan’s SQL Server Performance Tuning Blog
SQL Server 2012: Indirect Checkpoint (Target Recovery Time), get the real scoop
SQL Server 2012 brings a new feature called Indirect Checkpoint. You can read more about it here: http://msdn.microsoft.com/en-us/library/ms189573.aspx.
With Indirect Checkpoint, you get smaller and too many I/Os
Checkpoint normally writes large blocks of data to disk optimally in single write operation, up to 256KB, depending on the number… Read more
0 comments, 191 reads
Posted in Ramesh Meyyappan’s SQL Server Performance Tuning Blog on 19 March 2013
Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance
When you create a temporary table you expect a new table with no past history (statistics based on past existence), this is not true if you have less than 6 updates to the temporary table. This might lead to poor performance of queries which are sensitive to the content of… Read more
0 comments, 373 reads
Posted in Ramesh Meyyappan’s SQL Server Performance Tuning Blog on 15 August 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, 487 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, 608 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, 466 reads
Posted in Ramesh Meyyappan’s SQL Server Performance Tuning Blog on 18 July 2012
SQL Server IO handling mechanism can be severely affected by high CPU usage
Are you using SSD or SAN / NAS based storage solution and sporadically observe SQL Server experiencing high IO wait times or from time to time your DAS / HDD becomes very slow according to SQL Server statistics? Read on… I need your help to up vote my connect item… Read more
2 comments, 1,229 reads
Posted in Ramesh Meyyappan’s SQL Server Performance Tuning Blog on 29 May 2012
Prefetch – Querying at the speed of SAN
Prefetch is a mechanism with which SQL Server can fire up many I/O requests in parallel for Nested Loop join.
The SAN administrator says your data volume has a throughput capacity of 400MB/sec. But your long running query is waiting for I/Os (PAGEIOLATCH_SH) and Windows Performance Monitor shows your data… Read more
0 comments, 405 reads
Posted in Ramesh Meyyappan’s SQL Server Performance Tuning Blog on 7 January 2012