Discussions specific to the VMware vSphere hypervisor
Post Reply
fslot
Novice
Posts: 8
Liked: never
Joined: Feb 19, 2019 8:53 pm
Contact:

anyone increase SQL Max worker thread?

Post by fslot » Apr 12, 2019 5:17 pm

Case number is 03511236

I've got a couple of SQL servers that are failing due to "Failed to call RPC function 'Vss.Unfreeze': Error code: 0x80004005"
I know one option is to increase the max worker thread. I hear conflicting opinions on if it is safe/ok to do so. My backups don't fail all the time but do so enough to where I have to resort to SQL based backups. I'd prefer to use Veeam. Was thinking of bumping from default of 512 (0 setting) to 700 or even 1024.

Anyone have any experience with this? This is a 4 core VM with 384GB of ram containing 100 databases and about 1TB of total DB space.

select max_workers_count From sys.dm_os_sys_info
select count(*) from sys.dm_os_threads

returns 512/400

Mike Resseler
Product Manager
Posts: 5740
Liked: 612 times
Joined: Feb 08, 2013 3:08 pm
Full Name: Mike Resseler
Location: Belgium
Contact:

Re: anyone increase SQL Max worker thread?

Post by Mike Resseler » Apr 15, 2019 6:22 am

Hey fslot,

To be honest, I am not sure if this error has to do with the worker threads. It 80004005 is normally access denied. But you have a support case so it will be investigated more in depth.

To answer your other question. MSFT recommendation to go to 512 (or 0 in your case) because of your 4 CPU. While you can indeed increase it, my reason for not doing this is because with 0 it is SQL who is responsible for managing the worker threads. If you increase it yourself, you will have more threads available thus more queries can be handled at the same time, but it also mean more incoming connections and other resources that will increase. So honestly, increasing is possible (and I do not know about a downside) if you have enough resources to do everything else... More incoming and outgoing connections at the same time, potentially more IO on your storage system...

Just my 2 cents

Post Reply

Who is online

Users browsing this forum: Google [Bot] and 19 guests