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

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  November 14, 2016 at 5:59 am

  #1912219

  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

  November 14, 2016 at 6:10 am

  #1912221

  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: 182912

  More actions

  November 14, 2016 at 6:45 am

  #1912236

  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?