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 Agent Job Name

At time, you might need to report the actual SQL Agent job name that took the action in your notification. For example, you have a SQL Agent job that send user an email notification and you want to include in the notification the actual job name that sends the notification. You might hardcode the job name in the notification. However, when the job renamed, you need to also remember to update the hardcoded job name in the code as well.

Posted by John Liu Thursday, March 23, 2023

SQL Server File Size

There are serveral ways to find out database size. Apart from using the SSMS GUI, you can using following scripts options. --this return size info for all databases --the size is in kilobytes exec sp_databases --this return size info for all databases SELECT DBName = DB_NAME(database_id) ,FileName = name ,physical_name ,type_desc ,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.0/1024/1024) --,max_size --,growth --,is_percent_growth FROM sys.master_files WHERE database_id > 6 AND type IN (0,1) --this return size info for current database with free space info SELECT DBName = DB_NAME() ,FileName = name ,physical_name ,type_desc ,Allocated_Space_GB = CONVERT(DECIMAL(18,2),size * 8.

Posted by John Liu Sunday, March 19, 2023

SSMS Extensions

With SSMS 19, when trying to install Poor Mans TSQL Formatter SSMS extension, it doesn’t appear working after install. This is due to the install didn’t create/copy the package file onto folder C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Extensions\Extensions. To make it work, manually copy the PoorMansTSqlFormatterSSMSPackage.pkgdef from the Extension folder for SSMS 18 and past into the Extension folder for SSMS 19. You may need to create the Extension folder if it doesn’t exist.

Posted by John Liu Friday, March 17, 2023

SQL Server Extended Events

Following query provides the name mapping between trace and extended events. Not all event classes have equivalent Extended Events events. SELECT te.name ,xe.package_name ,xe.xe_event_name FROM sys.trace_events AS te LEFT JOIN sys.trace_xe_event_map AS xe ON te.trace_event_id = xe.trace_event_id WHERE xe.trace_event_id IS NOT NULL

Posted by John Liu Wednesday, March 1, 2023