Hot answers tagged

74

From http://www.sqlservercentral.com/scripts/tempdb/72007/ ;WITH task_space_usage AS ( -- SUM alloc/delloc pages SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS alloc_pages, SUM(internal_objects_dealloc_page_count) AS dealloc_pages FROM sys.dm_db_task_space_usage WITH (NOLOCK) WHERE ...


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 ...


27

There are three DMVs you can use to track tempdb usage: sys.dm_db_task_space_usage sys.dm_db_session_space_usage sys.dm_db_file_space_usage The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects. The following example query will give you allocations per ...


22

To move tempdb files, you simply need to do the following: alter database tempdb modify file ( name = tempdev, filename = 'C:\YourNewTempdbDir\tempdb.mdf' ) go alter database tempdb modify file ( name = templog, filename = 'C:\YourNewTempdbDir\templog.ldf' ) go If you want to add a new file to tempdb, you simply need to do the following (...


19

Do writes to TempDB always result in an actual physical write to disk, or are TempDB writes cached by SQL Server for delayed write like in the Windows file system cache? Do they always? Most definitely not. Do they ever? Yes but not as a result of the typical mechanism. Reference here is What does checkpoint do for tempdb?. In a "well behaved" system, ...


17

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 ...


14

I've seen this very issue and the hotfix that was ultimately released to fix it was actually a direct result of my case with Microsoft CSS. There is no public KB article for the fix. Please make sure you've applied Service Pack 4 and the most recent cumulative update to SQL Server (at the time of writing, that's Cumulative Update #3 (9.00.5259)). Until the ...


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

A ratio of 1/4 to 1/2 times the number of TempDB data files to machine cores has long been the recommendation... But there's now even better guidance. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files ...


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

SSMS query results cache to the C: drive by default. Go to Tool \ Options. See attached. Change this to another volume with more storage and you should be fine.


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 ...


11

Okay, I figured it out: Eric and I were both right! The path in the dialog is as I said, just a default path for saving query results. Query results are cached to disk (I was wrong), but in the local profile temp folder (C:\Users\<UserName>\AppData\Local\Temp in my case here). I checked, and there doesn't appear to be an obvious way to turn this ...


11

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 ...


11

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 ...


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

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 ...


10

Like most general guidelines, it is a an oversimplification in its most positive light. At best, it is a good starting point (provided you don't aren't keeping the 1:1 core:data file ratio with a large amount of cores). There is no replacement for proper design and proper follow-up monitoring and baselining. The reason behind having multiple data files ...


10

It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it? You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators: USE [your database]...


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

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 ...


10

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 ...


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

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 ...


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