Ali Shaik's Database Diary

  PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--'; SELECT TOP 10 GETDATE() runtime, * FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text"     FROM (SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text             FROM sys.dm_exec_requests AS req                 CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats     GROUP BY query_hash) AS t ORDER BY Total_Request_Cpu_Time_Ms DESC; PRINT '--top 10 Active CPU Consuming ...

In the world of database administration, performance tuning is an ongoing challenge. As SQL Server processes increasingly complex workloads, administrators seek every opportunity to optimize performance. One such advanced feature is the Affinity Mask option in SQL Server. While not commonly used in modern setups, it can be a valuable tool for certain scenarios where precise control over CPU usage is necessary. What Is the Affinity Mask Option? SQL Server is designed to utilize all available CPUs by default for its processing needs. However, there are situations where controlling how SQL Server threads interact with CPUs can lead to significant performance improvements. The Affinity Mask setting allows administrators to: Bind SQL Server threads to specific CPUs. Reduce context switching, which happens when threads jump between CPUs. Ensure certain CPUs are reserved for SQL Server, avoiding competition with other processes on the server. This level of control is particularly useful in ...