TempDB – אופטימיזציה

בס"ד

TempDB – אופטימיזציה

TempDB הוא אחד מבסיסי הנתונים של המערכת שמגיע בהתקנה של כלSQL Instance  עם הגדרות בררת מחדל שחשוב מאוד לשנות אותם בשרתי ייצור. TempDB שימש מאז ומעולם כבסיס נתונים מרכזי להרבה פעולות כגון:

1. Worktables (sort, hash tables, order by, …)
2. table variables (@) and temp tables (# and ##)
3. DBCC CHECKDB

החל מגרסת SQL 2005 נוספו לו עוד הרבה שימושים בעקבות מנגנון ה Version Store: (http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/22/managing-tempdb-in-sql-server-tempdb-basics-verison-store.aspx)

1. Online index operations
2. MARS (Multiple active results sets)
3. Snapshot Isolation
4. Triggers (INSERTED and DELETED)

ומכאן החשיבות הרבה לביצוע אופטימיזציה נכונה עבור ה TempDB.

אחד המרכיבים המרכזיים באופטימיזציה של ה TempDB הוא נושא ה allocation, אז לפני שנתחיל במספר כללי אצבע על ה TempDB קצת על Allocation bottleneck.

Allocation bottleneck

ישנם שלושה סוגי דפים המאכלסים את את ה allocation structures:

1. PFS (Page Free Space) – מכיל מידע עבור כל עמוד בגודל של 1 byte  לעמוד, סה"כ מידע על 8088 עמודים.  המידע שנשמר עבור כל עמוד הוא:
2. כמה מקום פנוי וזמין יש בעמוד.
3. האם הוא מוקצה או לא.
4. האם יש בעמוד ghost records – ( כאשר מוחקים שורה – היא לא נמחקת בפועל אלא מסומנת – והסימון הזה נקרא ghost).
5. GAM (Global Allocation Map) – מכיל מידע על extent , לדעת האם הוא פנוי או לא, ‘1’  מסמן שה extent  זמין להקצאה. סה"כ מידע על 64000 extents  שזה אומר על 4GB.
6. SGAM (Shared Allocation Map) – מכיל מידע על extent , לדעת האם הוא מסוג mixed extent . '1' מסמן שהוא mixed ושיש בו עוד עמודים פנויים. (mixed extent  נועדו לשמור את 7 העמודים הראשונים של כל אוביקט. ברגע שיש לאוביקט 8 עמודים ומעלה הוא מאוכלס כולו ב uniform extents ולא ב mixed extent). גם כאן סה"כ מידע על 64000 extents  שזה אומר על 4GB.

ולדוגמא אם יש לנו בסיס נתונים בגודל 16GB – זאת אומרת שיש לנו:

1. 256 PFS
2. 4 GAM
3. 4 SGAM

חשוב לציין שלכל FILE של בסיס הנתונים את יש את שלושת סוגי הדפים האלו משל עצמו.

עכשיו ננסה לראות מה קורה בפועל בתהליך של ה allocation.

הקצאה של עמוד מתרחשת כאשר מתרחשת אחת משתי האפשרויות – או כאשר אוביקט נוצר לראשונה או כאשר יש פעולת insert שמצריכה עמוד חדש.

בשלב הראשון ה-SQL בודק האם ב extent  הייעודי יש עמוד פנוי להקצאה ע"י  חקירת עמוד ה PFS תוך שימוש בנעילה מסוג SH  על העמוד. במידה ונמצא עמוד זמין להקאצה הוא מוקצה לאוביקט והמידע עבור העמוד שהוקצה מעודכן בעמוד ה PFS ע"י שימוש בנעילה מסוג UPDATE .

במידה ולא נמצא עמוד פנוי ב  extent  הייעודי, ה SQL  מחפש בעמוד ה  GAM   (גם כאן תחת נעילה מסוג SH)  extent  פנוי  – במידה ולא נמצא הוא עובר לחפש בעמוד ה GAM הבא. כאשר נמצא extent  להקצאה ה SQL מבצע נעילה מסוג UPDATE  על עמוד ה GAM על מנת לעדכן אותו.

כאשר מדובר על אוביקט שקטן שווה ל  8 pages  , ה SQL מחפש עבור עמוד ב mixed extent  – החיפוש מתבתע בעמודי ה SGAM  וגם כאן תחת נעילה מסוג SH. במידה ונמצא mixed extent ה SQL מבצע נעילה מסוג UPDATE על ה SGAM  על מנת לעדכן את המידע בו.

באותו מידה וההפך מתרחש כשאר עמוד הוא deallocated  או אוביקט נמחק  – כל ה allocation structures אמור להיות מעודכן בהתאם. במערכות כבדות עם הרבה כתיבות ו\או מחיקות המון threads   ימתינו בתור לביצוע נעילות מסוג X דבר שיגרום ל allocation bottleneck.

החל מגרסת SQL2005  ה caching mechanism של TEMPDB שופר משמעותית דבר שאמור להפחית את ה allocation contention.

נמשיך על האופטימיזציה של TEMPDB.

Managing TempDB in SQL Server

אז אייך אני בעצם מקנפג את ה TEMPDB שלי בצורה הטובה ביותר? איפה לשים אותו? מה הגודל שיש לתת לו? וכמה קבצים הוא צריך להכיל?

מספר קבצי ה DATA עבור ה TempDB

כלל אצבע –עבור כל מעבד בין אם הוא לוגי או פיזי אנחנו נטצרך data file.

אם למשל למכונה יש  4 quad-core cpus זאת אומרת 16 cores – אנחנו נצטרך 16 data files עבור TempDB.

הסיבה – עבור כל מעבד שה-SQL רואה הוא מיצר  logical scheduler  – ככה שכל  logical scheduler  יעבוד מול data file שלו וזה ימנע מאיתנו allocation contention – וישפר ביצועים.

הסבר על ה Logical Scheduler ניתן למצוא כאן:
http://blogs.msdn.com/b/saponsqlserver/archive/2011/04/18/how-many-logical-processors-does-sql-server-2008-r2-enterprise-edition-support.aspx
ומידע נוסף ומורחב על עבודה עם TempDB החל מגרסת 2005 ניתן למצוא כאן:
http://technet.microsoft.com/en-us/library/cc966545.aspx.

ה-allocation contention נכונים לכל סוגי הדפים,  בין אם זה SGAM  בגרסת SQL2000 ובין אם זה PFS בשאר הגרסאות.

בגרסת SQL2005 הוסיפו  temp table caching דבר שבהחלט עוזר לשיפור ביצועי ה-TempDB, אבל במערכות כבדות במיוחד ה allocation contention  עדיין קורה. ומכאן החשיבות לגדיר ל TempDB מספר קבצים זהה למספר המעבדים.

איפה לשים את ה TempDB?

על דיסק מהיר. למה? כי ה TEMPDB משמש את ה SQL SERVER  במהלך העבודה השוטפת. למשל יצירה של טבלאות HASH  או לצורך מיון של נתונים.
אמנם, משפט זה מתאים לכל הבסיס נתונים שלך – עדיף שכולם ישבו על דיסק מהיר, אבל יש לקחת בחשבון שצורת העבודה של  TEMPDB שונה משאר בסיס נתונים ולכן מומלץ ליצור דיסק יעודי עבור TEMPDB ואפילו אם אפשר, שימוש ב-RAM  דיסק.

איזה גודל להקצות ל TempDB?

אז לזה אין באמת תשובה ברורה, לכל אחד יש שימוש שונה וצריכה שונה ולכן הדרך הטובה ביותר היא לאמוד את הגודל הרצוי על ידי הרצה של האפליקתיות בסביבת בדיקות, אח"כ יש להגדיל בשרת הייצור את הגודל המתאים (וכמובן עדיף לתת קצת יותר שטח כמקדם ביטחון) –  בכל מקרה חשוב מאוד – לא לתת ל TEMPDB לגדול בעצמו על ידי הגדילה האוטומטית!

 אני מצרף סקריפט שכתבתי שיעזור לכם להגדיר נכון את ה TEMPDB, הוא ייצר לכם סקריפט מוכן להרצה עם כל הפרמטרים שדברנו עלהם: העברה לכונן יעודי שתבחרו , מספר קבצים ובעלי גודל זהה.

 בהצלחה

USE [master]

GO

Create Proc TempDBConfiguration
      @Path varchar(4000) = NULL,
      @TotalSIZE varchar(4000)  = '50',
      @FILEGROWTH varchar(1000) = '10%'
As
Declare @ProcessorCount int ,
            @COUNT int,
            @SQL varchar(1000),
            @NAME varchar(1000),
            @FILENAME varchar(1000),
            @SIZE varchar(1000)

IF    @Path is null
Begin
            Select      @Path = LEFT(filename,PATINDEX ( '%\%' , REVERSE(filename))-1)
            From  tempdb.sys.sysfiles
            Where fileid = 1
End

If          OBJECT_ID ('Tempdb..#SVer') is not null Drop Table #SVer

create      table #SVer(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))

insert      #SVer exec master.dbo.xp_msver
SELECT      @ProcessorCount = Internal_Value
from  #SVer
where Name = N'ProcessorCount'

Set         @SIZE = @TotalSIZE / @ProcessorCount

Declare     TempdbFiles Cursor for
            Select      name,Right(filename,PATINDEX ( '%\%' , REVERSE(filename))-1)
            From  tempdb.sys.sysfiles
            Where fileid = 1

OPEN  TempdbFiles
Fetch next from TempdbFiles into @NAME, @FILENAME
While @@FETCH_STATUS = 0
Begin

      SET   @SQL = 'ALTER DATABASE [tempdb]
                  MODIFY FILE (     NAME = '+@NAME+',
                  FILENAME = N'''+@Path+@FILENAME+''' ,
                  SIZE = '+@SIZE+'MB ,
                  FILEGROWTH = '+@FILEGROWTH+')'
      Print (@SQL)
Fetch next from TempdbFiles into @NAME, @FILENAME
End
CLOSE TempdbFiles
DEALLOCATE  TempdbFiles

Select      @NAME = name,
            @FILENAME = Right(filename,PATINDEX ( '%\%' , REVERSE(filename))-1)
From  tempdb.sys.sysfiles
Where fileid = 2

SET   @SQL = 'ALTER DATABASE [tempdb]
            MODIFY FILE (     NAME = '+@NAME+',
            FILENAME = N'''+@Path+@FILENAME+''' ,
            SIZE = '+@SIZE+'MB ,
            FILEGROWTH = '+@FILEGROWTH+')'
Print (@SQL)

Select      @COUNT = COUNT(*)
From  tempdb.sys.sysfiles
Where fileid = 1

While @COUNT < @ProcessorCount

Begin
      Set   @COUNT = @COUNT+ 1
      SET   @SQL = 'ALTER DATABASE [tempdb]
                        ADD FILE (  NAME = N''tempdev'+cast(@COUNT as varchar(3))+''',
                        FILENAME = N'''+@Path+'tempdev'+cast(@COUNT as varchar(3))+'.ndf'' ,
                        SIZE = '+@SIZE+'MB ,
                  FILEGROWTH = '+@FILEGROWTH+')'
      Print (@SQL)

End
Go

נתראה ברשומה הבאה !

The following two tabs change content below.

• Bio
• Latest Posts

ערן שקד

Latest posts by ערן שקד (see all)

• TempDB – אופטימיזציה - 31/08/2012
• Indexes in SQL Server 2 - 13/06/2012
• PARSE בצורה מהירה ואלגנטית - 07/04/2012