Category Archives: Trace Flags

Inconsistent Tempdb Growth

— by Ginger Keys

Tempdb database is a system database that is shared across all databases and all connections in SQL Server.   As a general rule, I configure all my client’s servers to have multiple tempdb files with consistent sizes and consistent auto growth set according to best practices. The number of tempdb files created equals the number of cores or 8, whichever is lower. And the size set for each file depends on each individual SQL instance to accommodate a typical workload.

The Problem

Recently during a routine review of a client’s server, I noticed in the SQL Server Logs that there were messages indicating I/O pressure for the tempdb database.

Blog_20180111_1

I also noticed in Windows Explorer that my tempdb files were no longer uniform in size. In fact the first data file had grown way out of proportion compared to all the other data files:

Blog_20180111_2

After double-checking the database properties, I confirmed that these files should have grown in consistent amounts:

Blog_20180111_3

So what happened to cause this inconsistent growth and how should I fix it?

Tempdb is used (and can grow) when users explicitly create objects like temp tables, variables, cursors, or stored procedures. Tempdb is also used when the database engine creates work tables for sorts or spooling, and row versioning operations.

It’s often difficult to diagnose what happened on a SQL Server after the fact. There are many resources online to determine what is currently using tempdb but not a lot of information from processes that may have run days ago.

What Happened?

First, I want to see what is currently using tempdb to find out if there are any processes using all that space in the database. The TSQL statements in the following links are helpful in determining who is currently using tempdb:

https://littlekendra.com/2009/08/27/whos-using-all-that-space-in-tempdb-and-whats-their-plan/

http://www.sqlservercentral.com/scripts/tempdb/72007/

https://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/

If you find there are plans using the tempdb causing substantial growth it is probably a good opportunity for some performance tuning.

In my situation there was nothing significant currently using tempdb, so my issue happened prior to my discovering the problem. Since I was not able to see any active queries creating temp tables, stored procs, cursors, or variables, I wanted to see if there were row versioning operations occurring.

If snapshot isolation is enabled on a database, performance can improve because it eliminates locking on the underlying tables. However in order to do this SQL puts the data (as it existed at the start of the transaction) into a temp table with row versions for each transaction, so that all queries in the transaction see the same version (or snapshot) of the database. This can take up a large amount of space in tempdb, depending on the size of the tables in the database being queried.

Run this statement to find any user databases with snapshot isolation enabled:

select * from sys.databases

where (snapshot_isolation_state = 1 or is_read_committed_snapshot_on = 1)

and database_id > 4

My client had a few databases with snapshot isolation enabled, so we had a conversation about the reasoning behind these settings. However it was still unclear as to why the tempdb grew and why the files grew out of proportion. As stated earlier, unless you are actively collecting performance data for this type of behavior, it is often very difficult to troubleshoot the cause of the problem after the fact. So what do we do about it?

Solution

When your tempdb files grow beyond the original size set in the properties, and they grow out of proportion, the preferred and best solution is to grow the files to a uniform size, as long as there is room on your disk. This is the preferred solution because if the tempdb files grew, it apparently needed that much room to perform the tasks that caused it to grow. You can adjust the size of your tempdb files using the GUI, or you can run the following statement for each data file:

USE master

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, SIZE = 2000MB)

If there is not enough space on your drive and you need to resolve the issue right now, shrink the tempdb files down to a uniform size. Keeping your tempdb data files the same size helps to avoid page contention issues.

This can be done through the GUI by selecting each individual data file and specifying the desired size, or you can run this statement for each data file:

USE tempdb

GO

DBCC SHRINKFILE (tempdev, 1000)

GO

 

If for some reason the data files don’t shrink to the size you specified, try running this

DBCC FREEPROCCACHE

then execute the SHRINKFILE statement again. Do this during a maintenance time of course and not during production time while users are connected.

Prevention and Best Practice

Put your tempdb on its own volume if possible. If your tempdb is on the same drive as your user database files and that drive runs out of space, it will be difficult to restart SQL Server. If your tempdb is on its own drive you can utilize all of the space for your tempdb files and also reduce I/O contention.

Create as many data files as you have processors, up to a maximum of 8. (Note that dual-core CPU is the same as 2 CPUs). This will reduce storage contention and is more scalable. There is much debate on the necessity of this practice…this is a general guideline.

Make each data file the same size. This helps to avoid SGAM page contention issues, and will allow for optimal proportional-fill performance. Set the size of your data files large enough to accommodate a typical workload. And if you have tempdb on its own volume, size your files to use the majority of the space on the volume. This way SQL doesn’t have to stop activity in order to grow the files.

Set the autogrowth to a fixed amount (not percentage) and to a reasonable size. If the threshold value is set too low compared to the amount of data written to tempdb, the files will have to expand too often. During autogrowth the database is unavailable, which will cause user transactions to have to wait until the growth process completes. Note – every time SQL server instance is restarted, the tempdb will be recreated to the original size specified in the database properties.

Enable Trace Flag 1117 – If you are not yet using SQL Server 2016 and are on an earlier version, enabling trace flag t1117 will ensure that your tempdb files all grow when any of the files reach the autogrow threshold. (Starting with SQL Server 2016 this flag is unnecessary.) Trace flag 1117 is a global setting, and will impact every database in your instance, not just tempdb. You can enable the trace flag by going to SQL Server Configuration Manager > SQL Server Services > Right click on SQL Server (MSSQLSERVER) > Properties > Startup Parameters > enter –t1117 and click Add. You must restart the service for the change to take effect.

Blog_20180111_4

Finally, you can minimize tempdb utilization by practicing the following:

  • Avoid SORT_IN_TEMPDB option
  • Avoid unnecessary cursors
  • Avoid spooling (CTEs referenced multiple times)
  • Avoid using MARS
  • Don’t enable snapshot isolation unless there is a very compelling reason to do so
  • Avoid using triggers for bulk operations
  • Avoid using LOBs as local variables

Reference: https://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

 

SQL 2016 Automatic Seeding of Availability Groups

–By Lori Brown

I came across something today that I did not know existed in AG’s, Automatic Seeding. This allows SQL to automatically create the secondary replicas for all databases in an AG. Pretty cool!! So if you set things up correctly when you set up your AG’s, you don’t have to worry about backing up databases and tlogs and taking them to the secondary replica, restoring and then getting the AG fully set up. It is a one stop shop. This is new in SQL 2016 only as far as I can tell.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group

Blog_20170420_1

You have to set up your AG by script but that is not too difficult.

—Run On Primary

CREATE AVAILABILITY GROUP [<availability_group_name>]

FOR DATABASE db1

REPLICA ON ‘<*primary_server*>’

WITH (ENDPOINT_URL = N’TCP://<primary_server>.<fully_qualified_domain_name>:5022′,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),

SEEDING_MODE = AUTOMATIC),

N'<secondary_server>’ WITH (ENDPOINT_URL = N’TCP://<secondary_server>.<fully_qualified_domain_name>:5022′,

FAILOVER_MODE = AUTOMATIC,

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,

BACKUP_PRIORITY = 50,

SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),

SEEDING_MODE = AUTOMATIC);

GO

Of course you have to be aware that if you set this on an AG with large databases, this could cause an issue since SQL would be pushing an entire database across the network. There is trace flag 9567 that can help compress the data stream for AG’s using Automatic Seeding but there are some side effects of increased processor load that you need to be aware of.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/tune-compression-for-availability-group

We are setting up some new VM’s here at SQLRX and will be blogging later in much more depth on how this works.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

SQL 2016 SP1 USE HINT

–By Lori Brown   @SQLSupahStah

After installing Service Pack 1 for SQL 2016, I ran across USE HINT and thought I would put out what I could find to document it. Here’s where you find info from Microsoft: https://msdn.microsoft.com/en-us/library/ms181714.aspx and here is a link for all of the bugs that are fixed in SP1: https://support.microsoft.com/en-us/kb/3182545

USE HINT ( hint_name ) Provides one or more additional hints to the query processor as specified by a hint name inside single quotation marks. Hint names are case-insensitive. USE HINT can be utilized without having to be a member of the sysadmin server role.

The following hint names are supported:

  • ‘DISABLE_OPTIMIZED_NESTED_LOOP’ Instructs the query processor not to use a sort operation (batch sort) for optimized nested loop joins when generating a query plan. Equivalent to trace flag 2340.
  • ‘FORCE_LEGACY_CARDINALITY_ESTIMATION’ Forces the query optimizer to use Cardinality Estimation model of SQL Server 2012 and earlier versions. Equivalent to trace flag 9481 or Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON.
  • ‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’ Enables query optimizer hotfixes (changes released in SQL Server Cumulative Updates and Service Packs). Equivalent to trace flag 4199 or Database Scoped Configuration setting QUERY_OPTIMIZER_HOTFIXES=ON.
  • ‘DISABLE_PARAMETER_SNIFFING’ Instructs query optimizer to use average data distribution while compiling a query with one or more parameters, making the query plan independent of the parameter value which was first used when the query was compiled. Equivalent to trace flag 4136 or Database Scoped Configuration setting PARAMETER_SNIFFING=OFF.
  • ‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’ Causes SQL Server to generate a plan using minimum selectivity when estimating AND predicates for filters to account for correlation. Equivalent to trace flag 4137 when used with cardinality estimation model of SQL Server 2012 and earlier versions, and has similar effect when trace flag 9471 is used with cardinality estimation model of SQL Server 2014 or higher.
  • ‘DISABLE_OPTIMIZER_ROWGOAL’ Causes SQL Server to generate a plan that does not use row goal adjustments with queries that contain TOP, OPTION (FAST N), IN, or EXISTS keywords. Equivalent to trace flag 4138.
  • ‘ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS’ Enables automatically generated quick statistics (histogram amendment) for any leading index column for which cardinality estimation is needed. The histogram used to estimate cardinality will be adjusted at query compile time to account for actual maximum or minimum value of this column. Equivalent to trace flag 4139.
  • ‘ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS’ Causes SQL Server to generate a query plan using the Simple Containment assumption instead of the default Base Containment assumption for joins, under the query optimizer Cardinality Estimation model of SQL Server 2014 or newer. Equivalent to trace flag 9476.
  • ‘FORCE_DEFAULT_CARDINALITY_ESTIMATION’ Forces the Query Optimizer to use Cardinality Estimation model that corresponds to the current database compatibility level. Use this hint to override Database Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON or trace flag 9481.

The list of all supported USE HINT names can be queried using the dynamic management view sys.dm_exec_valid_use_hints. More this view can be found here: https://msdn.microsoft.com/en-us/library/mt791356.aspx

Some USE HINT hints may conflict with trace flags enabled at the global or session level, or database scoped configuration settings. In this case, the query level hint (USE HINT) always takes precedence. If a USE HINT conflicts with another query hint or a trace flag enabled at the query level (such as by QUERYTRACEON), SQL Server will generate an error when trying to execute the query.

Example:

DECLARE @qty INT

SET @qty = 4

SELECT h.OrderDate, h.Freight, d.OrderQty, d.UnitPrice

FROM Sales.SalesOrderDetail d

JOIN Sales.SalesOrderHeader h ON (d.SalesOrderID = h.SalesOrderID)

WHERE d.OrderQty > @qty

OPTION (USE HINT(‘DISABLE_PARAMETER_SNIFFING’,‘DISABLE_OPTIMIZED_NESTED_LOOP’));

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

SQL Trace Flag 4199

— by Lori Brown @SQLSupahStah

Once again, I find things about SQL Server that I had no idea were even available when I actually have time to do some in-depth reading and research. Most of the time, my regular duties keep me so busy that I don’t have time to play with new SQL functionality much so this makes it very easy to miss things like all the interesting trace flags that are available.

blog_20160929_1

I found TF 4199 while doing some research on SQL 2016. Trace flag 4199 is used to turn on all hotfix or cumulative update (CU) functionality after it is installed. So, if you had an issue that should have been resolved by installing a CU or SP and did not find it happening, you could turn on trace flag 4199 to force all of the fixes to become active. Fixes and CUs are apparently not supposed to be turned on by default since they could affect execution plans. If you installed a specific hotfix, most of the time those come with their own trace flag that was needed to enable the fix. TF 4199 is the code to turn them all on.

To turn on a TF globally use the –T4199 command in your SQL startup parameters.

blog_20160929_2

To turn on a TF for your session use DBCC TRACEON (4199).

TF 4199 can be enabled in a specific query by using the OPTION clause with QUERYTRACEON.

SELECT col1, col2, f4 FROM MyTable WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199)

In my defense, I have rarely installed hotfixes or CUs and have tended to install service packs and so far (knock on wood) to not have to use a trace flag to get expected benefits.

In SQL 2016, TF 4199 is enabled by default for databases in 130 compatibility.

blog_20160929_3

For those of you who have not upgraded to SQL 2016, please be very cautious about enabling trace flags globally. Even with TF 4199, you can cause unexpected behavior to show up. If you do enable a trace flag, please have a good reason to do so. I have seen places (ahem** Microsoft ** ahem) that recommend TF 4199 be set globally but I would be very skeptical of doing this.

More info on trace flag 4199 can be found here: https://support.microsoft.com/en-us/kb/974006

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!