Overvåke bruken av SQL Server

Blogs mis à jour

• SQL Fool

  Fermer l’aperçu

  Loading...
• The Deployment Guys

  Fermer l’aperçu

  Loading...
• Benoît Sautière

  Fermer l’aperçu

  Loading...
• Advisec Blog

  Fermer l’aperçu

  Loading...
• Ask the Directory Service Team

  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

  Tags : Monitoring , SQL Server
  
  • 
  • 
  • 
  • 
  • 
  • 
  • 
  • 
  • 
  • 
  • 
  • 
  • 
  •