December 29, 2014 at 4:49 am
The only 2 advantages table variables have over temporary tables (in my opinion) are:
1. Do not cause statement recompilation (or procedure recompilation in SQL Server 2000) as frequently, reducing compile locks
2. Do not cause as much latch contention in tempdb, there are work arounds (trace flags and multiple tempdb files) but it's still a pain.
The rule I follow is if a query is liable to be invoked very frequently (multiple times per second) then try and use a table variable. If the table (temporary or variable) needs to hold more than about 10 rows then try and use a temporary table.
The problem is obviously where a query runs 100's of times a second and needs to store 100's of rows, a balancing act needs to be performed. CTE's can offer an alternative coding strategy but they frequently suffering from bad plans when they become overly complex.
In situations like this, try all alternatives in search of the optimal solution, and typically, what might work best in the development environment may not perform so well in live. You need a representative stress testing environment, same statistics, data cardinality, workload etc!
December 29, 2014 at 4:53 am
Paul Brewer (12/29/2014)
2. Do not cause any latch contention in tempdb, there are work arounds (trace flags and multiple tempdb files) but it's still a pain.
Are you sure about that one? Table variables are allocated pages in TempDB, so there's no reason why they should avoid the latch contention caused by frequent allocations of pages.
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
December 29, 2014 at 4:57 am
I'm not 100% sure to be honest, no. From experience the latch contention associated with table variables 'seems' a lot less profound but I've never investigated or tested to be honest. The compile locks I know for certain based on very painful experience!
December 29, 2014 at 5:02 am
Paul Brewer (12/29/2014)
I'm not 100% sure to be honest, no. From experience the latch contention associated with table variables 'seems' a lot less profound but I've never investigated or tested to be honest.
I'm now sitting (at work) trying to figure out how to test this. Hm....
Compile, yes. Since they don't have stats they can't trigger a stats-based recompile, so you do see fewer recompiles.
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
December 29, 2014 at 5:18 am
This might be a good way of comparing 'create table' differences between table variables/temporary tables:
http://www.sqlskills.com/blogs/paul/determine-causes-particular-wait-type/
It describes a way of defining an extending events session to capture the internal call stack using debug symbols. The call stack is a bit cryptic but differences during the 2 different 'create table' operations somewhere might be revealing.
December 29, 2014 at 5:24 am
Paul Brewer (12/29/2014)
This might be a good way of comparing 'create table' differences between table variables/temporary tables:http://www.sqlskills.com/blogs/paul/determine-causes-particular-wait-type/
It describes a way of defining an extending events session to capture the internal call stack using debug symbols.
The call stack is a bit cryptic but differences during the 2 different 'create table' operations might be revealing.
Cryptic is an understatement 🙂 I'll poke at that when I have some time. Already checked and there are no 'latch acquired' events, pity as that would be the easy way. There's a latch_suspend, for when something is waiting on a latch, maybe I'll try that one first. Both the latch_suspend and Paul's wait trick require the process to be waiting on a latch.
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
December 29, 2014 at 6:26 am
I'd like to see reasons added to the checklist items. Why should you do (or not do) something?
December 29, 2014 at 7:00 am
Hi all, hopefully you all had a wonderful christmas time and big presents 🙂
Concerning the "checklist" there are to much "it depends" on it as I would prefer to use it.
Basically there can be no final check list because every system is working different!
Beside the given concerns I found the following checks I wouldn't agree to in general:
Set "Cost Threshold for Parallelism" = OLTP = 45 and Reporting = 25.
Why should I do this? An OLTP is defined by heavy DML-Operation, isn't it?
So DML is ALWAYS single threaded and the threshold of 5 seems to be o.k.
Why increasing the threshold in a system with >= 24 cores?
If I would find heavily CPPACKET waits I would first start investigating my queries.
If a query would consume all cores I would limit the MAXDOP but not the threshold!
Any ideas are wellcome 🙂
Set "Max Degree of Parallelism" leave it on after you have changed the cost threshold.
On what? If I leave it to 0 it could cause a process taken all my cores. As decribed before I would limit the MAXDOP depending on the number of cores I have and careful examiniation of the workloads!
Set TEMPDB data and logs onto separate disks
If you mean the log file of TEMPDB than i wouldn't recommend it as a "default" because TEMPDB is minimally logged. I didn't see high WRITELOG waits when I ran heavy workloads on tempdb. But - as always - it depends!
Use multiple files wtih equal sizes, not equal to the number of processors
Sorry - this is nonsence as general advice. There will be database designs which cannot participate from the benefits (e.g. contigious numbering clustered key generates a hot spot ALWAYS on the last page). Mulitple files will make sence for a ETL-process where data need to be loaded into HEAPS or random clustered indexes!
Use index compression
Is an Enterprise Feature and will be used in conjunction with higher CPU usage.
http://msdn.microsoft.com/en-us/library/cc645993.aspx
Number of pages matter,defrag below 300-500 pages
Depends on the size of the index itself. 300 - 500 for a clustered index is - just my point of view - neglectable while in a NCI with only 10 bytes length per record this amount of pages can be seen as quite high!
When I run index maintenance jobs I take the density of the pages into consideration. This can be investigated with sys.dm_db_index_physical_stats in DETAILED mode!
Cannot defrag below 8 pages
What if you have TF 1118 activated 🙂
Use multiple file groups even on a single disk
Why? If you want to use partial restore you need to have Enterprise Edition, too!
Turn off AUTO_GROW (depends).
Never ever! I would monitor the available disk size but would never cause an application to stop although we have 100's of GB free on the disc. Doesn't make sense to me 🙂
Avoid recompiling execution plans
As always - it depends. How will you handle parameter sniffing. Maybe I'm wrong but if you deal with AD HOC queries with usage of literals it will always create new plans if you don't have "Optimize for Ad Hoc Workloads" activated.
Avoid using sp_* stored procedure name
As always - it depends. I think it is a misconception from pre 2005 when we didn't had schemas for work. I wouldn't agree to that "historical statement" due to performance reasons but only for "continuity" of development. I've written a blog article about it here: http://db-berater.blogspot.de/2014/02/eigene-systemprozeduren-im-kontext-der.html
(Unfortunately it is in german language but for experienced SQL Professionals the coding will explain it quite well 🙂 )
The difference between variables and temp tables has been mentioned by others. So no comment to this 🙂
I wish you all a happy new year and a successful 2015...
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
December 29, 2014 at 7:10 am
Uwe Ricken (12/29/2014)
So DML is ALWAYS single threaded and the threshold of 5 seems to be o.k.
SELECT, INSERT, UPDATE, DELETE are definitely not always single threaded. They can all run in parallel when the optimiser decides that the plan is expensive enough (compared with the cost threshold)
While I may not agree with the fixed values, the cost threshold almost certainly needs to be increased, especially on an OLTP system. 5 is incredibly low. The threshold is increased to reduce the chance that cheap queries, which don't benefit from parallelism, get parallel plans
Use multiple files wtih equal sizes, not equal to the number of processors
Sorry - this is nonsence as general advice. There will be database designs which cannot participate from the benefits (e.g. contigious numbering clustered key generates a hot spot ALWAYS on the last page). Mulitple files will make sence for a ETL-process where data need to be loaded into HEAPS or random clustered indexes!
It's a recommendation for TempDB and only TempDB. It is not, and never has, been for a user database. For TempDB it's good advice.
Cannot defrag below 8 pages
What if you have TF 1118 activated 🙂
Then you definitely cannot defrag below 8 pages as all 8 pages will be in the same extent from the start.
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
December 29, 2014 at 7:41 am
Hi Gail,
SELECT, INSERT, UPDATE, DELETE are definitely not always single threaded. They can all run in parallel when the optimiser decides that the plan is expensive enough (compared with the cost threshold)
uups - I didn't see DML in parallel in any workload 🙂 Adam - or you - has to do a demontration for it 🙂
While I may not agree with the fixed values, the cost threshold almost certainly needs to be increased, especially on an OLTP system. 5 is incredibly low. The threshold is increased to reduce the chance that cheap queries, which don't benefit from parallelism, get parallel plans
Agreed - but in a real OLTP system the queries should be quite fast because the data input is in the foreground. As in most cases ... - it depends :-).
It's a recommendation for TempDB and only TempDB. It is not, and never has, been for a user database. For TempDB it's good advice.
In this case YES. I didn't understand it as a recommendation for TEMPDB but in general. My fault!
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
December 29, 2014 at 7:49 am
Uwe Ricken (12/29/2014)
SELECT, INSERT, UPDATE, DELETE are definitely not always single threaded. They can all run in parallel when the optimiser decides that the plan is expensive enough (compared with the cost threshold)
uups - I didn't see DML in parallel in any workload 🙂 Adam - or you - has to do a demontration for it 🙂
You've never seen a SELECT run in parallel?
DML = Data Manipulation Language. A collective term for SELECT, INSERT, UPDATE, DELETE and MERGE
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
December 29, 2014 at 7:51 am
There are important items missing from the checklist, 'Instant File Initialization' for example. Also, no mention is made of the consequences of changing certain settings, changes to MAXDOP for example will flush the procedure cache.
It might have been nice if the article had included:
1. A PowerShell script to automate the initial SQL install, one that checked and enforced some of the rules specified in the article such as 'data / logs on different drives'.
2. Include a SQL script to configure initial settings (tempdb log files, maxdop, min/max memory etc) based on the hardware available to the instance at install time.
3. Included queries or Central Management Server policies that check for compliance with the standards defined in the article.
4. Reference links to URL's for Microsoft Risk Appraisal (RAP), sp_Blitz, T-SQL Code smells and other SQL Server resources that identify some of the points in the article..
December 29, 2014 at 7:53 am
Hello everyone,
I see this article has received much discussion. I believe I should have mentioned that this is only a guideline and that, as usual.. it depends. This checklist has worked for me and many DBAs that I know. Please use your own judgment as each environment and SQL Server installation is different.
Thanks,
Rudy
Rudy
December 29, 2014 at 8:20 am
GilaMonster (12/29/2014)
Uwe Ricken (12/29/2014)
SELECT, INSERT, UPDATE, DELETE are definitely not always single threaded. They can all run in parallel when the optimiser decides that the plan is expensive enough (compared with the cost threshold)
uups - I didn't see DML in parallel in any workload 🙂 Adam - or you - has to do a demontration for it 🙂
You've never seen a SELECT run in parallel?
DML = Data Manipulation Language. A collective term for SELECT, INSERT, UPDATE, DELETE and MERGE
🙂
I've never seen INSERT, UPDATE, DELETE in parallel.
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
December 29, 2014 at 8:36 am
My 2 cents.
There are things that make sense, but once again, there are a lot of "it depends" cases here.
Anyway, two things that I would like to add.
NUMA - It is more related to the CPU architecture, and the memory access it is going to perfom (local or foreing, which means being faster or slower access method respectively). I would say that saying it is the number of physical processors, only, is not correct. But you're right at a certain point, usually the number of NUMA nodes dictate the MAXDOP configuration, which, once again, depends on other aspects.
Temp Tables.
Temp tables have a low Recompilation Threshold (RT) compared to permanent tables and excessive recompilations due to temp table cardinality changes is not uncommon and can cause a performance impact. On the other hand table variables do not have a RT, which means that a recompile cannot occur due to cardinality changes. So, once again, saying which one is better depends on the case.
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply