Jump to content
  • entries
    292
  • comments
    368
  • views
    59874

The value of sys.dm_io_virtual_file_stats DMV

antonch

Entry posted by antonch ·

608 views

 Share

Όπως αρκετές φορές έχω αναφέρει, τρεις είναι οι παράγοντες που επηρεάζουν την λειτουργία ενός SQL Server και αυτοί είναι: Memory, CPU, I/O.

Αρκετοί συνάδελφοι δηλώνουν λάτρεις των Dynamic Management Views (DMV) μεταξύ αυτών είμαι και εγώ , όμως το μυστικό σε αυτές είναι να τις χρησιμοποιείς έτσι ώστε να παίρνει από αυτές συνδυαστικά τις περισσότερες φορές τη μέγιστη δυνατή πληροφορία που αυτές μπορούν να σου σώσουν.

Σε αυτό το post θα σας δείξω πως χρησιμοποιώντας μια εξ αυτών θα μπορέσουμε να αποφανθούμε αν έχουμε IO πρόβλημα στον SQL Server μας γενικά, αλλά και ειδικά σε ποια ή ποιες database(s).

Δεν θα σας απασχολήσω με την γενική σύνταξη της DMV αυτής ούτε και το τι δείχνει, καθώς είναι εύκολο να το κάνει κάποιος τα BOL.

Για λόγους καθαρά πρακτικούς όμως θα αναφέρω ότι η sys.dm_io_virtual_file_stats επιστρέφει Ι/Ο στατιστικά για τα database files.

Εκτελώντας απλά ένα

Query 1
  1. select * from sys.dm_io_virtual_file_stats(null,null)

θα πάρουμε ένα αρκετά μεγάλο, ανάλογα πάντα με τον αριθμό των βάσεων και τα αρχεία που έχουμε σε αυτές.

Επίσης αρκετά μεγάλος είναι και ο αριθμός των πληροφοριών που μας επιστέφει η dmv αυτή. Έτσι κάπως εύκολα χάνεται κάποιος και δεν μπορεί να βγάλει το επιθυμητό αποτέλεσμα. Αν όμως αρχίσει να φιλτράρει το τι πληροφορία θα χρησιμοποιήσει από την DMV αυτή τότε πραγματικά θα πάρει το αποτέλεσμα που επιθυμεί. Αφού έχουμε διαβάσει από τα BOL το τι μας επιστρέφει αυτή εύκολα μπορούμε να υπολογίσουμε το I/O για κάθε database χρησιμοποιώντας τα num_of_bytes_read και num_of_bytes_written πεδία αυτής. Αυτό μπορεί να γίνει με το παρακάτω query

Query 2
  1. SELECT
  2.     DB_NAME(database_id) AS database_name,
  3.     CAST(SUM(num_of_bytes_read + num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB
  4. FROM
  5.     sys.dm_io_virtual_file_stats(NULL, NULL)-- AS d
  6. GROUP BY
  7.     database_id
  8. ORDER BY
  9.     2 DESC

 

Όμως θα ήθελα να έχω ακόμα μία στήλη που να μου δείχνει το ποσοστό Ι/Ο σε κάθε βάση σε σχέση με το συνολικό Ι/Ο που έχω. Έτσι το παραπάνω query διαμορφώνεται ως εξής:

Query 3
  1. WITH DB_IO_STATISTICS
  2. AS
  3. (
  4.   SELECT
  5.     DB_NAME(database_id) AS database_name,
  6.     CAST(SUM(num_of_bytes_read + num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB
  7.   FROM
  8.     sys.dm_io_virtual_file_stats(NULL, NULL)
  9.   GROUP BY
  10.     database_id
  11. )
  12. SELECT
  13.   database_name,
  14.   db_IO_in_MB,
  15.   CAST(db_IO_in_MB / SUM(db_IO_in_MB) OVER() * 100  AS DECIMAL(5, 2)) AS [% IO]
  16. FROM
  17.     DB_IO_STATISTICS
  18. ORDER BY
  19.     [% IO] desc;

 

Στις περισσότερες περιπτώσεις το παραπάνω query και το αποτέλεσμα του μας κάνει, και μπορούμε να εντοπίσουμε αυτές τις databases που έχουν πρόβλημα Ι/Ο. Όμως ποιο ή ποια είναι τα αρχεία από αυτή ή αυτές που πραγματικά έχουν πρόβλημα Ι/Ο, ώστε να τα μεταφέρουμε σε άλλο disk array; Απάντηση στο ερώτημα αυτό δίνει το παρακάτω query

Query 4
  1. WITH DB_IO_STATISTICS
  2. AS
  3. (
  4.   SELECT
  5.     DB_NAME(fs.database_id) AS database_name,
  6.     mf.name as logical_file_name,
  7.     CAST(SUM(fs.num_of_bytes_read + fs.num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB
  8.   FROM
  9.     sys.dm_io_virtual_file_stats(NULL, NULL) as fs
  10.   INNER JOIN
  11.     sys.master_files as mf on fs.database_id=mf.database_id and fs.file_id = mf.file_id
  12.   GROUP BY
  13.     fs.database_id,mf.name
  14. )
  15. SELECT
  16.   database_name,
  17.   logical_file_name,
  18.   db_IO_in_MB,
  19.   CAST(db_IO_in_MB / SUM(db_IO_in_MB) OVER() * 100  AS DECIMAL(5, 2)) AS [% IO]
  20. FROM
  21.     DB_IO_STATISTICS
  22. ORDER BY
  23.     [% IO] desc;

 

Από το παραπάνω εύκολα βλέπουμε ποιο file και σε ποια database έχει I/O issues. Όμως και αυτό δεν είναι αρκετό. Αν για παράδειγμα δούμε ότι κάποιο file έχει 80% Ι/Ο δεν σημαίνει απαραίτητα ότι είναι και προβληματικό. Για να μπορέσουμε να αποφανθούμε θα πρέπει να ελέγξουμε το πόσο περιμένει ή περιμένουν ο χρήστης ή οι χρήστες μέχρι να εκτελέσουν ένα read ή ένα write και αυτό διότι άλλο να είναι ένας χρήστης στην βάση και άλλο πολλοί.

Για να γίνει αυτό θα πρέπει να δούμε τα στοιχεία που τα πεδία io_stall_read_ms και io_stall_write_ms της DMV μας δίνουν και να τα συνδυάσουμε με τα μέχρι τώρα διαμορφωμένα στοιχεία. Αυτά δείχνουν πόσο περιμένουν οι χρήστες για να κάνουνε ένα read ή write. Βέβαια υπάρχει και το io_stall που δείχνει το σύνολο που οι χρήστες περιμένουν για read και write. Εδώ επειδή δεν μας ενδιαφέρει να το αναλύσουμε περισσότερο αν και είναι εύκολο σαν άσκηση να το κάνετε θα πάρουμε το σύνολο και το παραπάνω query μετατρέπεται ως εξής

Query 5
  1. WITH DB_IO_STATISTICS
  2. AS
  3. (
  4.   SELECT
  5.     DB_NAME(fs.database_id) AS database_name,
  6.     mf.name as logical_file_name,
  7.     CAST(SUM(fs.num_of_bytes_read + fs.num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB,
  8.     SUM(fs.io_stall) as io_stall
  9.   FROM
  10.     sys.dm_io_virtual_file_stats(NULL, NULL) as fs
  11.   INNER JOIN
  12.     sys.master_files as mf on fs.database_id=mf.database_id and fs.file_id = mf.file_id
  13.   GROUP BY
  14.     fs.database_id,mf.name
  15. )
  16. SELECT
  17.   database_name,
  18.   logical_file_name,
  19.   db_IO_in_MB,
  20.   CAST(db_IO_in_MB / SUM(db_IO_in_MB) OVER() * 100  AS DECIMAL(5, 2)) AS [% IO],
  21.   CAST(io_stall / 1000. as DECIMAL(10,2)) as io_stall_in_seconds,
  22.   CAST(100. * io_stall / SUM(io_stall) OVER() AS DECIMAL(10, 2)) AS [% IO stall]
  23. FROM
  24.     DB_IO_STATISTICS
  25. ORDER BY
  26.     [% IO stall] desc

 

Και επειδή πιθανότατα κάποιος να θελήσει να δει όλα τα παραπάνω αλλά σε επίπεδο drive στο οποίο φυσικά έχει database files ώστε να δει που μπορεί να μεταφέρει database files ώστε να εξομαλύνει το I/O πρόβλημα του, υπάρχει και αυτό σαν δυνατότητα χρησιμοποιώντας την ίδια dmv όπως παρακάτω

Query 6
  1. WITH DB_IO_STATISTICS
  2. AS
  3. (
  4.   SELECT
  5.     left(mf.physical_name,1) as drive,
  6.     CAST(SUM(fs.num_of_bytes_read + fs.num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB,
  7.     SUM(fs.io_stall) as io_stall
  8.   FROM
  9.     sys.dm_io_virtual_file_stats(NULL, NULL) as fs
  10.   INNER JOIN
  11.     sys.master_files as mf on fs.database_id=mf.database_id and fs.file_id = mf.file_id
  12.   GROUP BY
  13.     left(mf.physical_name,1)
  14. )
  15. SELECT
  16.   drive,
  17.   db_IO_in_MB,
  18.   CAST(db_IO_in_MB / SUM(db_IO_in_MB) OVER() * 100  AS DECIMAL(5, 2)) AS [% IO],
  19.   CAST(io_stall / 1000. as DECIMAL(10,2)) as io_stall_in_seconds,
  20.   CAST(100. * io_stall / SUM(io_stall) OVER() AS DECIMAL(10, 2)) AS [% IO stall]
  21. FROM
  22.     DB_IO_STATISTICS
  23. ORDER BY
  24.     [% IO] desc

/*antonch*/

 Share

0 Comments

Recommended Comments

There are no comments to display.

Guest
Add a comment...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
×
  • Create New...