SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 361 of 365 Next >

In a small DBA shop, one with only a few SQL Servers and a limited number of applications, third-party management tools aren't always necessary. The workload in these environments usually allows for a more thorough understanding of each system. You have the ability to watch the servers closely over time, and you can manage them using the included toolset such as Enterprise Manager (SQL Server 2000) or SQL Server Management Studio (SQL Server 2005).

Of course in smaller shops there isn't always a dedicated DBA. Often the technical professional in a small shop is asked to do many jobs, the DBA task being only one. When I had this type of position I relied on immersing myself in a particular platform (such as Oracle or SQL Server) and then writing scripts to check things for me. I made sure the scripts were accurate and comprehensive and then relied on their output to keep me informed about the state and growth of the systems, as I moved on to other tasks.

Larger shops, however, may be faced with multiple servers with hundreds of databases on each one. Even with one or more full-time DBAs assigned to the server farm it's difficult to track everything. One of the most neglected tasks is to monitor the systems for growth. If this is done at all it's often a matter of recording the drive sizes, memory use and CPU load periodically for changes. For instance, you might record that six months ago the drives were 20% full; now they are 40% full. You might be tempted to extrapolate that information to a conclusion of 20% growth every six months. In fact, all of the growth might have occurred in the last week — or all at one time. This might be due to new applications being installed on the server, a rise in transactions or some other factor. The point is that it is quite dangerous to work from too little information.

Another issue with monitoring the servers is that you may not need to purchase more resources each time one system shows that it is reaching capacity. Instead of buying more drives each time a server gets full, you might be able to move an application's database from one server that shows rapid growth to a system that is showing little or no growth. But how do you track the growth to begin with?

The method I've used in the past is to develop an application that runs a monitoring task periodically and places it into a database. I use various statistical techniques to find trends and patterns in the data, and make my decisions based on what I find. There are tools that you can purchase that do the same thing, or even more. One of those tools is Capacity Manager from Quest Software.

I've reviewed other products from Quest here on Informit, such as Spotlight on SQL Server. Quest produces those kinds of tools that contain information you can get yourself with the right T-SQL statements, but packaged together so well that you don't need to. Could you write a tool like I've done in the past to collect metrics on your system's growth? Sure. Should you buy a product that does the same thing? Read on...

First of all, to the particulars: You can download a free evaluation copy of Quest's Capacity Manager here. If you decide to buy it, you'll pay around $1,000.00 (U.S.) per instance of SQL Server (2000 SP3a or 2005), which includes one year of maintenance. I always like to define what "maintenance" means, because each software firm seems to have a different idea about it. In this case maintenance means that during the year you're able to upgrade the software for free, and you'll get support on the product. After the year of maintenance is up, you're able to keep the version you have, but you won't be able to download the newer versions for free. If you renew the maintenance, you can continue to download the newer versions.

Capacity Manager works the same way that you would write a monitoring program. It creates and runs various SQL Server Jobs in combination with proprietary code that evaluates the objects on an instance of SQL Server and stores metrics about them in a tracking database. Where it exceeds what you or I might write is in the depth of the analysis and the range of objects it tracks. It tracks everything down to the tables on an individual database. Using that information you can determine not only server resource growth, but all the way down to an application growth pattern.

Capacity Manager also has some very nice reporting. This is useful when you've got to present your findings to a manager or justify new purchases. It also presents those reports with the ability to export the data they contain.

Another advantage over a home-grown system is that Capacity Manager can help you determine other storage optimizations. It watches log trends and tempdb use and can recommend optimal sizes and placement. One of the most useful features I found during the evaluation is the fragmentation report. If you're like me, you've probably wanted a feature in SQL Server that shows database fragmentation much like the Disk Defragmenter in the Windows operating system. Capacity Manager has a view that shows this kind of information. That can be invaluable if you're trying to decide the best time to do maintenance on a system.

Figure 195

Capacity manager also contains a partitioning display, with wizards to guide you through re-distributing the filegroups onto various partitions. My testing was successful with this part of the tool, but I used small samples and I wasn't connected to a SAN. I'm not certain I'd trust any tool to redistribute my files, since every system is configured so differently where I work. When you have a truly global enterprise with clustering and 64-bit systems connected to SAN fabric, you have to plan and be extremely cautious during moves and changes. My primary thought during these kinds of changes is in how long it will take and how quickly I can recover, since I usually only have a short maintenance window.

After you download the package, it unzips to a single install file of around 8 Megabytes. You can install it on a workstation and it will store its tracking databases on a server. You'll need to register an instance of SQL Server, and then create a "repository", which is the tracking database. The repository can be placed anywhere, not just on the installing system or the production servers.

During the installation, the system creates two logins for the jobs it runs. You can assign passwords to these accounts or let the system generate its own. As a final step, you select objects for the system to monitor.

Figure 196

Once you've worked your way through the wizard that adds the objects and sets the job schedules, the system builds the repository. You may find that some of your security settings on SQL Server 2005 restrict what these jobs can do, so make sure you note down any messages you receive during the job creation process. Once you're done, you'll have some basic information about your system available right away.

Figure 197

The most important thing about using the product is that the data you're looking at is specific to the object you've selected. You'll have to build and run various jobs to collect information about the object at each step. There's a screen that shows that you haven't collected enough information about an object and a hyperlink that guides you through the job creation whenever this happens.

Figure 198

The system is full of features in addition to reporting and predicting growth trends. Based on the object you're in you can shrink objects, reorganize them or allocate them differently.

You may have to refresh the screens if you fire off the jobs manually, and not all objects are "active". Some are just graphics on a report, and it's not always clear which are which.

Here are the things I liked about the product from my notes:

Simple interface — non-modal dialogs
Fantastic charts and graphs
Very comprehensive
Database Maps
Fragmentation Information
Partitioning Information

And here are the things I wasn't as pleased with:

Price seems rather high in a per-instance mode
Having to refresh the screens
Objects aren't drill-down
A bit too specific for a single product

If you're in a large shop and have to deal with allocating system resources, then you should definitely evaluate Capacity Manager. It's a shame that this product was separated out from their earlier product called Quest Central. Quest Central bills itself as a performance tool, but I'd like to see them change that product to have Capacity Manager's look and feel, and bundle it all together with Spotlight.

Informit Articles and Sample Chapters

I've reviewed other Quest products here on Informit. You can find the last one here.

Online Resources

Quest does more than just Capacity Management or even SQL Server. You can find out more here.

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