Afin de déterminer quels sont les utilisateurs qui monopolisent un serveur SQL Server il suffit d’utiliser la requête suivante :
SELECT TOP 20 SPID, Blocked, convert(varchar(10),db_name(dbid)) as Base, CPU, datediff(second,login_time, getdate())/60 as Minutes, convert(float, cpu / datediff(second,login_time, getdate())) as PScore, convert(varchar(16), hostname) as Hôte, convert(varchar(20), loginame) as Login, convert(varchar(50), program_name) as Programme FROM master..sysprocesses WHERE datediff(second,login_time, getdate()) > 0 and SPID > 50 ORDER BY PScore desc
Plus le PScore est élevé plus l’utilisation de la base est intensive.
De la même manière on peut déterminer les programmes les plus gourmands en observant la colonne BFactor lors de l’exécution de la requête suivante :
SELECT convert(varchar(50), program_name) as Programme, count(*) as CliCount, sum(cpu) as CPUSum, sum(datediff(second, login_time, getdate())) as SecSum, convert(float, sum(cpu)) / convert(float, sum(datediff(second, login_time, getdate()))) as Score, convert(float, sum(cpu)) / convert(float, sum(datediff(second, login_time, getdate()))) / count(*) as BFactor FROM master..sysprocesses WHERE spid > 50 GROUP BY convert(varchar(50), program_name) ORDER BY score DESC