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)

NEW-STYLE DATABASES FOR NEW-STYLE PROBLEMS

Old-style database systems solve old-style problems; we need new-style databases to solve new-style problems. While the need for conventional database management systems isn't going away, many of today’s problems require a configurable database system. Even without a crystal ball, it seems clear that tomorrow’s systems will also require a significant degree of reconfigurability. As programmers and engineers, we learn to select the right tool to do a job; selecting a database is no exception. We need to operate in a mode where we recognize that there are options in data management, and we should select the right tool to get the job done as efficiently, robustly, and simply as possible.

Above is the excerpt from the article published in ACM. It is an interesting one and take sometime to look at it.

Beyond Relational Databases

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

Sample Databases for Microsoft SQL Server 2008R2

The below link can be used to download SQL Server 2008 R2 sample databases. 

Download