SQL Server : Réduire l'E/S de disque
Le réglage et l'indexation de requête est une façon efficace de réduire l'E/S de disque logique et physique.
Extrait de « SQL Server DMV Starter Pack, » publié par Red Gate Books (2010).
Glenn Berry, Louis Davidson et Tim Ford
Il y a un besoin persistant pour minimiser les e/s logiques et physiques. La collection d'objets de gestion de base I/O-liées (OGD) aide à étudier, e/spécifiquement, s physiques qui se déroulent sur votre système, lorsque les données sont écrites et la lecture du disque.
L'offices du tourisme dans cette catégorie fournit une image explicite des e/s de disque du point de vue du sous-système disque. Ils nous montrent, par exemple, comment les e/s est distribuée sur plusieurs fichiers sur le disque, endroits où les e/s est devenir un goulot d'étranglement et résultant en stalles de I/O et ainsi de suite. Vous pouvez utiliser ces informations pour optimiser l'architecture du sous-système disque. Vous pouvez également recueillir des données et utilisez-le pour appuyer les demandes aux responsables d'unités opérationnelles pour une plus grande capacité de stockage.
Naturellement, des e/s physiques est inévitable. SQL Server doit écrire des données d'application sur le disque. Il doit également écrire dans le journal des transactions pour chaque insert, update et delete et même pour les opérations en bloc. Toutefois, avant de sauter à la conclusion que vous avez besoin tout simplement plus de puissance de disque, n'oubliez pas il y a que beaucoup que vous pouvez faire sur le plan de requête tuning et indexation afin de minimiser les inutiles logiques et physiques des e/s.
Vous devriez considérer les informations I/O dérivées de l'OMD dans ce sous-segment (tous qui commence par « sys.dm_io_ »), ainsi que des données à partir d'autres vues de gestion dynamique (DMV) faisant référence à des performances d'e/s d'une certaine manière, y compris :
- sys.dm_exec_query_stats – e/s qui en a coûté au fil du temps qu'il a été exécuté une requête donnée
- sys.dm_exec_connections – e/s qui a eu lieu sur ce sujet
- sys.dm_exec_sessions – e/s qui a eu lieu au cours de cette session
- sys.dm_os_workers – e/s en attente pour un thread de travail donné
Toutes les requêtes contenues dans cet article fonctionnent avec SQL Server 2005, 2008 et 2008 R2, et tous doivent obtenir l'autorisation View Server State.
Enquêter sur les goulots d'étranglement de disque via étals d'e/s
La DMV nous allons utiliser ici est sys.dm_io_virtual_file_stats, qui qualifie de documentation en ligne de SQL Server : "Retourne les statistiques de I/O pour les fichiers journaux et de données. Cette vue de gestion dynamique remplace la fonction fn_virtualfilestats."
Cette DMV accepte deux arguments : database_id et file_id. Vous pouvez spécifier NULL pour une ou l'autre. Dans ce cas, il retournera des informations sur toutes les bases de données ou tous les fichiers.
Notez que cette DMV est cumulative. En d'autres termes, les valeurs dans les colonnes de données incrémentent continuellement depuis le point lorsque le serveur a été redémarré dernière. Cela signifie que vous devez prendre une mesure de référence, suivie de la mesure réelle. On soustrait les deux, afin que vous puissiez voir où s'accumulent les I/O.
Ce script vous permet de voir le nombre de lectures et écrit sur chaque fichier de données et de journal pour chaque base de données en cours d'exécution sur une instance de SQL Server. Elles sont triées par temps de décrochage I/O moyen, en millisecondes :
-- Calculates average stalls per read, per write, and per total input/output -- for each database file. SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
Cette requête affichera les fichiers en attente la plus longue pour les e/s disque. Il peut vous aider à décider où se trouvent les fichiers individuels basés sur vos ressources de disque disponible. Vous pouvez également utiliser cela pour contribuer à persuader quelqu'un comme un SAN ingénieur que SQL Server est voyant disque goulets d'étranglement pour certains fichiers.
Enquêter sur les goulots d'étranglement disque via dans l'attente d'e/s
Cela prend une approche légèrement différente pour enquêter sur les goulots d'étranglement des e/s disque. Utilisez le sys.dm_io_pending_io_requests DMV, qui qualifie de documentation en ligne de SQL Server : « Retourne une ligne pour chaque demande d'e/s en attente dans SQL Server. »
Les données contenues dans le DMV fournissent un instantané « moment » des demandes d'e/s en attente sur votre système, juste au moment où que vous exécutez le script :
-- Look at pending I/O requests by file SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs. num_of_reads , fs. num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;
Parce que ces données représentent un instantané de point-à-temps d'activité, vous voudrez exécuter cette requête plusieurs fois pour voir si les mêmes fichiers (et les mêmes lettres de lecteur) s'affichent toujours en haut de la liste. Si cela arrive, c'est la preuve des goulets d'étranglement I/O pour cette lettre de lecteur ou le dossier particulier. Vous pouvez utiliser ceci pour aider à convaincre votre SAN ingénieur système éprouvait des problèmes de I/O pour un LUN particulier.
Les deux dernières colonnes dans la requête retournent le nombre cumulé de lectures et d'écritures pour le fichier étant donné que SQL Server a été démarré (ou depuis le fichier a été créé, selon ce qui était plus court). Cette information est utile lorsque vous essayez de décider quel niveau RAID à utiliser pour une lettre de lecteur spécifique. Par exemple, les fichiers avec l'activité d'écriture plus habituellement effectuera mieux sur un RAID 10 LUN qu'ils vont sur un LUN de RAID 5.
Connaître le ratio de lecture/écriture relative pour chaque fichier peut vous aider à placer vos fichiers de base de données sur un numéro d'unité logique appropriée. Ceci, à son tour, aidera vous syntoniser vos requêtes pour une meilleure efficacité.
.png)
.png)
.png)
Glenn Berry travaille comme architecte de base de données à NewsGator Technologies, à Denver, au Colorado. Il est MVP SQL Server et possède une collection entière de certifications Microsoft, y compris les MCITP, MCDBA, MCSE, MCSD, MCAD et MCTS, ce qui prouve qu'il aime à faire des tests.
Louis Davidson a été dans l'industrie informatique depuis 16 ans comme architecte et développeur de base de données. Il est un MVP de Microsoft SQL Server depuis six ans et a écrit quatre livres sur la conception de base de données. Actuellement, il est l'architecte de données et parfois DBA pour le Christian Broadcasting Network, soutien des bureaux à Virginia Beach, Virginie et Nashville, au Tennessee.
Timothy Ford J'ais MVP SQL Server et travaille avec SQL Server depuis plus de 10 ans. Il est le principal DBA et expert en la matière pour la plateforme SQL Server pour la santé de spectre. Il a été écrit au sujet de la technologie depuis 2007 pour une variété de sites Web et gère son propre blog à thesqlagentman.com, couvrant SQL comme thèmes de développement bien que le télétravail et professionnel.**
En savoir plus sur « SQL Server DMV Starter Pack » à red-gate.com/our-company/about/book-store.