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:
Tag: Performance Tuning
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… Read more