question

Mohamed4053 avatar image
Mohamed4053 asked

SP and DMV's

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?
dmvinterview-questionssp
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.