Sql Server : What is the recommended limit for Maximum number of connections allowed or max worker threads

Interesting Topic Today:

In general sense, we know that, internally within operating system, any work is performed using worker threads. Similarly, Sql Server relies on Windows Operating systems thread services to perform work; Each running query has a separate thread. When we have a large number of client requests, assigning a dedicated thread to each request can consume a large amount of resources. So, Sql Server and OS internally manage this efficiently with Thread Pooling.

 

Thread Pooling allows to bucket of threads that are used to service any client requests. If the number of requests are lower than the number of total threads, then each request gets a dedicated thread. But when the number of requests exceeds the total threads (in the thread pool), Sql Server manages that incoming request to assign the next available thread. So there would be some waiting. Since this is done at a high pace, multiple requests are serviced by a single thread, one after another.

 

Sometimes, when there are a large number of requests coming in at a faster pace than the workload is being completed, we’ll notice “application slowness” or “application timeouts” or certain error messages that say “could not establish connection to database” or “thread unavailable in connection pool” (see error message below). To reduce this ‘lack of threads’ issue, we could create start with a higher count of threads at OS startup.

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  
This may have occurred because all pooled connections were in use and max pool size was reached.
Error Number:  5 on <instance name>

In Thread Pooling, the number of threads made available at start up is determined by this max worker threads option. By default, the value is set to 0 —  meaning, Sql Server will manage it internally. The maximum allowable limit is 65,535. Since, at any given point, we have limited hardware and software resources, we do not want to make this number too high. We also do not want to make it too low, as it would impact the application queries.

 

Configuring these threads depends on several factors — CPU being the prominent one. Each thread takes turns on the processor to complete its work – all of this is managed by SQL OS and Windows Schedulers. Microsoft has proposed some general recommendations on this to figure out a way to get to a good number.

Number of CPUs	32-bit Database Server	64-bit Database Server
Less than 4 processors	256	512
8 processors	288	576
16 processors	352	704
32 processors	480	960

 

Note: For DBA’s, the DAC  has its own scheduler, with its own threads. So, if the server becomes unresponsive, that could be used to login.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: What is running in my SPID?

Quick one today:

Frequently, need arises to check what is running in some of the SPID’s on your Sql Instances. As always, with Sql Server, there are several approaches to this.

Use case:

One necessary and unavoidable fallout of concurrency is locks and latches. To maintain data integrity, Sql Server employs some internal structures to serialize certain overlapping actions. This results in short lived locks where one process (SPID) is blocking other process (SPID). In some worst case scenarios, this blocking goes on for extended period of time resulting in appearance of application slowness. In these situations, you want to uncover the SPID that is blocking everything else (you could do that with sp_who2 or using Activity Monitor). Then you want to know what is that process running?

Following are some options:

Using InputBuffer

DBCC INPUTBUFFER (52)
GO

InputBuffer

Using DMF’s

SELECT text AS [Query]
FROM sys.sysprocesses AS P
CROSS APPLY sys.dm_exec_sql_text (P.Sql_Handle) AS T
WHERE spid = 52
GO

SysProcesses

Using DMF’s

This is a depricated feature, so please fall back on using above options more.

SELECT text AS [Query]
FROM sys.sysprocesses AS P
CROSS APPLY sys.dm_exec_sql_text (P.Sql_Handle) AS T
WHERE spid = 52
GO

Sql Text function

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : How to KILL a SPID and then check the status of KILL / ROLLBACK progress

Quick one today:

A few days ago, this script came in handy when a certain thread needed to be cleaned on a lab server.

--
-- Kill a SPID
--
KILL 52
GO

Sometimes such a command could take a long time depending on the payload of the SPID that we are trying to kill. Sql Server will first try to rollback the SPID, to make sure it brings the session back to a stable point and then kills it. Such rollback sometimes takes a long time. So, to check how far along it has progresses, this below script comes in handy.

--
-- Kill a SPID
--
KILL 52 WITH STATUSONLY
GO

 

KILL With StatusONLY

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: All the locks held by a process

Quick one today:

Sometimes, we end up in situations where we need to uncover the underlying lock escalation or lock assignments due to some query executions. In these situations, we have an internal system procedure that displays this information in great detail.

Locks help attain concurrency, but they are expensive to maintain. So Sql Server continuously strives to maintain the right balance to achieve better performance with higher concurrency.

--
-- Retrieve locks held by current session
--
EXEC sp_lock
GO

--
-- Retrieve locks held by session 53
--
EXEC sp_lock 53
GO

Lock Allotment

When you are running a query on several tables or on a large table, several locks are issued. Sometimes reviewing this pertinent information could help explain the nature of Sql Server behavior with certain long running queries.

In this above snapshot, the ‘ALTER TABLE‘ query is a single threaded query that needs locks on each and every key / data page to complete the action successfully. A total of 2100 locks are issued to this SPID 53. After a few seconds, Sql Server realizes that it is better to perform lock escalation to page lock or even table lock than several individual locks.

Running it without any parameters returns the lock held by current session. With SPID as parameter, we could retrieve locks held by other sessions.

Note: Going forward, Microsoft recommends that we use sys.dm_tran_locks dynamic management view. sp_lock is still supported well into Sql Server 2016, but it is better to be familiar with old and new techniques. I’ll add a separate post on the new one soon.

Hope this helps,
_Sqltimes

Read Full Post »