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!
Is an Enterprise Feature and will be used in conjunction with higher CPU usage.
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!)