John Liu Blog

Every drop counts

How to convert a single-client database to multi-client database

Consider you have a database (with single dbo schema) that’s design to be used by a single client. How can you turn the database to be multi-client database with minimal impact to your existing application? Row Level Security (RLS) could provide a considerably easily solution. Some assumptions: Your application code or database object (trigger, SP, function etc.) code doesn’t hard code schema name (as we will using schema to separate each client) Your application needs to be able to using different login for different client when connect to the database Same login will not be access more than one client data Following are the steps to convert the database.

Posted by John Liu Monday, April 10, 2023

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

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

SQL Server Session Settings

Following SQL Server session settings impact on if an existing plan in cache can be used or not. Only if the session settings are identical to the settings for the cached plan, the plan can then be reused; otherwise, it will be recompiled. Different setting may impact performance at execution. ANSI_DEFAULTS ANSI_NULL_DFLT_ON ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL NUMERIC_ROUNDABORT For stored procedure, the ANSI_NULLS and QUOTED_IDENTIFIER settings will be used are the settings used when the stored procedure is created and runtime settings are irrelevant.

Posted by John Liu Tuesday, January 24, 2023