articles

Articles of SQLschool.gr Team

The value of sys.dm_io_virtual_file_stats DMV

Antonios Chatzipavlis

Wednesday 12 October 2011

Όπως αρκετές φορές έχω αναφέρει, τρεις είναι οι παράγοντες που επηρεάζουν την λειτουργία ενός 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
 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
 SELECT
    DB_NAME(database_id) AS database_name,
    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB
FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL)-- AS d
GROUP BY 
    database_id
ORDER BY 
    2 DESC
 

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

Query 3
 WITH DB_IO_STATISTICS
AS
(
  SELECT
    DB_NAME(database_id) AS database_name,
    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB
  FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL)
  GROUP BY 
    database_id
)
SELECT
  database_name,
  db_IO_in_MB,
  CAST(db_IO_in_MB / SUM(db_IO_in_MB) OVER() * 100  AS DECIMAL(5, 2)) AS [% IO]
FROM 
    DB_IO_STATISTICS
ORDER BY 
    [% IO] desc;
 

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

Query 4
 WITH DB_IO_STATISTICS
AS
(
  SELECT
    DB_NAME(fs.database_id) AS database_name,
    mf.name as logical_file_name,
    CAST(SUM(fs.num_of_bytes_read + fs.num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB
  FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) as fs
  INNER JOIN
    sys.master_files as mf on fs.database_id=mf.database_id and fs.file_id = mf.file_id
  GROUP BY 
    fs.database_id,mf.name
)
SELECT
  database_name,
  logical_file_name,
  db_IO_in_MB,
  CAST(db_IO_in_MB / SUM(db_IO_in_MB) OVER() * 100  AS DECIMAL(5, 2)) AS [% IO]
FROM 
    DB_IO_STATISTICS
ORDER BY 
    [% 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
 WITH DB_IO_STATISTICS
AS
(
  SELECT
    DB_NAME(fs.database_id) AS database_name,
    mf.name as logical_file_name,
    CAST(SUM(fs.num_of_bytes_read + fs.num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB,
    SUM(fs.io_stall) as io_stall
  FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) as fs
  INNER JOIN
    sys.master_files as mf on fs.database_id=mf.database_id and fs.file_id = mf.file_id
  GROUP BY 
    fs.database_id,mf.name
)
SELECT
  database_name,
  logical_file_name,
  db_IO_in_MB,
  CAST(db_IO_in_MB / SUM(db_IO_in_MB) OVER() * 100  AS DECIMAL(5, 2)) AS [% IO],
  CAST(io_stall / 1000. as DECIMAL(10,2)) as io_stall_in_seconds,
  CAST(100. * io_stall / SUM(io_stall) OVER() AS DECIMAL(10, 2)) AS [% IO stall]
FROM 
    DB_IO_STATISTICS
ORDER BY 
    [% IO stall] desc
 

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

Query 6
 WITH DB_IO_STATISTICS
AS
(
  SELECT
    left(mf.physical_name,1) as drive,
    CAST(SUM(fs.num_of_bytes_read + fs.num_of_bytes_written) / (1024*1024) AS DECIMAL(12, 2)) AS db_IO_in_MB,
    SUM(fs.io_stall) as io_stall
  FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) as fs
  INNER JOIN
    sys.master_files as mf on fs.database_id=mf.database_id and fs.file_id = mf.file_id
  GROUP BY 
    left(mf.physical_name,1) 
)
SELECT
  drive,
  db_IO_in_MB,
  CAST(db_IO_in_MB / SUM(db_IO_in_MB) OVER() * 100  AS DECIMAL(5, 2)) AS [% IO],
  CAST(io_stall / 1000. as DECIMAL(10,2)) as io_stall_in_seconds,
  CAST(100. * io_stall / SUM(io_stall) OVER() AS DECIMAL(10, 2)) AS [% IO stall]
FROM 
    DB_IO_STATISTICS
ORDER BY 
    [% IO] desc
 

/*antonch*/

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.