John Liu Blog

Every drop counts

SQL Server Session Settings

Following SQL Server session settings impact on if an existing plan in cache can be used or not. Only if the session settings are identical to the settings for the cached plan, the plan can then be reused; otherwise, it will be recompiled. Different setting may impact performance at execution. ANSI_DEFAULTS ANSI_NULL_DFLT_ON ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL NUMERIC_ROUNDABORT For stored procedure, the ANSI_NULLS and QUOTED_IDENTIFIER settings will be used are the settings used when the stored procedure is created and runtime settings are irrelevant.

Posted by John Liu Tuesday, January 24, 2023

SQL NoLock query hint

SQL query hint NOLOCK is similar as using READ UNCOMMITTED isolation level. People often under the impression that this query hint will make query run faster (as no blocking or lock allocation required). However, one needs to consider carefully the following big drawback with NOLOCK query hint and better step away from using it. The drawbacks/issues with using NOLOCK query hint: Query may return the same row twice Query may skipping rows Query may see rows not actually commited Query may fail with error “could not continue scan with nolock due to data movment” Unless inaccurate query results is not an issue, we should never use the NOLOCK hint.

Posted by John Liu Wednesday, November 24, 2021

SQL Wait Statistics

SQL Server Wait statistics can be used to troubleshooting performance problem. Wait statistics are broken down into three type of waits: Resource waits: work thread is waiting for access to server resources. Such as lock, latch and disk I/O waits. Queue waits: work thread is idle and waiting for work to be assigned. Such as deadlock monitoring and deleted record cleanup. External Waits: SQL Server is waiting for external process to complete, like linked server query.

Posted by John Liu Monday, November 22, 2021

SQL Execution Plan

SQL Server generate execution plan based on available table column statistics. It’s important to have more accurate statistics for SQL to come up with a better execution plan. Memory grant is memory SQL Server thinks is required to hold the estimated data based on the statistics. If SQL Server estimated a large amount of data will be processed but actually only small amount is process, more memory grant will be required than actually required.

Posted by John Liu Sunday, November 21, 2021

SQL Table Expression

Itzik Ben-Gan has a great series of articals about table expressions, focus on four types of named table expressions: derived tables, common table expressions (CTEs), views, and inline table-valued functions (inline TVFs). Fundamentals of table expressions, Part 1 Fundamentals of table expressions, Part 2 – Derived tables, logical considerations Fundamentals of table expressions, Part 3 – Derived tables, optimization considerations Fundamentals of table expressions, Part 4 – Derived tables, optimization considerations, continued

Posted by John Liu Thursday, November 11, 2021