SQL SERVER – Is tempDB behaving like a Normal DB?

The more I work with SQL Server, more I am baffled with the way SQL Server works. In all these interactions, I have seen DBA’s and Developers think about TempDB as something totally different from a usage point of view and treat it differently. Yes, I do agree TempDB is a special purpose database and needs special attention because it is a shared database for the SQL Server instance. As the name suggests, it is for temporary working of SQL Server. In this blog, I am not going to rehash hundreds of blogs / whitepaper on how important tempDB is and what are the processes which utilize tempDB.

In this blog I would take a middle ground of how I used to work and coach DBA’s when doing performance tuning activities during my consulting days. These are interesting ways of looking at TempDB because in my opinion, tempDB is also similar to normal DB in multiple ways with a twist. Whenever I say this statement many question to how I can say this? If tempDB is like normal database, then it should also function like a normal database.

I generally take this challenge and say the twist is there for a reason. But the fundamentals of databases, pages, allocations etc. are all the same – no matter what database we are talking about. Hence, in my opinion TempDB is similar to normal database in many ways. Generally to illustrate this, I use the following explanation.

USE tempdb
GO
CHECKPOINT
GO
SELECT Operation, Context, [Transaction ID], AllocUnitId, AllocUnitName, [Page ID],
[Transaction Name], [Description]
FROM sys.fn_dblog(NULL, NULL)
GO

Since TempDB can be set with SIMPLE recovery model only, the CHECKPOINT will make sure it flushes all the data and we will normally be left out 3 rows. To drive home concept, we will try to create a simple single table and insert a row. It is shown below:

CREATE TABLE #Understanding_temp_table
(id INT,
col1 CHAR(20),
col2 DATETIME,
col3 CHAR(50));
INSERT INTO #Understanding_temp_table
VALUES (1, 'Pinal', '01-Dec-2014 07:01:01', '* * DUMMY * *')
SELECT * FROM #Understanding_temp_table

Now, if we go ahead and execute the below DMV query again we will get close to 150+ rows. If we look at the PFS page allocation, IAM allocations, Data page allocation and many more entries. The values and way to read fn_dblog() will be reserved for some other blog post, but let me take show you some interesting things to lookout for.

SELECT Operation, Context, [Transaction ID], AllocUnitId, AllocUnitName, [Page ID],
[Transaction Name], [Description]
FROM sys.fn_dblog(NULL, NULL)
GO

In the output of above DMV, we can see these two entries available on our Description column which are interesting to look at. In my example, I have taken the two rows and shown for reference.

Changed type HOBT_FIRST_IAM (2) for AU 1008806317071335424 in rowset 1008806317066092544 from page 0000:00000000 to page 0001:0000008e

Changed type HOBT_FIRST (1) for AU 1008806317071335424 in rowset 1008806317066092544 from page 0000:00000000 to page 0001:0000007f

If we take the page of 0000008e , 0000007f and convert it from Hex to Decimal, the values are: 142 and 127 respectively. Now let us try to map this to the allocations inside our TempDB database.

SELECT allocated_page_file_id, allocated_page_page_id, allocated_page_iam_page_id, *
FROM sys.dm_db_database_page_allocations(2, NULL , NULL, NULL, 'DETAILED')
WHERE OBJECT_ID NOT IN (SELECT ID FROM sysobjects WHERE TYPE IN ('S','IT','SQ'))

From the query output, we can see the pages 142 and 127 are allocated to IAM page and Data pages respectively. In a sense this is almost similar to how a normal database would function too. The allocation of pages would be similar. The only catch here is the object ID is negative. These are user defined temp tables inside tempDB, that is the only difference. We can take a look at the object name using the below query. I remember reading an article from my good friend Vinod Kumar around Database Page Basics if you want a quick refresher.

As defined before, we know the data page is 127. Let us use the DBCC command to dump the page data and check what is on our page. When I execute the command, you can see the single row we inserted at the start of this article. This is very much in line to our understanding of inserting into a table.

The twist is simple, if we go ahead and close the session that created the Temp Table (#Understanding_temp_table), then SQL Server goes ahead and cleans the pages allocated. This can be confirmed again by running the command. The typical output is shown below.

I hope this blog gives you a feeler to how allocations happen to tables, irrespective of them being temp or normal. Secondly, we can also see once the session is closed, the allocations are released back for temp tables. In future blogs, we will take a deeper look into tempdb and learn about how they are different when compared to normal databases.

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

About these ads

SQL SERVER – Maximum Allowable Length of Characters for Temp Objects is 116 – Guest Post by Balmukund Lakhani

Balmukund Lakhani (Blog | Twitter | Site) is currently working as Technical Lead in SQL Support team with Microsoft India GTSC. In past 7+ years with Microsoft he was also a part of Premier Field Engineering Team for 18 month. During that time he was a part of rapid onsite support (ROSS) team. Prior to joining Microsoft in 2005, he worked as SQL developer, SQL DBA and also got chance to wear his other hat as an ERP Consultant.

Here is the guest post by Balmukund Lakhani

Recently Dhananjay (B | T) posted an interesting question on the SQLServerFAQ community page since the answer was new to almost all in the group, I have taken idea to write a blog post to explain this in detail. Here we go.

Question:
All objects can have minimum 1 and maximum of 128 characters in their names in SQL Server. Only exception is local temporary tables that can have maximum of 116 characters. What’s the technical reason behind this? Anyone, why that’s so?

Manas (B | T) posted this link from books online which tells this limitation but I always wanted to explore what happens under the hood.  Here is the simple repro of problem statement

DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('A', 116)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)

In above script I am trying to create a temp table (starts with #) and you would see below output.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i int)

Msg 193, Level 15, State 1, Line 1
The object or column name starting with ‘#AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ is too long. The maximum length is 116 characters.

The URL which was pointed by Manas (B | T) clearly explained this
{
Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.
}

If you try to create a normal table with more than 128 characters then you would see

Msg 103, Level 15, State 4, Line 1
The identifier that starts with ‘#AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ is too long. Maximum length is 128.

If you read the message closely, there are two error numbers 193 and 103 which have different length.  Now the real question is why there is differentiation between regular table and temp table Let’s create two temp tables now. I am giving name as A and A [repeated 128 times]

DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('A', 115)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)
GO
DECLARE @i NVARCHAR(800)
SELECT @i = REPLICATE('B', 1)
SELECT @i = 'CREATE TABLE #'+@i+'(i int)'
PRINT @i
EXEC(@i)
GO

Here is the output which you would see. Now execute the same output in the SSMS.

CREATE TABLE #AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA(i INT)
CREATE TABLE #B(i INT)

If you run above piece of code in SSMS, you would see object getting created. Now, let’s find out the name of the object in tempdb database

SELECT LEN(name) 'Length', name
FROM tempdb.sys.objects
WHERE TYPE = 'u'
AND name LIKE '#AAAAAA%'
OR name LIKE '#B%'

Here is the output.

Click to expand

Length of both the object name is 128 even if we create object with length name as 116 and 1. That should give you little clue about the behavior.

Summary:
Whatever object name you give for temp table, SQL is going to pad the value with underscores and few “magic” bit as the end and would cover complete 128 characters. Have a closer look at length of magic bit “000000000058” and “000000000059” in above output [yeah, its 12]. Those are needed because you are allowed to create same name temp table by different sessions.  The length of magical number is 12 so 128 – 12 = 116 is the max length which is allowed for temp objects as SQL adds 12 chars at the end.

In case you are in Facebook, you may want to check the group SQLServerFAQ for other interesting questions.

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

SQL SERVER – TempDB in RAM for Performance

Performance Tuning is always the most interesting subject when we talk about software application. While I was recently discussing performance tuning with my friend, we started to talk about the best practices for TempDb. I also pointed my friend to the excellent blog post written by Cindy Gross on the subject: Compilation of SQL Server TempDB IO Best Practices. One of the discussion points was that we should put TempDB on the drive which is always giving better performance.

But my friend suddenly asked, “what if we can put TempDB on RAM, as RAM is the fasted drive?”

Good question! This was supported in earlier versions of the SQL Server (I think in 6.5). In later version of the SQL Server, the whole algorithm was written more efficiently and it is not that much of an absolute requirement. However, I still prefer putting TempDB on the drive which has lesser IO pressure.

Afterwards, he introduced to me a tool which can create drive from RAM. Well, that was an interesting thought. But then again, I will not go for this solution as it is not natively provided with SQL Server. For me, SQL Server Engine knows the right thing to do and how to maximize the usage of the RAM. Taking away RAM from OS and from other applications may not be a good idea. There are more optimization tricks that exist for TempDB than going for this option.

I would like to ask my readers who among you use this method in the production environment. What is your experience?

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)