Plan recompilation hurts performance just like a first compile. Learn what causes it, from schema changes to SET options, and how to reduce recompilation.
SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Cannot drop a database because it is in use? SET SINGLE_USER WITH ROLLBACK IMMEDIATE rolls back open work first. Be careful with it on production.
smalldatetime: Minute Rounding Can Change the Day
smalldatetime does more than hide seconds. These explicit conversions show minute rounding and a midnight carry, with complete original timestamps retained.
SQL SERVER – Find the Size of Database File – Find the Size of Log File
A quick script on sys.master_files to find the size of database file and log file in MB. Remove the WHERE clause to see every database on the server.
ISO Week Numbers: Find the Matching ISO Week Year
ISO week numbers can belong to a different year from the calendar date. A Thursday-based calculation keeps the ISO year and week together across December and January.
SQL SERVER – Stream Aggregate Showplan Operator – Reason of Compute Scalar before Stream Aggregate
Stream Aggregate is a common showplan operator, even in a simple COUNT(*) plan. I explain why a Compute Scalar often shows up right before it.
SQL SERVER – Get the List of Object Dependencies – sp_depends and information_schema.routines
sp_depends can return wrong dependency results where information_schema.routines gets it right. See where it fails and the DMVs recommended in its place.