SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 176 of 365 Next >

Performance Tuning can be a little confusing — there are so many places that you can start the process that it is difficult to know where to begin. You can start with the hardware side of things, the queries, the indexes, or any number of other places. It often becomes a matter of the tools you have at hand, and are familiar with. Hardware-oriented professionals tend to start with tools like the Windows Performance Monitor to locate hardware bottlenecks to see if adding more memory or faster drives will help. Those with more of a development background might start with the queries the server is running, using various tools provided in Visual Studio and SQL Server Management Studio (SSMS) to find and diagnose those queries taking the most amount of time. Still other professionals start with the database platform, evaluating the indexes, looking at the "Wait-States" that indicate which activity is taking the longest.

But if you start with only a "tools" approach, you might miss a chance to fix not one but many performance issues. You also run the risk of making a decision because of that tool that actually doesn't address the problem. For instance, why add more memory if the query is poorly designed? And why tune a query over and over if the indexes are inefficient or the drives are too slow?

What is more important is to define a process to systematically tune your system. I'll cover tools throughout this section of the InformIT Guide, but you should start with a solid plan for evaluating your system first.

Start With a New System or Tune One That is Currently in Use

The overall process of the tuning effort is based on when you start it. It's a bit like tuning an automobile for a race. If a mechanic is brought in during the design phase, he or she can build the car with light materials, a large engine, and will leave out the things like radios and air conditioning that just add weight and aren't necessary to win the race. If, on the other hand, they're brought in after the car is already built, there's a different process to follow to tune the car, especially if time is limited. And when isn't time a big factor?

Tuning a system is the same way. If you are brought in during the design, you can evaluate the requirements along with the application team. You can apply proper object considerations and add in just what you need. On the other hand, if you're brought in after the application is already in place and running, there are other processes to follow.

So I'll start with what you're more likely to be faced with. Most of the time you'll be called in to look at a current system that is having problems. Often these problems deal with timeouts in the application or general slowness, and the business staff will contact the application team. Many times they are also being brought into the system after its deployed, so they're starting in the same place you are. Sometimes technical professionals immediately suspect the database, and indeed as DBAs we have the most levers and knobs we can move around that affect performance. For that reason, you may even be asked to lead the tuning effort, which involves far more than just the database.

I've developed a simple outline that I use to tune a system. When I say "system," I mean all of the components — technical or not — that go into producing the output the business uses. The reason you have to consider all of these elements is that it is impossible to adequately tune a system if you just focus on one area. Think about the system as a series of roads. If you focus on one road to create better traffic patterns, you only create bottlenecks elsewhere. You have to evaluate the system as a whole.

I've broken this process into phases. Tuning a system takes time. Don't take the quick approach and bring your system down below the threshold of pain and then stop the process. Be systematic, and follow through with each step.

Unless you're only evaluating the database (always a bad idea), you need to include lots of other professionals in the process. With a set of phases like these, however, not everyone has to be involved al the time. You can bring in others or even ask them for information. The beauty of this approach is that you can define the tasks that can be done in parallel, and the ones that need to be done serially.

Let's take a look at the phases of the process. After you understand what is done in each phase, future tutorials will focus on the steps within the phases and how you can accomplish them.

Step One: Defining Components

Before you can tune a system, you need to know what the parts are. That's what is involved in the first phase of the process. You need to document each and every part of the system, starting with an overview of what it does.

Expect a lot of pushback from the business when you describe this part of the process as an integral step. For one thing they don't understand its importance. You'll need to explain that each of the technical professionals is focused on their individual areas, and that the problem might exist in more than one part of the system. Without a solid understanding of how the system works, the team won't be able to correct the issues.

Step Two: Evaluation

In the next phase you'll detail what can and should be tested, and develop a plan for that. Almost everything in a computer system has metrics that can be measured, but not all of them need to be. In fact, since monitoring the system can impact performance, you shouldn't monitor everything.

During the evaluation, you'll dispatch the members of the team to collect metrics on their respective parts of the system. You'll do the database, the developers will check the application, and the hardware folks will measure the physical parts of the system.

Step Three: Interpretation

Once you've collected the metrics from your measurements, you need to bring the team back together all at one time to look over the results. The process I recommend is that each member is required to review all of the metrics from every component and comment on what they think it means. While the hardware folks might not know what a database lock is, they might notice that the number changes dramatically at a certain time. They might be able to correlate that change with a hardware event, something you can't do.

Once a coherent view emerges, you can move on to the next phase.

Step Four: Creating the Action Plan

Finally you'll develop the steps you want to take to correct the problems you've seen in the previous phase. For each step of the plan you need to include how long the correction will take, what kind of impact it will have, the risks and who is assigned to implement it. With that list you can group the activities into things that you should do now, later and never. You can also assign the timeline for the implementation.

To perform a tuning exercise you'll need all of the skills an experienced DBA has. You can't just focus on the hardware, the platform settings, or the code. You'll also need to understand the business and the "soft" processes the users employ. For instance, if the users are printing large reports from the production database in the middle of the day, you'll have to deal with that.

The process I've described is certainly reactionary. A much better place to start the performance tuning is during the system's design. I've covered a lot of the material in this guide that you can use to develop good coding practices, size your hardware properly, and even the business practices you need to navigate to ensure that you have a fast system. I'll also cover more specific steps you can take during the design phase to prevent the major performance issues in the first place.

In the articles that follow, I'll show you how to follow each of these steps, and I'll detail the tools you need to perform each one.

< Back Page 176 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