כוונון ביצועים ב SQL Server ל- DBA העסוק – חלק 5 - ilDBA Portal

כוונון ביצועים ב SQL Server ל- DBA העסוק – חלק 5

26/03/2014 | פורסם על ידי

בחלק זה של המאמר נדגים איך מנתחים את הפלט של השאילתה שמביאה את ה-Wait Events הארוכים ביותר ומה עושים בכל מקרה כזה.

מאמר זה הוא מאמר בסדרה המתגלגלת. לקריאת המאמרים הקודמים: חלק 1חלק 2חלק 3, חלק 4.

רשימת Wait Events נפוצים ופתרונות – עבור גיבויים ודיסקים

BACKUPIO wait – כאשר משימת גיבוי ממתינה למידע, או ל- BUFFER שמכיל את המידע. ברור שמחיצת הגיבויים תהיה על דיסק או lun יעודי.

Disk I/O waits – סוג זה של המתנות קורה כאשר תהליך ממתין לסיום פעולת I\O מול הדיסקים ומערכת האחסון. ה- I/O waits הנפוצים ביותר הם IO_COMPLETION ASYNC_IO_COMPLETION, WRITELOG ו waits מסוג PAGEIOLATCH_*. בד"כ זה סימן לעומס על הדיסקים ומערכת האחסון. אבל לא בהכרח. Disk I/O waits נובעים גם מאינדקסים חסרים, בעיית ביצועים של שאילתות עםfull table scans או בעיות זיכרון בשרת.

הערה : נבצע בדיקה של I/O waits אם ב- error log של SQL server מופיעות הודעות כמו:

SQL Server has encountered <xx> occurrence(s) of IO requests taking longer than 15 seconds to complete on file <filename>

בשלב הראשון נמצא את ה- I/O ברמת database ע"י השאילתה הבאה :בשביל לבדוק I\O נשתמש ב DMV sys.dm_io_virtual_file_stats, שמקבל 2 פרמטרים: database_id ו- file_id. אם אנו מעוניינים בכל ה- DBs ובכל הקבצים נספק לו כפרמטר את הערך NULL.

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME(database_id) AS [DatabaseName]

, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)]

, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2)))

AS [IO read (MB)]

, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2)))

AS [IO written (MB)]

, SUM(CAST((num_of_bytes_read + num_of_bytes_written)

/ 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)]

FROM sys.dm_io_virtual_file_stats(NULL, NULL)

GROUP BY database_id

ORDER BY [IO stall (secs)] DES

פלט:

iovirtul_results

הסבר: מחשב את מספר ההמתנות (stalls) פר DB ע"י סיכום העמודה io_stall עבור כל DB. התוצאה מחולקת ב-1000 בכדי לבצע המרה מאלפית השנייה לשניות. התוצאה היא של סיכום זמני ההמתנה עבור קריאה, כתיבה וסיכום זמני הכתיבה והקריאה ממוינת ע"פ IO stalls בסדר יורד פר DB.

ניתן לקבל זמן כתיבה וקריאה ממוצעים עבור כל קובץ ב- DB. בד"כ זמנים תקינים הם מתחת ל-20 מילי שניות.

SET TRAN ISOLATION LEVEL READ UNCOMMITTED

SELECT DB_NAME(database_id) AS DatabaseName

, file_id

, io_stall_read_ms / num_of_reads AS 'Average read time'

, io_stall_write_ms / num_of_writes AS 'Average write time'

FROM sys.dm_io_virtual_file_stats(NULL, NULL)

WHERE num_of_reads &amp;amp;gt; 0 and num_of_writes &amp;amp;gt; 0

ORDER BY DatabaseName

dm_io_results2

מידע על כל הקבצים של כל ה- DBs והמיפוי ל- file_id ע"י השאילתה הבאה:

-- Volume info for all databases on the current instance (SQL Server 2008 R2 SP1 or greater)  (Volume Info)

SELECT DB_NAME(f.database_id) AS [DatabaseName], f.file_id, vs.volume_mount_point, vs.logical_volume_name,

CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],

CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],

CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]

FROM sys.master_files AS f WITH (NOLOCK)

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs

ORDER BY f.database_id OPTION (RECOMPILE);

פלט לדוגמא:

master_files_results

זה הכל בינתיים למאמר זה, נתראה במאמר הבא.

The following two tabs change content below.

דוד יצחק

דוד יצחק , DBA אפליקטיבי ותשתיתי של SQL Server, Oracle ,Sybase Anywhere מזה 17 שנה עם התמחות בשיפור ביצועים, שרידות, זמינות גבוהה, רפלקציה, תוספים גיאוגרפיים, אבטחת מידע ו-Business Intelligence. הוא מרכז את פורום DBA צפון של ilOUG ומרצה בפורום משתמשי SQL Server.

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]
מבוא

מבוא לבעיות ביצועים באורקל

The following two tabs change content below.BioLatest Posts עודד רז עודד רז, מנכ"ל חברת בריליקס ומייסד אתר זה. עודד הוא Oracle ACE Director ואחד מה-DBA-ים הבכירים ביותר בישראל, עם מעל 15 שנות ניסיון כ-DBA תשתיתי ואפליקטיבי. לעודד [...]

ה-SQL Performance Analyzer ב-Real Application Testing

בפוסט הקודם הזכרתי בקצרה את ה-SQL Performance Analyzer, בפוסט הזה אני אציג את ה-SPA, יתרונות וחסרונות של המוצר ואיך כדאי להשתמש [...]

מבוא ל- Real Application Testing

הפעם רציתי לסקור feature שלם שנקרא RAT (או בשמו המלא Real Application Testing). ה- feature הזה הוא database option של Oracle Enterprise Edition והוא לא חדש בכלל. הוא הוצג ב- 11gR1 וגם נמצא בשימוש לא מועט בעולם. משום מה, אצלנו בארץ לא יוצא [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: