June 29, 2009 at 5:26 am
Dunno if this is the right thread for the following.
We have a DB-server with SAN. On it, among other DB's a TempDB which grows to almost 400 Gb, which is empty most of the time. I'm finding out which processes use the Tempdb heavily, but I suspect there are a lot of processen involved with virtual tables. I've got all the best practices implemented on this TempDB I can think of, but still the system which uses this TempDB(a Reportingsystem) suffers from bad performance occasionally. Some logfiles on this server also grow to enormous sizes occasionally, tho not as big(far from it) as the TempDB; I am still busy trying to correlate these growths.
I am inclined to believe you can use too much virtual tables, but I wonder if this is correct. My thought is, that a virtual table has some limitations compared to a 'physical' table, and if you use the same virtual table other then incidentally you would be better of creating a 'real' table for it. But am I right in this? I searched the Internet, but could not find any article confirming or denying this idea of mine.
Any ideas?
Greetz,
Hans Brouwer
June 29, 2009 at 6:42 am
If this is a reporting system, it might need a larger tempdb. It's not just virtual tables (by these I assume you mean temp tables and table variables) that use tempdb, but sorts from an ORDER BY clause, work tables created within queries, aggregations that are part of GROUP BY, more. All that activity can act to bloat tempdb. If it's getting too large, you should monitor the queries being run against your databases to identify the offenders. They may be in need of tuning.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 29, 2009 at 7:58 am
Tnx for answering, Grant.
You're right, I should have mentioned the other reasons myself. I have been monitoring this system for a while now and I see a lot of SQL which use a lot of table variables. I wanted to know if my assesment of the differences between virtual tables and 'real' tables was correct.
I have noticed bloated queries, but I check those more with an eye on IO and CPU use, not TempDB use. Some of those queries defenitely can do with restructuring.
Tnx again.
EDIT: typo's
Greetz,
Hans Brouwer
June 29, 2009 at 8:23 am
It is very possible to over-use temp tables and table variables. A lot of developers will load data into a temp table and then go through various joins to other tables to update the temp table data and then return it to the app. Frequently (not always) this can be replaced by a single SELECT statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply