To clear procedure cache for one database, run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE inside that database. This post tests it on two demo databases, shows that the other database keeps its plans, and shows how to remove a single plan by its plan handle. It also covers the cost and the lighter alternatives.
SQL SERVER – Simple Script to Match Session ID to OS Thread ID
How is a SQL Server Session ID mapped to an OS Thread ID? Here is a simple script to match Session ID to OS Thread ID, with the scheduler and task state.
SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE
Has your identity column ever jumped by 1000 after a restart? SQL Server 2017 fixes it with IDENTITY_CACHE. Here is a demo with the setting ON and OFF.
Kill All Processes for a Database in SQL Server
To kill all processes for a database, set it to SINGLE_USER with ROLLBACK IMMEDIATE and then back to MULTI_USER. This post tests that method against two open sessions, compares it with a KILL script and with RESTRICTED_USER, and explains when each one fits. It also covers what the killed sessions see and why you should not do this on production without asking.
How to Find DISTINCT COUNT of Column Rows Without Using Distinct Count Keywords? – Interview Question of the Week #157
Only one of ten candidates solved this: find the DISTINCT COUNT of a column without using those keywords. Here are the traditional answer and the trick.
SQL SERVER – DMV to Get Host Information – sys.dm_os_host_info
SQL Server now runs on Linux too, so I check the host first in health checks. Here is how the DMV sys.dm_os_host_info returns Host Information about the OS.
SQL SERVER – Performance Observation of TRIM Function
Does the new TRIM function slow your queries like other functions in a WHERE clause do? Here is my performance observation of TRIM function in SQL Server 2017.