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 – 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.
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.
SQL SERVER – Location of Resource Database in SQL Server Editions
The Resource database is read-only and holds the system objects. See where it lives in SQL Server 2005 and 2008, and how to check its version.
SQL SERVER – Recently Executed T-SQL Query
fn_get_sql() and DBCC INPUTBUFFER will be deprecated. Use sys.dm_exec_query_stats and sys.dm_exec_sql_text to see each recently executed T-SQL query.
sys.objects, sys.all_objects and sys.system_objects
Compare sys.objects, sys.all_objects and sys.system_objects to find SQL Server user objects, system objects, and metadata visibility gaps.
SQL SERVER – Get Query Plan Along with Query Text and Execution Count
A handy script that shows each query’s text, its execution count and its query plan, so you can see what runs most often on your server.