ColdFusion Muse

Processor Usage in MSSQL

Here's a useful query for showing how much processor a given database is consuming compared to other DBs. The query returns a perctentage. Note, this is a percentage of the overall processor usage of MSSQL. If MSSQL is using 10% of your server proc usage and a given DB is using 50%, you should realize that the DB is, in reality, using 5% of the capacity - no cause for alarm. If, however, a SQL server is at or near capacity (70% or above) and a given DB is using the majority of the proc cycles, then of course, that DB is a good candidate for upgrading to a dedicated server (or at least one with more horsepower). Of cours, this assumes that you have examined the schema, indexing and caching to insure it scalable to begin with.

<cfquery ...>
declare @tot AS decimal(18,0)

select @tot = sum(cpu) from sysprocesses

select as dbname,
CAST(((sum(cpu)/@tot)*100) AS decimal(18,1)) as PercentUsage,
sum(cpu) as totalCPU

from sysprocesses t1 join sysdatabases t2
on t1.dbid=t2.dbid
group by
order by sum(cpu) desc

Mark Kruger's Gravatar Phil Senn added this note:

"Sysprocesses is stored only in the master DB." I accidentally deleted the note (sorry Phil). So the revised query should have a "use Master" command at the top. It goes without saying that you have to have sa permissions to get server level stats.
# Posted By Mark Kruger | 11/29/10 11:14 AM

Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.