In SQL Server, Filtered Statistics can improve cardinality estimation, i.e. when joining lookup table, or while joining fact table and dimension table. For this reason, SQL Server supports the creation of up to 30,000 statistics on non-indexed columns. Better estimation with filtered statistics can lead to faster query execution against star schema based data warehouses, in many cases by estimating the optimal join strategy, amount of memory and executing the query in parallel.

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, for insert into select statements, when the target for insert into is temporary table, the select statement can execute in parallel. When the target is table variable, SQL Server will not execute the select statement in parallel, which can lead to poor performance.

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 creates and maintains statistics for temporary tables, which lead to better cardinality estimation and optimal execution plan generation. Table variables have no statistics, which can result in poor cardinality estimation and non-optimal execution plan creation. When you add query hint option (recompile) when using table variable, SQL Server can use heuristics based estimates, taking into consideration the total number of rows in the table variable; this can be helpful in some scenarios.

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, ASYNC_NETWORK_IO wait time can be high due to slow network, like when the database is in the cloud and the application is on premise. Furthermore, it can be slow when CPU utilization is high in the application server preventing timely fetching of all rows or, in most cases, when the application reads a row, does some work processing the row, before reading the next row. When the server sends data, the ASYNC_NETWORK_IO wait time will not increase beyond 2000ms. After 2000ms the server will increase the wait count and reset the wait time, which is demonstrated in the video.

The below video covers ASYNC_NETWORK_IO waits while the server is sending data to the client. The video suggests ways to identify slow network and discusses high ASYNC_NETWORK_IO wait time and guidelines to reduce the waits.

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

Resource Governor in SQL Server provides CPU, Memory and I/O throttling. In SQL Server 2014 and earlier, CPU throttling works only for similar kind of workloads and not for mixed workloads, where high importance workload group gets 9 slices of CPU, medium importance gets 3 slices and low 1 slice. In SQL Server 2016, CPU throttling works fine for all kinds of workloads.

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 http://www.sqlvideo.com for additional hands-on videos.

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