Hello All, Yesterday i attented an SQL DBA interview. Interviewer asked me a question as what are all the SP and DMV you frequently used in your database environment. I answered him as **SP** 1. SP_readerrorlog 2. sp_help 3. sp_helptext 4. sp_who 5. sp_who2 **DMV's** 1. sys.dm_db_index_physical_stats Apart from the above what are the sp And Dmv's we freequetnly used?
All the views and functions around query execution: > sys.dm_exec_requests > sys.dm_exec_query_stats > sys.dm_exec_sql_text > sys.dm_exec_query_plan Wait statistics: > sys.dm_os_wait_stats unless we're on azure then it's: > sys.dm_db_wait_stats I'd suggest picking up a copy of Louis Davidson and Tim Ford's book for lots more.
There's really no correct answer to that. I use whichever management views which are useful to me :) But on the serious side: It really depends on what your role as a dba is. You may or may not create scripts using sys.dm_db_file_space_usage to keep track on datafile usage. You may or may not use sys.dm_db_index_operational_stats to watch (or watch out for) tons of information about waits, scans, locks etc. You may or may not use the sys.dm_db_missing_index* views to help developers index their databases sane (or insane...). At some places, you will as a DBA do more or less **everything**. At other places, you will as a DBA work with more or less only backups/restores and disaster recovery. There's no fixed universal job description for a dba.