Trace flags can be good

Are special codes we, the DBA, can use to change the way the SQL Server engine works.

Some perform simple changes, like turning off excessive backup success notices in the logs.

Other can completely cripple your SQL Server if you don’t know what you are doing; Trace flag T3609, is undocumented, but it tells SQL to not create a tempdb when it starts. Needless to say, this trace flag should be used only if Microsoft support team directs you to use it.

 

Columnstore indexes can be good

Are a special kind of index in SQL server. They essentially turn row indexing on their side to make things like aggregates work a lot faster. Generally they are used in data warehouse environments.

 

Together they can be not good

Sometimes combining columnstore indexes and trace flags can impact performance in a not good way.

How much?

I don’t know, but I do know it is not recommended to have trace flag 834 on when using columnstore indexes in your databases.

Since the 834 trace flag is a global level flag, and columnstores are in individual databases I wrote the script below to go through and check if you ave any columnstore indexes, and then check if the trace flag is enabled.

It simply returns an alert message if the conflict is found, otherwise nothing happens.

This code also does not fix any issues you may have with the trace flag.

You need to turn it off yourself using the following command:

DBCC TRACEOFF (834); 
GO

You also have to make sure it isn’t set as a startup parameter on the SQL Server startup parameters. There is would look like:  ; -T3226

Just remove that code and you will be ready on the next reboot automatically.

Here’s the script:

-- global temp table for the server to store if we find any columnstore indexes
CREATE TABLE ##Columnstore ( itype TINYINT );

-- cycle through the DBs on the instance looking for columnsotre indexes
-- this does use the undocumnted proc sp_MSforeachdb, but you could change it out for a loop
-- this was written for quick use.

EXEC sys.sp_MSforeachdb N' use [?]
insert ##columnstore (itype)
SELECT  i.type AS [Index type]
            FROM    sys.indexes AS i
            WHERE   i.type IN ( 5, 6 )';

-- check if any columnstore indexes on instance
IF EXISTS ( SELECT  1
            FROM    ##Columnstore )
    BEGIN
-- if ther eis a columnstore in a database on the server, check if the 834 traceflag is enabled
        DECLARE @TraceFlags TABLE
            (
             TraceFlag SMALLINT
            ,status BIT
            ,global BIT
            ,session BIT
            ); 

        INSERT  INTO @TraceFlags
                EXECUTE ( 'DBCC TRACESTATUS(834)'
                       );

        IF EXISTS ( SELECT  1
                    FROM    @TraceFlags
                    WHERE   status = 1 )
            BEGIN
-- if active traceflag found, then return a statement
                PRINT 'Trace Flag 834 - use MS Windows large-page allocations for the buffer pool is enabled. This is not recommended on servers that are using Columnstore indexes.';
            END;
    END;

-- clean up the global temp table
DROP TABLE ##Columnstore

 

[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]

Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services, training and the FREE sp_WOxCompliant compliance check script for your SQL Server environment.

Click here to contact Chris or to follow him on twitter. [/icon_box]