Denver SSUG Presentation About Latch Behavior

Yesterday I did a presentation about SQL Server Latch behavior at SQL Server User Group at Denver.

Hope everyone has learnt something and got some exposure to SQL Server latches which is internal to SQL Server and some time create issues. So that knowing about what they are and how they behaves is important in my opinion.

Below link contains the ppt and the demo scripts I used. The demo scripts are compatible with SQL Server 2014 or later.

Presentation and Demo scripts

Cheers!

Mysterious system table: master.sys.sysdbreg

Have you ever seen the system table, "master.sys.sysdbreg" in your SQL Server. I've seen this unintentionally while working on something else. I was analyzing a query plan in XML view. Suddenly I saw this system table. See below portion of execution plan.

SQLOS and Windows OS – Part I

If you’re a SQL Server professional you may have already familiar with SQLOS. At least you might have heard it but may not know much about it. However everyone familiar with the Windows OS but again may not know much technical aspect of Windows. Windows is a general purpose OS designed to install application programs and manage them. E.g. you can install MS Office into your computer only after configuring Windows OS.

Windows OS

When you execute an application it creates a process in Windows OS. Until such time it is in inactive state (stored in storage). Let’s consider SQL Server. It is just another application as far as Windows is concerned. SQL Server process will create in Windows as soon as you start SQL Server. You can simply see the processes in Windows Task Manager. (See Figure 1.1)

How to identify which CPU(s) is using for a query

If a query uses parallelism when executing, it may be interesting to see actually which CPU(s) are getting involved.

The below query gives you that information;

SELECT     t.session_id
          ,t.request_id

          ,t.scheduler_id

          ,t.task_state

          ,s.cpu_id

          ,r.database_id

          ,r.sql_handle

          ,w.last_wait_type

FROM sys.dm_os_tasks t

INNER JOIN sys.dm_os_schedulers s

     ON t.scheduler_id=s.scheduler_id

INNER JOIN sys.dm_exec_requests r

     ON t.session_id=r.session_id

INNER JOIN sys.dm_os_workers w

     ON t.task_address=w.task_address 

WHERE t.session_id=51 -- put the SPID

Does SPID unique in sys.sysprocesses?

Sys.sysprocesses contains connection information for each connection made to the SQL Server. As you see it, it looks like SPID is unique. But actual it is not, because of KPID. KPID is Windows thread id. Each SQL Server task is assigned a Windows thread. SQL Server task is the unit of execution for SQL Server. The queries submitted to SQL Server may have parallel tasks like parallelism operator. In this case, SPID has several KPIDs, which duplicate SPID in the sys.sysprocesses view.