A Fine Slice Of SQL Server
Erik Darling requires the vaudeville-style hook:
I solve a lot of problems with #temp tables, indeed I do. And I hate people who are reflexively anti-#temp table.
If you’re into jokes (I’m definitely not into jokes; SQL is serious business), you could even call them #foolish.
Get it?
Click through to see what might cause multiple temp table references in the plan cache.
Comments closedDavid Fowler has a warning about stored procedures which use temp tables created by other processes:
Here’s an interesting issue that recently came up. We were seeing very high compilations and recompilations on a server to the point that it started causing us some very serious issues (admittedly this wasn’t the sole issue but it was certainly a contributing factor, the other factors were also very interesting so I might look at those in another post).
After looking in the plan cache we could see a very high number of single use plans for a particular stored procedure. Now as you probably know, SQL will usually cache an execution plan and use it over and over whenever a particular query runs. SQL’s lazy and it doesn’t want to bother compiling queries unless it really has to.
So what was going on, why wasn’t SQL able to reuse the cached plan?
Read on for the solution, as well as the impact of the problem and ways to work around it.
Comments closedJared Poche explains some of the value of table variables:
In having a talk reviewed recently, it was suggested I spend more time defining some of the subject I touched on. It occurred if I should go over (or at least introduce) these ideas during a talk for a SQL Saturday audience, some might find a post on the subject useful. Hence my recent post on key lookups.
Another such topic is table variables. I use table variables frequently at my current job, but they came up very infrequently when I worked at CSS in Microsoft. I remember the conversations about them being very simple at the time, as in, “you should just use temp tables instead.” But there is a lot of utility with table variables, and they could be a useful arrow in your quiver.
Click through for the full story.
Comments closedDeepthi Goguri summarizes a detailed session from Bob Ward:
While I was preparing for my Tempdb presentation, I learned many interesting facts about Tempdb. Thanks so much Bob Ward (t|g) for providing me with the resources to prepare for my presentation. Bob Ward has presented an amazing 3 hour session about Tempdb for the PASS Summit couple of years ago. This information is invaluable.
Read on for 15 notes of interest.
Comments closedChad Callihan invokes Betteridge’s Law of Headlines:
Generally speaking, it’s best to put things away that aren’t being used. Don’t keep indexes that aren’t getting utilized because they are taking up disk space and still have to be kept up to date with changes. A table is still loaded up with old data that’s not being used but needs kept? Maybe it’s time for options like an archive database or partitioning.
While it’s not on the same level of importance, one related argument I’ve seen and been in is how to handle temp tables in stored procedures. Do you drop them at the end of a stored procedure or do you leave them to be cleaned up by SQL Server? Is one way better for performance than the other? Let’s do some testing and see what we find out.
Read on for the answer.
Comments closedJohn Morehouse takes us through a catch in creating temp tables inside nested stored procedures:
SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables. One of the capabilities is to allow for stored procedures to call other stored procedures. This is called “nesting” one stored procedure within the context of the calling stored procedure. In addition, you can instantiate a temporary table from within the parent procedure that can be utilized within the context of the child procedure.
But wait! I’m not done yet!
You can also instantiate a temporary table with the same name as the parent temporary table within the child procedure. But there is a catch!
I’ve done the former (and more frequently, had to support when somebody else did the former), but I don’t think I’ve done the latter. Mostly because it seems like it’s an invitation for problems.
Comments closedMonica Rathbun shares part two in a series:
As you learned in part one of this Mastering TempDB series, TempDB is a global resource used for many operations within SQL Server. You create and allocate temporary user objects such as temporary tables and table variables, rebuild indexes with
SORT_IN_TEMPDB=ON, use it for version stores (RCSI), internal objects (worktables, spools, group by, order by) and evenDBCCCHECKDBjust to name a few. All these operations require space to be allocated in the TempDB database. At times, these operations can result in TempDB growing rapidly, which, in turn, can fill up the file system and cause failures. In this article, you will learn how to fix an overgrown TempDB file that has resulted in it running out of space.
It beats my answer, which is to rip the server out of the rack and chuck it in the ocean.
Comments closedWilliam Assaf shows how you can use sp_help to learn about temp tables:
For example, I was trying to convert a query from using a #temp table to a CTE instead, and wanted to see the column list and resulting data types of the #temp table.
Sp_help is a helpful SQL Server system sproc to return schema of objects. It’s that magic that happens when you press Alt+F1 in SSMS. (Side note: showing someone the Alt+F1 shortcut in SSMS for the first time and seeing their life change for the better is really rewarding.)
But Alt+F1 doesn’t work on #temp tables[…]
Read on for an alternative which does work.
Comments closedErik Darling puts his pants on one leg at a time and once his pants are on, he makes gold records:
One persistent idea is that tempdb is something to be avoided. Either because it was “slow” or to avoid contention.
Granted, if a query has been around long enough, these may have been valid concerns at some point. In general though, temp tables (the # kind, not the @ kind) can be quite useful when query tuning.
Erik is absolutely right in this post. Ceteris paribus I’d rather not directly use tempdb because I’d prefer one query over multiple queries. But once performance comes into question, working on smaller subsets of data one step at a time will typically give you at least an acceptable solution.
Comments closedErik Darling continues receiving big paydays from Big Temp Table:
When you have queries that need to process a lot of data, and probably do some aggregations over that lot-of-data, batch mode is usually the thing you want.
Originally introduced to accompany column store indexes, it works by allowing CPUs to apply instructions to up to 900 rows at a time.
It’s a great thing to have in your corner when you’re tuning queries that do a lot of work, especially if you find yourself dealing with pesky parallel exchanges.
Read on to see how you can create a temp table which triggers batch mode processing fairly easily.
Comments closed