Tag Archives: Performance Tuning

Query to find space occupied by each database in Data cache

Every database in SQL Server which is getting read by the user or application is retrieved from the buffer cache. If the pages which are not in buffer cache are read by the user/application, those pages will be loaded into buffer cache first and then retrieved to the user/application.

 

Database Pages which occupied in the cache are retrieved by the following query:

 SELECT count(*)*8/1024 AS 'Cached Size (MB)', 
CASE database_id  WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)  END AS 'Database'  
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id 
ORDER BY 'Cached Size (MB)' DESC

Top 10 processes running in the SQL SERVER

 select top 10 * from master.dbo.sysprocesses
 where
 status <> ‘background’
 AND cmd NOT IN
 (‘signal handler’,’lock monitor’,’log writer’,’lazy writer’,’checkpoint sleep’,’awaiting command’,’TASK MANAGER’)
 and spid > 50
 and spid <> @@spid
 order by CPU desc

SQL Server index related dynamic management views and functions

Microsoft SQL Server provides DBAs and Developers with several index related dynamic management views and functions, which they can use to maintain, modify, tune and identify missing indexes. Some of these dynamic management views (DMVs) and functions (DMFs) you might use are listed as follow:

  • sys.dm_db_index_operational_stats — Returns information about low level input/output (I/O) operations, locking and latching contention, and access methods.
  • sys.dm_db_index_usage_stats — Returns an index operations count, identifying operations performed and the last time each operation was performed.
  • sys.dm_db_index_physical_stats — Returns information about table and index size and fragmentation, including both in-row and LOB data.
  • sys.dm_db_missing_index_details — Returns detailed information about missing indexes, including the table, columns used in equality operations, columns used in inequality operations, and columns used in include operations.
  • sys.dm_db_missing_index_group_stats — Returns information about groups of missing indexes, which SQL Server updates with each query execution (not based on query compilation or recompilation).
  • sys.dm_db_missing_index_groups — Returns information about missing indexes contained in a missing index group.