TempDB – אופטימיזציה - ilDBA Portal

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

31/08/2012 | פורסם על ידי

בס"ד

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

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

ערן שקד – Eran@brillix.co.il
יועץ SQL Server בחברת Brillix
The following two tabs change content below.

ערן שקד

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

5 תגובות ל- “TempDB – אופטימיזציה”

David Itshak | 04/02/2013 בשעה 08:17
commenter

ערן מאמר מצוין !!! . רק כמה תוספות מהניסיון שלי : 1.            קינפוג וכיוונן של TEMPDB   כללי אצבע  : כל קובץ  mdf יהיה 1G ללא גדילה , אלא אם כן : ·         ע"פ הדיון בפרק הנ"ל יסתבר שצריך יותר . ·         מספר קבצי ה- MDF יהיה לכול היותר 8 בין 0.5 ל- 0.25 מספר ה- Cores במכונה  ולא יותר מ 8 .   הסבר מפורט להלן :   Configuring Multiple Tempdb Data Files ·         For SQL Server 2005 and 2008, Microsoft recommends creating 1⁄4 to 1⁄2 the number of files per CPU core, up to a maximum of 8 files, so on an 8-core server you would configure 2 or 4 fies.   ·         This is just for guidance, though; the important point is not to exceed 8 files, as Microsoft’s tests indicate no performance benefit beyond that.   The key takeaways from this chapter are: 1.  There are three types of objects that can be found in tempdb: user-created, internally created, and the version store. 2.  Latch contention is a common problem even in SQL Server 2008 but is easy to resolve and avoid. 3.  You should learn to be familiar with these DMVs to help you troubleshoot urgent tempdb space issues: a.  sys.dm_db_fi le_space_usage b.  sys.dm_db_task_space_usage c.  sys.dm_db_session_space_usage 4.  Appropriate sizing and configuration of tempdb should be paramount for any SQL Server installation to avoid performance issues later on.     דוגמא : יש לנו 4 Cores . מספר קבצי ה- MDF יהיה0.5*4  = 2 דוד יצחק מנהל פורום ORACLE DBA צפון ( ILOUG) BSC Information System, ME System Engineering, MBA

David Itshak | 04/02/2013 בשעה 08:20
commenter

ערן מאמר מצוין !!! . רק כמה תוספות מהניסיון שלי :
1.            קינפוג וכיוונן של TEMPDB
 
כללי אצבע  :
כל קובץ  mdf יהיה 1G ללא גדילה , אלא אם כן :
·         ע"פ הדיון בפרק הנ"ל יסתבר שצריך יותר .
·         מספר קבצי ה- MDF יהיה לכול היותר 8 בין 0.5 ל- 0.25 מספר ה- Cores במכונה  ולא יותר מ 8 .
 
הסבר מפורט להלן :
 
Configuring Multiple Tempdb Data Files
·         For SQL Server 2005 and 2008, Microsoft recommends creating 1⁄4 to 1⁄2 the number of files per CPU core, up to a maximum of 8 files, so on an 8-core server you would configure 2 or 4 fies.
 
·         This is just for guidance, though; the important point is not to exceed 8 files, as Microsoft’s tests indicate no performance benefit beyond that.
 
The key takeaways from this chapter are:
1.  There are three types of objects that can be found in tempdb: user-created, internally created, and the version store.
2.  Latch contention is a common problem even in SQL Server 2008 but is easy to resolve and avoid.
3.  You should learn to be familiar with these DMVs to help you troubleshoot urgent tempdb space issues:
a.  sys.dm_db_fi le_space_usage
b.  sys.dm_db_task_space_usage
c.  sys.dm_db_session_space_usage
4. 
Appropriate sizing and configuration of tempdb should be paramount for any SQL Server installation to avoid performance issues later on.

 

דוגמא :

יש לנו 4 Cores . מספר קבצי ה- MDF יהיה0.5*4  = 2

דוד יצחק

מנהל פורום ORACLE DBA צפון ( ILOUG)

BSC Information System, ME System Engineering, MBA

 

 

 
 

commenter

ערן ,
מאצר יפה מאוד ,שאלה ראשונה בהקשר לגודל קובץ ה – LDF , המאמריםפ רבים שקראתי בנושא גודל ה LDF צריך להיות כפול 2 מגודל הקובץ – DATA .
כלומר עם וקבית כי גודל קבצי ה – DATA הוא 50 MB אז הקובץ – LOG אהיה = 2 * 50 = 100 MB .
מה אתה אומר על כך ?
תודה רבה 
 
איציק 

commenter

שאלה שניה באותו נושא מאמר ( סליחה שלא הכנסתי באותו COMMENT ) .
אני הגעתי לבסיס נתונים בסביבת ייצור שכבר חי תקופה ארוכה , וגודל הקובץ DATA כרגע עומד על 8 MB ,ומספר המעבדים הינו 4 , כלומר עפ"י מאמרך אני צריך להוסיף עוד 3 קבצי DATA – LDF , מה אתה ממליץ לי לייסם מבחינת גודל אותם קבצים ( קבצי ה – DATA ,כול הארבע כמובן ).
לגבי קובץ ה – LOG ,הוא עומד על 6 GB לערך , האם לשנותו ( מבחינת הגודל כמובן ) ומה היא המלצתך ?
תודה רבה שוב 
ותודה.

השאר תגובה:

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

*



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

איך לבצע 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 בלוגרים אהבו את זה: