Syncing logins between SQL 2005/2008 servers

Below SQL code can be used to generate the SQL that can be used to sync SQL logins across SQL 2005/2008 data servers.

SELECT ‘create login [‘ + name + ‘] with password=’, convert(varbinary,password_hash),
‘HASHED, sid=’, sid FROM master.sys.sql_logins


Significance of Page Life Expectancy (PLE) to analyze memory bottlenecks

Page Life Expectancy can tell whether SQL Server has enough memory for its bufferpool to function properly. Extremely low Page Life Expectancy values can be an indication of:
  • Too little memory on the system
  • Too little memory configured for SQL Server’s use
  • Poorly written application code
According to Microsoft, 300 seconds is the minimum target for page life expectancy. If the buffer pool flushes the pages from memory in less than 300 seconds, that indicates a memory bottleneck. If PLE goes low only during certain times of the day, a badly written query could be behind the problem.

On NUMA machines, you need to be looking at the Buffer Node:Page Life Expectancy counters for all NUMA nodes otherwise you’re not getting an accurate view of buffer pool memory pressure and so could be missing performance issues.

For instance, for a machine with 8 NUMA nodes, with PLE of each being 4000, the overall PLE is 4000. If one of them drops to 1000, the overall PLE only drops to 3625, which likely wouldn’t trigger your alerting as it hasn’t even dropped 10%.

Today the value 300 is ridiculously small as a threshold for when to start worrying about buffer pool pressure. Don’t use the value 300 as any kind of Page Life Expectancy threshold. Anyone that continues to recommend doing so is doing you a disservice. Use an adaptive formula like (DataCacheSizeInGB/4GB*300). Even better – monitor the steady-state value of PLE and react when it dips *and stays* below your steady-state value.

Which SPID is taking most of the tempdb space

Find out the SPIDs taking most of the space in tempdb

SELECT top 10
s.host_name, su.[session_id], [DBName], su.[database_id],
su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
(su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
(su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
FROM [sys].[dm_db_session_space_usage] su
inner join sys.databases d on su.database_id = d.database_id
inner join sys.dm_exec_sessions s on su.session_id = s.session_id
where (su.user_objects_alloc_page_count > 0 or
su.internal_objects_alloc_page_count > 0)
order by case when su.user_objects_alloc_page_count > su.internal_objects_alloc_page_count then
su.user_objects_alloc_page_count else su.internal_objects_alloc_page_count end desc

Find out the space consumed by internal objects

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used], (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB] FROM sys.dm_db_file_space_usage;

Find out SPIDs generating most of the internal objects

SELECT top 10 session_id, (SUM(internal_objects_alloc_page_count)*1.0/128) AS task_internal_objects_alloc_page_count, (SUM(internal_objects_dealloc_page_count)*1.0/128) AS task_internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage GROUP BY session_id order by task_internal_objects_alloc_page_count desc, task_internal_objects_dealloc_page_count desc