The simplicity of installing SQL Server out of the box is very appealing to the accidental database administrator (DBA). Run the install program, select default options, and you have a running database. The ease of installation is a double edge sword. You now have a database management server (DBMS) that might be using settings that are not optimal for your company.
The purpose of today’s talk is to introduce settings that a DBA can change to make the DBMS perform better.
I will be showing where information can be found and changed in SQL Server Management Studio (SSMS). Not all settings can be viewed or updated via SSMS. Therefore, TSQL code snippets will be presented that can do the same thing.
Since database settings are different from version and edition, you will need to determine what software is installed on the server. The SERVERPROPERTY() function can be used to return the correct information.
1 2 3 4 5 6 7 |
-- Determine server property SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition; GO |
The general page under server properties in SSMS can show the same values.
The sp_configure system stored procedure is used display and change settings. The show advance options needs to be enabled for you to change most of these features. A call to the reconfigure system stored procedure is needed for the option to take on the new value. Some options require a server reboot.
1 2 3 4 |
-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; GO |
Memory is a very precious resource to both the Operating System (OS) and the Database Management System (DBMS). However, the default minimum memory setting, zero megabytes, allows SQL Server to give up all of its memory if the OS is under memory pressure.
On the other hand, the default maximum memory setting, 2147483647 megabytes or 1.99 petabytes, is more than the actual physical memory of most machines. An extremely bad query might cause SQL Server to ask for more memory from the OS. The OS might have to use the page file since all physical memory is currently used. Because disk is a lot slower than RAM, this situation will cause severe performance issues with your server.
The best way prevent these situations from happening is to set the min and max memory settings to suggested values per amount of physical memory. Glen Berry has an excellent article on the suggested memory settings for SQL server. Since I have 4 GB of physical memory,
I am going to use 1 GB for my min setting and 3 GB for my max setting.
1 2 3 4 5 6 7 8 9 |
-- Set min memory to 1 GB EXEC sp_configure 'min server memory (MB)',1000; RECONFIGURE WITH OVERRIDE; GO -- Set max memory to 3 GB EXEC sp_configure 'max server memory (MB)',3000; RECONFIGURE WITH OVERRIDE; GO |
The memory page under server properties in SSMS can be used to get and set the same values.
Each user connection takes up memory on the server. This can vary from 12 KB to 24 KB. Please see the attached link for more details on MSDN.
Long running remote queries use valuable resources and should be re-written if they do not complete in a timely fashion. Both of these values can be set using sp_configure.
I choose to allow only 50 users on my development server at one time and each query must complete within 300 seconds or 5 minutes.
1 2 3 4 5 6 7 8 9 |
-- Only want 50 users connected to server at one time EXEC sp_configure 'user connections', 50; RECONFIGURE WITH OVERRIDE; GO -- The amount of time before a query times out (5 minutes) EXEC sp_configure 'remote query timeout', 300; RECONFIGURE WITH OVERRIDE; GO |
The connections page under server properties in SSMS can be used to get and set the same values.
The index fill factor setting determines how much free space to leave on the index pages. If the data inserted into a table is random, leaving to little free space will cause a large number of page splits. If the data inserted into the table is incremental, the free space left on the index pages will never be used. Knowing you data helps on determining the fill factor correctly.
Backups have been improved in 2008 with the ability to compress the data. The amount of time to keep a backup before a maintenance job removes it can also be set.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Fill index leafs at 80% EXEC sp_configure 'fill factor', 80; RECONFIGURE WITH OVERRIDE; GO -- Keep backups around for 30 days EXEC sp_configure 'media retention', 30; RECONFIGURE WITH OVERRIDE; GO -- Use compressed backups (>= 2008) EXEC sp_configure 'backup compression default', 1; RECONFIGURE WITH OVERRIDE; GO |
The database settings page under server properties in SSMS can be used to get and set the same values.
There are some features that you might want to setup for your developers that are not enabled out of the box. I have already talked in detail about the first two in my blog.
Enabe database mail
Allow command shells
Enabling CLR execution
These features can not be set via the Server Properties dialog box.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Allow use of command shell EXEC sp_configure 'xp_cmdshell', 1 GO -- Must have database mail to get alerts EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE WITH OVERRIDE; GO -- Allow clr assembly execution EXEC sp_configure 'clr enabled', 1; RECONFIGURE WITH OVERRIDE; GO |
Last but not least, there are two settings that are directly related to queries that I would like to cover.
On a system that has many ad hoc queries, the query plan cache can grow and/or rotate plans out due to the high volume of one time queries. The ‘optimize for ad hoc workloads’ setting causes the engine to store the signature, not the whole plan in memory on the first execution. On sequential executions, the whole plan will be stored. This can improve how long plans stay in the cache before being flushed.
1 2 3 4 |
-- Reduces query plan cache for single use stmts EXEC sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE WITH OVERRIDE; GO |
The ‘ad hoc distributed queries’ setting allows the openrowset() and opendatasource() commands to use remote OLE DB sources. As a default, this is disabled.
1 2 3 4 |
-- Allow openrowset and opendatasource to connect to remote ole db EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE WITH OVERRIDE; GO |
Only the ‘optimize for ad hoc workloads’ feature can be get and set via the advance page on the server properties dialog box in SSMS.
In summary, there are many more settings that can be changed using the sp_configure stored procedure. Please read the warnings and details for each one. Choosing the wrong value to set can do the opposite to performance on your server.
Some of the settings that can be changed via server settings in SSMS are stored in the registry such as default database location. I will be exploring how to get and set these settings in a upcoming articles.
Thanks for taking time to write down this write-up. It is been extremely useful. It couldn’t have arrive at a greater time for me!