November 15, 2012 at 9:43 am
I think we have all been in this situation before. Devs fill TEMPDB then say they need more TEMPDB.
Here is my setup: I have a 4 node DW each with about a 1-1.5TB database living on them. My largest table is about 850GB in each. (1,440,695,540 rows, 353,312,408KB data, 510,599,720KB index (lol yea)).
My tempdb is setup like this:
8 5GB data files (so 40GB total tempdb data file space)
1 10GB log file
As a dba here is what I have always maintained: if your query takes more than 10GB of tempdb to run, the query and/or design is flawed. in this case I have given them 40GB data and 10GB log and yet they still manage to fill that from time to time with queries that run for hours. yes, the fill can be a stats issue. In either situation, imo, the solution is NOT to add more tempdb, but to fix the underlying problem w/ the query/stats.
So really discuss this and help chime in with your feedback on whether you think my logic is correct in that my file sizes are adequate and you should fix the root cause, or I should give in and give more tempdb space for what I viewed as flawed logic.
November 15, 2012 at 10:01 am
There are some things that need lots of TempDB space. Let's take that 850MB table of yours, say a query needs 10% of it and needs to sort the results. That requires ~35GB of sort space. That's either in memory or it's in TempDB, I somehow doubt you have 35GB of memory available for a single query to use (that's above and beyond the buffer pool, plan cache and all other caches), so the sort spills to TempDB
CheckDB uses lots of TempDB. Index rebuilds use lots if you're doing sort in TempDB. Snapshot and read committed snapshot isolation levels use TempDB. Triggers use TempDB. Temp tables, table variables, hash joins, spools, sorts all use TempDB.
Before you conclude it's flawed logic and idiot devs, take a look at what queries are using the most TempDB space and investigate why they're using so much.
Personally, I think you're a little low on TempDB. I remember a 1 TB database with 120GB of TempDB space in total, and it used that space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2012 at 12:41 pm
All valid and appreciated points.
One quick thing to add on, in 100% of circumstances a stats update solves the issue. We do have regular maint on stats too. We just have a mix of large and small customers in our warehouse so sometimes a scan may occur due to the "tipping point" of data being returned from the table.
November 15, 2012 at 12:45 pm
In that case you have queries that are very sensitive to stats inaccuracies and probably need to identify the tables involved and schedule more frequent stats updates on those tables/indexes/stats.
Edit: Or you have queries/procedures susceptible to bad parameter sniffing problems and probably want to identify and fix them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2012 at 3:17 pm
You would love our environment. All these problem queres are "Advanced Search" queries which call a proc which generates and executes dynamic sql. Most of which have tons and tons of joins.
November 15, 2012 at 4:08 pm
Nothing wrong with that. Better than trying to do a catch-all type query that can handle any number of optional parameters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2012 at 6:59 pm
you kinda sound like my DBEs 🙂
but no seriously this advanced search thing has an endless number of parameters and generates 100+ line queries that are just ugly. it's like "lets do a 30 table view" situation
November 20, 2012 at 1:13 am
Brian-444611 (11/15/2012)
but no seriously this advanced search thing has an endless number of parameters and generates 100+ line queries that are just ugly. it's like "lets do a 30 table view" situation
i think you also need to think to move tempdb files (i assume you have multiple) on different seperate disks.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 20, 2012 at 5:17 pm
Bhuvnesh (11/20/2012)
Brian-444611 (11/15/2012)
but no seriously this advanced search thing has an endless number of parameters and generates 100+ line queries that are just ugly. it's like "lets do a 30 table view" situationi think you also need to think to move tempdb files (i assume you have multiple) on different seperate disks.
My tempdb is on solid state in my cx4 array attached to my host w/ 8GB HBAs. Not gonna get much faster than that 🙂
November 21, 2012 at 11:25 am
40gb of tempdb isn't a lot (even if it's on SSD). For even a relatively small DW (say, a couple of TB in size), I generally aim for around 10% as tempdb (more if the DW and ETL are sharing the same system -- a couple of large online index rebuilds will blow out tempdb in no time flat).
While I appreciate the sentiments of the DBA saying "oh, the developers don't need that much tempdb", it's actually about getting the data out to the customers in the most expeditious fashion. Sometimes that means just getting on with it and not having to constantly worry about the "best approach"; there's an old saying that "best is the enemy of good enough."
The tempdb is one of those things where you can throw extra space at it to enable getting stuff done easily. Think of it as helping to enable your developers, if you like (and in most cases, developer time is the most precious resource of all).
You still need to keep an eye on what's going on and ensure that there is a process to get the inefficient stuff refactored (otherwise you will need infinite resources and processing power). From what you have described, you already have candidates in mind for that.
My suggestion would be to negotiate with the developers: you give them additional tempdb if they put in place a refactoring process. That way they can still do quick turn-around on data and query delivery, while you can ensure that long term your system doesn't bog down.
November 21, 2012 at 12:21 pm
It sounds like you have to be very careful of how you rebuild those large tables - note that index rebuilds can take TempDB space in a range of 0% to 232% of the size of the index, online vs. offline, clustered vs. nonclustered, drop and create vs rebuild, and SORT_IN_TEMPDB options.
http://msdn.microsoft.com/en-us/library/cc966402.aspx.
In general, for a data warehouse, I'd be expecting large historical queries looking at weeks, months, or years of data - larger datasets need larger tempdb space, and only 40GB is something I'd consider small for even a 500GB database used for large historical queries... or for any database where any single index is more than 40GB/1.07 or so.
I agree with the above comments regarding parameter sniffing and stats sensitive queries, though I'll note that unless you can rewrite the queries/application to completely prevent, it's unpredictable when you'll have it - maybe some busy day your stats updates can't keep up, or some customer hits several parameter sniffing cases based on their requirements. You'd still need to have a larger tempdb for them to be able to operate at all.
I have to take issue with your 10GB tempdb limit. Why 10GB? Why not 8GB or 12GB? Is it 10GB on a 5GB database as well as a 1500GB database? Is it 10GB for OLTP as well as small historical queries as well as large historical queries? There's too much "it depends" in some of these situations for a set rule of thumb like that to sit well with me.
More importantly, why so stingy with your tempdb? For an OLTP database doing offline index work, that may be perfectly fine, but I would consider a tempdb data size 2.6% of the total data and 11.8% of the largest table's clustered index of it to be very small for data warehouse work.
P.S. Local SSD's in a RAID config (either SAS or PCIe) can not only easily top your 8Gb FC channels, but in a throughput limited situation, they can do so while allowing FC to carry their full throughput in other traffic, allowing for a still higher aggregate throughput.
P.P.S. Gail, I don't work with the OP, but I have worked with servers which have more than enough RAM (100GB+) to allocate 35GB to a single query and not bat an eye; they're built primarily for large historical queries, of which we usually have less than two running.
November 21, 2012 at 12:51 pm
Nadrek (11/21/2012)
P.P.S. Gail, I don't work with the OP, but I have worked with servers which have more than enough RAM (100GB+) to allocate 35GB to a single query and not bat an eye; they're built primarily for large historical queries, of which we usually have less than two running.
Not really...
That's 35GB over and above the plan cache and the data cache. So for a server with 100GB of memory to allocate 35GB to a single query requires that there is more than 35GB unused, not allocated to the data cache or plan cache. That's what doesn't happen.
I've worked with servers with 128GB memory or more. A 35 GB sort will still likely spill to TempDB because while there's more than 35GB of memory allocated to SQL, SQL is not keen on throwing 35GB out of the data or plan cache to run a single query.
35GB of workspace memory for a single query. Not 35GB of buffer pool space (my file server in the lounge has almost that)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 21, 2012 at 8:50 pm
Brian-444611 (11/15/2012)
you kinda sound like my DBEs 🙂but no seriously this advanced search thing has an endless number of parameters and generates 100+ line queries that are just ugly. it's like "lets do a 30 table view" situation
I'm with you. Queries shouldn't be so sensitive to stats and they shouldn't be "all-in-one" queries that join 30 tables. The use of multiple smaller queries that hold much smaller result sets (maybe even just one depending on the data) in thoughtfully created Temp Tables can greatly reduce resource requirements and cause performance to skyrocket.
Take a look at the execution plan for one of those queries. If you see huge rows with counts much larger than any of the tables, you probably have a query with some many-to-many joins that could well benefit from "Divide'n'Conquer". A dead giveaway will be if they used DISTINCT (or possibly, GROUP BY) to overcome the problem of duplicated rows in the result set (although you could still have the problem without this particular smell).
Then, rewrite the query by dividing it up and see if it doesn't perform better while using less of Temp DB in the process. If you can pull it off, use it as a teaching tool to get the devs to write code a bit more thoughtfully. Speaking of "thoughfully", don't scream at the devs about it. They're doing the best they can with what they know. Be a mentor about it teach them a better way. If you can't make the code any better, give them an additional 40GB of Temp DB because there's no room to talk unless you can set the example.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply