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'
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC
select top 10 * from master.dbo.sysprocesses
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
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.