SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 73 of 365 Next >

SQL Server is a complex platform for applications, which means that configuring your environment isn't limited to the SQL Server software alone. You have to take all the factors into consideration, such as the hardware each server is running, the network, and even the architecture of the application. In this tutorial, I explain configuration options that are pertinent in architectures found in mainstream applications today. I’ll cover SQL Server versions 2000 through 2008 R2, but not for SQL Azure. SQL Azure starts your environment at the database level, not the Instance level. If you’re not familiar with the idea of a SQL Server Instance, check out this article first.

SQL Server is largely self-tuning. It's normally best to allow SQL Server to dynamically adjust the settings it maintains and not take the reins, but it can be useful to know what each setting does and how it affects the platform. Don't make changes to any server without understanding what the effect will be on your environment. In other cases, it’s important to make changes to environment based on your needs. In both cases, you need to know what the settings do to your server to make the right choice.

Once you understand the settings do and when you should use them, you can adjust the settings for SQL Server using either Transact-SQL commands or by graphically changing them using the graphical management tools. For SQL Server 2000 and lower, that’s Enterprise Manager, and in SQL Server 2005 and higher, it’s SQL Server Management Studio (SSMS). I’ll focus first on the graphical tools, and then explain the commands you can use once you understand what each graphical area does.

In this tutorial I’ll use SSMS, but to access these types of settings graphically in SQL Server 2000 and earlier, just use Enterprise Manager, right click the name of the Instance, and select “Properties” from the menu that appears. In SSMS, connect to the Instance you want to change, right-click the name of that Instance in the Object Explorer pane, normally found on the left-hand side of the SSMS tool. Once the menu appears, select “Properties” from the menu that appears. That will bring you to the “General” tab.

Figure 1

The General tab shows a lot of information about your server, such as how many processors it has and the OS level. (You can get some of this same information with the T-SQL command EXEC xp_msver) In SQL Server 2005 and higher, this is a read-only panel. You’ll notice the data on the right is grayed-out, and you can’t edit it.

In SQL Server 2000, you can set options such as whether the server will auto-start, as well as the SQL Server Agent and the MSDTC service. Depending on how your application is set, you probably want all three; in the case of 3-tier or N-tier architectures, you'll want MSDTC to auto-start. This service provides for Distributed Transactions, something those architectures depend on.

Also in version 2000, the Network Protocol section allows you to tie out the server to the protocols on which it will listen. Order isn't important here, since the server listens to whatever the client sends. Order is important at the client. Network and services functions are handled in the SQL Server Configuration Manager tool in SQL Server 2005 and higher.

The next tab is “Memory.” More than just about any other tuning you can do, the memory settings have the greatest affect on your system. I assume that you've got plenty of RAM on the server; 2GB is just about the lowest I recommend, even for small applications.

Figure 2

The number sections (2005+) and sliders (2000-) on this tab set the lower and upper boundaries that SQL Server will work with. If your server is only running SQL Server, I normally recommend a fixed setting (setting the upper and lower boundaries the same), leaving the OS at least 1 GB of RAM. You might be running the middle tier on this server if you're in a small environment. In that case, ensure that you leave enough RAM for that software to run.

If you do leave a minimum and maximum value for the memory, SQL Server will automatically use as much memory as it needs, and release that memory back to the operating system when the OS needs it or SQL Server does not. The reason I advocate setting a fixed amount on the RAM is so that SQL Server won't have to constantly adjust itself while it's running. It's got enough to do already, without adding work.

You can also set a fixed amount for queries, but it's best to leave this at the default. SQL Server is normally better at managing that. In SQL Server 2005 and higher, you can also change the memory used to create indexes, but once again the default (0, in this case) is most often the correct choice.

On this tab and most that follow, you also see that you can change the settings to reflect "running values." That means that SQL will look at what is in place at this moment, and adjust the sliders to those levels. Once you change the settings, they are the new “configured” values. Some of these need a restart for the new setting to take effect, and in others it doesn’t. Since this behavior changes from release to release, it’s best to read up on any setting you plan to change to find out whether that’s needed or not.

The next tab sets the processor options. The first option allows you to set which processors SQL Server uses.

Figure 3

This is the only Microsoft product that I'm aware of that has this setting, although with other products you can locate their “process” using the Windows Task Manager and change the processors there. I don’t recommend you do that in either place, unless you need a processor or two left out for use by other applications. I’ve also seen this setting altered when only a few of the processors have been licensed for SQL Server.

The Microsoft Windows operating systems use Symmetric Multi-Processing (SMP). This means that all processors in a system are treated as one. Other operating systems are Asymmetric Multi-Processing (AMP), meaning that programs must be coded to run on one or more processors specifically.

Both architectures have advantages. SQL Server claims its heritage from the UNIX world (when Microsoft licensed the engine from Sybase) and so has the ability to be tied to a particular processor.

I've seen shops use three of four processors in a system this way. Unless you've installed the middle-tier on the SQL Server, I don't recommend this. I think that using all the processors is the best way to go, something I've seen proven in experience.

In some cases you’ll see more processors than you physically have in the server, because of “multi-core” or hyper-threaded processors. As of this writing, SQL Server is licensed per physical socket, so you don’t have to pay extra for those “enhanced” processors. As always, this is subject to change, so be sure you check your licensing documentation so that you stay legal.

Another setting you can change here are worker threads, which is the number of threads the system uses for each connection. It's set by default to 255 in early versions of SQL Server, and 0 in later versions, which means the system manages the connections. Changing this setting means that each connection get a thread. After the number you specify, the system starts pooling the connections, which might drive up processor context switches, which can be bad. If you have a constant number of connections, adjust this number a bit higher than the connections. Any higher, and you're wasting resources; any lower, and you're creating too many threads.

If you're running in a 3-tier or N-tier architecture, make sure you average the connections over a long period of time. Connections from these architectures pool and break, so a single measurement, or even a few isn't adequate.

You can also boost the priority of SQL Server on the operating system, which at first might seem like a good thing. If you're using a third-party backup solution, it might be best to leave this alone so that the backup takes priority during the run and gets done faster.

The next option allows you to change from threads to fibers. Switching threads from user to kernel mode can be expensive from an operating system standpoint. Fibers make up threads, and aren't managed by the OS.

Setting this switch allows SQL Server to manage this switch, which is less expensive. But if you do, then you've added workload to SQL Server, which is usually bad. The best thing to do is watch the Performance Counter of Processor:Context Switches. If this number is into the tens of thousands, switch to fibers and see if performance improves. This isn't a necessary exercise, it's still usually best to leave this at the defaults.

In SQL Server 200 and lower, the final area on the Processor tab allows you to set the way that queries will parallelize. I recommend that you use all your processors to do this kind of work. If SQL Server determines that a query plan would benefit from parallelization, give it room to do so. Again, the defaults are normally best here.

You can also set how "deep" SQL Server will look to see if it can parallelize a query. The default of 5 is the right choice for almost every instance. If it has to look deeper than that, you might want to re-factor your query.

In the next tutorial, I'll continue the server settings with the rest of the tabs.

InformIT Articles and Sample Chapters

The article Windows Processes and Threads: Weaving It All Together discusses NT threads in depth.

Books and eBooks

I cover these settings in more depth for SQL Server 2005 in my book Administrator's Guide to SQL Server 2005.

Online Resources

Each setting is explained in more depth in Books Online.

< Back Page 73 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address