Saturday, 3 December 2016
     
AUSQLUG Forums Home   Search   Login   Register   Member List  
SQL Server 2000 Forums  > SQL Server - General  > Should table variables be changed to work IN-MEMORY exclusively?  
 
Display using:  
Previous Thread :: Next Thread 
 Author Thread: Should table variables be changed to work IN-MEMORY exclusively?
Greg_Linwood is not online. Last active: 20/07/2012 11:25:21 AM Greg_Linwood
www.sqlservants.com.au
Top 25 Poster
Forum Moderator
Joined: 07 Jun 2004
Total Posts: 123
 
Should table variables be changed to work IN-MEMORY exclusively?
Posted: 21 Feb 2006 12:19 PM
Hi folks

I'm currently discussing whether table variables should be changed to work as "in-memory" objects exclusively in a future release of SQL Server with some SQL MVP colleagues & SQL team members. Rather than simply pushing my own bandwagon on this issue, I'd thought gather the your thoughts to see where the "wider interests" rest, so to speak.

Currently, table variables work very much like regular tables in that insert, update & delete activity is logged through the tempdb's tlog. Select activity is also streamed through SQL Server's Buffer Manager similar to regular tables. This allows table variables to contain an un-restricted number of rows, where if they were "in-memory" objects, they'd be limited by the amount of memory actually available.

In my experience with discussing this issue with developers, I've found that many devs are surprised to learn that table variables are not in-memory objects, becaus the name "variable" suggests to them that they are either by reference or by value types. Hence, devs tend to develop against table variables in a manner similar to arrays (by applying business rules to small datasets) but are often disappointed with table variable performance (as updates occur on disk).

If MS were to turn table variables into "in-memory" objects, they would operate similar to "pinned" tables, in that all page buffers would be "pinned" in memory, which could cause detrimental effects on overall server performance, because other query activity in the server would be pushed through a smaller buffer cache area, leading to increased disk activity & slower overall responsiveness.

I'm also arguing that, with SQL 2005, we are now able to use CLR arrays to perform business logic on in-memory datasets, so the need to transform table variables into in-memory objects is not really required because we now have an alternative.

Does anybody else have any opinions on this topic?

Do you feel that table variables "should" be in-memory, or continue to work as they do today, streaming via a combination of disk & memory?

If you do feel they should be changed to work in-memory, what benefits do you see this having over using CLR objects such as arrays?

Regards,
Greg Linwood
SQL Server MVP
robf is not online. Last active: 10/06/2010 12:59:05 AM robf
msmvps.com
Top 25 Poster
Joined: 23 Feb 2005
Total Posts: 57
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 21 Feb 2006 12:47 PM
Greg,

I think people prefer table variables over temporary tables when they want the performance of a variable rather than writing to disk. They also like the fact that the table variable will fall out of scope at some point, etc...

So I think perhaps they need a way of specifying whether a table variable should be in-memory or not at creation. The default should be the way it is of course, but just introduce another option.

Of course the benefits of having them in-memory as opposed to using CLR objects is that you don't need to use CLR! I know that people can easily demonstrate how much quicker things run in CLR, but surely that's largely because of this exact thing you're describing? I'm all for using CLR when appropriate, but I think much of the hype would be removed if table variables worked much faster.

Rob
Not a SQL Server MVP
philcart is not online. Last active: 7/01/2013 4:04:01 PM philcart
philcart.blogspot.com
Top 25 Poster
Forum Moderator
Joined: 20 Jul 2004
Total Posts: 345
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 21 Feb 2006 12:56 PM
Maybe there should be a parameter on the create to specify IN-MEMORY or TempDB usage.

We had a third-party web app that called a table function to list the details of all people in a given company. In this case the application was just setup for one company, but it can be setup for multiple companies.

Maybe this is just bad application design, but having this work in-memory only would have caused memory issues on our servers, so in this instance using TempDB is good.


Thanks
Phill

Colt 45 - the original point and click interface ;)
http://philcart.blogspot.com/
robf is not online. Last active: 10/06/2010 12:59:05 AM robf
msmvps.com
Top 25 Poster
Joined: 23 Feb 2005
Total Posts: 57
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 21 Feb 2006 12:58 PM
Yup. Hence, the default should definitely by 'the way it is now'. But an option (or maybe even a new type, INMEMORY_TABLE) should be introduced.
jhuppatz is not online. Last active: 21/02/2006 1:49:26 PM jhuppatz
ozziemedes.blogspot.com
Top 50 Poster
Joined: 04 Aug 2004
Total Posts: 5
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 21 Feb 2006 01:49 PM
Hmm... my $0.02 worth is that there should be database-level configuration option called something like 'tablevar_option' as follows:
A) 'tablevars_in_memory': Default behaviour for table valued variables. (Not all of us are .NET CLR SMEs)
B) 'tablevars_in_tempdb': Create all table valued variables in tempdb.
C) 'tablevars_by_threshold': Create table valued variables in memory by default, but flush them to tempdb when they exceed a threshold number of data pages.

For option C, you'd need a default value (say 1 Allocation Unit's worth - 512KB) for the tempdb threshold and an additional database option called something like 'tablevar_threshold' in which a higher/lower threshold can be set.

One further refinement would be the addition of a server-wide configuration option that sets the default behaviour for all databases in which the option is not explicitly set. You might also want to be able to set this as a session variable (a la transaction isolation levels, ANSI NULL handling, etc...).

Like I said - my opinion only, but this approach gives the best range of managability and flexibility.


Jeremy Huppatz
EDS Australia
Adelaide Solutions Centre
jeremy.huppatz@eds.com
dgosbell is not online. Last active: 28/04/2011 4:03:52 AM dgosbell
geekswithblogs.net
Top 25 Poster
Joined: 12 Oct 2004
Total Posts: 12
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 21 Feb 2006 03:14 PM
I like Jeremy's idea.

Initially I used to favour table variables over temp tables in certain circumstances because their name lead me to believe that they were only a memory structure. And when I was under this incorrect assumption I used to target my usage so that any large temporary structures I put in #temp tables and smaller ones I put in @temp tables.

I think the main problem here is one of perception. If MS had called them "temp table pointers" or "temp table mapped variables" then I don't think we this would be so much of an issue. Table variables do the job they were designed for - they just don't give the same performance that you would expect from a "variable".

I think it would be good to have memory based table variables, but as with any new feature (like CLR integreation) there is the potential for misuse. Could they even be tuned so that there was a global threshold so that table variables could not consume more than x Mb or x % of RAM?

Darren
Greg_Linwood is not online. Last active: 20/07/2012 11:25:21 AM Greg_Linwood
www.sqlservants.com.au
Top 25 Poster
Forum Moderator
Joined: 07 Jun 2004
Total Posts: 123
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 21 Feb 2006 11:05 PM
Hi Rob

Not having to use the CLR is a good point - this sure will go down well with those who aren't totally comfortable with that kind of thing (DBAs that don't program in VB / C# for example)

Cheers,
Greg
Greg_Linwood is not online. Last active: 20/07/2012 11:25:21 AM Greg_Linwood
www.sqlservants.com.au
Top 25 Poster
Forum Moderator
Joined: 07 Jun 2004
Total Posts: 123
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 21 Feb 2006 11:16 PM
Excellent ideas Jeremy & Darren

Having thresholds & "knobs" to control how much memory table variables can chew up is a good idea. I think MS tend to like avoiding such knobs though, so it'll be interesting to hear some opinions back from them.

One thing arrays will always have over table variables though is that they hold multi-dimensional structures elegantly, which table variables aren't appropriate for (due to their two dimensional'ness).

I often see stored procs that use multiple table variables / temp tables, which are really acting in concert to contain complex data structures, which might be better represented in arrays or collections of classes than multiple two dimensional objects.

I'm personally hoping that an array type does get up here, especially if it has muli-dimensional & ragged capabilities..

Should be interesting to see how this pans out..

Cheers,
Greg
dgosbell is not online. Last active: 28/04/2011 4:03:52 AM dgosbell
geekswithblogs.net
Top 25 Poster
Joined: 12 Oct 2004
Total Posts: 12
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 08 Mar 2006 10:34 AM
The following is an excerpt from the sql-server-performance.com newsletter which I recieved today. Which from the discussion on this thread is just plain wrong, but explains some of how the incorrect perceptions have been propogated.

<-- SNIP -->

If you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them much faster in some cases. But not in all cases. Because of this, you will need to test both options to determine which works best for you under your particular circumstances.

In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000]

<-- SNIP -->

Darren
JSBBS is not online. Last active: 23/08/2010 4:54:20 AM JSBBS
www.jsbbs.com.au
Top 50 Poster
Joined: 04 Mar 2005
Total Posts: 6
 
Re: Should table variables be changed to work IN-MEMORY exclusively?
Posted: 05 Apr 2006 02:52 PM
Further to Darren's snippet, table variables aren't subject to transactions (yes, it is true).

That leads me to another 'nice to have'.... autonomous transactions (an Oracle concept). This is great for automated sql jobs that need to be transaction aware but rows inserted into a logging (or instrumentation etc.) table are never affected by the success or failure of the transaction.

John
Previous Thread :: Next Thread 
Page 1 of 1
 
AUSQLUG Forums  > SQL Server 2000 Forums  > SQL Server - General  > Should table variables be changed to work IN-MEMORY exclusively?