הבלוג של גרי רשף

22/09/2010

נפח אחסון טבלאות

Filed under: Uncategorized — תגיות: , , , , , — גרי רשף @ 07:27

בפוסט זה אני עוסק בנפח טבלאות כפי שמחושב על ידי פרוצדורת המערכת SP_SpaceUsed,
והשפעת פעולות הכנסת המידע ומחיקתו עליו.

ניצור תשע טבלאות, נכניס לתוכן מספרים, נחשב את נפחן בעזרת SP_SpaceUsed, נרוקן אותן – בדרכים שונות, ושוב נחשב את נפחן בעזרת SP_SpaceUsed:
T_1000_NoIndex -טבלת Heap בת 1000 שורות שתרוקן בעזרת פקודת Delete פשוטה

T_1000_ClusteredIndex -טבלה בת 1000 שורות בעלת Clustered Index שתרוקן בעזרת פקודת Delete פשוטה

T_1000_Index -טבלה בת 1000 שורות בעלת Index רגיל שתרוקן בעזרת פקודת Delete פשוטה

T_1000000_NoIndex – טבלת Heap בת 1000000 שורות שתרוקן בעזרת פקודת Delete פשוטה

T_1000000_ClusteredIndex – טבלה בת 1000000 שורות בעלת Clustered Index שתרוקן בעזרת פקודת Delete פשוטה

T_1000000_Index – טבלה בת 1000000 שורות בעלת Index רגיל שתרוקן בעזרת פקודת Delete פשוטה

T_1000000_NoIndex_TabLock – טבלת Heap בת 1000000 שורות שתרוקן בעזרת פקודת Delete With (TabLock)

T_1000000_NoIndex_Truncate – טבלת Heap בת 1000000 שורות שתרוקן בעזרת פקודת Truncate

T_1000000_ClusteredIndex_TabLock – טבלת בת 1000000 שורות בעלת Clustered Index שתרוקן בעזרת פקודת Delete With (TabLock)

ולצורך איסוף המידע ניצור טבלה עשירית- לתוכה ישלח הפלט של פרוצדורת המערכת (מתנצל על החזרה המסורבלת על אותו CTE עבור כל טבלה):

-------------------------------------------------------------------------------------

If object_id('T_SpaceUsed') Is Not Null Drop Table T_SpaceUsed;

Go

Create Table T_SpaceUsed(ID Int Identity Primary Key,

name Varchar(Max),

rows Int,

reserved Varchar(Max),

data Varchar(Max),

index_size Varchar(Max),

unused Varchar(Max));

Go

-------------------------------------------------------------------------------------

If object_id('T_1000_NoIndex') Is Not Null Drop Table T_1000_NoIndex;

Go

Create Table T_1000_NoIndex(N Int);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000)

Insert Into T_1000_NoIndex

Select *

From T

Option (MaxRecursion 0);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000_NoIndex';

Go

-------------------------------------------------------------------------------------

If object_id('T_1000_ClusteredIndex') Is Not Null Drop Table 

T_1000_ClusteredIndex;

Go

Create Table T_1000_ClusteredIndex(N Int Primary Key);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000)

Insert Into T_1000_ClusteredIndex

Select *

From T

Option (MaxRecursion 0);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000_ClusteredIndex';

Go

-------------------------------------------------------------------------------------

If object_id('T_1000_Index') Is Not Null Drop Table T_1000_Index;

Go

Create Table T_1000_Index(N Int);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000)

Insert Into T_1000_Index

Select *

From T

Option (MaxRecursion 0);

Go

Create Index Idx_T_1000_Index On T_1000_Index(N);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000_Index';

Go

-------------------------------------------------------------------------------------

If object_id('T_1000000_NoIndex') Is Not Null Drop Table T_1000000_NoIndex;

Go

Create Table T_1000000_NoIndex(N Int);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000000)

Insert Into T_1000000_NoIndex

Select *

From T

Option (MaxRecursion 0);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_NoIndex';

Go

-------------------------------------------------------------------------------------

If object_id('T_1000000_ClusteredIndex') Is Not Null Drop Table 

T_1000000_ClusteredIndex;

Go

Create Table T_1000000_ClusteredIndex(N Int Primary Key);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000000)

Insert Into T_1000000_ClusteredIndex

Select *

From T

Option (MaxRecursion 0);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_ClusteredIndex';

Go

-------------------------------------------------------------------------------------

If object_id('T_1000000_Index') Is Not Null Drop Table T_1000000_Index;

Go

Create Table T_1000000_Index(N Int);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000000)

Insert Into T_1000000_Index

Select *

From T

Option (MaxRecursion 0);

Go

Create Index Idx_T_1000000_Index On T_1000000_Index(N);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_Index';

Go

-------------------------------------------------------------------------------------

If object_id('T_1000000_NoIndex_TabLock') Is Not Null Drop Table 

T_1000000_NoIndex_TabLock;

Go

Create Table T_1000000_NoIndex_TabLock(N Int);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000000)

Insert Into T_1000000_NoIndex_TabLock

Select *

From T

Option (MaxRecursion 0);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_NoIndex_TabLock';

Go

-------------------------------------------------------------------------------------

If object_id('T_1000000_NoIndex_Truncate') Is Not Null Drop Table 

T_1000000_NoIndex_Truncate;

Go

Create Table T_1000000_NoIndex_Truncate(N Int);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000000)

Insert Into T_1000000_NoIndex_Truncate

Select *

From T

Option (MaxRecursion 0);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_NoIndex_Truncate';

Go

-------------------------------------------------------------------------------------

If object_id('T_1000000_ClusteredIndex_TabLock') Is Not Null Drop Table 

T_1000000_ClusteredIndex_TabLock;

Go

Create Table T_1000000_ClusteredIndex_TabLock(N Int Primary Key);

Go

With T As

(Select 1 N

Union All

Select N+1 N

From T

Where N<1000000)

Insert Into T_1000000_ClusteredIndex_TabLock

Select *

From T

Option (MaxRecursion 0);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_ClusteredIndex_TabLock';

Go

-------------------------------------------------------------------------------------

Delete From T_1000_NoIndex;

Delete From T_1000_ClusteredIndex;

Delete From T_1000_Index;

Delete From T_1000000_NoIndex;

Delete From T_1000000_ClusteredIndex;

Delete From T_1000000_Index;

Delete From T_1000000_NoIndex_TabLock With (TabLock);

Truncate Table T_1000000_NoIndex_Truncate;

Delete From T_1000000_ClusteredIndex_TabLock With (TabLock);

Go

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000_NoIndex';

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000_ClusteredIndex';

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000_Index';

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_NoIndex';

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_ClusteredIndex';

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_Index';

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_NoIndex_TabLock';

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_NoIndex_Truncate';

Insert Into T_SpaceUsed Exec SP_SpaceUsed 'T_1000000_ClusteredIndex_TabLock';

Go

-------------------------------------------------------------------------------------

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

Select *

From T_SpaceUsed

Order By ID;

clip_image002

שורות 1-9 מציגות את תשעה הטבלאות לאחר הכנסת הנתונים,

ושורות 10-18 לאחר מחיקתם.

נתמקד תחילה בטבלאות המופיעות בשורות 1-6, ובהתאמה – בשורות 10-15:

ההבדל בין טבלת Heap לטבלה עם Clustered Index – זניח (שורות 1,2 ושורות 4,5): ה-Clustered Index אינו אובייקט עם תוכן המתווסף לטבלה, אלא אופן ארגון המידע בטבלה, ולפיכך האינדקס עצמו (index_size) כמעט ואינו תופס מקום והנתונים (data) אותם נתונים; וגם בין טבלה קטנה לגדולה (שורות 2,5) – ההבדלים הם בנתונים ולא באינדקס.

אינדקס רגיל לעומת זאת – כן תופס מקום, והוא בא בנוסף לנתונים עצמם, ולכן טבלאות עם אינדקס (שורות 3,6) כפולות בגודלן מטבלאות ללא אינדקס.

לאחר המחיקה (בעזרת פקודת Delete פשוטה) הטבלה ללא האינדקס נשארה בגודלה המקורי (שורות 4, 13): הנתונים נמחקו, אבל השטח המשוחרר לא הוחזר (!), וזאת בניגוד לטבלת ה-Clustered Index שבה האינדקס נבנה מחדש לאחר המחיקה והשטח התפנה (שורות 5,14). בטבלה עם האינדקס הרגיל (שורות 6,15) – האינדקס עצמו נבנה מחדש והתכווץ, אבל הנתונים עצמם לא.

לשם השוואה נתבונן בשלוש הטבלאות האחרונות (שורות 7,8,9 ו-16,17,18): הראשונה ללא אינדקס והנתונים ממנה נמחקו תוך נעילת הטבלה-

Delete From T_1000000_NoIndex_TabLock With (TabLock);

נעילת הטבלה אולי מונעת מאחרים לעבוד איתה במהלך המחיקה, אבל מאפשרת למערכת לשחרר את השטח שהתפנה (שורות 7,16). הערה- כל זה בהנחה שאיננו משתמשים במנגנון של Row Versioning.

הנתונים של השניה, אף היא ללא אינדקס, נמחקו בעזרת פקודת Truncate והיא ביצעה את העבודה בצורה היסודית ביותר: הטבלה נוצרה למעשה מחדש, אינה תופסת שטח בכלל, והמחיקה מהירה מאוד (שורות 8,17). כמובן שיש לשיטה זו גם חסרונות- לא ניתן לבצע מחיקה חלקית, לא ניתן למחוק מטבלאות בעלות Foreign Key או שמשתתפות ב-Indexed Views ועוד.

לבסוף, לשם השווה, הטבלה השלישית (שורות 9,18): גם Clustered Index וגם Delete With (TabLock) – התוצאות זהות למחיקה רגילה של טבלת Clustered Index ונעילת הטבלה על ידי TabLock מיותרת.

מסקנות:

1. Clustered Index אינו מגדיל את הטבלה, ובעת מחיקת נתונים הוא מאפשרר לשחרר את השטח שהתפנה. (יש לו גם חסרונות- אך זה לא כאן)

2. מחיקת נתונים בעזרת Hint מסוג TabLock נועל את הטבלה בעת המחיקה, אך מאפשר לשחרר את השטח שהתפנה, וזה פתרון טוב לטבלאות Heap.

3. פקודת Truncate יעילה מאוד מבחינת המהירות ושחרור השטח שהתפנה, אבל היא פועלת רק על כל הנתונים, וגם זה במספר מגבלות.

לסיום- נבטל את כל הטבלאות:

Drop Table T_1000_NoIndex;

Drop Table T_1000_ClusteredIndex;

Drop Table T_1000_Index;

Drop Table T_1000000_NoIndex;

Drop Table T_1000000_ClusteredIndex;

Drop Table T_1000000_Index;

Drop Table T_1000000_NoIndex_TabLock;

Drop Table T_1000000_NoIndex_Truncate;

Drop Table T_1000000_ClusteredIndex_TabLock;

Go

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: