Hyperthreading against SQL Server

Well, The correct answer to all interview questions is valid for this topic as well.

IT DEPENDS :)

If we assume a box with 8 Core CPUs with 8MB of L3 Cache (as can be seen in the picture below) each core gets 1MB of cache. When Hyperthreading is enabled you have 16 cores (logical processors) and a core has only 0.5 MB of cache left. 

Well It definately cuts Cache per Core but You have to test it with your workload.
It all depends on how your system behaves with HT enabled or not. :)


I found this article useful, about L3 Cache
http://www.extremetech.com/computing/55662-top-tip-difference-between-l2-and-l3-cache
 and thanks http://www.brentozar.com/ for the original explanation.




Cheers!!

Contention in TempDB

I wanted to give out some thoughts about issues related to SQL server TempDB, but it appeared that most of the useful information are found in the below KB article.

https://support.microsoft.com/en-us/kb/2154845 

However, I have explained some of them which could also have been explained in the above KB article.

• One of the main reasons for contention issues in TempDB is that TempDB is shared across all databases in the instance.

• PageIOLatch (Thread waiting to be read from the disk) and PageLatch (Multiple threads competing access for a in-memory page) wait types pointing to TempDB usually notifies us about this problem.
• PageIOLatch wait types can be avoided by having multiple datafiles as per the recommendations in the KB article, But remember if you see memory spills to tempdb which causes performance issues and if you are having large number of datafiles YOU MIGHT BE HAVING TOO MANY Datafiles!!
• PageLatch waits which occured mostly in SQL Server 2000 are now tweaked from SQL Server 2005 & onwards. However, if you think you are still suffering from them you can go with the trace flag T1118 which is explained in the kb article below. It turns off the use of mixed extents in TEMPDB ONLY.

https://support.microsoft.com/en-us/kb/328551


Cheers!!