sp_configure slow to apply

Question

Post reply

sp_configure slow to apply

adb2303

SSCertifiable

Points: 7588
More actions
November 11, 2016 at 6:29 am

#318798

Hi,

I have just upped the cost threshold for parallelism to 50 on two of our test servers:

SP_CONFIGURE 'advanced', 1 GO RECONFIGURE WITH OVERRIDE GO SP_CONFIGURE 'cost threshold for parallelism',50 GO RECONFIGURE WITH OVERRIDE GO SP_CONFIGURE

This took 4m33s to apply. I was quite surprised it took so long and am a bit worried about how long it may take on our prod servers.

I was running sp_whoisactive whilst it was applying. Although there was no blocking, the results from sp_whoisactive were definitely slower coming back.

Anything to be concerned about? Anyone experience any problems applying this change to very busy transactional servers?

Thanks

Andrew

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

adb2303 (11/11/2016)

Hi,

I have just upped the cost threshold for parallelism to 50 on two of our test servers:

SP_CONFIGURE 'advanced', 1 GO RECONFIGURE WITH OVERRIDE GO SP_CONFIGURE 'cost threshold for parallelism',50 GO RECONFIGURE WITH OVERRIDE GO SP_CONFIGURE

This took 4m33s to apply. I was quite surprised it took so long and am a bit worried about how long it may take on our prod servers.

I was running sp_whoisactive whilst it was applying. Although there was no blocking, the results from sp_whoisactive were definitely slower coming back.

Anything to be concerned about? Anyone experience any problems applying this change to very busy transactional servers?

Thanks

Andrew

Strongly suggest that you DO NOT run this during busy hours, the statement will flush the plan cache, causing the recompilation of all queries the next time they are executed.

😎

What version are you using SELECT @@VERSION and what are the specs of the server?

adb2303 SSCertifiable Points: 7588 More actions · Answer 2

Hi,

Thanks for the response.

SQL Server version

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

The servers are:

24 core, 2 x Intel Xeon X5675 3.06Ghz

192GB RAM

Windows 2008 R2 Enterprise SP1

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 3

adb2303 (11/14/2016)

Hi,

Thanks for the response.

SQL Server version

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

The servers are:

24 core, 2 x Intel Xeon X5675 3.06Ghz

192GB RAM

Windows 2008 R2 Enterprise SP1

First suggestion is to patch the servers up to 10.50.6542 (the lates/final for 2008R2) or at least 10.50.6529 (Security update).

😎

As I mentioned earlier, these changes should, if possible, be done when the systems are not under heavy load, that is out of business hours. The servers look beefy enough to handle it but then again it depends on the load how big the impact is going to be. What are the servers' stats (Active Session Counts, Transaction/Second, Batch Requests/Second, CPU Utilization etc.) during off hours?