Deep dive into SQL Server 2008

Microsoft's 'Katmai' is packed with performance and management features enterprises will love, and even small shops will have reasons to make the upgrade

The processes, resource waits, and data file I/O drilldowns are more useful. The resource waits monitor shows all of the server-level resource wait types and gives you stats about each one. Similarly, data file I/O puts all of the important info at your fingertips, though it does take forever to load.

Processes is really just the same sys.sysprocesses table that DBAs are used to, but with a GUI that makes it a little more user-friendly than a manual query. You can easily filter data that you want to see, which makes for easy troubleshooting, especially when you're looking for a specific user or blocking process. One beef: Although you can rearrange the columns in the display, the order doesn't persist, so the next time you open the Activity Monitor, you'll be arranging your columns again.

The recent expensive queries drill-down is almost completely pointless. Does it give you the most recent expensive queries? Yes, it does. Does it give you important stats on these queries such as CPU, number of reads and writes, average duration, and plan count? Absolutely. Does it tie these stats to a user so that you can tell who or what is performing these offending actions? No. What is a DBA supposed to do with that?

One thing you get from the GUI drill-downs that you don't get from their manual counterparts is auto-refresh. You can configure your refresh rate, but here again, your changes don't persist. A bigger issue is being able to collect the performance data you want within the refresh period. This can be a problem on busy systems that collect a lot of performance data.

For example, if you have dozens of data files on your system, it could take longer than the 10-second default refresh rate to pull the data file I/O data. And while you can increase the refresh interval to, say, one minute, the CPU graph will be refreshed only once per minute as well. Unfortunately, you can't set different refresh intervals for different monitors. It's one size fits all.

For all that it tries to be, I have to give the Activity Monitor a thumbs-down, or at least a thumbs-sideways. In its current form, it's just not useful enough to do DBAs much good. Not only did it take too long to give me the info I needed, but the info was so sketchy that I needed another tool to complete it. If I'm going to look at CPU, I'm immediately going to want to see memory and disk. And as long as I have to go somewhere else for those other metrics, I might as well get CPU there too. Sorry Microsoft, better luck next time.

Resource Governor

Microsoft touts the new Resource Governor as one of the biggest enhancements in this release, and there's no question it will come in handy for shops that want to keep some processes from interfering with others. For example, one of the best fits for a resource governor would be on an OLTP system where you're forced to run reports against live OLTP data. You don't want the reports to get in the way of transactions, so you would push them to a group that caps their resources.

Join the newsletter!


Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags databasessql server 2008software applications

More about InformaticaMicrosoftNICEOracleParadigmSAP AustraliaSpeed

Show Comments