Get best of the week

Se faire des amis SQL Server et PerfMon

Sur la photo, il y a une scène de la bonne vieille comédie "Airplane" - les boomers s'en souviennent.



Elle n'est pas là par hasard. Mais utile plus tard.

Dba n'aime pas perfmon


Ce n'est pas du tout parce que l'interface PerfMon n'a pas changé avec Windows NT 3.1 (?) Et redonne la chaleur de deux millièmes. Soit dit en passant, peut-être que quelqu'un connaît l'explication de ce fait étrange. Le panneau de contrôle est réécrit plusieurs fois par an. Même la calculatrice a été réécrite. Mais pas perfMon.

DBA pense avec des mots de couverture. Quand un bon médecin DBA arrive sur un serveur SQL malade, il ouvre sa valise, et dans la valise il y a un grand nombre de quartiers qui le servent fidèlement. Il prend le plus court d'entre eux de mémoire. Parfois, un administrateur de base de données peut examiner les mesures de PerfMon - Disque physique ou Longueur de la file d'attente de disque. Cependant, un vrai DBA obtiendra ses propres métriques de serveur SQL, non pas via PerfMon, mais avec un correctif, par exemple, comme ceci:

select * from sys.dm_os_performance_counters 
  where object_name='SQLServer:Buffer Manager' 
    and counter_name like 'Page life expectancy%' 
	and instance_name=''

Lorsque les responsables informatiques, divorcés de la sphère DBA, lui demandent: «quelles mesures souhaitez-vous que nous collections?» (eh bien, il y a toutes sortes de splunk, squared-up, etc.), puis le DBA commence à penser aux scripts, et le reste aux métriques de perfmon. En même temps, en règle générale, ils essaient de vider toutes les métriques sur le DBA. Lorsque cela se produit, l'image est inutile dans sa congestion. Et puis un court clip vidéo du film "Airplane" m'aidera .

Par conséquent, DBA écrit keri, qui écrit des statistiques toutes les N minutes sur une plaque de la base de données DBAtasks. Vous reconnaissez-vous?

Advocate Devil (perfmon)


Néanmoins, les compteurs perfmon sont un outil standard. Ils peuvent être enregistrés, définir des alertes, mais surtout, il existe un grand nombre de systèmes qui "fusionnent" ces mesures de nombreuses machines dans un référentiel centralisé, vous permettent de les analyser et de créer de beaux graphiques (et non comme perfmon).

Il s'avère que nous pouvons nous faire des amis avec le monde SQL et le monde PerfMon!

Ci-dessous, je montrerai comment.

Maintenant, les détails iront


À titre d'exemple, nous allons montrer comment exporter des métriques que SQL lui-même ne publie pas sur PerfMon. Par exemple, la requête ci-dessous:

select
  convert(numeric(10,2),round(((sum(version_store_reserved_page_count)*1.0)/128.00),2)),
  convert(numeric(10,2),round(((sum(user_object_reserved_page_count)*1.0)/128.00),2)),
  convert(numeric(10,2),round(((sum(internal_object_reserved_page_count)*1.0)/128.00),2))
from
  tempdb.sys.dm_db_file_space_usage;

renvoie la quantité d'espace tempdb utilisée par le magasin de versions (pour l'instantané), les objets utilisateur (onglet #tab et ##) et l'espace de stockage de tri / temporaire. Créons des mesures pour eux (18+, les équipes ne sont pas documentées)

dbcc addinstance ('SQLServer:User Settable', 'TempDB version store KB')
dbcc addinstance ('SQLServer:User Settable', 'TempDB user store KB')
dbcc addinstance ('SQLServer:User Settable', 'TempDB sort store KB')

Cela créera trois mesures dans SQLServer -> UserSettable -> Query. Les métriques sont des entiers - int (pas bigint). De plus, leur signification est interprétée telle quelle. Autrement dit, si vous avez besoin d'un delta de la valeur précédente, c'est votre préoccupation. Mais dans ce cas, nous avons juste besoin d'attribuer des valeurs aux métriques:

declare @TEMPDBver int, @TEMPDBuser int, @TEMPDBsort int
select

@TEMPDBver = convert(numeric(10,2),round(((sum(version_store_reserved_page_count) *1.0)/128.00),2)),
@TEMPDBuser = convert(numeric(10,2),round(((sum(user_object_reserved_page_count) *1.0)/128.00),2)),
@TEMPDBsort = convert(numeric(10,2),round(((sum(internal_object_reserved_page_count) *1.0)/128.00),2))
from tempdb.sys.dm_db_file_space_usage;

if @TEMPDBver is not null    
  dbcc setinstance ('SQLServer:User Settable', 'Query', 
    'TempDB version store KB', @TEMPDBver)
if @TEMPDBuser is not null   
  dbcc setinstance ('SQLServer:User Settable', 'Query', 
   'TempDB user store KB', @TEMPDBuser)
if @TEMPDBsort is not null   
  dbcc setinstance ('SQLServer:User Settable', 'Query', 
    'TempDB sort store KB', @TEMPDBsort)

En fait, c'est tout. Maintenant, appelez ce code régulièrement et tout fonctionnera.

Compteurs Delta


Faisons maintenant un compteur delta. Ce sera le délai de verrouillage en ms.

dbcc addinstance ('SQLServer:User Settable', 'LOCK ms per s')

Ici, vous pouvez m'objecter qu'une telle métrique existe déjà:

select * from sys.dm_os_performance_counters 
  where counter_name like 'Lock Wait Time (ms)%' 
    and instance_name='_Total'
    and object_name='SQLServer:Locks'

Mais cette métrique n'est finalement cohérente. Comme vous vous en souvenez, si les protons sont instables, alors toute base de données dans l'univers est finalement cohérente. Autrement dit, si vous créez un verrou et regardez la valeur de cette métrique, alors elle ne grandira pas! Et seulement à la fin de la serrure, sa valeur augmentera fortement pendant tout le temps d'attente. Sur le graphique, au lieu du «plateau» attendu, vous obtiendrez un pic net, ce qui gâche également votre échelle le long de l'axe Y.

Le même problème est avec kvery:

select sum(wait_time_ms) 
  from sys.dm_os_wait_stats 
  where wait_type like 'LCK_%'

Par conséquent, nous devons ajouter l'heure des actuelles à l'heure des verrous «terminés»:

declare @NEWlock bigint
select @NEWlock=sum(wait_time_ms) 
  from sys.dm_os_wait_stats 
  where wait_type like 'LCK_%' -- finished waits
select @NEWlock=@NEWlock+isnull(sum(waittime),0) 
  from master.dbo.sysprocesses 
  where blocked>0 and lastwaittype like 'LCK_%' -- waits in progress

Cette requête donne déjà le plateau correct sur le graphique.

Maintenant, nous devons considérer le delta de la valeur précédente. Il y a deux façons:

  • Concevez un processus comme un travail qui est appelé fréquemment (disons, une fois par minute). Créez une étiquette pour stocker les valeurs précédentes. En fait, il est souvent mauvais d'appeler job (overhead + competition for the execution history of Jobs), et vous pouvez vous passer d'une plaque supplémentaire:
  • Créez un processus qui s'exécute dans une boucle sans fin avec waitfor. Le processus se souvient de la valeur précédente dans la variable. Nous traiterons le processus comme un travail avec deux planifications: au démarrage et une fois par heure (en cas de chute du travail, il redémarrera)

Dans le second cas, il est tout à fait possible de faire un cycle toutes les cinq secondes voire plus souvent. Ainsi, nous obtenons:

declare @OLDlock bigint, @NEWlock bigint
declare @lock int, @seconds int = 5 -- must match WAITFOR
loop:
  set @OLDlock=@NEWlock -- shift new to old
  select @NEWlock=sum(wait_time_ms) 
    from sys.dm_os_wait_stats 
	where wait_type like 'LCK_%' -- finished waits
  select @NEWlock=@NEWlock+isnull(sum(waittime),0) 
    from master.dbo.sysprocesses 
	where blocked>0 and lastwaittype like 'LCK_%' -- waits in progress
  set @lock=(@NEWlock-@OLDlock)/@seconds -- this is delta
  if @lock is not null    
    dbcc setinstance ('SQLServer:User Settable', 'Query', 
	  'LOCK ms per s', @lock)
  waitfor delay '00:00:05' 
goto loop

Veuillez noter que la première fois que vous exécutez une boucle DBCC, elle sera ignorée (ce qui est correct), car @OLDlock (et fermer à clé) sera nul.

More articles:


All Articles