Hot answers tagged

55

writing to tempdb is faster than an actual table not in tempdb It's true. There are two IO enhancements in TempDb. Writes to a table in a user database must have their log records flushed to disk on commit, or if a minimally-logged insert (like SELECT ... INTO), must have the database pages flushed to disk on commit. The way the minimal logging works ...


28

when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB The article you linked to is misleading at best, and incorrect in some places. I think the author was attempting to over-simplify some complicated things, and in doing so went a little too far. SQL Server doesn't move data from memory (the buffer ...


18

I asked for something to be built in back in 2007, on Connect. This was rejected for the 2008 release, and subsequently ignored, until Connect died a few years ago. I tried to find it on the new feedback site for SQL Server, but that search is an absolute dumpster fire. The title of my request was "dmv to map temp table to session_id" - since the search can ...


18

File this under "just because you can, doesn't mean you should" If you generate a bind token in the first session, and somehow publish it, you can join its transaction from another session. Eg from spid 61: if @@TRANCOUNT > 0 rollback go begin transaction select * into ##t from sys.objects declare @bind_token varchar(255); exec sp_getbindtoken @...


16

What is hot spotting? "Hot spotting" in this context means that, even though tempdb has multiple files, all the I/O work is being done in a single file. If tempdb is busy enough to justify adding files, the imbalance that leads to hot-spotting (due to proportional fill) will be short-lived, so I think the warnings may be a little Chicken Little. In my ...


15

Is it possible that this frequency of spills could be a primary culprit in our high tempdb write latency? Yes it is possible, though typically it is the average size of the spills, and how deep they go (i.e. recursive hash spills, multi-pass sorts) that matters more than the frequency per se. SQL Server provides a wide range of metrics and DMV information ...


14

Enable trace flag 7470. FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct As I wrote in answer to Query Plan question: This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a ...


13

When you restart the service or reboot, the buffer pool and plan cache get wiped out as a result, and so when the server starts back up again, they don't get loaded until users start executing queries. What you can do to mitigate this is have a stored procedure aimed at simply running some queries that will load up your most frequently-used data into ...


13

I'd ask the person who told you that, to at least hear why they think it will decrease performance. One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your ...


12

Start SQL Server as an application, not a service, in minimal config mode, and only recovering master, from a command prompt: [path to this instance]\BINN\sqlservr.exe -c -f -T3608 (Add -s InstanceName if this is a named instance.) Now, in a different command prompt, connect using SQLCMD: sqlcmd -S InstanceName -E And issue a correction (triple-check ...


12

Truncating a temp table at the end of the stored procedure that creates it seems to cause the space the table uses in tempdb for the data to be released faster than if no truncate statement is used, despite expectations to the contrary. Why? If the temporary table is large enough (more than 128 extents), the physical page deallocations are deferred, and ...


12

Moving the TempDB files is a 2-step process: Tell SQL where you want your new TempDB files to go to Restart the SQL Server service for the change to take effect To tell SQL where to create the new TempDB files, you can use: DECLARE @newDriveAndFolder VARCHAR(8000); SET @newDriveAndFolder = 'Z:\YourTempDBfolder'; SELECT [name] AS [Logical Name] ,...


12

If you truly feel you need to clear it, then yes, restarting SQL Server will clear it. However, that will likely be a temporary solution and you'll be back in the same condition after a few days. Restarting SQL Server is the only way to clear the tempdb content from a SQL Server perspective. Another possible alternative would be to restart the application ...


11

So first, why is your data file growth set to 1MB? If you need to accommodate 20MB worth of data in tempdb the file will have to grow 20 individual times! Imagine if you have a query that requires a 200MB or 2GB spill to disk? Yikes. Growth events are expensive, especially on older SAS/SATA storage and especially if you don't have instant file ...


11

Both references are valid and will resolve correctly, but the #temp table is created under the dbo schema. Same answer (on your system, some number I couldn't possibly guess): SELECT OBJECT_ID('dbo.#MyTempTable'); SELECT OBJECT_ID('SomeSchema.#MyTempTable'); Same answer (both 1, which is dbo): SELECT schema_id FROM sys.tables WHERE [object_id] = ...


11

First, patch: make sure you're on 2012 Service Pack 1 Cumulative Update 10 or newer. In SQL 2014, Microsoft changed TempDB to be less eager to write to disk, and they awesomely backported it to 2012 SP1 CU10, so that can alleviate a lot of TempDB write pressure. Second, get exact numbers on your latency. Check sys.dm_io_virtual_file_stats to see the average ...


11

For me, the best practice is to just size the files large enough, at the outset, to accommodate the growths you expect. Let's use some simple numbers. Say you have a 100 GB drive that you've allocated to tempdb (or 100 GB of some shared drive). Your instance has 4 tempdb files, and you've decided to set them initially at 100 MB and to grow 10 MB at a time. ...


10

If you asking if SELECT INTO can use parallelism when writing, the answer is currently "no". Parallel SELECT INTO is being added to the product in SQL Server 2014. It is functional in Community Technical Preview 1, but performance testing is not encouraged (or valid) on pre-release software. Parallel SELECT INTO does not require multiple files or file ...


10

ADD - no outage required. Although as Sean from Microsoft pointed out, SQL will prefer to use the lower filled files. If you are going from 1 data file and adding more, then SQL will use the new ones for a while, but your performance won't be worse than only having one file. However, if you have 2+ already and add one more, then it will hotspot on the new ...


10

I can't answer for all versions, but for SQL Server 2012 up until SQL Server 2017 I am certain that they are supported LocalDB has the same programmability features as SQL Server Express. SQL Server Express LocalDB, a lightweight version of Express that has all of its programmability features, yet runs in user mode and has a fast, zero-configuration ...


10

There are going to be several possible workarounds here. You can manually adjust the memory grant, though I probably wouldn't go that route. You can also use a CTE and TOP to push the sort lower, before grabbing the max length column. It will look something like below. WITH CTE AS ( SELECT TOP 1000000000 r.ID, s.ID AS ID2, s.typeID FROM Resources r inner ...


10

What is hot spotting? Aaron is correct and I'm not going to rehash what he has said above, however it's not just about disk IO. The main part that most people have issues with in TempDB is due to contention on certain tracking structures. Since having multiple tempdb files allows the proportional fill and round robin algorithms to effectively take place ...


9

Note: this post might be useful too: Issues with TempDB mdf file ever increasing Unless you can figure out what process is using that work table (and can safely kill it), I'd have to agree with what your searches have already yielded: cycle the server and you should be able to shrink tempdb. A different question has dealt with figuring this out for #temp ...


9

For SQL Server 2000 → 2014 I generally agree with Paul Randal, that this is something you could always turn on, but I do have a little bit of hesitation to say it should always be on and you should leave it on regardless of what you observe (with or without adequate testing). People can have poorly set up tempdb configurations, for example not enough ...


9

does that mean no matter how good the CE is, the query has no chance of not spilling over? since the query max ram is hard capped at 38 GB The overall memory grant for your query appears capped at 37GB given your current hardware and SQL Server configuration. If the Sort cannot be performed within the Memory Fraction (0.860743 in that plan) of the ...


9

This isn't a big deal -- in fact, it's the way a lot of people handle tempdb in the first place: Add multiple data files Pre-size files equally to nearly fill tempdb drive Set autogrowth to a small increment If the space you have in there is empty and caused by a rogue report, I wouldn't worry about it, period. (This does assume you've set up tempdb on a ...


8

I have just suffered the same issue. After reading the answers above, I found the following. Tools | Options is not the answer. Mine was set to Y: drive yet I watched as my query ran and space on the C: drive dived from 2.9GB to 5.04MB (before I killed the query). So I thought it's probably caching results (as they are very large with every row returned ...


8

Not really, no. The created, but uncommitted, global temporary table is protected by a schema-modification lock, which is incompatible with everything else, including the minimal schema-stability lock required under read uncommitted isolation. It's hard to guess why you need this, but the usual issues revolve around the lifetime of the global temporary ...


8

As well as writes to tempdb often not every hitting disk/network IO, as extended upon in David Browne's answer, depending on your IO configuration you may find that even when the data is big enough to have to be spooled to disk it is still faster than selecting into a "normal" table: TempDB may be on different drives, so have its own IO bandwidth. This is ...


Only top voted, non community-wiki answers of a minimum length are eligible