Query Post Execution Showplan event is a very useful event to find problematic queries and execution plans based on CPU usage or duration while analyzing performance issues.

It can increase the execution time of all queries by a fraction of a millisecond, irrespective of the total query cost, which means, the overhead of query_post_execution_showplan event is high for cheaper queries compared to expensive queries.

In all cases, one should enable the event on an ad hoc basis and with restrictive predicate to limit the number of events.

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

In SQL Server, concurrent writes or read / write to the same page can lead to PAGELATCH (like PAGELATCH_EX and PAGELATCH_SH) waits or what is knowns as PAGELATCH contention.

One common use case is when invoice numbers are stored in a table. If the row is narrow, then many rows can fit in the 8KB page. This can lead to many invoicing number sequences being stored on the same page. When different invoicing number sequences are accessed from concurrent sessions, this can lead to PAGELATCH waits.

The way to solve this is to widen the rows so each row takes about a page and we don’t have contention on the same page.

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

In SQL Server, the concurrent creation of temporary objects (temporary tables and table variables) from many sessions can lead to tempdb allocation contention. This contention occurs on PFS and SGAM pages in tempdb (like PAGELATCH_EX and PAGELATCH_SH waits). It is recommended to create additional data files for tempdb and implement trace flag 1118 to reduce this type of contention. Creating temporary objects, in a stored procedure will lead to temp table caching, this can also help reduce tempdb allocation contention.

After addressing Tempdb Allocation Contention, you will most probably encounter Tempdb Metadata Contention when you are using temporary tables (not when you are using table variables due to Temp Table Caching).

Below video with hands-on example demonstrates the tempdb allocation contention and explains the steps to reduce 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 www.sqlvideo.com for additional hands-on videos.

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

Starting SQL Server 2014, it is possible to use memory optimized table variables. These are table variables declared using a table type which is memory optimized. Memory optimized table variables have no disk footprint and don’t have PAGELATCH (like PAGELATCH_EX and PAGELATCH_SH) or LOGBUFFER waits, hence they result in faster performance compared to traditional disk based table variables.

Memory optimized table variables can be used in place of disk based table variables when possible.

Below video compares the performance difference between memory optimized table variables and traditional disk based table variables. 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 caches temporary objects (temporary tables and table variables), that are created in a stored procedure. Temporary objects that are created either in dynamic SQL statement or by using sp_executesql are not cached. Temp table caching can lead to better performance by reducing Tempdb Allocation Contention. SQL Server will not cache temporary table in certain cases, for example, if a DDL operation is performed on the temporary table (dropping temporary table is not a problem), or if a named constraint is created on the temporary table.

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