John Liu Blog

Every drop counts

SQL Server Performance tool scripts

Buffer cache usage summary SELECT D.name ,MBUsed = COUNT(*) * 8 / 1024 ,MBEmpty = SUM(CAST([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) FROM sys.dm_os_buffer_descriptors B INNER JOIN sys.databases D ON B.database_id = D.database_id GROUP BY D.name Current memory status --https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/dbcc-memorystatus-monitor-memory-usage DBCC MEMORYSTATUS; Index usage SELECT DatabaseName = DB_NAME() ,TableName = o.name ,IndexName = i.name ,s.index_id ,i.type ,i.is_primary_key ,i.is_unique_constraint ,s.last_user_seek ,s.user_seeks ,SeekPercentage = CASE s.user_seeks WHEN 0 THEN 0 ELSE s.user_seeks*1.0 /(s.

Posted by John Liu Sunday, April 9, 2023

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 Server on Azure config best practice

Each Azure VM has a OS disk (C drive) and a temporary disk (D drive). Only store on the temporary disk temp data that doesn’t need to be retained as all data on this disk will be lost after server reboot. The best practice for SQL Server on Azure VM is to use Premium Disks pooled for increased IOPs and storage capacity. Data file should be on its own pool with read-caching on the Azure disk.

Posted by John Liu Sunday, November 7, 2021