(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) You install your brand-new SQL Server using the installation wizard, and at the very end you hit the Finish button.Woohoo – now we can go into production with our server!
Hmm, with an address bus of 48 bits we can physically only address up to 256 TB of memory, and now SQL Server can consume up to 2 Petabytes? The Maximum Server Memory setting is just the largest possible 32 bit integer value – 2147483647. So SQL Server can consume more memory that can be addressed physically? SQL Server can eat up your whole physical memory by default!
In today’s blog posting I want to show you the 3 configuration options that you have to change immediately after the installation for faster performance. By now you should have a 64 bit SQL Server in front of you.
64 bit means that you can theoretically address a main memory size of 2^64 = that is 16 Exabytes (1 billion gibabytes)!
And as I have already covered in a previous SQL Server Quickie, CXPACKET waits are not an indication that you have problems with parallelism in your system!
When an execution plan goes parallel in SQL Server, the Max Degree of Parallelism defines how many worker threads each parallel operator in the execution plan can use.