SELECT 'EXEC sp_AddRoleMember ''' + DBRole.NAME + ''', ''' + DBUser.NAME + '''' AS '--Add Users to Database Roles--'
FROM sys.database_principals DBUser
INNER JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
INNER JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id
select *from sysprocesses where blocked >0
SELECT (physical_memory_in_use_kb/1024) AS Used_Memory_By_SqlServer_MB
FROM sys.dm_os_process_memory
select
(physical_memory_in_use_kb/1024)Phy_Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 )Total_Memory_UsedBySQLServer_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory
process usage
select
physical_memory_in_use_kb/1048576.0 AS 'physical_memory_in_use (GB)',
locked_page_allocations_kb/1048576.0 AS 'locked_page_allocations (GB)',
virtual_address_space_committed_kb/1048576.0 AS 'virtual_address_space_committed (GB)',
available_commit_limit_kb/1048576.0 AS 'available_commit_limit (GB)',
page_fault_count as 'page_fault_count'
from sys.dm_os_process_memory;
buffer wise usage
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Database Pages';
;WITH DBBuffer AS
(
SELECT database_id,
COUNT_BIG(*) AS db_buffer_pages,
SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
)
SELECT
CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS 'db_name',
db_buffer_pages AS 'db_buffer_pages',
db_buffer_pages / 128 AS 'db_buffer_Used_MB',
[mbempty] AS 'db_buffer_Free_MB',
CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) AS 'db_buffer_percent'
FROM DBBuffer
ORDER BY db_buffer_Used_MB DESC;
