SQL Server 2016 – Enhancements with TempDB

SQL Server 2016 is loaded with tons of new enhancements that I can’t get enough of it. I have been playing around with the bits since multiple CTP’s and it is getting difficult to catch up on all the releases coming so fast and the number of enhancements from performance and scalability point of view have been too many to track.

When I wrote the enhancements to Setup with SQL Server 2016 and it had a tempdb enhancement section, many wrote back stating there are more to it than just adding it on the setup wizard. So I thought to pen down the current availability of enhancements to TempDB with SQL 2016 here.

In multi-core environments users see Tempdb contention under load and it is one of the common errors one will see. In SQL Server 2016, Microsoft delivered improvements to make tempdb more scalable out of the box. Trust me, I am liking the thought gone behind it. To my knowledge, these are the key improvements to be anticipated:

  • Users will automatically get multiple tempdb files per instance (if the server is running on more than 1 core) up to a maximum of 8 tempdb files (on machines running on more than 8 or more cores).
  • Users will no longer have to use T-1117 (autogrows all files) or T-1118 (always allocate uniform extents) for tempdb. They are enabled by default on tempdb.
  • Reduce metadata contention on system table pages. Though this is subtle, some of the recommendations/steps DBA’s and Developers used to take to avoid this in prior versions of SQL Server are:
    • Converted temp tables to table variables
    • Avoid using constraints on temp tables
    • Removed TVF’s from queries

One of the readers wrote to me with a link to the T1117 enhancement and asked how they can verify.  You can verify the usage of multiple files and growth using the following query against sys.dm_db_database_page_allocations:

SELECT allocated_page_file_id, COUNT(*) AS Counts
FROM sys.dm_db_database_page_allocations (2, NULL, NULL, NULL, 'LIMITED')
GROUP BY allocated_page_file_id
GO

I have 4 core machine with Hyperthreading enabled, it shows up as 8 cores on my HyperV environment. My files on a default setup looks like:

tempdb sql16 01 SQL Server 2016   Enhancements with TempDB

So how many of you have used some of the trace flags mentioned above in your environments? How do you feel this will enhance your operational ability in your prods? Do let me know if I missed any other enhancements that needs to be mentioned. This will act surely as an working blog as I discover more.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Interview Question of the Week #048 – How to Move TempDB to Another Drive?

In a recent interview when the interviewer asked interview that TempDB can be moved to a different drive,  the candidate was extremely surprised. For the benefits of the interviewee who did not know the answer, I have written this blog post.

Question: How to move TempDB to another drive?

Answer:

There are major two reasons why TempDB needs to move from one drive to other drive.
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.

USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB
along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\datatemplog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – New features in SQL Server 2016 Setup Wizard

In this world of change, the SQL Server product is also changing. One thing which is very clear is that Microsoft is listening to feedbacks from customers and enhancing the setup experience also. Some of the classic feedback implementation can be seen in the setup of the next version of SQL Server 2016. In this blog post, I will try to bring out some of the enhancements that are being incorporated in the Setup UI. Some are critical and worth making a note.

Here are the few things which I noticed in SQL Server user interface.

SQL Setup allows user to add Database Engine Service SID into “Perform Volume Maintenance Task” privilege under security policy. This helps in instant file initialization

sql2016 setup 01 SQL SERVER   New features in SQL Server 2016 Setup Wizard

Here is the new parameter added in command line for SQL Setup.

SQLSVCINSTANTFILEINIT=”True”

This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal.

TempDB Configuration

In SQL Server 2016, we have the option to specify TempDB file specifications during setup itself. Most of the companies have “post deployment script” and one of the steps is to optimize TempDB files. It’s a tab called “TempDB” under “Database Engine Configuration”

sql2016 setup 02 SQL SERVER   New features in SQL Server 2016 Setup Wizard

Since UI has it, there is also a parameter in command line as below:

Parameter Meaning
SQLTEMPDBFILECOUNT The number of Database Engine TempDB files
SQLTEMPDBFILESIZE Initial size of a Database Engine TempDB data file in MB.
SQLTEMPDBFILEGROWTH automatic growth increment of each Database Engine TempDB data file in MB
SQLTEMPDBLOGFILESIZE initial size of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILEGROWTH automatic growth increment of the Database Engine TempDB log file in MB.
SQLTEMPDBDIR Directories for Database Engine TempDB files.
SQLTEMPDBLOGDIR Directory for the Database Engine TempDB log files

Hopefully, we would see more feedback embedded in the product by default.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Interview Question of the Week #027 – Move TempDB from One Drive to Another Drive When Low Disk Space

I am often invited to attend various interviews and once in a while, I see practical questions discussed in the interview. Here is one of the important interview question which is related to TempDB.

Question – “What will you do when your log file of TempDB is full?”

Answer – The best solution when Log file of TempDB is full is to move it to a new drive. Here is more details about the same.

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to another drive.
1) TempDB grows bigger and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on a different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move the database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.

USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

Along with other information related to the database. The names of the files are usually tempdev and templog by default. These names will be used in the next statement. Run following code, to move mdf and ldf files.

USE MASTER
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\datatemplog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

You can read more about this over here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #015 – How to Move TempDB to Different Drive

Here is one of the most popular questions I often come across-

Question – How to move the TempDB to different drive when the log files are filled?

Answer – In most of the cases which I have observed one has to move the TempDB to different drive when TempDB log file is filled up or one believes when moving to different drive will help the growth of the file. Sometimes user also moves to different drive due to performance reasons as keeping TempDB on a different drive from your main database helps.

Here is the error user usually engage when they come across TempDB log file growth.

The LOG FILE FOR DATABASE ‘tempdb’ IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free up SOME LOG SPACE

Here is my earlier blog post where I have described how one can change the TempDB location to another drive SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reducing Page Contention on TempDB

I have recently received following email.

“We are using TraceFlag 1118 to reduce the tempDB contention on our servers (2000 and 2005). What is your opinion?

We have read lots of material, would you please answer me in single line.”

Wow, this was very interesting question. What intrigued me was the second last where I am asked to answer in a single line. There is something about this strong email, I feel like blogging it here.

I think I can talk over this subject forever – well, there is no clear answer. There are so many caveats about everything.  Again, I must stay honest to the request about answering in single line. I also do not like to answer which is YES/NO. What should I do?

Let me ask this question to community today? What will you answer to this email?

Let me start this by answering it myself in one line and taking one side.

“I enable this trace flag in SQL Server 2000 without hot patch or service pack and not in later versions (2005+) onwards as code is improved”.

What do you do in this case? The best answer will feature in this blog with due credit.

Regarding further read and hint here is Microsoft KB which I think is very helpful.

In quick summary: (Read KB for accuracy)

When any page is allocated first 8 pages are allocated in mixed extended. This trace flag allocates uniform extended at the time, reducing contention. You can enable this trace flag at startup.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SQL Challenge – SQL Puzzle – Query Creating Most TempDB IO Usage

melting SQL SERVER   SQL Challenge   SQL Puzzle   Query Creating Most TempDB IO Usage

Recently, there have been a lot of interesting concepts in various challenges. My friend Jacob Sebastian is running the SQLQuiz for the entire month, and it has been very popular and going just great. So here I thought I would put something very similar to the quiz bee.

The award here is simple, all valid answers will be published on this blog with due credit to you, plus the credit would link back to your desired profile.

Now the question is: What are the queries which are creating lots of IO operations in TempDB?

You can use any DMV to answer this question. You need to list all the operations which are creating IO operations in TempDB, and those which may grow the size TempDB. Sometimes it is not TempDB but the open transactions with lots of queries that can lead to lots of TempDB IO and size growth. In that case, we want to find those open transactions, too. There are no limits as to how many DMVs you can use or how many suggestions you can give. Just find a reason that grows TempDB and creates lots of IO.

I think this is very interesting and many also want to learn the answer for this great question.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – How to Drop Temp Table – Check Existence of Temp Table

I have received following questions numerous times:

“How to check existence of Temp Table in SQL Server Database?”

“How to drop Temp Table from TempDB?”

“When I try to drop Temp Table I get following error. Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.
How can I fix it?”

“Can we have only one Temp Table or we can have multiple Temp Table?”

“I have SP using Temp Table, when it will run simultaneously, will it overwrite data of temp table?”

In fact I have already answer this question earlier in one of my blog post. I have even explained how Temp Table works in TempDB and how they are managed.

Visit my earlier written article for answers to questions listed above.

SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error : Msg 2714, Level 16, State 6 – There is already an object named ‘#temp’ in the database

Recently, one of my regular blog readers emailed me with a question concerning the following error:

Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.

This reader has been encountering the above-mentioned error, and he is curious to know the reason behind this. Here’s Rakesh’s email.

Hi Pinal,
I’m a  regular visitor to your blog and I thoroughly enjoy your articles and especially the way you solve your readers’ queries. I work as a junior SQL developer in Austin. Today, when I started to create a TSQL application, I detected an interesting scenario.

It is a common practice for many of us to use the following statements to create a new object.But when I used local temporary table, i.e.

IF EXISTS (
SELECT *
FROM sys.tables
WHERE name='#temp')
DROP TABLE #temp
CREATE TABLE #temp(id INT )

An error was displayed as:  Msg 2714, Level 16, State 6, Line 4
There is already an object named ‘#temp’ in the database.

On running the statement
SELECT *
FROM
sys.tables


I see the object under name column in sys.tables as ,
#temp_______________________________________________________________________________________________________________0000000002E1

As far as I can discern, my SQL Server is writing the above entry instead of #temp. Therefore,  I’m getting the error as table already exists – There is already an object named ‘#temp’ in the database. Can you please explain me why this happens to local temporary table?

Thanks and Sincerely Yours,
Rakesh

Rakesh, your question is noteworthy. Let’s look at the solution first and then we will look into the behavior.

Fix/Workaround/Solution:

In case of Temporary tables, run following T-SQL. Please note do not run this for any physical tables. Additionally, please note to replace #temp with yourtemptable name.

IF EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp%')
DROP TABLE #temp
CREATE TABLE #temp(id INT )

However, make sure not to run this for physical tables. Additionally, please note to replace #temp with yourtemptable name.
Local temp tables can be created using hash (#) sign prior to table name. They are visible only in current connection.. When connection is dropped its scope ends as well. It is possible to create and use local temp table with the same name simultaneously in two different connections. In order to allow this behavior SQL Server suffixes name of the local temp table with incremental hex digit, which is reset when SQL Services are restarted. It is because of this reason that when looking into sys table it has to compared using LIKE and ‘%’.

Let me create few temporary tables, and then we will observe how hex numbers are suffixed to local temporary tables.

BEGIN
IF
EXISTS (
SELECT *
FROM sys.tables
WHERE name LIKE '#temp%')
DROP TABLE #temp
CREATE TABLE #temp(id INT )
SELECT name
FROM sys.tables
WHERE name LIKE '%#temp%'
END
GO 10

 

%name SQL SERVER   Fix : Error : Msg 2714, Level 16, State 6   There is already an object named #temp in the database

Rakesh and my other readers, I encourage all of you to send me interesting and challenging questions that intrigue you as well as observations that you would like to share. As always, I will try my best to answer all your questions. Also, do send me your valuable opinions regarding this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Ideal TempDB FileGrowth Value

Just a day ago, while installing SQL Server on our development machine Jr. DBA asked me what should be kept file growth of the TempDB. I really have not thought about this till moment and I looked at MS site.

I found following answer and I think it is quite interesting.

tempdb file size – FILEGROWTH increment for tempdb

0 to 100 MB – 10 MB

100 to 200 MB – 20 MB

500 or more – 10%

Initially you can not know what will be the final size of your TempDB, but as you continue using server for a while and can detect what is the size of the TempDB. Based on your size of TempDB ideal size of FILEGROWTH can be determined.

Reference : Pinal Dave (http://blog.SQLAuthority.com), MS – TempDB