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

OSTRESS Tool

OSTRESS is a Microsoft tool comes with RML utilities package and it uses to stress SQL Server. This is especially useful when you want to troubleshoot SQL Server while SQL Server is under heavy load.

Use below link to download RML Utilities


SQL Server (x64)


RML Utilities


After installing RML utilities, you can use RML cmd prompt to execute scripts with OSTRESS.

Tools for System Internals

While googling on some SQL Server internals, I found a site which has lots of tools to get vital information about your system. I've used some of them and they are very useful.  


One of such tools is "Coreinfo.exe".
"Coreinfo is a command-line utility that shows you the mapping between logical processors and the physical processor, NUMA node, and socket on which they reside, as well as the cache’s assigned to each logical processor. It uses the Windows’ GetLogicalProcessorInformationfunction to obtain this information and prints it to the screen, representing a mapping to a logical processor with an asterisk e.g. ‘*’. Coreinfo is useful for gaining insight into the processor and cache topology of your system. "

Rename a constraint

If you need to rename a constraint after creating with typo or due to any other reason, you could follow the below steps.

--creating test table with PK constraint
CREATE TABLE dbo.RenameConstraintTest
(
ID int CONSTRAINT PKC_RenameConstraintTest PRIMARY KEY CLUSTERED
)
GO

--check the existance of the constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest'
GO

--rename the constraint
EXEC sp_rename N'[dbo].[RenameConstraintTest].[PKC_RenameConstraintTest]', N'PKC_RenameConstraintTest2', N'INDEX'
GO

--check the old constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest'
GO
--check the renamed constraint
SELECT * FROM sys.key_constraints WHERE name='PKC_RenameConstraintTest2'
GO

What should you know more about NOLOCK?

As you may know already, NOLOCK is used in some queries to avoid, SQL Server to acquire shared locks on the records being red. The main reason to use nolock is to eliminate the performance overhead, especially for large data sets, due to shared locks. Though you have the gain in performance wise, it also gives you dirty records (uncommitted data).

This article explains another behavior of nolock.

Let’s create a sample table with test data to illustrate the behavior.

Shortcut to view tables

As a DBA I need to see the list of tables available in a particular database frequently. Traditionally, this can be done by traversing through the object explorer. This may be the popular method among GUI lovers. But for me it is a pain. I hope it is the same for most of the DBAs.