You Dream it...We Build it...
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
t2.name 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 t2.name
order by sum(cpu) desc
</cfquery>
declare @tot AS decimal(18,0)
select @tot = sum(cpu) from sysprocesses
select
t2.name 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 t2.name
order by sum(cpu) desc
</cfquery>
Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.
"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.