Quick Look at SQL Server Configuration for Performance Indications

Performance tuning is one of the most interesting concepts in SQL Server. Every expert looks at performance tuning with a different perspective and unique viewpoint. And most of the time, all of their viewpoints are correct in their own way. Moreover, the fact is that none of the answers or solutions provided by them can be applied by default. There are various viewpoints and each view point has its own merits and pre-conditions. Everything cannot be implemented at the same time.

In an earlier article (Beginning Performance Tuning with SQL Server), we have looked at the scenario regarding how I started performance tuning. I prefer to have an overall look at the system before I start modifying few of the data.

I often encounter two types of clients.

1) Those who want instant results
This type of client wants me to start talking about server’s performance as soon as I touch their server. At times, they are so impatient that they cannot even wait for even few minutes. I have also faced a client who provided me with the results of my configuration queries and then started asking me the following immediately: “Do you think you can tune our server now? What is the next action item?”

2) Those who want the right results
This type of client has prior knowledge about performance tuning or has worked with other consultants in past. They do understand the lifecycle of the system. They also understand that they have spent years building a system and that system cannot be understood by an external consultant within a very short period of time. Every system has to be understood from the business as well as the technical points of view. In my career, many times I have seen that performance gets affected because the business needs are not properly integrated with the system. There are always cases when something was right previously but as the business grows and as needs change, the whole approach also requires a change. This type of customer understands the complete picture and also supports me to understand their business as whole, which in turn helps me to suggest the best tuning practices.

Now, from the abovementioned classification, I hope that it is evident that handling a customer who understands performance tuning is quite easy. They, in fact, become a part of the process, expediting the performance tuning process very smoothly. However, in this area, we all are aware that the client has the final say every time. I don’t have a problem if the client asks for an immediate explanation on performance tuning. I can totally understand their eagerness. It is also a matter of comfort as they are skeptic at times that how can an outsider understand and tune the performance of something, which has taken long time for them to build, in few minutes.

When I face an “impatient” client, I immediately start checking the configuration details, and after a quick look, I start providing them the required information. Here are some easy tips which can help a performance consultant to provide the necessary information regarding the server within a short time.

You can run one of the following queries to obtain the configuration details:

SELECT *FROM sys.configurationsGO

Or

EXEC sp_configure

Both the queries will give you almost same type of information. I prefer querying sys.configurations as it has few additional columns and additional information. I briefly examine the current settings of the above configuration options and give them a generic opinion. I cannot give a precise opinion without diving deep, but there are few aspects which one can consider as significant without going further into performance tuning.

Here is the list of settings which I always make sure to glance before giving my opinion.

1) Fill Factor (%) - The fill-factor value determines the percentage of space on each leaf-level page to be filled with data.

2) Priority Boost – The priority boost value decides if SQL Server processes are run on high priority on the operating system or on normal priority.

3) Min Server Memory (MB) – The minimum server memory server configuration option can be used to specify the minimum amount of memory the SQL Server can allocate when it starts.

4) Max Server Memory (MB) ‑ The maximum server memory server configuration option can be used to specify the maximum amount of memory the SQL Server can allocate when it starts and while it runs.

5) AWE enabled –The address windowing extensions assist a 32-bit application to use more than 3 GB of memory.

6) Optimize for Ad Hoc workloads - Optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single-use ad hoc batches.

7) Cost Threshold for Parallelism – The cost threshold for parallelism option is used to specify the threshold at which SQL Server creates and runs parallel plans for queries.

8) Max Degree of Parallelism – The max degree of parallelism option is used by SQL Server to execute queries in parallelism. SQL Server uses the number of CPUs to run a parallel query based on the value of this option.

There are many other settings which can help you to talk about performance after a quick glance. In a nutshell, we have talked here about the options that I pay attention to, but it is necessary to understand how each of these options plays an important role in the first glance after understanding the workload and business needs.

In the next blog post, we will try to understand all these settings and their significance. Some aspects of these settings are quite good, and at times, the default values are your best friends.