High CPU usage in SQLServer

Question

Is there any post installation steps that I need to follow after installing SQL server 2016 so that the CPU usage is not high for SQLserver processes>

Answer 1

Good day,

I am upgrading to SQL2016... I am in the process of installing sqlserver 2016...

(1) Have you already installed the new version or have you already updated to the new version?

In first glance, I understand that you ask before doing so, which is good. I think this is not clear, since Erland for example ask about executing query in the new version. Please clarify is this is a question before the new version is installed.

(2) I HIGHLY recommend NOT to upgrade version but to have a clean installation of the new version.

If this is a dev machine and you want to test the newer version then you can install the new version in addition to the old version. You can have multiple SQL Server instances on the same machine. This will allow you to compare the behavior of different versions (obviously this mean that each instance requires resources).

If this is production then it is tens times more important to have a clean installation probably. In this case you can build a new environment -> design a replication till you confirm that all is working well in the new machine -> test while keeping the servers sync -> and only when all is well after tested remove the old machine if needed.

(3) What to do after the installation (usually)?

3.1. As Erland mentioned, apply latest Update

3.2. In most cases you should probably see improvement in performance when you move from 2012 to 2016 (there are some improvement in the server engine), but not always. You should test your queries!

In some cases you might need to adjust the queries to get better execution plan and in rare cases you might need to stay in the old compatibility level as well (especially for the sake of the your existing application and existing queries). To gain the full power of the new version you will probably need to use the new compatibility level, and you might need to make some changes in the client side.

I restarted the server and the memory usage percentage reduce.

Obviously. When the server starts, it uses the minimum memory but from this time forward when you execute query the server start to fill the buffer pool. For example if you select data from a table then the server load the data from the disk to the memory.

The server continue to use more and more memory according to the configuration of the "max server memory" of the instance. The default value is 2,147,483,647 megabytes which is probably more than you machine have - this mean that the server might keep using more and more memory until there is none left on the system.

Get more information here: Server memory configuration options

Is there any post installation steps that I need to follow after installing SQL server 2016 so that the CPU usage is not high for SQLserver processes

I am not sure what you are looking for.

In general, you should monitor the CPU and adjust your query for best performance.

Do you want to limit the server for CPU?!?

In most cases THIS IS NOT RECOMMENDED, especially if you use a dedicate server!

There are options like

1. Setting the server to use specific NUMA nodes and number of cores
2. Setting Up a Login and User for Low-Priority Operations
3. Configuring Resource Governor to Limit CPU Usage using the parameter MAX_CPU_PERCENT

More common is to configure the max degree of parallelism (MAXDOP) in the query level if needed, but again... by default you should let the server choose what it find best.

In most cases, if you have issue then we should start with checking the query which lead the issue.

We need more information, which you will probably have only after the installation and test, in order to provide specific advice.

Answer 2

You did apply Service Pack2 and the latest Cumulative Update, did you? Can you post the output from "SELECT @@version".

Usually, high CPU usage is to queries running on the system. Whether this needs action, depends. If these queries are evil DWH queries, and you already have a columnstore in place, it may be difficult to optimize. But it could also be that you could easily address it with adding an index.

In short, you need to tells us more. Did you upgrade from an earlier version of SQL Server? Or is it a fresh install, which is supposed to be idle? Or?

Answer 3

Hi @Avyayah ,

>I am planning to start from clean install of sqlserver 2016.

After you have a clean install of SQL server 2016. Please apply SP2 for SQL 2016, and apply CU15 for SQL 2016 SP2. You can get them from here.

After that, migrate SQL 2012 databases, logins, jobs to SQL 2016 instance or this is a test environment, you will create new.

You can try the suggestion from below blog to migrate SQL server instance and check the note in this blog.

A Faster Way to Migrate SQL Server Instances!

For migrate logins and jobs, we can try the script from this similar thread. How to move all SQL Server jobs and logins from one server to another?

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 4

Ronen wrote an elaborate answer, which I was not inclined to given the scant amount of information. I still know too little, but I like to supplement Ronen's advice with a few more points and also modify them a little bit:

1. Intially, stay at compatibility level 110 (SQL 2012).
2. Enable Query Store on all databases.
3. Run like this for a week or two.
4. Now switch to compat level 130.

The point is that if you see performance degradation with some queries, you can pick up the old plans from Query Store, and also force these plans as a short-term solution. (In the long run, you should identify why they were slow. Maybe they need tweaking. Maybe the need indexes.)

There is also a tool in SSMS to help you with this. Right-click a database and select Tasks. At the bottom you find Database Upgrade, which will walk you through the steps above.