SQL Server – Plan the CPU and Memory prior the SQL Server Deployment…

Hey Geeks,

do you have the right information about the planning of CPU and Memory capacities before SQL Server installs on your server. Well I have some keynotes:

CPU Planning:

Now days, SQL Server requires plenty of raw CPU horsepower. So the new trend which adds a big advantage to the CPU performance is that today’s servers use Multi-core CPUs. With the multi-core CPUs becoming commonplace in servers, the question might be arising is, how does this affect licensing?

The good news is that Microsoft is licensing SQL Server by the CPU socket, not by the number of CPU cores. This signifies that a dual CPU server running quad-core CPU’s will function almost as if the server had eight CPUs, but we have to pay only for the two CPU’s worth of SQL Server licensing. That’s great 🙂

While planning your Server note some points:

  • You should test your application to determine the number of transactions a core can provide.
  • A well-planed server will have CPUs running at 30%-50% utilization from the Performance monitor.
  • I would recommend you to use 64-bit CPUs for their large memory addressing. If the CPU will get much higher transactions, i.e. >10k per second, then it’s better to choose Itanium 64-bit CPUs because they have better throughput than x64 CPUs.

Memory Planning:

Memory is a big reason for the performance of SQL Server. So, the greater amount of memory will reduce the I/O requirement and also reduce the CPU requirement.

   

While planning your Server Memory, note some of the key points:

  • Buy as much memory as you can afford 🙂
  • SQL Server consumes memory for cached query execution plans and cached data pages, so the amount of memory needed does not depends upon the size of the database but on the number of queries.
  • I can give you the formula based upon which you can estimate  the baseline for required memory:
    • 2 GB for the (OS + SQL Server)
    • 1 GB per 1000 queries per second (Although it depends on the complexity of the query)
  • If the memory would exceed 4GB, then I would recommend you to use 64-bit versions of the Operating System and SQL Server because the memory addressing is very much smoother than 32-bit AWE solution.

Hope you like this…and the Installation Guide for the SQL Server 2008 R2 is coming soon…

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.