Home » Posts tagged 'DMV'
Tag Archives: DMV
How Row versioning impact tempDB ?
Scenario: DBA found that tempdDBdatabase usage is getting high and most of the size is consumed by row versioning. DBA raised the issue with application team running that query. Once Application team close the session, tempDB usage comes normal. Question: Application tea raise concern “How can a select statement on table consume huge amount of … Continue reading
Restrict user to login from single Host
Restricting user to login from single host may be sometimes required from security point and other business requirements. We can achieve it through SERVER LEVEL LOGON TRIGGER. ALTER TRIGGER TR_CHECK_LOGIN_TEST_HOST ON ALL SERVER FOR LOGON AS BEGIN DECLARE @HOSTNAME VARCHAR(48) DECLARE @PROGNAME VARCHAR(100) SELECT @HOSTNAME = HOST_NAME FROM SYS.DM_EXEC_SESSIONS WHERE SESSION_ID = @@SPID IF ORIGINAL_LOGIN() … Continue reading
DMV-15 : Pending I/O requests……..sys.dm_io_pending_io_requests
sys.dm_io_pending_io_requests DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms188762.aspx Returns a row for each pending I/O request in SQL Server. It’s a very simple DMV used to see all pending I/O requests & there description. You can combine this DMV with DMF – sys.dm_io_virtual_file_stats to see I/O pending requests with database files. You … Continue reading
DMV-14 : Which Table has Max. rows & Size……..sys.dm_db_partition_stats
sys.dm_db_partition_stats DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms187737.aspx Returns page and row-count information for every partition in the current database. This DMV gives useful information about rowcount, free & reserved space of each table in database. It can be used for analysis purpose while adding new indexes, doing index Maintenance or checking … Continue reading
DMV-13 : Finding locking & blocking……..sys.dm_tran_locks
sys.dm_tran_locks DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms190345.aspx Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: … Continue reading