How trigger recursion and nesting work in SQL Server 2005: triggers nest up to 32 levels, recursive triggers fire themselves, and both can be turned off.
SQL SERVER – Disable Index – Enable Index – ALTER Index
When DTS, BCP or BULK INSERT needs an index out of the way, I prefer to disable index with ALTER INDEX and enable it again with REBUILD. AdventureWorks example.
SQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Estimating How Much Memory SQL Server Needs
How much memory SQL Server needs depends on the active working set, grants, and host headroom; learn to measure pressure and avoid fixed ratios.
Data Types That Waste Space
Data types that waste space include oversized integers, blanket nvarchar(max), and unnecessary datetime2 precision; measure before changing.
SQL SERVER – Index Optimization CheckList
My Index Optimization checklist for Jr. DBAs: index columns used in WHERE, ORDER BY, GROUP BY and JOINs, drop indexes you do not need and keep keys small.
What Is a Heap in SQL Server?
A heap in SQL Server is a table with no clustered index. Forwarded records pushed one count from 11,113 reads to 199,844, and a single REBUILD put it back. When a heap is fine and when it is not.