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

Email Address

SQL Server Reference Guide

Informit Articles and Sample Chapters

Online Resources

InformIT Promotional Mailings & Special Offers

Overview

Collection and Use of Information

Questions and Inquiries

Online Store

Surveys

Contests and Drawings

Newsletters

Service Announcements

Customer Service

Other Collection and Use of Information

Application and System Logs

Web Analytics

Cookies and Related Technologies

Do Not Track

Security

Children

Marketing

Correcting/Updating Personal Information

Choice/Opt-out

Sale of Personal Information

Supplemental Privacy Statement for California Residents

Sharing and Disclosure

Links

Requests and Contact

Changes to this Privacy Notice