Troubleshooting High-CPU Utilization for SQL Server

The objective of this FAQ is to outline the basic steps in troubleshooting high CPU utilization on  a server hosting a SQL Server instance.

The first and the most common step if you suspect high CPU utilization (or are alerted for it) is to login to the physical server and check the Windows Task Manager. The Performance tab will show the high utilization as shown below:

Click on below link to read the entire article.

sql-server-performance.com

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.

How to identify objects used in a stored procedure

Recently there was a requirement from a project team to identify all objects belong to a stored procedure. Actually they wanted only the tables used in a stored procedure.You can use sp_depends with the SP and then you will be able to get all the dependent objects. But the actual requirement was much more complex than that because they wanted to analyze a trace they created for all the SPs captured in the trace.

Sp_depends and sys.sysdepends are provided in SQL Server 2008 for backward compatibility purpose only. So not recommending to use in new developments.

Refer below links for more information.


sp_depends


sys.sysdepends

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