July 28, 2012 at 12:40 am
Hello,
We have a server with 24 logical CPUs. SQL server uses about 80 threads which is above threshold mentiond in microsoft for a 24 core cpu server. If I want to go deeper in this issue, how can I find number of worker threads per Database? If there's any way to do that, I'll be so thankfulness if you'd let me know.
July 28, 2012 at 2:08 am
if you are referring to database connections when you mention threads you can execute the following query:
select a.name as "databasename",count(*) as "No of connections"
from sysdatabases a, sysprocesses b
where a.dbid = b.dbid
group by a.name
order by a.name
and if you want to ignore the connections from sysdatabases you can execute the followign qiuery
select a.name as "databasename",count(*) as "No of connections"
from sysdatabases a, sysprocesses b
where a.dbid = b.dbid
and a.name not in ('master','model','msdb','tempdb')
group by a.name
order by a.name
July 28, 2012 at 2:12 am
and further could you please clarify as to what you intend to state when you mentioned the following in your post :
SQL server uses about 80 threads which is above threshold mentioned in microsoft for a 24 core cpu server.
There seems to be some confusion, could you please share the KB article which mentions that?
July 28, 2012 at 3:42 am
Ooops! There's a problem! I meant 880 worker threads, not 80.
When you go to task manager and you add "threads" option, then you see what I meant. SQL server is using more than 880 threads. Does no of connections relate to no of threads?
July 28, 2012 at 7:55 am
ok got it.
The threads that you are seeing from task manager are not all SQL server threads. They are all windows threads and include all processes on the server including SQL Server.
The value for max worker threads is usually set to 0 which allows SQL server to mange number of threads on its own without you having to worry about it. Also that will work most of the times (if not all)
Can you run the following and check the values of max worker threads?
exec sp_configure 'show advanced options', 1;
go
reconfigure
and then execute:
exec sp_configure 'max worker threads'
if the run value is being shown as 0 in the output then you can conclude that the option is dynamic on your server and also that there is nothing additional for you to do. you may have to revisit this later only if required.
and After you are done with your tests you can turn off the advanced option again by executing:
exec sp_configure 'show advanced options', 0;
go
reconfigure
July 28, 2012 at 10:53 pm
Nops. The number I tell you is for SQL Server, not alll windows threads. go to task manager, then "processes", and select "select columns" from the "view" menu. then you'll find the threads which each process including SQL server uses.
Max worker threads is for SQL server is set to default "0". But I have some errors that tells SQL server is unable to handle parallel threads. This happens when SQL Server threads go beyond 850.
July 29, 2012 at 2:22 am
got it, appreciate the detailed explanation.
Can you post the exact error message that you are encountering?
The error related details will facilitate troubleshooting of this problem in further detail.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply