John Liu Blog

Every drop counts

Check SQL DB size and free disk space

Following script will list the db size for all user databases and the free disk space of the driver. SELECT DBName ,Owner ,Create_Date ,DBSizeGB = SUM(DBSizeGB) ,Drive ,DriveFreeSpaceGB = MAX(available_bytes)/1024/1024/1024 FROM (SELECT DBName = D.name ,Owner = P.name ,Create_Date = CONVERT(DATE,D.create_date) ,DBSizeGB = F.size * 8 / 1024 / 1024 ,Drive = LEFT(F.physical_name,2) ,DR.available_bytes FROM sys.databases D INNER JOIN sys.server_principals P ON D.owner_sid = P.sid INNER JOIN sys.master_files F ON D.

Posted by John Liu Thursday, October 27, 2022

SQL error

1. “Cannot load Counter Name data because an invalid index” When trying to install SQL2019 PolyBase (and possibly other programs as well), you may receive error “Cannot load Counter Name data because an invalid index” exception To fix this error, start CMD as administrator and run C:\WINDOWS\System32> lodctr /r then run C:\WINDOWS\SysWOW64> lodctr /r If all success, you should receive message: “Info: Successfully rebuilt performance counter setting from system backup store”

Posted by John Liu Thursday, November 25, 2021

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