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.

Check out http://www.sqlvideo.com for additional hands-on videos.

Subscribe to our newsletter: https://newsletter.sqlworkshops.com

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.

Check out www.sqlvideo.com for additional hands-on videos.

Subscribe to our newsletter: https://newsletter.sqlworkshops.com

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.

Check out www.sqlvideo.com for additional hands-on videos.

Subscribe to our newsletter: https://newsletter.sqlworkshops.com

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.

Check out www.sqlvideo.com for additional hands-on videos.

Subscribe to our newsletter: https://newsletter.sqlworkshops.com

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.

Check out www.sqlvideo.com for additional hands-on videos.

Subscribe to our newsletter: https://newsletter.sqlworkshops.com