Amazon – RDS – Parameters – Trace Flags

Background

As intimated in our last post, one is able to effect MS SQL Server DBCC Trace Flags through RDS Parameter groups.

Currently, there are about 74 options that one can access through parameter groups.

In this post, we will focus on the dozen DBCC trace flags that are exposed.

 

Trace Flags

 

Trace Flag Description Default Recommendation Reason
 1204 Returns the resources and types of locks participating in a deadlock and also the current command affected  Off  On The information is very useful for troubleshooting deadlocks
 1211 Disables lock escalation based on memory pressure, or based on number of locks. The SQL Server Database Engine will not escalate row or page locks to table locks  Off  Off  It is better to allow the SQL Engine to control lock escalation
 1222 Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.  Off  On Same as Trace Flag 1204, helpful for deadlock troubleshooting
 1224 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation.  Off  Off Same as Trace Flag 1211, best to allow SQL Engine control
 2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE  Off  Off Again, allow the SQL Engine to throttle whether to enable parallel checking
 3205 Disable hardware compression for tape drivers  Off  Off MSFT doc states – “If a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression“.
 3226 Suppress log entries for backup operations  On  On Suppress successful backup logging.  This reduces errorlog cluttering especially useful for the more frequent log backup
 3625 Limits the amount of information returned in error messages  On  On In our own environment, the default security put in place ( for errorlog) by SQL Server is sufficient
 4199 Controls multiple query optimizer changes previously made under multiple trace flags   Off  On You want to check your SQL Server Version and Patch level and see if it supports this Trace Flag.

If it does, I will say test the Trace Flag out, and keep it enabled, if it does cause harm.

Please read more here.

 4616 Makes server-level metadata visible to application roles  Off  Off No need to expose this class of information
6527 Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration  Off  Off Memory dump is useful for diagnostic and so no need to disable memory dumps
7806 Enables a dedicated administrator connection (DAC) on SQL Server Express  On  On Dedicated Administrator connection is useful for accessing stalled SQL instances

 

 

Code

Enable Trace Flag – 1204

aws rds Modify-db-parameter-group

Sample code for enabling a trace flag through AWS/RDS CLI.

Syntax


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group  --db-parameter-group-name [parameter-group-name]  --parameters "ParameterName=1204,ParameterValue=1, ApplyMethod=immediate"

Sample


@rem Returns the resources and types of locks participating in a deadlock and also the current command affected.
aws rds modify-db-parameter-group  --db-parameter-group-name corp  --parameters "ParameterName=1204,ParameterValue=1, ApplyMethod=immediate"

 

dbcc traceoff

Sample code for disabling a trace flag through Transact SQL.

Syntax

DBCC TRACEOFF ([trace-number], -1)
GO

Sample


DBCC TRACEOFF (1204, -1)
GO

Output – Image

DBCCTraceoff

Output – Text


Msg 2571, Level 14, State 3, Line 1
User 'guest' does not have permission to run DBCC TRACEOFF.

Explanation

  1. As we do not have sysadmin privileges, we are not able to issue DBCC TraceOn/DBCC TraceOff
    • This command “requires membership in the sysadmin fixed server role” ( Link )

 

 

Review Enabled Trace Flags

List all enabled trace flags

Code


DBCC TRACESTATUS(-1);
GO

Output

DBCCTraceStatus-Enabled

Explanation

  1. I am familiar with all the other Trace flags ( listed above ), outside of Trace Flags 4199 and 8017
    • 4199
      • Need to clear cached plan
        • SQL Server query optimizer hotfix trace flag 4199 servicing model
          https://support.microsoft.com/en-us/kb/974006

          If DBCC TRACEON\TRACEOFF is used this does not regenerate a new cached plan for stored procedures. Plans could be in cache that were created without the trace flag
    •  8017
      • Aaron Morelli
        Twitter – @sqlcrossjoin
        Topic – A Topical Collection of SQL Server Trace Flags
        Link – https://sqlcrossjoin.files.wordpress.com/2014/04/sqlcrossjoin_traceflagrepository_v2.pdf
        Ken Henderson 2005, page 387 (paraphrased): basically means “no offline schedulers”.
        Normally, when using affinity to restrict the CPUs that SQL can use, SQLOS starts up schedulers for every CPU on the box, but then keeps schedulers that it is not allowed to use in “offline” state. However, those schedulers are using resources, so you can prevent SQL from ever creating those schedulers by turning on this flag. You can combine this with 8002 to achieve the “move among CPUs” effect for your schedulers
        This flag appears to have been turned on by default in SQL 2005 Express Edition, as evidenced by all of the upgrade warnings people were experiencing when trying to upgrade to SQL 2008 Express.
      • Makes sense as SQL Server Express Edition does not support SQL Server Agent

 

References

Trace Flags – MSFT

  1. Trace Flags (Transact-SQL)
    https://technet.microsoft.com/en-us/library/ms188396.aspx


Trace Flags – Generic

  1. Warner Chaves – The Most Important Trace Flags for SQL Server
    http://sqlturbo.com/the-most-important-trace-flags-for-sql-server/
  2. Aaron Morelli – Trace Flag Respository
    https://sqlcrossjoin.files.wordpress.com/2014/04/sqlcrossjoin_traceflagrepository_v2.pdf
  3. Derik – sqlHammer – Derik’s Favorite Trace Flags
    http://www.sqlhammer.com/deriks-favorite-trace-flags/

 

Trace Flags – 4199

  1. SQL Server query optimizer hotfix trace flag 4199 servicing model
    https://support.microsoft.com/en-us/kb/974006
  2. Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON
    https://www.mssqltips.com/sqlservertip/3320/enabling-sql-server-trace-flag-for-a-poor-performing-query-using-querytraceon/
  3. Benjamin Pierce – SQL 2008 – 2012 Query Optimizer Trace Flag 4199 – Increase performance
    http://www.symantec.com/connect/blogs/sql-2008-2012-query-optimizer-trace-flag-4199-increase-performance
  4. Joe P – Developer Gems – SQL Server Trace Flag 4199
    http://developergems.blogspot.com/2012/06/you-know-sometimes-as-software.html
  5. David K. Lee – Convergence of Data and Infrastructure – SQL Server Trace Flag 4199
    http://www.davidklee.net/2012/08/23/sql-server-trace-flag-4199/
  6. Paul White – Optimization Phases and Missed Opportunities
    http://sqlperformance.com/2013/06/sql-indexes/recognizing-missed-optimizations
  7. Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON
    https://www.mssqltips.com/sqlservertip/3320/enabling-sql-server-trace-flag-for-a-poor-performing-query-using-querytraceon/

 

Trace Flags – 4616

  1. Gerard Conroy – SQL Server Trace Flag 4616 no longer required for Dynamics NAV 5.0 SP1 or Dynamics NAV 2009 SP1
    https://blogs.msdn.microsoft.com/nav/2010/02/11/sql-server-trace-flag-4616-no-longer-required-for-dynamics-nav-5-0-sp1-or-dynamics-nav-2009-sp1/

 

SQL Server Builds

  1. Microsoft SQL Server Version List
    http://sqlserverbuilds.blogspot.in/

 

SQL Server on Amazon RDS – Create Database – File Groups

Background

Quick commentary on creating a database that sits on multiple filegroups on Amazon RDS.

 

Guide

Launch SQL Server Management Studio (SSMS) and connect to your Amazon RDS MS SQL Server.

New Database

General

Initial

Here we specify the database name has DBLAB.

And, the system autofills the Logical names of DBLAB for the File Type of ROW.  And, DBLAB_log for the LOG.

NewDatabase - General

 

Filegroups

Initial

Here is the initial screen for filegroups.

NewDatabase-FileGroups

 

Post

Here is what things look like once we added Indexes and Indexes2 as file groups.

NewDatabase-FileGroups-AddedAdditionalFilegroups

General

Post

We return to the general Tab and added new files and positioning them to the corresponding Filegroup

  1. Logical name of DBLAB_Indexes attached to Indexes Filegroup
  2. Logical name of DBLAB_Indexes2 attached to Indexes2 Filegroup

 

NewDatabase - General-AddedAdditionalFiles

 

Best Practice

Best practice suggest that we should target a separate physical disk, LUN, for each file group.

If we try to seek out an alternate File path, we will get the error stated below.

Image

xp_fixeddrives-error-229

Textual


The execute permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'

Implication

We do not have permission to access xp_fixedrives.  xp_fixedrives lists available storage on attached physical drives.

SQL Script

Btw, here is the SQL Script.


CREATE DATABASE [DBLAB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DBLAB', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB.mdf' , SIZE = 5120KB , FILEGROWTH = 10%), 
 FILEGROUP [Indexes] 
( NAME = N'DBLAB_Indexes', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_Indexes.ndf' , SIZE = 5120KB , FILEGROWTH = 204800KB ), 
 FILEGROUP [Indexes2] 
( NAME = N'DBLAB_Indexes2', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_Indexes2.ndf' , SIZE = 5120KB , FILEGROWTH = 204800KB )
 LOG ON 
( NAME = N'DBLAB_log', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO


Summary

One has to rely on the underlying storage configuration when using a provisioned system such as RDS.

Typical engineering thoughts such as separating data and logs are not available.

Same with attempts to disambiguate data and index I/O.

Amazon – RDS – Command Line Interface ( CLI ) – Returning empty resultset

Background

Just blew a couple of hours trying to determine why a couple of RDS/CLI Commands are coming back empty.

 

Commands

Here are the commands I tried out

describe-db-instances

Command


aws rds describe-db-instances

Output


{
"DBInstances": []
}

 

describe-db-parameter-groups

Command


aws rds describe-db-parameter-groups

 

Output


{
 "DBParameterGroups": []
}

Remediation

Traced the error back to the fact that I had set a wrong region.

Workarounds are to override the default per each command utterance, or go back and reset the default.

Specify Region at Command Level

describe-db-instance

Command


aws rds describe-db-instances  --region us-west-2

Output

describe-db-instances-20160426

describe-db-parameter-groups

Command


aws rds describe-db-parameter-groups --region us-west-2

Output

describe-db-parameter-groups-20160426

 

Reset default region

Review Configuration

Command


aws configure

Output

Wrong default – us-west-1

aws configure - 20160426 - 0658PM

Reset Configuration

  1. Connect to Console and get default

 

Connect to Console and get default

dbinstances-RDS-ScreenShot

 

Reset default via “aws configure”

Here we change the default region from us-west-1 to us-west-2


aws configure

   default region name [us-west-1] : us-west-2

aws configure - 20160426 - 0708PM

 

Confirm

Issue query without specifying default

Code


aws rds describe-db-instances

Output

DescribeDBInstancePostSettingDefault

Amazon – Web Services – RDS – Command Line Interface – Day 1

Background

This is will be first of likely many posts on Amazon Web Services, specifically RDS.

RDS stands for Relational Data Services. And, it encompasses Oracle, Microsoft SQL Server, PostgreSQL, and MySQL and its variants ( Aurora, MariaDB )

 

Introduction

As a Database Administrator needs will arise to perform administrative tasks such as reviewing existing instances, creating new ones, taking a snapshot ( so that we can a fallback in case we mess things up), create and review security groups.

Yes, these tasks can be completed via the Web Console. But, seriously, in some cases nothing beats scripting.

And, so let us see how we can access scripting functions.

 

Web Portal

Took to the web portal https://console.aws.amazon.com/console/home.

Signin

AWS-AMAZON-COM-SIGNIN

Console

Homepage

No CLI Interface Menu

There is no interface to the CLI Console.

So how to get a CLI Console ?

 

Download

Found a nice lead-in “Installing the AWS Command Line Interfacehere.

MS Windows

MSI

As my current machine is MS Windows, chose the MSI Path.

MSIInstaller

 

Installed

Installed Downloaded application.

Custom Setup

Chose to target D:\Program Files\Amazon\AWSCLI.

CustomSetup

 

Usage

Launch Command Session

Launch command session (cmd.exe)

Change Directory

Syntax

CD /D [installed-folder]

 

Sample

CD /D D:\Program Files\Amazon\AWSCLI\awscli

 

AWS Configure

We need to provide our Access ID and Key.

And, optionally a default region.


D:\Program Files\Amazon\AWSCLI\awscli\> aws configure
AWS Access Key ID [None]:
AWS Secret Access Key [None]:
Default region name [None]:
Default output format [None]:

Skipped AWS Configure

If you try to issue commands, before first setting your credentials, you will get the error pasted below.

Output


Unable to locate credentials. You can configure credentials by running "aws configure".

Sample

Describe DB Instance

Syntax


D:\Program Files\Amazon\AWSCLI\awscli>aws rds describe-db-instances

Sample


D:\Program Files\Amazon\AWSCLI\awscli>aws rds describe-db-instances --region us-east-1

Output


A client error (AccessDenied) occurred when calling the DescribeDBInstances operation: User: arn:aws:iam:1i118717:user/daniel is not authorized to perform:
 rds:DescribeDBInstances

Explanation

  1. In our case, our account has yet to be privileged to query for DB Instances

 

Command Reference

Here is a list of RDS Commands – RDS – References

Argument Explanation
describe-certificates Lists the set of CA certificates provided by Amazon RDS for this AWS account.
describe-db-engine-versions Returns a list of the available DB engines.
describe-db-instances Returns information about provisioned RDS instances.
describe-db-log-files Returns a list of log files
describe-db-parameter-groups Returns list of DB Parameter groups
describe-db-snapshots Returns list of DB Snapshots

 

References

AWS Command Line Interface

  1. Getting Help with the AWS Command Line Interface
    http://docs.aws.amazon.com/cli/latest/userguide/getting-help.html
  2. Command Structure in the AWS Command Line Interface
    http://docs.aws.amazon.com/cli/latest/userguide/command-structure.html

 

Microsoft SQL Server on Amazon RDS

  1. AWS Documentation » Amazon Relational Database Service (RDS) » User Guide » Microsoft SQL Server on Amazon RDS
    http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html