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


20

To insert the results into a table, you just want INSERT....EXEC... not the VALUES part of the query. In your case, this would look like the following: INSERT INTO @DomainHistory(DomainId,HasHistory) EXEC GetOrSetDomainId 'test', 'test2';


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


17

What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure. Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the ...


16

Looking at the my.ini, I have two suggestions SUGGESTION #1 I would bump up the following settings in your my.ini sort_buffer_size=4M join_buffer_size=4M This will make some joins and sort stay in memory. Of course, once a JOIN or an ORDER BY needs more than 4M, it will page to disk as a MyISAM table. If you cannot login as root@localhost, then restart ...


16

Given: sp_rename uses object names instead of object IDs, we cannot use the object name since it starts with a # and that is interpreted as having special meaning and is handled differently, all other options have been exhausted You should try editing the underlying system catalog table directly via a Dedicated Admin Console (DAC) connection: Get the ...


13

I answer my own question here for completeness I will select @RolandoMySQLDBA as the preferred answer because it gave me the most hints even though it didn't actually solve my problem. Below are the results of my investigation Conclusion MySQL on Windows just creates lots of temporary tables and tuning MySQL by modifying the content of the configuration ...


13

Local temporary objects are separated by Session. If you have two queries running concurrently, then they are clearly two completely separate sessions and you have nothing to worry about. The Login doesn't matter. And if you are using Connection Pooling that also won't matter. Local temporary objects (Tables most often, but also Stored Procedures) are safe ...


13

SQL Server generally builds a complete set of execution plans for the statements in a stored procedure before the procedure begins executing. That approach is slightly modified when temporary tables are present: Compilation of each statement that references a temporary table is deferred until the statement is actually executed. SQL Server has supported ...


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

The bad plan has a warning on the temp table that the join column has no stats. What gives? There may be a more esoteric reason for this, but it is more likely a simple statistics creation failure. This might, for example, occur when the task fails to get the memory resources it needs, or when statistics creation is being throttled (too many concurrent ...


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

This applies to local temp tables. The difference between named and unnamed constraints is this: CREATE TABLE #t1 (c1 INT PRIMARY KEY CLUSTERED) CREATE TABLE #t2 (c1 INT, CONSTRAINT pk_c1 PRIMARY KEY CLUSTERED(c1) ) Letting the system name constraints makes it extremely unlikely that there will be a collision. In this example, if ...


11

In modern versions of SQL Server (2014+) you can create the indexes when you create the table, e.g.: create table #t(id int primary key, a int, index ix_a nonclustered(a)) Also you can create the temp table before the snapshot transaction starts. Almost all DDL is prohibited within a SNAPSHOT transaction. ALTER TABLE and TRUNCATE TABLE are obviously not ...


11

Minimal logging is not being used when using INSERT INTO and global temp tables Inserting one million rows in a global temp table by using INSERT INTO INSERT INTO ##t1 (r) SELECT top(1000000) s1.r FROM dbo.s1 CROSS APPLY dbo.s1 S2; When running SELECT * FROM fn_dblog(NULL, NULL) while the above query is executing, ~1M rows are returned. One ...


10

Disco Inferno Since this is an older question, I decided to revisit the issue on newer versions of SQL Server to see if the same performance profile still exists, or if the characteristics have changed at all. Specifically, the addition of in-memory system tables for SQL Server 2019 seems a worthwhile occasion to re-test. I'm using a slightly different ...


10

I think of it as you can't have any duplicate names in tempdb.sys.key_constraints. Here's what's in that metadata view on one of my servers: All of the odd names that end with _6E... were names generated automatically by SQL Server. They are not named constraints because I did not explicitly give them a name when creating them. SQL Server generates a ...


10

Batch statements are only ever executed serially in the order they appear in the batch. Now, if you have two statements sent to the server by two different batches, they will run independently and essentially simultaneously (locking and latching aside). Take for example the following code: CREATE TABLE #t ( i int ); INSERT INTO #t (i) VALUES (0); ...


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

From an answer by Remus Rusanu: Dynamic SQL runs in the same session as the calling code. The issue is not session, but scope. Your dynamic SQL creates the temporary tables inside the sp_executesql call and thus the created #temp table is only visible within that sp_executesql call, as documented in MSDN: You can validate that you are using the ...


9

Are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them? You need a real stored procedure (CREATE PROCEDURE) to benefit from temporary table caching. This includes temporary stored procedures (#procname). Point #3 on this blog post ...


9

There is no guarantee of ORDER without ORDER BY. The execution plan for both has "Ordered = False". This means you may get the results in key order but equally may not. Specifically see When can allocation order scans be used? The only time such a scan will be used is when there’s no possibility of the data changing (e.g. when the TABLOCK hint is ...


9

The explanation would appear to be that the global temporary table is in fact in TempDB, not the database your code is running against. This is because the MIN_ACTIVE_ROWVERSION() function is scoped to the database level. The min_active_rowversion() function seems to work when you use the following code USE [db] CREATE table ##mytable ( Id int, rv ...


8

SQL Server has an upper bound on creating efficient query plans given a moderately complex query involving a lot of joins - there isn't a single upper bound or magic formula to determine when a query is complex enough to cause a problem; it is very case-by-case and involves baselining from some known expectation (people sometimes think a certain query ...


8

You need to perform the update in dynamic SQL too (or just create the table with all columns in the first place). The error is happening because the update with the new column is being parsed before the dynamic SQL has run. As an aside, you are probably only getting this error if you try to execute the stored procedure and choose 'Display estimated ...


8

The main factors in play here are: The optimizer does not try to find the best plan; its goal is to find a reasonable plan quickly It assumes the query will be run with a cold cache The cost model used favours sequential I/O over random I/O Repeated seeks into an index are assumed to be randomly distributed The cardinality estimate for a table variable is ...


8

This is somewhat subjective but I'm not at all a fan of SELECT ... INTO anyway and normally replace it with an explicit CREATE TABLE and INSERT ... SELECT as the datatypes, column names, and nullability can then be seen much more explicitly (and both can be minimally logged). In this case if you were to create the temp table with an unnamed primary key ...


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


8

Roughly speaking, cardinality estimates for statistics creation on #temp tables follows that for regular, non-temp tables. Having said that, as Paul White points out here, temp tables inside stored procedures can suffer from cached statistics values. From the page above, the salient takeaway is: Temporary tables in stored procedures have a number of ...


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