SQL Server – RECONFIGURE

When you want to change configuration settings for your SQL Server you would either do it via Management studio (under server properties) or via TSQL calling on sp_configure.

For example, I usually change the cost threshold for parallelism from the low value of 5 which is what I will show you now on my SQL Server, plus with a side-effect.

The side-effect is that your plan cache will get flushed, yes it really does.

Let’s check cache before changing the setting (adapted from Kimberly Tripp http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/)

SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs] DESC
GO

cacge.JPG

Let’s now change the setting and re-check cache.

EXEC sp_configure 'cost threshold for parallelism', '50'
RECONFIGURE;

cache1.JPG

 SELECT objtype AS [CacheType],
COUNT_BIG(*) AS [Total Plans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs] DESC
GO

cach4.JPG

Yes, it has flushed. I couldn’t find anything on books on line about this behaviour. However it was mentioned in the following whitepaper: http://msdn.microsoft.com/en-us/library/dn148262.aspx under the flush entire plan cache section page 22 where RECONFIGURE command is mentioned. I am not sure if this applies to all options or a subset of them, more testing is required.

I was intrigued so I did one more test – enabling backup compression which is currently off.

cach5.JPG

Or you can check it this way:

SELECT *
FROM sys.configurations
WHERE name = 'backup compression default' ;
GO

afasefswgf

On the same test server my cache now looks like:

llllyr.JPG

Let’s enable backup compression and re-check.

EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE
GO

gggg.JPG

Well, no effect this time.

SELECT objtype AS [CacheType],
    COUNT_BIG(*) AS [Total Plans],
    SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs] DESC
GO

hh.JPG

This is something that I would personally talk about to a group of people and is my entry to https://sqlbek.wordpress.com/2016/10/25/t-sql-tuesday-84-growing-new-speakers/ – yes it would be demo heavy.

tsql.JPG

3 thoughts on “SQL Server – RECONFIGURE

  1. Pingback: SQL Server – RECONFIGURE - SQL Server Blog - SQL Server - Toad World

  2. Pingback: T-SQL Tuesday #84: Growing New Speakers Round-Up | Every Byte Counts

  3. Hey there!

    First, I want to apologize for my delay in following-up with you. Life got in the way – I hope you understand.

    If you’d still like support in building out your first presentation, I am going to ask that you reach out to me via e-mail (sqlbek at gmail). Please answer the following questions, if possible.

    1. Have you decided on a topic? If yes, what?
    2. How far have you gotten in developing your first presentation?
    3. Have you set a deadline for yourself to have your first presentation ready?
    4. What specific obstacles do you face now that someone else can aid you with?

    Once I get your response, I’ll either assign you to one of the community speakers who volunteered to help, or respond to you myself.

    Let’s keep this momentum going. Thanks again for participating in T-SQL Tuesday!

    Andy

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s