A community of more than 1,600,000 database professionals and growing

This editorial was originally published on Aug 8. 2013. It is being re-run as Steve is out of the office.

We see a lot of questions at SQLServerCentral repeated over the years as workers new to the platform encounter some of the issues that many of us have dealt with repeatedly over the years. One of the more common questions that I see in the SQLServerCentral forums has to do with tempdb growth.

Many people coming to the SQL Server platform realize tempdb is a workspace, but they don't always understand how it works. Many times I see questions where people don't understand why tempdb will grow from it's default size. Even more surprising is that they don't understand why it doesn't shrink back to the default size. After all, it contains temporary information, and when it's not being used, the size should shrink, right?

If only that were true, it would make administration simpler, though performance might be worse. Many people view the files in a computer like balloons. We add data and they grow. We remove data, and they shrink. However that's not the model for SQL Server files. Our mdf/ldf/ndf files are allocations, and once we allocate space, we don't remove it if no data is present. The allocations are available for the next time we need them, minus the time we spent requesting and receiving the allocation from the Operating System.

The space you need for tempdb is the peak space you need for the largest set of temporary operations in your workload. Even if all your data sets are small, say 100kb, if you have 1000 of them occurring at once, you'll need 100MB of space. With sorts, intermediate worktables, and more, you could easily have an average data set above 10kb, which is why your tempdb size might need to grow.

Just like with any other database, you need to monitor the size and load for tempdb. Adjust it as needed, based on your requirements, so that it will be properly sized each time to start the instance. The administration level isn't high for tempdb, as long as you have monitoring in place and you periodically perform the administration to appropriately size your system.

Join the debate, and respond to today's editorial on the forums

In-Memory-Tables can't be created without at least one index (either an HASH or an NONCLUSTERED index).

Since the data of the table will be stored only once in the memory, regardles of the number of indexes (they are only "pointer lists" to the next entry), they do not to count as HEAP -> answer 0 is wrong.

Furthermore In-Memory-Tables does not support CLUSTERED indexes (aswere 1 is wrong).

And because I created at least one index (and have a In-Memory-Filegroup in my database) both queries runs without an error, so the correct answer is Index_ID = 2.

You have be aware of this unusual behavior, when you e.g. want to filter sys.indexes or sys.partitions for the "main" index of a table by using WHERE index_id < 2, since this will return no rows for In-Memory-Tables

References: click here

» Discuss this question and answer on the forums