בדיקת ניצול הנפח של tempdb
עדי כהן
מומחה SQL Server בעל כ-15 שנות ניסיון מגוון בתחום. ר"צ DBA בחברה למסחר פיננסי באינטרנט מטעם נאיה טכנולוגיות.
בתחילת יום עבודה אחד, קיבלתי התרעה על חוסר מקום באחד הדיסקים של אחד השרתים שלנו. בבדיקה מהירה שעשיתי, ראיתי, שאכן אחד מקבצי tempdb, גדל מאד במהלך הימים האחרונים. מאחר שמדובר בשרת פיתוח, הנחתי שמדובר בתהליך שבנה טבלה זמנית (ענקית), אבל סיים לרוץ ופינה את השטח.
ניסיתי לכווץ את הקובץ ע"י הפקודה dbcc shrinkfile. הפקודה רצה ללא בעיה, אבל גודלו של tempdb לא השתנה. בשלב הזה החלטתי לראות מהם גודלי הקבצים של tempdb וכמה מנפחם אכן תפוס ע"י נתונים.
את המידע הזה אפשר לקבל באמצעות השאילתה הבא:
USE [tempdb]
GO
SELECT mf.file_id,
df.name as LogicalName ,
mf.type_desc,
FILEPROPERTY(df.name,'SpaceUsed')*8/1024.0 as SpaceUsedMB,
(df.size-FILEPROPERTY(df.name,'SpaceUsed'))*8/1024.0 as AvailableSpaceMB,
df.size * 8/1024.0 CurrentSizeMB, mf.size * 8/1024.0 as InitialSize_MB,
LTRIM(CASE mf.is_percent_growth
WHEN 1 THEN STR(mf.growth) +' %'
ELSE STR(mf.growth*8/1024.0) +' MB'
END) as AutoGrow
FROM sys.master_files
mf INNER JOIN sys.database_files df
ON mf.file_id = df.file_id
WHERE mf.database_id = DB_ID()
למרבה ההפתעה, למרות שרוב המפתחים עדין לא הגיעו, ומי שהגיע לא ממש הספיק לעשות יותר מידי דברים היום, ראיתי שאכן הקובץ נמצא ברובו המוחלט בשימוש. החלטתי לנסות לראות מה תופס את המקום בקובץ.
הרצתי את השאילתה הבאה, שמראה את הגודל של כל הטבלאות בבסיס הנתונים:
use tempdb
go
SELECT schema_name(schema_id) + o.name as TableName,
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * 8192) / 1024)/1024)) ASTotalSpaceUsedInMB,
f.name As FileGroupName
FROM sys.sysindexes i (NOLOCK)
INNER JOIN sys.objects o (NOLOCK)
ON i.id = o.object_id
INNER JOIN sys.filegroups f
ON i.groupid = f.data_space_id
WHERE indid IN (0, 1, 255)
AND i.groupid = f.data_space_id
GROUP BY o.schema_id, o.name, f.name
ORDER BY TotalSpaceUsedInMB DESC
go
למרבה ההפתעה, היה מספר קטן מאד של טבלאות, ואף לא אחת מהן היתה מעל מגהבייט אחד. זה לא מה שציפיתי לאחר השאילתה הראשונה.
בשלב הזה התחלתי לחשוב מה עוד משתמש ב tempdb ומנצל מקום בקובץ. הדבר הראשון שחשבתי עליו היה ה-version store, שמחזיק גרסאות של נתונים לטובת snapshot isolation level, טריגרים וכד'. בדיקה ב-sys.databases, העלתה שאין לנו מסד נתונים שמופעלת בו האופציה snapshot isolation level. טריגרים קיימים במערכת, אבל מאחר שכמות המקום התפוסה בקובץ עלתה על 50 GB, הנחתי שזאת איננה הבעיה.
בדיקות נוספות שביצעתי היו לבדוק האם יש לי טרנזקציה גדולה פתוחה, והאם מישהו, שכרגע מחובר לשרת, מבצע פעולת שינוי נתונים גדולה. כל הבדיקות הראו תשובה שלילית. עכשיו הגיע הזמן להתחיל להשתמש בגוגל, שאכן לא איכזב .
תוך כדי חיפוש בגוגל, נודע לי על מספר DMV, שאני מודה, שלא היכרתי אותם קודם. ה-DMV הראשון הוא sys.dm_db_file_space_usage. המספק מידע על הקצאת דפים ב tempdb. תוצאת השאילתא הראתה שעבור version store הוקצתה כמות קטנה מאד של דפים. גם עבור user objects הוקצה מספר קטן של דפים. לעומת זאת, למבנים פנימיים של השרת (internal_objects_reserved_page_count) הוקצה הרוב מוחלט של הקובץ.
בשלב הבא השתמשתי בעוד שני DMVs:
sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
שני אלו מראים נתונים המשלימים זה את זה. שניהם מראים את הכמות ואת סוג הדפים המוקצים לטובת session/task. sys.dm_db_task_space_usage מראה את ההקצאה של הדפים לתהליכים הפועלים באותו רגע ואילו sys.dm_db_session_space_usage מראה את ההקצאה שכל session קיבל את עד להרצה של הbatch/rpc הנוכחי. באמצעות הDMVs האלו יכולתי לראות לאיזה session הוקצה המקום. לאחר מכן נשאר לי לבדוק מה ה-session עושה ולהתחיל את הטיפול בבעיה.
דרך אגב, אחת הבעיות שגיליתי היא שהתצורה של tempdb שלנו שגויה לחלוטין. אמנם דאגנו לבנייה של מספר קבצים על מספר דיסקים, אבל הגדלים ההתחלתיים של כל קובץ היו גרועים. וכן קצב ההרחבה האוטומטי היה 10 אחוז ולא מספר קבוע. כדאי לכולנו לבדוק מידי פעם את המצב של ה tempdb.
למאמר מצוין מבית Idera על קביעת תצורה של tempdb:
