Who's Hogging My Server?

I recently spoke with someone who was thinking about creating multiple SQL Server instances on his server and placing one database in each instance. You might wonder why he would want to do such a thing. He was concerned about system performance and thought that by placing one database in each instance, he could monitor them independently so that he’d know which ones were using the most resources.

Seems like a reasonable DBA request these days, right? In fact, it’s one of the most common scenarios I see among DBAs who have to account for separate applications that share a common database server—particularly when server consolidation is high on the list of corporate priorities. Certainly, there are some situations in which you should use multiple SQL Server instances, but you don’t typically see performance monitoring among those situations. Suffice it to say, there’s a much easier and cleaner way to get the kind of metrics that multiple SQL Server instances might provide—by using SQL Server 2005’s built-in dynamic management views (DMVs).

Cost and Performance Questions

Two questions arise from situations involving multiple applications on a common database server, and they involve cost and performance.

What’s the cost? First, you need to determine each application’s ratio of server usage. This metric arises mainly from corporate policies in which different departments must pay an internal fee for their use (really, their applications’ use) of the consolidated database server. There are several methods that companies use to decide how much each application must pay compared with others. Some use a ratio of individual database size to the total size of all the databases. Some make the decision based on the number of users for each application. But one of the most requested methods is to make the decision based on how much database-server CPU each application uses in relation to the other applications. In the past, this metric wasn’t always easy to determine and typically involved running a 24 × 7 trace to permit data analysis to obtain the ratio on a per-application basis from the calls to each database.

How’s the performance? The second and most important question is how each application affects overall server performance. For example, suppose you have 10 applications and each one has one database. How do you determine which application is using the most resources on the server? That question is only part of the story. Just because an application is using lots of resources doesn’t tell you whether that application really needs to use so many resources for proper operation. However, because all 10 databases are on the same SQL Server instance, you’ll probably find it difficult to isolate resource usage and performance problems on a database-by-database level.

A Better Solution

You can see why many people believe that they can easily identify the aforementioned metrics by isolating each database in its own SQL Server instance. Without going into all the pros and cons of using multiple SQL Server instances, there’s a better option available to you, and that is to use SQL Server 2005’s built-in DMVs. In my article “Are Your SQL Server Statements Performing Well?” (InstantDoc ID 97761), I discussed how to use the sys.dm_exec_query_stats DMV to gather performance-related data on individual query plans. The one detail missing from that DMV is the database ID (DBID) in which each query is run. Without the DBID, it would be impossible to track the metrics on a database-by-database level (hence, application level) without resorting to a much more involved trace or some other complex process.

You can, however, use another DMV—called sys.dm_exec_plan_attributes—to get the DBID. This DMV lets you access individual attributes (e.g., the DBID, date format, user ID) for each query plan by simply cross-applying the DMV and specifying the other DMV’s plan_handle identifier.

Now, with the relatively simple query that Listing 1, page 41, shows, you can identify and, more important, aggregate the information from the query_stats DMV by each database. Note that the DBID for ad hoc or prepared plans is the DBID from which the batch is executed. If you’re connected to Northwind and run a query against pubs, the stats would show up under Northwind.
For example,

use Northwind; select * from pubs.dbo.sales

Listing 1’s query represents only a subset of the information available through the DMV, but it answers the two aforementioned questions. Running the query gives you a quick way to see the total resources used by all the queries currently in the plan cache at a database-by-database level. You’ll probably be most interested in the Total Worker Time column because it displays the amount of CPU used to process all the queries for a given database. You can then easily determine how much server CPU resources were used for each application in relation to one another and the total.

Effective and Free

This DMV method isn’t a foolproof way to track application usage or performance, but it’s simple, effective, and—best of all—free. If you’re concerned that one or more databases might be loading down your server, use this approach to set your mind at ease. But keep in mind my March warnings about plan cache volatility.