A first look at any server needs three read-only T-SQL checks: configuration, databases and backups, and current activity.
SQL SERVER – Introduction to sys.dm_exec_query_optimizer_info
While browsing Books Online I found sys.dm_exec_query_optimizer_info, which returns statistics about the query optimizer to help you tune a workload.
Finding the Most Frequently Run Queries
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.