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
- Setting the server to use specific NUMA nodes and number of cores
- Setting Up a Login and User for Low-Priority Operations
- 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.
How can we know? This depend on the specific system.
In order to know what is optimum, we need to fully know your system :-)
Setting max server memory to the same value as minimum value can prevent SQL Server from starting. It is documented. Always use bigger value for the max.
In most cases, in dedicate servers we do not need to configure the memory (min/max), in other cases we need only to configure the max server memory. It is a bit rare to configure the min server memory and is usually needed when the machine is not dedicated for the instance.
If the max and min server memory are the same can I reduce to min to 0 and Max to 2147483647 MB?
You can (will this be the best value for you I am not sure by probably yes)
Sign in to comment