Posts

Showing posts from October 16, 2016

Getting read-only access to DMV's for querying system and performance stats

Most of what we're going to want to look at when you're having production issues are available through DMV's.If granting select to sys.dm* is a bit too much, these are the tables I'm generally most interested in:
Required Permissions:To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.
For Query tuning:sys.dm_exec_query_statssys.dm_exec_sql_textsys.dm_exec_text_query_plan
For general Waits and locks:sys.dm_tran_lockssys.dm_os_wait_stats
For Missing Indexes:sys.dm_db_missing_index_group_statssys.dm_db_missing_index_groupssys.dm_db_missing_index_detailssys.databases
For IO_STALL:sys.dm_io_virtual_file_statssys.master_files

/* NOTE: The following should not be needed as users are already set upUSE [master]GOCREATE USER [theUser] FOR LOGIN [theUser] WITH DEFAULT_SCHEMA=[dbo]GO
USE [master]GOGRANT CONNECT TO [theUser] GO
*/
USE [master]GO
GRANT SELECT ON sys.dm_os_hosts TO [theUser]GRANT SELECT ON…