There are variety of performance issues that can cause SQL Server performance issues. Performances can range from low server Hardware configuration, improper OS or SQL Server settings, fragmentation, blocking, no proper maintenance like index rebuilds or stats updates, etc. It is not easy to identify or pin point what is causing the slowness or performance problems with the SQL Server. As a DBAs we will need to review everything related to the hardware, OS and SQL Server to able to find the root cause of the issue.
There are many cases where performance issues are caused due to low server hardware configuration like lack of CPU power, or insufficient memory available or under-performing disk IO. We can look at various parameters like perfmon, SQL Server dmv statistics, query waits, etc to be able to understand if there is real bottleneck with the hardware and if we need to upgrade the hardware for better performance. However there can be situations, where the server although has a good hardware configuration interms of CPU, Memory or Disks, improper settings on some limitations cause the SQL Server not to use all of the available hardware on the server.
One of such issue related to the slowness caused by CPU usage due to limitations on the SQL Server edition been used. On one of our client server, there were multiple SQL Server instances installed. There was a monitoring tool which used to monitor the server and used to raise intermittent alerts for high CPU usage. Upon connecting to the server and verifying, the CPU usage was only reported as 50% usage on the server. Users also used to report about the performance issues some times, but not frequently.
Upon checking the server, from task manager the CPU usage was reported around 50% and this server has 8 sockets with one core each, The server has Windows Server 2012 as the Operating System and the task manager, by default combines all the processors available on the server and shows the average usage of all the processors. There is also an option to see the detailed view of CPU usage of each individual processor usage, checking which we found that only four processors are pegged to 100%, where as rest 4 CPUs were having negligible usage. So, it appeared that the SQL Server instances were using only 4 CPUs thus the high CPU usage with the four CPUs only.
Upon checking further, we noticed that all the SQL Server instances installed on the server were Standard Edition. Checking the documentation showed that SQL Server standard edition can use maximum of 4 sockets or 16 cores, which ever is less. Similar limitations on other SQL Server 2008 R2 or SQL Server 2005 versions of 4 sockets only.
We can also check and confirm this on SQL Server 2012 instances by checking the SQL errorlog which will have a message like “SQL Server detected 8 sockets with 1 cores per socket and 1 logical processors per socket, 8 total logical processors; using 4 logical processors based on SQL Server licensing”.
For SQL Server 2008 R2 or SQL Server 2005, we can query sys.dm_os_schedulers to see how many processors are been used by the SQL Server instances.
The server where we had issues had multiple SQL Server instances, so we had to use processor affinity mask settings and map each instance to use first 4 or last 4 processors allowed us to use all the available processors on the server, thus reducing the CPU contention.
Hope this was helpful.
This is applicable for below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings