Discussion :

[vinch999]

Bonjour,

Je souhaite faire des stats sur l'utilisation de ma db.
Je souhaite savoir quelles sont les tables les plus lues (SELECT) par les utilisateurs, je suis plus intéressé par une tendance que par un chiffre exact.
Point important , je souhaite pouvoir exclure certains users de ces statistiques. Idéalement exclure les schemas qui ne m'intéressent pas.

J'ai trouvé une requête qui permet d'avoir de telles stats mais je ne peux faire intervenir la notion de user dedans.

La voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
	OBJECT_SCHEMA_NAME(s.object_id) as nom_schema
	,object_name(s.object_id) as nom_table
	,SUM(user_seeks + user_scans + user_lookups) as lectures
	,SUM(user_updates) as ecritures
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id
WHERE 
	objectproperty(s.object_id,'IsUserTable') = 1
	/*Database ciblée*/
	AND s.database_id = db_id('maDatabase')
	/*Exclusion schemas*/
	AND OBJECT_SCHEMA_NAME(s.object_id) NOT IN ('staging','log') 
GROUP BY OBJECT_SCHEMA_NAME(s.object_id),object_name(s.object_id)
ORDER BY lectures DESC

Une idée ? Un moyen de grouper par utilisateur? J'imagine que ce n'est pas possible avec cette query...

Merci d'avance ,
Vinc

[elsuket]

Bonjour,

Un moyen de grouper par utilisateur?

L'audit de l'utilisation des index d'une table est une chose, celui qui concerne les utilisateurs y ayant eu accès en est une autre
Pour la seconde, il vous faut passer par un audit de sécurité pour la base de données concernée : là vous pourrez filtrer sur les tables, et donc les schémas à auditer.
Cela est détaillé dans le chapitre 20 du livre référencé dans ma signature

@++

[Bernardos]

En ce qui concerne la partie stat sans lien avec les utilisateurs.
j'aime cette requête trouvée sur stackoverflow

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select  db_name(US.database_id)
	, object_name(US.object_id)
	, I.name as IndexName
	, OS.leaf_allocation_count
	, OS.nonleaf_allocation_count
	, OS.leaf_page_merge_count
	, OS.leaf_insert_count
	, OS.leaf_delete_count
	, OS.leaf_update_count
	, *
from	sys.dm_db_index_usage_stats US
	join sys.indexes I 
		on I.object_id = US.object_id 
		and I.index_id = US.index_id
	join sys.dm_db_index_operational_stats(db_id(), null, null, null) OS
		on OS.object_id = I.object_id and OS.index_id = I.Index_id
where	I.type <> 0  -- not heap
	and object_name(US.object_id) not like 'sys%'
	and i.type = 1 --pour n'avoir que les tables, pour avoir tous les index mettre cette ligne en commentaire
order by	OS.leaf_allocation_count desc, 
		OS.nonleaf_allocation_count desc, 
		OS.leaf_page_merge_count desc,
		US.User_updates desc, 
		US.User_Seeks desc, 
		US.User_Scans desc, 
		US.User_Lookups desc

elle donne plus de détail

Cordialement,

[SQLpro]

Cette requête est assez stupide car elle ne prend pas en compte les tables et filtre sur des noms ne commençant pas par sys ce qui n enlève pas les objets systèmes

A Plus

[vinch999]

Je suis finalement passé par un audit de sécurité comme l'a suggéré elsuket.
Ca semble répondre à mon besoin merci