SQL SERVER – Quick Look at SQL Server Configuration for Performance Indications

Earlier I wrote SQL SERVER – Beginning SQL Server: One Step at a Time – SQL Server Magazine. That was the first article on the series of my real world experience of Performance Tuning experience. I have written second part the same series over here. In it I take a quick look at SQL Server configuration for performance.

Read second part over here: Quick Look at SQL Server Configuration for Performance Indications.[Articles are relocated so links are disabled]

In this second part I talk about two types of my clients.

1) Those who want instant results

2) Those who want the right results

It is really fun to work with both the clients. I talk about various configuration options which I look at when I try to give very early opinion about SQL Server Performance.

There are various eight configurations, I give quick look and start talking about performance.

Settings to Review in SQL Server Configuration for Performance

The magazine link is gone, so I cannot point you to the full list of eight settings from that article. The idea behind it has not changed, though. Before I touch a single query on a new server, I spend a few minutes on the instance settings, because one wrong value there can slow down every query at once.

You can see every setting with one query: SELECT name, value, value_in_use, is_dynamic FROM sys.configurations ORDER BY name; If value and value_in_use are different, someone changed the setting but it has not taken effect yet, either because RECONFIGURE was not run or because the setting needs a restart.

Here are the settings I like to look at first:

• max server memory: the default does not cap SQL Server at all, which can leave too little memory for the operating system and other services.
• max degree of parallelism and cost threshold for parallelism: together they decide when a query goes parallel and how many cores it may use.
• optimize for ad hoc workloads: helpful when the plan cache is full of plans that ran only once.
• tempdb and autogrowth: not part of that view, but worth a look on the same day, since tiny growth steps on busy files cause a lot of small pauses.

My advice is simple. Change one setting at a time, write down the old value, and measure before and after. A value that helps one workload can hurt another, so there is no magic number that fits every server.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.