I understand that there is no one way to describe the CPUs and that is the primary reason, why I get a different answer when I try to get CPU details.
Query Ignoring CPU Threads – SQL in Sixty Seconds #161
I have an interesting experience to share about the Query Ignoring CPU Threads. I was hired by a large eCommerce organization.
OS Threads by Scheduler in SQL Server: Count Them per CPU
OS threads by scheduler shows how many Windows threads each SQL Server scheduler owns. This post joins the scheduler, worker and thread views and counts them per CPU. It compares the counts with the server totals and explains the hidden schedulers that a careless query counts as CPUs.
SQL SERVER – AdHoc Queries and Optimize for Adhoc Workloads
Optimize for AdHoc Queries reduces the first-use plan-cache footprint of ad hoc batches. A customer variable-query workload prompted testing.
OS Thread of a Session: Map Session ID to Windows Thread ID
To find the OS thread of a session, join sys.dm_os_tasks to sys.dm_os_threads on the worker address. This post reads the map for your own session and for every user session. It explains why idle sessions have no row, why parallel queries have several, and how to follow a thread in Performance Monitor.
Find Expensive Queries – SQL in Sixty Seconds #159
Earlier I wrote a blog post about SQL SERVER – List Expensive Queries – Updated March 2021. Let us learn how to Find Expensive Queries.
Expensive Queries by query_hash: Total Cost, Not One Plan
Expensive queries by query_hash are ranked by total cost, not by single plans. A demo runs one lookup with 40 different literals and shows that the top single plan is a scan, while the grouped total, ranked by reads, points at the lookup. The query ranks by reads, CPU, time or runs.