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

Let's take an example of scripting databases. You can script a database pretty easily in SSMS, but you can't schedule it. Until now, if you want to schedule a script or code the scripting process to perform the exact same options every time without having to click through the wizard or worry about making mistakes, all you could do was code a solution in VB and compile it. But not everyone has VB skills, and writing SMO (SQL Server Management Objects) is neither easy nor intuitive. PowerShell allows you to script a database in just 21 characters of code and schedule it inside an agent job. You don't have to compile it, so you don't have to keep up with separate source code. PowerShell also makes it a lot easier to work with multiple objects -- and multiple servers -- in SQL Server.

Another good example is adding user permissions to all the schemas in a database. You can code this in T-SQL, but T-SQL requires the inclusion of unsafe dynamic SQL code inside a cursor. You can accomplish the same thing in PowerShell with just a couple of lines of code, and without introducing any unsafe constructs into your environment.

Pretty blinkies

SSMS has a new Activity Monitor that provides information DBAs formerly had to gather from multiple sources. At the top is a series of live graphs where you can see useful stats such as CPU, waiting tasks, and batch requests. Under the graphs, you can see detailed information on processes, resource waits, recent expensive queries, and data file I/O. In fact, the Activity Monitor looks exactly like the resource overview from Vista.

So the Activity Monitor is presentable enough, but using it is a mixed bag. If you want just the top-level server stats from the graphs, then you won't be disappointed. The graphs load fast enough and the information is useful. The trouble begins when you start digging into the drill-down information. For starters, on a really busy system it takes too long for these drill-down areas to populate. I tested this on a 64-bit data warehouse system with 1,500 reports running and a few dozen more ad-hoc queries going on. The drill-downs took so long to populate -- more than five minutes -- that I thought they weren't working. That's not what you need in a troubleshooting scenario.

Second, much of the information you'd expect isn't there. When the drill-down info did come up, I immediately saw that my server was maintaining about 67 percent CPU -- so far, so good. But the next pieces of information you would want -- namely, memory usage and disk usage -- aren't in the Activity Monitor at all. You can see disk throughput, but that's not really going to tell you if you're having disk problems.

In short, while there are some useful server-level stats, they're incomplete enough to render them pointless. If I have to go to another tool for my memory and disk stats, then I might as well skip the Activity Monitor and use perfmon (or other tool) to begin with.

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