---

How to check if trace flag 834 and column store indexes are used concurrently in SQL Server (PowerShell)

Introduction

This PowerShell script will demo how to check if trace flag 834 and column store indexes are used concurrently in SQL Server.

Scenarios

If trace flag 834 and column store indexes are used concurrently, you may encounter performance issues with applications. If this kind of problems exist, this script will warn you.

Script

You can use this script in this way:
1. Run Microsoft PowerShell as Administrator
2. Run the script in the form: &Path
For example: &"E:\OneScript\Upload\Julie\CheckColumnStoreIndexAndTF834Exist.ps1"

3. Press Enter and enter the server name in this type: Server Name\ Instance Name.
Note: If it's a default server, please enter the server name
4. Press Enter and choose the authentication type. If you choose SQL Server authentication, please enter the correct user name and password.

5. After the script runs, we抣l get the following figure:

Here are some code snippet for your reference:

PowerShell
Edit|Remove
powershell
Function GetChoice($message)
{
    $Win = New-Object System.Management.Automation.Host.ChoiceDescription "&Windows","Windows authentication"
    $SQL = New-Object System.Management.Automation.Host.ChoiceDescription "&SQL Server","SQL Server authentication"
    $Quit = New-Object System.Management.Automation.Host.ChoiceDescription "&Quit",""
    $choices = [System.Management.Automation.Host.ChoiceDescription[]]($Win,$SQL,$Quit)
    $caption = "Confirming"
    $result = $Host.UI.PromptForChoice($caption,$message,$choices,0)
    $result
}

Function GetChoice($message) 
{ 
    $Win = New-Object System.Management.Automation.Host.ChoiceDescription "&Windows","Windows authentication" 
    $SQL = New-Object System.Management.Automation.Host.ChoiceDescription "&SQL Server","SQL Server authentication" 
    $Quit = New-Object System.Management.Automation.Host.ChoiceDescription "&Quit","" 
    $choices = [System.Management.Automation.Host.ChoiceDescription[]]($Win,$SQL,$Quit) 
    $caption = "Confirming" 
    $result = $Host.UI.PromptForChoice($caption,$message,$choices,0) 
    $result 
}



 

Prerequisites

SQL Server 2012

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.