|
|
|
|
|
Memory - This is the single most important
parameter to configure on your system. Memory is used for a variety of things within SQL
Server. Upon start up, a portion of this is used management the number of connections
(40K), locks (32 bytes), and open objects configured on the server. The remaining amount
is split between the data cache and procedure cache. You want to maximize the data cache
on the system. As data pages are read from disk, they are placed in the data cache. When a
query is executed, it first looks in the data cache for the corresponding page. If not
found in the data cache, it reads the page in from disk. Once the data cache fills up, the
least recently used pages are removed from the data cache to make room for new data pages.
Obviously as the size of the data cache increases, the number of data pages it contains
also increases. The goal is to minimize or eliminate the physical disk reads during query
processing. Open objects - This configures a pool of object descriptors or pointers upon startup. As an object is accessed, an open object descriptor is assigned to it in order to locate the object in memory. Once the pool of descriptors is used up, SQL Server must search for the least recently accessed and free that descriptor to be used by the next object accessed that does not have a descriptor. This requires processor overhead and should be minimized. Each open object configured consumes memory that will be unavailable for the data cache, so you will want to minimize this to cover the objects in your system. Procedure cache - This works just like the data cache, except its purpose is to store the compiled query plans and object code on the system. As each trigger and stored procedure is accessed, it is read form disk and placed in the procedure cache. Remember that procedures and triggers are non-reentrant. This means that when you access a stored procedure, SQL Server will first look in the procedure cache for that object. If it is found and is not being used by another process, it will execute the object it found. If the procedure is in use by another process, it will make a copy and then execute it. This means you can have more than one of the same object residing in the procedure cache. Advanced options - Some of the server configuration objects are standard and some are considered advanced. Turning on this option simply shows all of the configuration options available. Set working set size - When SQL Server starts up, it normally does not allocate all of the memory configured for it. It simply takes as much as it needs to satisfy the processing it is doing. This can lead to fragmentation of your RAM, because SQL Server may need to access multiple non-contiguous memory blocks to do its work. Set working set size tells SQL Server to allocate all of the RAM it is configured for at startup so that it is working with a single contiguous block of memory. Locks - As data pages are accessed, a lock is placed on each to control the concurrency on your system. even a small and relatively simple query can easily generate hundreds or thousands of locks in a system. Once the pool of locks is exhausted, SQL Server will attempt to free locks from other processes to use. If no locks can be freed, each remaining process will be queued on the server until such time as enough locks are available. Once locks become free, each process will be released to execute. Because of this, configuring locks too low can become a severe bottleneck on the system. Each configured lock consumes 32 bytes of the memory allocated to SQL Server, so you do not want to set this too high and take away from the data cache unnecessarily. Priority boost - This determines if the SQL Server process should run at a higher priority than normal. This is an option that should never be set unless specifically directed to do so by Microsoft technical support. Turning on this option has the side effect of causing random server crashes, because SQL Server interrupts processes that NT needs to function. tempdb in RAM - This option should never be set as well and is no longer available in future releases. The memory wasted here is taken away from the data cache. tempdb is a scratch area for temporary processing and does not reuse any information that it holds. This database is completely transitory. Some installations will have this set at the urging of a developer, because they are making very heavy, unnecessary, use of temp tables. Turning this on will actually see a localized boost in performance. But, the overall system performance will be severely impacted. Turning this on is an attempt to cover up poorly written code. Temp Tables The solution to the performance problem is not to turn this on, but to eliminate the temp tables. There is almost no reason whatsoever to need to use temp tables. If you find yourself using temp tables to gather the results you need, it is a very good indication of one of three things: 1. Laziness on the part of the developer 2. Lack of understanding of SQL 3. Poor database design When tuning systems, temp tables are the first thing that are marked for elimination. After modifying configuration options, eliminating temp tables always produces a very large performance increase on every system I have tuned to date. |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.