SQL F.A.D. – Trace flag 834 and columnstore indexes

Digby Stick 2

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:

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:

 

[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]

 

 

Comments 1

  1. Pingback: SQL F.A.D. – Trace flag 834 and columnstore indexes - » Syndication - SQL Server - SQL Server - Toad World

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.