World Search

Saturday, March 4, 2017

Maximum Worker Thread


About Worker Thread:

When a request is received, SQL Server will try to locate an idle thread to schedule the request. If an idle worker was not located, new worker will be created. If all workers have been created and none is idle, then the request is queued which will be picked up by any worker thread from the pool of worker threads created.


The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server.


CPU CORE COUNT
MWT- 32 BIT
MWT-64 BIT 
Up-to 4 processors
256
512
Octa-Core 8 processors
288
576
16 processors
352
704
32 processors
480
960
64 processors
736
1472
128 processors
4224
4480
256 processors
8320
8576


-- show Max number of worker threads on the SERVER
select serverproperty('instancename') as instancename, max_workers_count  as 'AllowedOn64Bit' from sys.dm_os_sys_info 

-- show number Current no of worker threads
select serverproperty('instancename') as instancename, COUNT(*) as MAXCOUNT  from sys.dm_os_workers

-- show the THREADPOOL wait time
select * from sys.dm_os_wait_stats where wait_type = 'THREADPOOL'

---- most of the sessions are waiting for LCK_M_S
select * from sys.dm_os_waiting_tasks

Friday, March 3, 2017

SQL Server 2000 - the application failed to start because msvcr71.dll or msvcp71.dll was not found

Error:The application failed to start because msvcr71.dll or  msvcp71.dll  was not found  

These files must be present on C:|Windows\System32 folder and also they should be present on SQL\Binn folder, either any software installation/upgrade /Anti virus might bombed these files.

Solution:

1. Search these files on the computer.
2. Paste them to SQL Binaries folder  - C:\Pf\MS\MSSQL\Binn
3. Paste them to C:\Windows\System32 folder also
4. Bring SQL Services online

Hope it helps someone! Thanks.

SQL Server Agent can't Connect-Comes Online - 2016

ERROR: SQLServerAgent could not be started (reason: Unable to connect to server 'WIN\Group'; SQLServerAgent cannot start).


Solution:

1. Connect to SQL Server Instance
2. Go to SQl Server Agent
3. Select -> right Click -> properties -> choose Connection
4. paste instance name as follows
    servername.domain.biz\instance_name
5. Click ok 

Now go to failover cluster manager and bring agent online. Issue resolved.