Comment find les statistics de lecture / écriture de la table SQL Server?

• sys.dm_db_index_physical_stats (taille et fragmentation)
• sys.dm_db_index_usage_stats (utilisation, nombre d'parsings / searchs / mises à jour, etc.)
• sys.dm_db_index_operational_stats (activité en cours sur l'index)

Rappelez-vous que 'table' signifie l'index clusterisé ou le 'tas'.

La requête suivante peut être utilisée pour find le nombre de lectures et d'écritures sur toutes les tables d'une database. Ce résultat de requête peut être exporté vers un file CSV, puis en utilisant des formules Excel, vous pouvez facilement calculer le rapport lecture / écriture. Très utile lors de la planification d'index sur une table

 DECLARE @dbid int SELECT @dbid = db_id('database_name') SELECT TableName = object_name(s.object_id), Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates) 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 AND s.database_id = @dbid GROUP BY object_name(s.object_id) ORDER BY writes DESC 

Pour déterminer un facteur de remplissage approprié pour les index d'une table, vous devez examiner le nombre de divisions de page qui se produisent. Ceci est montré dans sys.dm_db_index_operational_stats :

Nombre d' allocations de feuilles : nombre total de divisions de page au niveau feuille de l'index.

Nombre d'allocations non feuilles : Nombre total de divisions de page au-dessus du niveau feuille de l'index.

Nombre de fusion de pages feuille : nombre total de pages fusionnées au niveau feuille de l'index.

Après avoir creusé un peu, j'ai vu quelques articles qui disent que les numéros de page divisés par les DMV ne sont pas très utiles (je ne l'ai pas confirmé personnellement), mais il y a aussi un countur de performance "page splits / sec" (mais c'est seulement au niveau de l'instance SQL Server).

J'utilise la règle empirique selon laquelle les tables ordinaires utilisent le facteur de remplissage par défaut de 90%, les tables d'insertion élevées quelque part entre 70 et 85% (en fonction de la taille des lignes). Les tables en lecture seule peuvent utiliser un facteur de remplissage de 100%

Si vous avez un bon index clusterisé (c.-à-d. Toujours croissant, unique, étroit), les véritables problèmes déterminants pour le facteur de remplissage sont la façon dont la table est mise à jour et les types de données des colonnes. Si les colonnes sont toutes de taille fixe (par exemple, integer, Decimal, Float, Char) et non nullable, une mise à jour ne peut pas augmenter le stockage requirejs pour une ligne. Étant donné le bon index clusterisé, vous devez choisir un facteur de remplissage de 90+, même 100, car les divisions de page ne se produiront pas. Si vous avez quelques colonnes de longueur variable (par exemple un Varchar pour contenir le nom d'user) et que les colonnes sont rarement mises à jour après insertion, vous pouvez toujours conserver un facteur de remplissage relativement élevé. Si vous avez des données de longueur très variable (par exemple, paths UNC, champs de commentaire, XML), le facteur de remplissage doit être réduit. Particulièrement si les colonnes sont mises à jour fréquemment et grandissent (comme les colonnes de commentaire). Les index non clusterisés sont généralement les mêmes, sauf que la key d'index peut être plus problématique (non unique, peut-être pas toujours croissante). Je pense que sys.dm_db_index_physical_stats donne les meilleures statistics pour cela, mais c'est après coup. Regardez la taille de l'logging moyenne / min / max, la taille moyenne des fragments, l'espace moyen de la page utilisé pour get une image de la façon dont l'espace d'index est utilisé. HTH.