John Liu Blog

Every drop counts

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

SQL database roles

One way SQL server to easily manage access is using database roles. SQL server has several built-in database roles. db_owner role member by default can perform any action within the database. However, except the actual database owner (user name dbo), other members in the db_owner role can be deny access by placing them in other database roles with deny access to objects, such as db_denydatareader. Other roles functions: db_backupoperator: perform database back db_datareader: can read data db_datawriter: can INSERT, UPDATE and DELETE data db_ddladmin: can create/modify any objects but can read/write data db_denydatareader: can’t read data db_denydatawriter: can’t write data db_securityadmin: can grant access to user db_accessadmin: can create user but can’t grant access to objects and data

Posted by John Liu Sunday, November 14, 2021