Team, today I want to continue from from previous article. I will talk about the best practices of Virtual Servers and how we can set or utilize our CPUs.
I would like us to look at our windows systems and SQL Servers configurations before proceeding with this article. Try and think it through why certain systems are configured that way. So I will take CPU and Memory first but let us define Virtualization
What is Virtualization?
Let’s go back to the era of Main Frames and Multi-Users and Networking. In the 1990s, virtualization was used primarily to re-create end-user environments on a single piece of mainframe hardware.
This kind of architecture was forgotten after the era of x36 machines until VMWare which is one of the leading market of virtualization revived the market and almost 86 percent IT industries today have and are still implementing the said technology.
In computing, virtualization means to create a virtual version of a device or resource, such as a server, storage device, network or even an operating system where the framework divides the resource into one or more execution environments. Example, if I have a Hard Disk that is drive C: of 100GB and I decided to divide the drive into two or three maybe Drive C: 30GB, Drive D:60GB and drive E: 10GB, then we refer to this as Virtualization. Revival and adjustment of this technology of virtualization has resulted to having technologies like VDI, VLANs etc.
We have different types of virtualizations example, the partitioning of a Server into smaller servers is referred to as Server Virtualization (hardware Virtualization is our focus), multiple network storage devices into what appears to be a single storage unit is Storage Virtualization, using network resources through a logical segmentation of a single physical network is Network Virtualization (like VLAN) and the operating system virtualizations is the use of software to allow a piece of hardware to run multiple operating system images at the same time. With virtualization, several operating systems can be run in parallel on a single central processing unit (CPU).
In Server virtualization, a virtual machine that acts like a real computer with an operating system is created. Software executed on these virtual machines is separated from the underlying hardware resources. For example, a computer that is running Microsoft Windows may host a virtual machine that looks like a computer with different operating system;
In Server virtualization, the host machine is the actual machine on which the virtualization takes place, and the guest machine is the virtual machine inside the host. We can have as many Guests inside the host (physical machine) as possible depending on the resources (CPU, memory, HDD etc) available and if the machine allows virtualization. In server virtualization each virtual machine runs its own operating system known as “guest Operating system”. Typically this guest operating system is running onto another operating system called “Host Operating System” with the help of virtualization software. Each virtual machine (VM) running in this manner is totally unaware of any other software is also running on the same fabric. The software that creates a virtual machine on the host hardware is called a hypervisor or Virtual Machine Manager.
I just have to stop!! And then talk about SQL Server Administration. Although its good to deviate to explain the basics and history as most of us can’t even define what a computer is, yet we are Database Administrators, Application Developers or IT Professional…. What kind of IT Pro are you?? I had to explain all these so that when I begin to explain performance tuning in SQL Server as it’s relate to infrastructure, you will know what to look at. We have always looked at the scripts but that is not enough. This is why Developers finds it difficult to become DBAs.
So let us talk about Virtualization in SQL Server.
CPU
As Database Administrators and Developers, when we see CPU growing or a spike to a certain level we worry why this is happening and begin to point fingers! Yes its our job in ensuring that CPU can even be zero percent if possible. The truth is, looking at it from the management perspective, they will not commit any financial resources if the CPU is not fully utilized (closed to 100% use). I guess some of the CIOs, CEOs and other IT experts know what I am talking about lol.
When we used to work on physical servers life was much easier and since the advent of virtual servers, life became complex for SQL Server administrators because more things are to be considered before configuring SQL Servers on Virtual Servers. Today SQL Server on physical Server can accept a higher number of transactions per second (TPS) on a high OLTP workload than a Virtual Machine SQL Server. Simple query like below can help you determine the TPS on any machine. You can try it out but I am talking from experience as Microsoft Certified Data and BI expert. You also use a third party software
select * FROM sys.dm_os_performance_counters WHERE OBJECT_NAME = ‘SQLServer:Databases’ AND counter_name = ‘Transactions/sec’ AND instance_name = ‘db_name’ —database name
This is an example:
SELECT cntr_value, * FROM sys.dm_os_performance_counters WHERE counter_name = ‘transactions/sec’ AND OBJECT_NAME = ‘SQLServer:Databases’ AND instance_name = ‘AdventureWorks’
Now, when we are to set up Windows Server, it is not advisable to allow the WINTEL or windows team carry out the configuration or setup alone. You need to give them your requirements for that Virtual Server. Allowing them to do the setup may not actually meet your requirements and you may have serious issues at the end. If it was a physical server, that is understandable and your primary focus then will just be CPU speed and the number of Cores. But we are talking about Virtual Servers.
Before, you give the Server Team your configuration, you may need to snip around and know the following points or ask the following questions:
What is the current configurations of the physical host resources (Memory, CPUs, hypervisor, storage etc) ?
How many Guests are going to be on the Host? A host of 32GB memory can house 30 Guests of 4GB memory resulting to 120GB memory utilization… Surprise? What came to your mind is how can a 32GB Physical host house internally (Guests) consuming 120 GB as against 30GB? Yes, this is where Hypervisor come to play.
What is the make of the server and the Hypervisor (how updatable)?
How many LANs will be attached to the Guest?
These are some of the basic questions applying to the physical Host of your Guest (Virtual Machine). You can add if you want to lol. Understanding some of those questions from the Infrastructure Team will help you to determine your configuration requirements for your Virtual Server. Example, what happened if the WINTEL Team decides to have more Guests than it’s required for the physical machine? You don’t want to have issues with the Guests you may not be able to determine. So some of the questions about the Guest VM are as follows:
What will be the speed of the Virtual Cores on the Guest? The power saving CPUs like INTEL will automatically slow down when some Cores are not been used. If we are not pushing the CPU hard it will go down and return some speed back to the host.
Do we get the same amount of usage of each of these Cores is another question. Some system administrators (WINTEL) always want to limit the amount of power we get from every Cores been used even if we have them available or unused!! U see why I said you have to understand your infrastructure before you can be a good Database Administrator? Especially now that every organization is going Virtualization and cloud! Don’t be left out, get out of your legacy systems and update your skills!
How many Virtual Cores do we really need?
Do we need 4 Cores or 8 Cores, if we have 8 Cores does not mean we really need the entire 8 Cores because it might result to idleness of Cores or may result to performance issues. SQL Server schedule work on the 8 virtual cores and if we are unable go get the 8 Virtual Cores to work at the sometime, what happens? If we have 8 Cores then our codes may need to use the 8 cores concurrently.
As I said before now, having 8 Cores on a Virtual Box does not mean you will be using the entirely the 8 Cores, it will also depends on availability and the instructions and the Hypervisors.
That brings me to the last question, how is our buffer Caches working with these Cores, do we use the same Cores for running instructions remembering how we have cache our stored procedures and other instructions.
Let me explain this into details because its very vital in Virtualization.
Lets assume we have 6 Cores on a typical Physical Server. Then we decided to configure this server to have two Guests or Virtual Servers. One of the Guest Servers is SQL Server and the other to be an Application Server (App Server)
From the diagram above. SQL Server has some set of Stored Procedures to run and data to be retrieved and worked upon. So SQL Server will use the first available 3 Cores (SET A). It may have retrieved some data and instructions and those are cached automatically in the L1 and L2 Cores respectively, it continues working and continue storing data on the Cores memory until the process complete and make the Cores available as Sleeping or idle. So the Cores (SET A) are now Idle.
Ok, now let’s imagine another program or application like EasyMoney is been executed? It will go straight to the sleeping first 3 Cores as instructed by the hypervisor. The sleeping cores are still SET A because to the application all 6 cores are available, so it will decide to choose the first 3 Cores.
Now, as the application begin to execute, it may now decide to make a call to SQL Server which is where the Application Database is stored. Now SQL Server will be looking for Core to use because of the way the hypervisor is running. Remember, SQL Server has data already cached on the first 3 Cores but the Application Server is using the Cores currently. What will SQL Server do? Use the 2nd set of Cores (SET B)? If it does, it will loose the data is has cached on SET A (first 3 cores) and would have to copy them across or cache over again! Do we now see where performance issues will come from?
SQL Server may also decide to wait for the application to complete using the first three cores before it will now schedule its own job! It may be little wait though! That was why I said, it’s not how many Cores you may have (if not fully utilized) will determine the processing power or task completion. Do not get me wrong, the other part of the story may be better be scheduling the job on the last 3 cores and copy data across. Its soo complete to provide a solution to this! Sometimes, we have L3 Cache as well which may make all Cores sharing the same CPU Cache. Which means, it may not matter if you schedule SQL Server to run on SET A or SET B but there is always a but! Then we should have to know how we schedule CPUs for our Virtual Machines. This gives us the opportunity to discuss some facts on CPU best practices and settings
Don’t use too much Virtual Cores. From the example above you see that its not how many virtual cores but the ability to get all of them to work the same time.
Remove all unnecessary items attached to your virtual drives because by default when a virtual Guest is created it automatically at all unnecessary accesses not required like CD Drive. Do you noticed even your Laptop with CD drive sometimes work slower than the one without CD Drives.
Ensure the Hypervisors are updated on a regular basis because the newer it is the better it helps in managing the Guests.
Background processes are challenges to Servers and ensure they are disabled because it will impact performance.
SQL Server is NUMA and instance aware. Please try and install single SQL Server on each Guest than having more than one sql server on a Guest. It will impact the CPU.
Well, the next part, will be on Storage and Memory. I will call that PART 111. I would have promised you next week but I am going to be on call so may not have the time for the write up…. Have fun guys and a happy.