Amount of sql server processors with sys.dm_os_schedulers

I needed to establish the amount of processors available to a SQL Server. A SQL Server scheduler is mapped to a single logical processor, so using this query you can establish the amount of processors. Any scheduler_id < 255 is a scheduler.

It gets lightly more complicated if hyperthreading is enabled. . Find out from the server administrator , use a product  such as the coreinfo or this Powershell script  - Number of physical processors with Powershell – number of cores to establish if hyperthreading is turned on.

If hyperthreading is enabled – this equates to double the schedulers. 

SELECT * 
FROM sys.dm_os_schedulers 
WHERE scheduler_id < 255;

 Read More

SOS_SCHEDULER_YIELD and how to reduce it - SQL Server DBA

Affinity Mask on SQL Server 2008 R2 - SQL Server DBA

How to report a SQL Server performance problem - SQL Server DBA

Monitor SQL Server TempDB usage

Large queries spilling into tempdb such as SQL Server – SORT IN TEMPDB and Sort Warnings  and large temporary tables can use Tempdb space very quickly and lead to outages. Use the DMVs : sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, sys.dm_db_task_space_usage

How to Troubleshoot

First Step - Comprehensive Tempdb Sizing exercise. There are many different methods  , such as : a) create a workload b) running large maintenance jobs e.g index rebuild

Second Step - monitoring Tempdb usage. As well as a  SQL Server monitoring alerts solution based on events , such as disk full, there are some useful DMVs  to drill into TempDB usage based on tasks and sessions.

sys.dm_db_file_space_usage -  Lists space usage information for each file in the Tempdb database. Reports on unallocated extent page count.

sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated

sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity

 

go
select * from  sys.dm_db_file_space_usage 
go 
select * from  sys.dm_db_session_space_usage
go
select * from sys.dm_db_task_space_usage

Read More

SQL Server – List current tempdb statements

SQL Server - TempDB performance and strategy checklist

SQL Server – tempdb and solid state drives

SQL Server - Shrink tempdb without sql server restart

 

SQL Server - Find last sql statement based on SPID

Find the last query based on a SPID can be useful . For example, a  client may be experiencing SQL performance issues  and the DBA needs to isolate the last run query.  There are three methods below  to return the query.

Before we explore the methods – you’ll need to obtain the SPID. The SPID is the session User ID.

Try:

--this returns your current session ID

SELECT @@SPID

--this returns list of all sessions-check the spid column
 sp_who

 

 Method 1 - DBCC INPUTBUFFER

-----------------------------

DBCC INPUTBUFFER  returns the last sql statement  issued by a client. The command requires the SPID

 

DBCC INPUTBUFFER (SPID)

 

Method 2 - SYS.SYSPROCESSES and SYS.DM_EXEC_SQL_TEXT

-----------------------------------

Obtain the sql_handle from sys.sysprocesses and use the sql_handle to obtain the sql text from the DMV sys.dm_exec_sql_text.

 

DECLARE @sqlhandle VARBINARY(64)
SELECT @sqlhandle = sql_handle from sys.sysprocesses where spid = 987
SELECT text FROM sys.dm_exec_sql_text(@sqlhandle)

 

Method 3 – SYS.FN_GET_SQL

---------------------------------

Use sys.fn_get_sql to return SQL statement text based on a SQL handle. Microsoft intend deleting this function in an unspecified future version of SQL Server          

 

DECLARE @sqlhandle varbinary(64);
SELECT @sqlhandle = sql_handle 
FROM sys.dm_exec_requests 
WHERE session_id = 987 and request_id = 0;
SELECT * FROM sys.fn_get_sql(@sqlhandle);

 Read More

dm_exec_sql_text and Display text of SQL - SQL Server DBA

SQL Server - SQL open transactions and how to find

SQL Server – Current queries executing on SQL Server

SQL Server – LATCH_EX AND how to reduce it

SQL Server Books online defines the SQL wait type LATCH_EX as “Occurs when waiting for a EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A breakdown of LATCH_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.”

 Note:

1)The definition mentions “does not include buffer latches or transaction mark latches”.  This suggests this wait type is not related to IO or data.

How to reduce it

1) Investigate the DMV sys.dm_os_latch_stats  for latch contention source. Most sources are esoteric and there is very little in-depth information available on most latch classes. A DBA with deep knowledge of SQL Server Internals can interpret the results .

2) Investigate for administration procedures which may cause an underlying contention. Latches are internal SQL Server and there is very little impact by the DBA. Latches are short lived and it’s difficult to find the correlation between processes and latch contention

One method of investigating is to monitor the sys.dm_exec_requests  DMV while running a DBA procedure you think may cause latch contention. Monitor the wait_type column

 Read More

Timeout occurred while waiting for latch - SQL Server DBA

SQL Server – Pageiolatch_sh and how to reduce it - SQL Server DBA

Identify Page Latch contention - SQL Server DBA

 

SQL Server – Troubleshoot open cursors with sys.dm_exec_cursors

You receive a call from an application owner reporting a severe performance slow down on a production SQL Server Instance. They point you towards a blocking session id. Rather than killing the SPID –  you decide to  do a root cause analysis.

 Step 1 - check for queries executing

The first you do is check all SQL Server – Current queries executing on SQL Server , using sys.dn_exec_requests. You can see clearly the queries executing and on a number of the queries , there is a blocking_session_id , in the example - 77. Although the session_id does not appear on the list of active queries.

 Step 2 -

The  dbcc inputbuffer  gives you a clue – that cursors are involved.

 

dbcc inputbuffer(77)

--Language Event	0	FETCH API_CURSOR0000000000036AB1

 

Step 3 - Use sys.dm_exec_cursors 

Use the DMV sys.dm_exec_cursors  - which returns Returns information about the open cursors in the databases. The query returns  useful information  to help troubleshoot the offending process.

 

SELECT ec.session_id, ec.name,ec.properties, ec.creation_time, ec.is_open, ec.writes,ec.reads,t.text 

FROM sys.dm_exec_cursors (77) ec 

CROSS APPLY sys.dm_exec_sql_text (ec.sql_handle) t 

 

Read More

 

SQL Server – Current queries executing on SQL Server

Top 5 SQL Server DMV for Index Analysis

SQL Server - Find all DMV and DMF

SQL Server sql_handle returns sql statement