March 18, 2008 at 11:50 am
Jeff, just so you (and everyone else) knows: :exclamation: UDF usage was an even GREATER cause of horrible performance than table variables at all 3 of those clients I mentioned!! :exclamation:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 18, 2008 at 3:04 pm
Heh... No doubt... I can see the RBAR on steroids now... some of my favorite misuses are SPLIT/CONCATENATION functions in batch code, UDF's that format dates so they can be joined, Hierarchical UDF's, and UDF's that return a running total or running count for a single row using either a cursor or triangular join. Yeah... gotta love things like that.
--Jeff Moden
March 18, 2008 at 3:37 pm
The most common issue I have seen believe it or not is simply referencing a table UDF in a FROM clause. Anything more than a simple select therein (and WOW have I seen some doooOOOOZZZYYY UDFs!!) seems to always lead to 1 as estimated row count out of UDF and thus the same ol' nested loop, kajillion logical read query plans. MS gave developers a 6 shooter with 5.83 bullets in it and let them play Russian Roulette IMNSHO!!
Hey, more business for me!!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2008 at 12:37 am
Jeff Moden (3/18/2008)
I'm sorry that Microsoft ever added them to the mix. I'd much rather they had allowed the use of Temp Tables in UDF's especially since you can make a TempTable using SELECT/INTO and you can't with a Table Variable. But, that's just an opinion... 😉
I feel the same way sometimes. Especially when I hear misinformed people touting the 'advantages' of table vars (not persisted to disk, faster than temp tables etc). Usually none of their 'advantages' are even remotly true.
It's going to get worse in SQL 2008, since it allows table variables to be passed as stored procedure parameters. I can see a couple cases where that would be good, and such an opportunity for misuse it's not even funny.
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
March 19, 2008 at 10:04 am
OK, I hear poor old table variables sobbing in a corner somewhere, so I have to step in here... :w00t:
Table variables do cause less overhead in terms of locking, logging and recompilation than temp tables. (Less recompilation overhead is the upside of absent stats.)
Also:
Table variables are not part of an outer transaction, so they are not subject to outer transaction rollbacks.
Table variables differ in scope: table variables are only visible in the local batch (I think someone already mentioned this earlier), whereas temp tables are visible in the current and inner levels. This makes them the ideal choice in cases where an (attempted) change needs to be audited (say in a trigger), even if the transaction was subsequently rolled back.
I have to admit though that I'm surprised the issue arose with small data sets. My faith in table variables has been shaken somewhat, and I will be less forceful in singing their praises in the future... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 10:32 am
If you have small datasets AND any joins you do to the table variable do not result in large numbers of joined rows, then you are perfectly OK. You will get nested loops and they will be most efficient. But when you have 50 rows in the table var and you join to a table and come out with 3M rows and join that to other tables, you (rather your server) are dead meat.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2008 at 10:35 am
TheSQLGuru (3/19/2008)
If you have small datasets AND any joins you do to the table variable do not result in large numbers of joined rows, then you are perfectly OK. You will get nested loops and they will be most efficient. But when you have 50 rows in the table var and you join to a table and come out with 3M rows and join that to other tables, you (rather your server) are dead meat.
That's an excellent point, never thought about it that way. Thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 10:38 am
I should mention, while we are on the subject, "INSERT EXEC" does work with table variables in SQL 2005!
This is something I only realized recently.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 11:00 am
Marios Philippopoulos (3/19/2008)
OK, I hear poor old table variables sobbing in a corner somewhere, so I have to step in here...I should mention, while we are on the subject, "INSERT EXEC" does work with table variables in SQL 2005!
This is something I only realized recently.
Heh... I'd have never known... 😉 I only use temp tables (unless it's a UDF where I can't) and haven't run into a Recompile problem yet because I don't mix the DDL with the DML... I do all the DDL at the very beginning like BOL suggests. Most of the time, GUI related code doesn't need anything temporary anyway. A recompile on a batch job is expected just because the data is more than 5 minutes old.
So far as "blocking" goes... whatcha gonna block with a TempTable? Most "blocking" is left over wives tales from when that type of blocking occurred way back in 6.5. It's all been fixed since 7.0
Whatcha gonna rollback if you use a TempTable? Purpose of TempTable code like that is to get all of the data together before you even think of starting a transaction that might rollback. I never have had the need to include TempTable population code in an explicit transaction.
But, I do understand your points. I just haven't run into any of those problems in the last 11.5 years. First 1/2 year was a bugger because of the locks 6.5 put on TempDB but, like I said, those problems were all fixed in 7.
--Jeff Moden
March 19, 2008 at 11:15 am
Jeff Moden (3/19/2008)
Heh... I'd have never known... 😉 I only use temp tables (unless it's a UDF where I can't) and haven't run into a Recompile problem yet because I don't mix the DDL with the DML... I do all the DDL at the very beginning like BOL suggests. Most of the time, GUI related code doesn't need anything temporary anyway. A recompile on a batch job is expected just because the data is more than 5 minutes old.
So far as "blocking" goes... whatcha gonna block with a TempTable? Most "blocking" is left over wives tales from when that type of blocking occurred way back in 6.5. It's all been fixed since 7.0
Whatcha gonna rollback if you use a TempTable? Purpose of TempTable code like that is to get all of the data together before you even think of starting a transaction that might rollback. I never have had the need to include TempTable population code in an explicit transaction.
But, I do understand your points. I just haven't run into any of those problems in the last 11.5 years. First 1/2 year was a bugger because of the locks 6.5 put on TempDB but, like I said, those problems were all fixed in 7.
If you say so!
At the time that 6.5 came out, if someone had mentioned SQL Server to me I would have thought it was a drink!.. 😛
Obviously you guys are talking from experience, I'll take your word for it.
As for explicit transactions, are you saying there has been no case where you had an explicit transaction opened in which you are making a nested sproc call, and in the nested sproc you are creating a temp table??? I find that hard to believe.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 11:33 am
I've got an MS URL that explains the blocking in 6.5 somewhere. Lemme see if I can dig it up... I've always gotta prove these things so people don't think I'm off my rocker...
--Jeff Moden
March 19, 2008 at 11:45 am
I have no problem using table variables if the rowcount is low (100 rows is around my threshold) or I'm just going to do a straight select * from @tbl with no joins or where clause.
The only case I've had with blocking on temp tables was back in SQL 2000 when I had extensive blocking on page 2:1:3 (that's one of the allocation pages in TempDB) because too many temp tables were getting created at a time and there was only a single tempDB file. The app that uses this particular server was temp table heavy. Since table variables are entered in the tempDB system tables and are allocated space in TempDB, they too would have cause this kind of blocking.
The problem is, too many people aren't aware of the downsides of table variables, possibly are misinformed as to how they work with TempDB and overuse them.
As for explicit transactions, are you saying there has been no case where you had an explicit transaction opened in which you are making a nested sproc call, and in the nested sproc you are creating a temp table???
Rule of transactions: As short as possible doing just what is required. I try not to call nested procs within a transaction at all.
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
March 19, 2008 at 11:48 am
Rule of transactions: As short as possible doing just what is required. I try not to call nested procs within a transaction at all.
In our code base we do that a lot. Perhaps it's time to revisit...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 19, 2008 at 12:45 pm
Shorter transactions means less blocking and less chance of deadlocks. In turn means less work to do. Always a good thing. 😀
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
March 19, 2008 at 12:48 pm
Jeff Moden (3/19/2008)But, I do understand your points. I just haven't run into any of those problems in the last 11.5 years. First 1/2 year was a bugger because of the locks 6.5 put on TempDB but, like I said, those problems were all fixed in 7.
Well, not ALL of them. There is still to this day one VERY important tempdb locking issue if you have a high volume of temp object creation/drops. The SGAM (and one other page that escapes me) locking per tempdb file. Can bring a hard-hit OLTP system to it's knees because tempdb can't actually create the objects you are asking for due to locks on allocation page(s). Thus the new Best Practice of one tempdb data file per physical CPU core.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply