Most frequently run queries can consume large SQL Server resources even when each call is fast; use plan cache and Query Store to find them.
Reading Memory Clerks to See Where Memory Goes
Memory clerks show how SQL Server allocates memory among engine components; learn to read the DMV, spot unusual growth, and investigate safely.
SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table
How do you find Fragmentation Information for a table in SQL Server 2005? DBCC SHOWCONTIG has limits, so I use sys.dm_db_index_physical_stats instead.
SQL SERVER – 2005 – Last Ran Query – Recently Ran Query
Want to see the last ran query on SQL Server 2005? This quick script uses sys.dm_exec_query_stats to list recent queries with the time each one ran.
SQL SERVER – 2005 – Dynamic Management Views (DMV) and Dynamic Management Functions (DMF)
Dynamic Management Views and Management Functions show what SQL Server is doing right now. The two scopes, the main categories and where to start.
Finding Unused Indexes
Finding unused indexes requires a measured window, SQL Server usage counters, and a safe removal test before an index earns a drop decision.
Grouping Current Connections by Program Name and Host Name
Group connections by program name, host, and login, then inspect client addresses and idle sessions without trusting client labels blindly.