Microsoft SQL Server Performance Monitoring & Tuning

Main menu

Post navigation

About R Meyyappan

Ramesh Meyyappan (www.sqlworkshops.com) is a SQL Server specialist with expertise in Performance Tuning. Ramesh worked at Microsoft Corporation from 1994 to 2004. Nearly half of that time he worked in Redmond in the development teams - specifically as Program Manager in the SQL Server Development Team responsible for optimizing SQL Server product for SAP. Ramesh now offers onsite and offsite consulting and workshops independently as well as by partnering with various Microsoft Subsidiaries. LinkedIn Profile: http://de.linkedin.com/in/rmeyyappan.

In SQL Server, the concurrent creation of temporary tables from many sessions can lead to tempdb metadata contention. Tempdb metadata contention does not affect the concurrent creation of table variables.

When SQL Server creates temporary tables, it has to update metadata information in the system based tables, like sys.sysschobjs (like PAGELATCH_EX and PAGELATCH_SH waits). This overhead is not there for table variables. Tempdb metadata management overhead leads to faster table variable declaration than temporary table creation, which is demonstrated in the below video. The tempdb metadata management overhead associated with temporary table is due to the temporary table having different scope than table variable where it is limited to the batch of stored procedure. Concurrent creation of temporary tables from many sessions will lead to concurrent update of metadata information in the system based tables, which leads to tempdb metadata contention.

Below video with hands-on example demonstrates the tempdb metadata contention. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

With SQL Server AlwaysOn Availability Groups, when you configure a secondary in synchronous-commit mode for read-only access, there can be data access latency.

If you make changes to your data like if you perform insert, update or delete in primary, those changes may not be visible in the secondary, synchronous replica enabled for read-only access for a certain amount of time, because writing to Log is synchronous, but not REDO. REDO is asynchronous, that is, applying those changes from Log is asynchronous.

The below video demonstrates this with hands-on example. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

When SQL Server has a choice of plans, it will compare the cost among a set of execution plans and will choose the cheapest one.

In some cases, even though the cost of seek is slightly higher than the cost of scan, SQL Server Optimize might still choose a seek based plan as demonstrated in the video.

SQL Server may not evaluate all possible plans, there are various stages of optimization and SQL Server will evaluate a subset of plans at these stages.
The below video demonstrates this with hands-on example. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

SQL Server configuration parameter “optimized for ad hoc workloads” can be very useful when you execute lots of single use ad hoc statements or dynamic SQL statements by reducing memory usage of plan cache. If the majority of ad hoc statements or dynamic SQL statements execute more than once, then “optimize for ad hoc workloads” configuration parameter can increase the CPU usage due to additional optimization cost and hence it is not recommended in such scenarios

When the plan is stable, that is, when the query hash and query plan hash is the same for all executions, it is recommended that you parameterize these statements to reduce plan cache pollution.

The below video demonstrates this with hands-on example. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.

When it comes to lock waits, one of the important information we need to know is which statement waited for locks and how long. This is possible with extended events; it is not possible with SQL Server profiler.

locks_lock_waits event is very useful to find statements that waited for locks more than a certain amount of time.

When you are using extended events, always use the counters predicate to limit the amount of events you are collecting.

The below video demonstrates this with hands-on example. SQLTest Tool simulates the hands-on labs for you, no registration necessary. You can practice the online hands-on example while watching the video.