Its always important to look at the License type of your SQL Server

Its always important to look at the License type of your SQL Server!!!

Problem Statement:

We recently had an issue with CPU utilization reaching more than 95% always for database server in one of our performance test environment. Load test environments are resource intensive test, hence it is expected to have high CPU utilization. However, we could observe the number of tests processed and number of transactions are very less spiking the CPU utilization to 95%.

Let me explain a bit more on my environment, We have 4 sockets with 10 physical cores and HT enabled in our test environment. As per the configuration we have total 80 logical CPU available. SQL Server version information as below:


Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
               Jun 17 2016 19:14:09 
               Copyright (c) Microsoft Corporation
               Enterprise Edition ((missing))Core based Licensing>((missing)) (64-bit) on Windows NT 6.3  (Build 9600: )

Here we can observe that the version information is missing “Core based Licensing”, that means, the SQL version is not Core based, but CAL based. Let us look at the excerpt from the MSDN article:

“Enterprise Edition with Server + Client Access License (CAL) based licensing (not available for new agreements) is limited to a maximum of 20 cores per SQL Server instance. There are no limits under the Core-based Server Licensing model”

So, though we have 4 sockets with 10 Cores, ONLY 20 cores are VISIBLE for SQL Server. In our environment, this was 20*2(HT enabled) = 40 Logical CPUs are visible.

Ref: https://technet.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

Lets confirm the above with other parameters DMV -sys.dm_os_schedulers.


Select parent_node_id,Count(cpu_id) Total_Schedulers,
 count(Case when Status = 'VISIBLE ONLINE' Then 1 Else null End) Visible_Count,
 count(Case when Status = 'VISIBLE OFFLINE' Then 1 Else null End) NotVisible_Count
From sys.dm_os_schedulers 
where status in ('VISIBLE ONLINE','VISIBLE OFFLINE') 
and parent_node_id not in (64)--DAC
Group by parent_node_id

The result looks like below:


This is clear that SQL server was not able to utilize more than 40 logical CPU in the above environment.

Once we upgraded the license to Core Based License, we were able to use all available CPUs in our environment and observed the database CPU utilization has come down to 65% resulting more number of tests and transactions.

Let me reiterate, Its always important to look at the License type of your SQL Server !!!

Advertisements

Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one Of its dependencies. The system cannot find the file specified.

Problem Statement:

When trying to migrate SQL Server 2008 R2 to SQL Server 2016, we identified an issue with the following error:

Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one
Of its dependencies. The system cannot find the file specified.

As a knee jerk reaction, we initially thought it was an issue with SQL Server 2016 installation and need to install the Feature Pack for SQL Server 2016. Once applied the feature Pack, we again tested and found the same issue unfortunately(but really fortunate one!!!).

Analyzing further(I mean, reading the error message clearly, It is found the version 10.0.0 that represents Microsoft® SQL Server® 2008.
As we do not have SQL Server 2008 installed in the environment and the code(.NET application) has some reference to the version mentioned, this was not able to find the dlls in the specified path(C:\windows\assemblies\).

To proceed with our testing, we installed SQL Server 2008 R2 Feature pack and resumed our testing. Finally, it worked like a treat!!!

The learning is we always need to go to the facts(error message) than blindly presume things!!!

please find the below information for various version feature pack references:

Microsoft® SQL Server® 2016 Feature Pack (Version: 13.0.1601.0)
https://www.microsoft.com/en-us/download/details.aspx?id=52676
Microsoft® SQL Server® 2014 SP2 Feature Pack (Version: 12.0.5000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=53164
Microsoft® SQL Server® 2014 Feature Pack (Version: 12.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=42295
Microsoft® SQL Server® 2012 SP1 Feature Pack (Version: 11.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=35580
Microsoft® SQL Server® 2012 Feature Pack (Version: 11.0.2100.60)
https://www.microsoft.com/en-us/download/details.aspx?id=29065
Microsoft® SQL Server® 2008 R2 SP2 Feature Pack (Version: 10.50.4000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=30440
Microsoft® SQL Server® 2008 R2 SP1 Feature Pack (Version: 10.50.2500.0)
http://www.microsoft.com/en-us/download/details.aspx?id=26728
Microsoft® SQL Server® 2008 R2 Feature Pack (Version: 10.50.1600.1)
https://www.microsoft.com/en-us/download/details.aspx?id=16978
Microsoft SQL Server 2008 Service Pack 4 Feature Pack (Version: 10.0.6000.29)
https://www.microsoft.com/en-in/download/details.aspx?id=44277
Microsoft SQL Server 2008 Service Pack 3 Feature Pack (Version: 10.00.5500.00)
https://www.microsoft.com/en-us/download/details.aspx?id=27596
Microsoft SQL Server 2008 Service Pack 2 Feature Pack (Version: 10.00.4000.00)
https://www.microsoft.com/en-us/download/details.aspx?id=6375
SQL Server 2008 Service Pack 1 (Version: 10.00.2531.00)
https://www.microsoft.com/en-in/download/details.aspx?id=20302