John Liu Blog

Every drop counts

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 Server on Azure VM

Some considerations when configure SQL Server on Azure VM. This also applicable to AWS VM For storage: - Separate volume for data and log file - Enable read caching on data file volume - Disable any caching on log file volume - Use D: drive (local SSD that will be cleared each time with server reboot) for TempDB - Enable instant file initialization - Move trace file and error log directories to data volume To evaluate disk performance when migrate from on-premises to cloud, capture following counters in Performance Monitor:

Posted by John Liu Wednesday, November 17, 2021

SQL string split

There are different ways to split string in SQL query. One way I came across is using openjson function. DECLARE @String AS VARCHAR(100) = '1,2,3,4' SELECT value FROM OPENJSON('[' + @String + ']') This method assumed the string using comma as the delimitor. If your string using other character as the delimitor, consider using REPLACE() function to change it to comma first. Another simple method would be using STRING_SPLIT() function in SQL2016+

Posted by John Liu Monday, November 15, 2021