שדרוג מסד נתונים המכיל קוד CLR לגרסת SQL Server 2012

צח פניגשטיין

יועץ SQL בכיר, בעל 10 שנות ניסיון בתחום התוכנה, ר"צ DBA בפרוייקט ממשלתי מטעם נאיה טכנולוגיות

לאחרונה ניסיתי להעביר את מסד הנתונים שלי משרת SQL 2008R2 לשרת SQL 2012.
בצעתי Restore לקובץ גיבוי על השרת החדש. לאחר מכן העברתי את ה-Compatibility Level לגרסה 2012 ולחצתי OK:

לאחר עבודה מאומצת מצד השרת, צצה השגיאה הבאה:

אומנם פסח כבר מאחורינו, אבל מה נשתנה השדרוג הזה מכל השדרוגים?

חשוב לציין שבשלב זה, למרות הודעת השגיאה, מסד הנתונים זמין, ונראה שניתן להשתמש בו ללא כל בעיה.
אבל...
מסד הנתונים שלי מכיל קוד CLR. חלק מהפונקציות הללו משמשות בתוך Check Constraints.
כחלק מתהליך השדרוג, ה-Assembly שמכיל את קוד ה-CLR עובר שינוי מאחורי הקלעים.
כתוצאה מכך, פונקציות CLR עלולות להחזיר תוצאה שונה בין גרסאות.
לכאורה, רשומה בטבלה יכולה לעבור את הבדיקה הלוגית של Check Constraint שמבוסס על CLR בגרסה אחת, אך לא לעבור את הבדיקה לאחר השדרוג. כמובן שמצב זה אינו תקין, מאחר שהוא עלול לפגוע בכלילות הנתונים (Data Integrity).

כדי להימנע ממצב זה, SQL Server מסמן את הטבלאות וה- Check Constraintsהחשודים.
אני בחרתי להציג טבלה אחת ממסד הנתונים, ואת ה-Check Constraints שיש בה:

SELECT name , object_id , has_unchecked_assembly_data

FROM sys.tables WHERE object_id = 1590296725

SELECT name , object_id , parent_object_id , is_not_trusted

FROM sys.check_constraints WHERE parent_object_id = 1590296725

שימו לב שגם הטבלה, וגם ה-Check Constraint מסומנים כחשודים.
הטבלה מסומנת בעזרת העמודה has_unchecked_assembly_data, וה- Check Constraintsמסומן בעזרת העמודה is_not_trusted (בדומה למצב בו Check Constraint מוקם עם האופציה with nocheck).

ננסה "לתקן" את ה- Check Constraint:

ALTER TABLE Infrastructure.General_enum_State WITH CHECK

CHECK CONSTRAINT CK_General_enum_State_ValidEnumeration

והתוצאה:

ה-Check Constraint מסומן כתקין, אך הטבלה עדיין מסומנת כחשודה.

כדי לגרום ל SQL Server "לבטוח" באמינות הנתונים בטבלה, אין ברירה אלא להריץ את הפקודה DBCC CHECKTABLE:

DBCC CHECKTABLE ('Infrastructure.General_enum_State')

GO

SELECT name , object_id , has_unchecked_assembly_data

FROM sys.tables WHERE object_id = 1590296725

GO

וההודעה המתקבלת:

DBCC results for 'Infrastructure.General_enum_State'.

There are 7 rows in 1 pages for object "Infrastructure.General_enum_State".

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

במקום לבדוק כל טבלה, פשוט יותר להריץ DBCC CHECKDB, שכחלק מפעולתו מפעיל את CHECKTABLE על כל הטבלאות במסד הנתונים.

SELECT COUNT(*) has_unchecked_assembly_data_count

FROM sys.tables

WHERE has_unchecked_assembly_data = 1

go

DBCC CHECKDB

go

SELECT COUNT(*) has_unchecked_assembly_data_count

FROM sys.tables

WHERE has_unchecked_assembly_data = 1

Go

אך האם כל ה-Check Constraints אכן תקינים?

SELECT COUNT(*) is_not_trusted_count

FROM sys.check_constraints

WHERE is_not_trusted = 1

Go

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

DECLARE @cmd NVARCHAR(max) = N''

SELECT @cmd += N'ALTER TABLE ' + SCHEMA_NAME(schema_id) + N'.' + OBJECT_NAME(parent_object_id) + N' WITH CHECK CHECK CONSTRAINT ' + name + N';'

FROM sys.check_constraints

WHERE is_not_trusted = 1

EXEC (@cmd)

נבדוק שוב את מצב ה-Check Constraints:

SELECT COUNT(*) is_not_trusted_count

FROM sys.check_constraints

WHERE is_not_trusted = 1

Go

עכשיו נראה שהכל בסדר, ותהליך השדרוג הושלם.

לקריאה נוספת על חשיבות הסימון is_not_trusted ראו במאמרים הבאים:

Hugo Kornelis: Can you trust your constraints?

Kalen Delaney: Can You Trust Your Data? -- Untrustworthy Constraints

בדיקת ניצול הנפח של 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)) AS TotalSpaceUsedInMB,

            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:

 Demystify Tempdb Performance & Management

מקרי מבחן של Deadlock: ניתוח ופיתרון

מיכל סומך

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

מניסיוני, בכל פעם שנתקלתי באירוע deadlock, זה היה בעקבות בעית קידוד שניתן היה לתקן. במקרים שיתוארו במאמר זה, נתקלתי בשני deadlocks מסוגים שונים. אצל הלקוח המדובר היתה טבלה אחת גדולה וכמה טבלאות קטנות. הודות לכך, חקירת הבעיה היתה קלה ומהירה. עם זאת, מציאת הסיבות ל-deadlock חשפו בעיות עיצוב עמוקות.

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

להלן מקרה ה-deadlock  הראשון:

במקרה הזה תהליך A (שבוטל ב-rollback) הוא איחזור של כ 1000 רשומות מהטבלה הגדולה. תהליךB  הוא עדכון של רשומה אחת בלבד באותה הטבלה. תהליך A  נועל בנעילת Shared Lock (S) את הדף המצוין בחלק התחתון של התרשים (Page ID: 1264622) לטובת האיחזור ואילו תהליך B נועל בנעילת Intent-Exclusive Lock (IX) את הדף המצוין בחלק העליון (Page ID: 1264621). כעת, כל אחד מהתהליכים צריך את הדף האחר כדי להמשיך וממתין לתהליך האחר ומכיוון שכך, מתרחש אירוע deadlock. התהליך שיבחר כ-Victim ע"י SQL Server כדי להפסיק את הנעילה ההדדית הוא תהליך A, משום שהושקעו בו משאבים מועטים יותר –  פקודת SELECT בלבד.

תהליך A  קורא הרבה שורות, וכדי להימנע מנעילת שורות רבות, דבר שיעלה בהרבה משאבים, הוא נועל את הדף כולו בנעילת S. תהליך B  מעדכן שורה אחת, והדף ננעל בנעילת IX כדי לציין ששורה מתעדכנת בתוך הדף. זו דוגמא מצוינת למנגנון הנעילות. תהליך A  לא ניגש לשורה המעודכנת (לפי תנאי ה where שבשאילתא) אבל כיוון ש-SQL Server החליט שהנעילה תהיה ברמת Page, הרי שכל הדף נעול. זאת משום שנעילת שורות ספיציפיות במקרה שלנו תגרור עלות גבוהה מדי, ולכן חסכוני יותר לנעול את כל הדף.

אבל רגע, מדוע תהליך B מבקש לנעול שני דפים בנעילת IX  אם רק שורה אחת מתעדכנת? ואכן, במקרה הזה, קרה דבר מעניין: תהליך B  מעדכן שורה אחת, אך מרחיב אותה באופן משמעותי, משום שהערך המקורי היה NULL  ולאחר העדכון הוא גדל בעוד כ 1000 תווים.

הדוגמא הזו חושפת מנגנון נוסף – Page Split. השורה שהיתה במקור בדף הראשון גדלה וכבר לא היה לה מקום בדף הנוכחי. לכן, SQL Server היקצה בעבורה את הדף הבא הפנוי. אילו השורה היתה יכולה להשאר בדף המקורי, לא היה מתרחש אירוע ה-deadlock. העדכון שבתהליך B היה נועל את הדף וה-SELECT שבתהליך A פשוט היה ממתין לסיומו.

העדכון שהרחיב את השורה, הוביל אותנו לעניין נוסף: Fill Factor. מאפיין זה קובע את שיעור המקום הפנוי שיוותר בדפי האינדקס (ב-Leaf Level, לאחר יצירה או בנייה מחדש של אינדקס) כדי לאפשר הרחבה של הנתונים ללא הקצאת דפים (למשל בהרחבה או בהוספה של שורות חדשות). במקרה הזה Fill Factor היה מוגדר 0 (ברירת המחדל. למעשה משמעותה 100%), כלומר, הדף יתמלא בשלמותו, ללא הותרת מקום פנוי. אילו במקרה הזה היינו משאירים מקום פנוי, האפשרות להופעת deadlocks עדיין היתה קיימת, אך בסיכוי נמוך יותר ובתדירות הרבה יותר נמוכה. זאת, מכיוון שרוב השורות היו נכנסות בדף, אם כי עדיין אחרות היו עלולות לגרום להקצאת דף נוסף, כלומר ל-Page Split.

ההמלצה שלי במקרה האמור היתה לשנות את ערך ה  Fill Factorל- 80% ובכך לאפשר הרחבה של נתונים עם סיכוי נמוך יותר להתרחשות deadlock.

עוד על Fill Factor אפשר לקרוא כאן.

לאחר שפתרנו את ה-deadlock  הקודם, נתקלנו באחד נוסף:

תהליך C  מאחזר 1000 שורות מהטבלה הגדולה, ותהליךD   הוא אותוUPDATE  שבמקרה הראשון המעדכן שורה אחת. ניתן לראות ששני התהליכים משתמשים באותה הטבלה, וגם כאן שינוי הקוד לא יעזור. אם נתמקד בנעילות נבחין שהנעילה המופיעה בחלק העליון היא על Clustered Index (CIX) שבמקרה הזה איננו ה-PK, והנעילה השניה היא על אינדקס רגיל Non-Clustered. תהליךC  מחפש לפי האינדקס את מיקום השורות הדרושות ואז מבצע Lookup  כדי להביא את הנתונים הדרושים מהשורות שב- Clustered Index, דהיינו מהטבלה עצמה. לעומתו, תהליךD  מבצע עדכון קודם כל בשורה עצמה, כלומר ב-Clustered Index, ואח"כ, כחלק מן העדכון, מתעדכן האינדקס הרגיל בהתאם. מאחר שכך, כל אחד מן התהליכים ממתין לאינדקס התפוס ע"י התהליך השני ואז מתעורר deadlock.

כשחקרתי את הקוד של תהליך C, גיליתי שה-Lookup  מתבצע כדי להביא עמודת נתונים אחת, שבמקרה היא עמודת המפתח PK. על מנת לפתור את הבעיה הזאת הוספתי את עמודת המפתח של ה-PK  לאינדקס כעמודת INCLUDED (כלומר, עמודה הקיימת רק כנתון נוסף ולא כמפתח של האינדקס. קראו עוד על תכונה זאת כאן), ובזאת חסכנו את ה-Lookup שהרי העמודה הדרושה נמצאת כבר באינדקס, ועל ידי כך נפתרה בעית ה-deadlock.

פה אנו נתקלים בבעית עיצוב: האם להפריד את ה-PK  מה-CIX? במקרה הזה, אילו הם היו ביחד, עמודת ה-PK  היתה קיימת כעמודת הפנייה לטבלה ב-Leaf Level של האינדקס והLookup  היה נמנע, שהרי הנתון הדרוש זמין מייד.

הוכחה נוספת לכך שבמקרה כזה מקומם הוא ביחד היא התוצאה הבאה משאילתא המאחזרת את סטטיסטיקות השימוש באינדקס מתוך sys.dm_db_index_usage_stat:

רואים שבכל פעם שמחפשים עם אינדקס ה-PK  מבוצע Lookup. ניתן לראות זאת מערכי המספרים הזהים שבעמודת ה-user_seek של אינדקס ה-PK ועמודת ה-user_lookup של ה-Clustered Index. במקרה שלנו, עמודת ה-PK  היא מטיפוס GUID (uniqueidentifier) ועמודת ה-CIX היא מטיפוס datetime, וזאת הסיבה להפרדה, מכיוון שקביעת CIX לפי GUID, כך שהטבלה מסודרת וממויינת פיזית לפי שדה מטיפוס כזה, מביאה לפרגמנטציה של האינדקס ולכן היא חסרת טעם ולא כדאית.

המלצתי במקרה הזה ללקוח היתה לחשוב שוב על ההחלטה ואולי לשנות את עמודת ה-GUID  לעמודה מטיפוס integer ואז להפוך את ה-PK ל-Clustered Index ובכך למנוע deadlock בסופו של דבר.

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

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

לקריאה נוספת על deadlock בבלוג הזה:

חקירת deadlocks ב- SQL Server

מאפיינים לא מתועדים ב- UPDATE STATISTICS

צח פניגשטיין

יועץ SQL בכיר, בעל 10 שנות נסיון בתחום התוכנה, ר"צ DBA בפרוייקט ממשלתי מטעם נאיה טכנולוגיות

אני מודה לסטאס בוגצ'ינסקי על העזרה בכתיבת המאמר.

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

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

ניתן לבחון לעומק את המידע הסטטיסטי על האינדקס בעזרת הפקודה DBCC SHOW_STATISTICS

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

(מאמר זה לא מתיימר לגעת בנושאים הללו. מידע מעמיק על סטטיסטיקות והדרך שבה SQL Server משתמש בהן ניתן למצוא במאמר תחת קריאה נוספת)

מנגנון זה מאפשר יצירה של תוכניות ביצוע יעילות, אך הוא מקשה מאוד על ה-DBA בזמן פיתוח. בזמן פיתוח אנו בדרך כלל עובדים מול טבלאות ריקות, או טבלאות המכילות כמות זעומה של נתונים המשמשים לבדיקות בסיסיות. כאשר נבחן שאילתה במסד נתונים ריק, SQL Server יבחר תוכנית ביצוע שונה לחלוטין מזו שתיבחר מול מסד נתונים המכיל כמויות מידע גדולות. הבדל זה נובע מהעובדה שבחירת אופן הגישה לטבלה ובחירת האלגוריתם לביצוע JOIN בין טבלאות, מושפעים בצורה משמעותית מההערכה לגבי כמות המידע שתהיה מעורבת בתהליך. הערכה זו מתבססת על המידע הסטטיסטי שמתאר את הנתונים הנוכחיים הקיימים בטבלה. מסיבה זו לא נוכל להעריך כיצד יתנהגו שאילתות בסביבת הייצור בעזרת בדיקה של תוכניות ביצוע בסביבת הפיתוח.

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

UPDATE סטאאאם....

הסיבה היחידה לבעיה כל-כך מורכבת היא הדרך שבה SQL Server משתמש בסטטיסטיקות על מנת ליצור תוכנית ביצוע. אם נצליח לזייף מידע סטטיסטי - נפתור את הבעיה.
לשמחתנו, הפקודה UPDATE STATISTICS (החל מגרסת 2005) מכילה מספר אפשרויות לא מתועדות, המאפשרות לערוך את המידע הסטטיסטי. מתוך BOL:

כאשר הפירוט לגבי האופציה update_stats_stream_option הוא:

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

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

אבל סביבת בדיקות נועדה כדי שיהרסו אותה, לא? (;

מבין שלושת המאפיינים שמיקרוסופט מואילה בטובה לחשוף, שניים ברורים מאליהם:

ROWCOUNT מציין את מספר השורות שהאובייקט מכיל.
PAGECOUNT מציין את מספר העמודים שעליהם האובייקט נפרס.

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

אל המידע לגבי שלושת הערכים הללו ניתן לגשת בעזרת:

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target ) WITH STATS_STREAM

אם כן, כיצד נזייף סטטיסטיקות?

הדרך הפשוטה ביותר תהייה להשתמש ב- UPDATE STATISTICSכדי לשנות בצורה פיקטיבית את מספר הרשומות או העמודות בטבלה. פעולה זו תגרום ל-SQL Server לחשוב שבטבלה יש כמות נתונים גדולה (בדומה למצב שקיים בסביבת הייצור), וליצור תוכנית ביצוע המתאימה לנפח נתונים גדול.

USE tempdb

GO

CREATE TABLE tbl

      (

      ID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_tbl PRIMARY KEY,

      Data1 NVARCHAR(255),

      Data2  NVARCHAR(255)

      )

GO

INSERT tbl VALUES ('a','b')

GO

CREATE INDEX IX_Data1 ON tbl(Data1)

GO

כרגע, הטבלה tbl מכילה שורה אחת. נבחן את מצב הסטטיסטיקות על הטבלה שיצרנו (שימו לב שלמעשה אין לנו מידע סטטיסטי):

DBCC SHOW_STATISTICS (tbl,PK_tbl)

DBCC SHOW_STATISTICS (tbl,PK_tbl)  WITH STATS_STREAM

נריץ את השאילתה הבאה:

SELECT      Data2

FROM        tbl

WHERE Data1 = 'some value'

כאשר נבחן את תוכנית הביצוע לשאילתה נגלה ש- SQL Serverבוחר להתעלם מהאינדקס שבנינו על עמודה Data1, למרות שהשאילתה מבצעת סינון על-פי עמודה זו.

הסיבה לכך היא פשוטה: כדי להחזיר תשובה, SQL Server זקוק למידע מעמודה Data2, שאינו נמצא באינדקס. מבחינת SQL Server, שימוש באינדקס על Data1 יחייב בהמשך פעולת Lookup יקרה מול המפתח הראשי של הטבלה, כדי לאחזר את עמודה Data2. מאחר ש- SQL Server יודע שהטבלה tbl כמעט ריקה, הוא מעריך שסריקה של כל הטבלה עבור שורות שמקיימות את התנאי Data1 = 'some value', תהייה זולה יותר מאשר שימוש באינדקס וביצוע פעולת Lookup ועל כן בוחר בפעולת Cluster Index Scan (הזהה למעשה ל-Table Scan).

"נבשל" מידע סטטיסטי כאילו יש לנו 3000 רשומות בטבלה:

UPDATE STATISTICS tbl (PK_tbl) WITH ROWCOUNT = 3000

נריץ שוב את אותה השאילתה:

במצב זה, SQL Server חושב שהטבלה tbl מכילה 3000 שורות. סריקה של טבלה בגודל כזה, ובדיקת התנאי Data1 = 'some value' עבור כל שורה, הופכת להיות יקרה יותר מאשר להשתמש באינדקס על Data1 וביצוע פעולת Lookup (על האלגוריתם הקובע מתי לעבור מסריקה של הטבלה לחיפוש באינדקס Non-Clustered, מה שנקרא Tipping-Point, ניתן לקרוא כאן).

במאפיין STATS_STREAM קשה להשתמש בצורה ישירה, מאחר שמבנה הקלט שלו אינו מתועד (לפחות לרגע כתיבת שורות אלו). שימוש במאפיין זה אפשרי אם מייצרים סקריפט לסטטיסטיקות בעזרת ה-Wizard של ייצור סקריפטים לאובייקטים:

במסך השלישי:

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

חשוב לזכור שבעזרת השיטות שתיארתי לא ניתן לבחון ביצועים מעשיים של שאילתות מול טבלאות הפיתוח (בדיקה כזו אינה רלוונטית כדי להגיע למסקנות לגבי סביבת הייצור). במקרה זה SQL Server יוצר תוכנית ביצוע המתאימה למספר גדול (או קטן) יותר של שורות. אך התוכנית מבוצעת בפועל מול טבלה שלא מכילה את כמות הנתונים שאליה המנוע התכונן. מסיבה זו סביר להניח שתוכניות אלו יפגינו ביצועים ירודים אל מול התוכניות שהמנוע יצור ללא התערבות.

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

כדי להחזיר את מצב הסטטיסטיקות לקדמותו ניתן להשתמש באחת משתי דרכים:
1. בניה מחדש של האובייקט שעליו נבנו הסטטיסטיקות המזויפות.
2. שימוש ב-UPDATE STATISTICS בתוספת הפרמטרים ROWCOUNT או PAGECOUNT עם ערכים המתאימים למצב לפני השינוי.

שימו לב ששימוש ב- UPDATE STATISTICSללא ROWCOUNT או PAGECOUNT אינה משנה את מצב הסטטיסטיקה. לעומת זאת, בניה מחדש של הסטטיסטיקה ע"י SQL Server מאפסת את השינויים הללו.

סיכום

UPDATE STATISTICS מכיל מאפיינים לא מתועדים שאינם מוכרים לרוב המשתמשים. שימוש במאפיינים אלו יכול להקל מאוד על עבודת ה-DBA בניסיון לבחון אסטרטגיות אינדוקס, ללא צורך בטעינת נתונים. חשוב לזכור שמדובר במאפיינים שאינם מתועדים, ושימוש בהם מחייב אמצעי זהירות מתאימים.

קריאה נוספת

מאמר מעמיק, המדגים בצורה יפה את השימוש של מנוע SQL בסטטיסטיקות:

http://www.simple-talk.com/sql/sql-training/questions-about-sql-server-distribution-statistics/

חקירת deadlocks ב- SQL Server (חלק 2)

אביאל אילוז

MCT, מומחה ב-SQL Server, בעל 15 שנות ניסיון בתחום ומנהל אקדמי של מכללת נאיה

במאמר הראשון בסדרה זו הכרנו את תופעת ה-deadlock בפירוט והמחשנו אותה באמצעות דוגמא פשוטה. הופעתו של אירוע deadlock דורש את התערבותו של צוות הפיתוח או ה-DBA כדי להתחקות אחר מקורותיו ולעשות את הנדרש כדי למנוע את הישנותו. לשם כך מספק לנו SQL Server כלים שונים לתחקור התופעה. במאמר זה נכיר את אחד מן הכלים האלה ונלמד כיצד לנתח נכון את המידע המופק ממנו אודות מקורותיו של deadlock.

עקוב אחרי!

הכלי הראשון שזורה אור על מקורות אירוע ה-deadlock הוא Trace Flags, 'דגלי מעקב' במינוח העברי, שהם כמו מתגי הפעלה שונים ששולטים על התנהגותו של SQL Server בפרמטרים שונים. ישנם דגלי מעקב רבים מאוד ורובם אינם מתועדים בספרות הרשמית. רשימת דגלי המעקב המותעדים ב-Books On Line איננה ארוכה, אך כוללת את השניים שמעניינים אותנו: 1204 ו-1222. שניהם מאלצים את SQL Server לדבר קצת יותר אל תוך הלוג שלו (Error Log) בעת אירוע deadlock ולתת לנו מידע מפורט על מה שקרה.

ראשית, כדי להפעיל דגל מעקב כל שהוא יש להריץ ע"י פקודת DBCC המקבלת שני פרמטרים:

·         מס' הדגל המופעל.

·         הטווח (Scope) שלו; (0) עבור החיבור (session) הנוכחי בלבד, (1-) הפעלה גלובלית.

כמו כן, ישנן עוד 2 פקודות חשובות: אחת לביטול הפעלת הדגל והשניה לבדיקת סטטוס ההפעלה שלו. בקטע הקוד הבא מופעל דגל 1222 באופן גלובלי לניטור deadlock, וכן מודגמת פקודת הביטול (TRACEOFF) והבדיקה (TRACESTATUS).

--turn on trace flag 1222 globally

DBCC TRACEON (1222, -1)

GO

--view all trace flags status

DBCC TRACESTATUS

GO

--turn off trace flag 1222 globally

DBCC TRACEOFF (1222, -1)

GO

אם הרצתם את הקוד הנ"ל כולו, הריצו שוב פקודת ההפעלה לדגל 1222 כדי לנטר את אירועי ה-deadlock.

כדי להפעיל דגלי מעקב עם הפעלתו של SQL Server יש לקבוע זאת בפרמטרי האיתחול של SQL Server ולהוסיף את הפרמטר (-T) ואחריו את מספר הדגל. לקריאה כיצד לבצע זאת ראו ב-Startup Options.

בשלב זה, נבחר להפעיל רק דגל 1222 כי המידע שמופק ממנו שלם, מפורט יותר וכולל מידע נוסף הקשור לתהליכים המעורבים באירוע ה-deadlock ועל מצבם. דגל מעקב מספר 1204 מספק מידע פשוט יותר ועל כן גם נוח יותר לניתוח מהיר שלא דורש העמקת יתר. בהמשך המאמר נשווה בקצרה את הפלט שלו לזה של דגל 1222.

בדיקת סטטוס ההפעלה באמצעות TRACESTATUS תראה את הפלט הבא, המעיד שדגל 1222 הופעל באופן גלובלי (הכולל כמובן את רמת ה-session, אף על פי שהיא מסומנת ב-0):

TraceFlag Status Global Session

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

1222      1      1      0

הפעלת דגל המעקב 1222 מתועדת ב-Error Log של SQL Server בנוסח הבא:

DBCC TRACEON 1222, server process ID (SPID) 51. This is an informational message only; no user action is required.

אנטומיה של אירוע

נחזור כעת לקוד הטרנזקציות שהרצנו במאמר הראשון. נריץ אותו שוב ונראה מהו המידע המופק אל תוך הלוג של SQL Server בעקבות ההפעלה של דגל 1204. כפי שבצענו בפעם הקודמת, נריץ את הקוד של טרנזקציה A בחלון שאילתא אחד ונעבור מייד להריץ את קוד טרנזקציה B בחלון שני:

--Transaction A

USE AdventureWorks2008

GO

BEGIN TRANSACTION

--Statement 1

UPDATE Sales.SalesOrderDetail

SET OrderQty = OrderQty * 2

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

--Hold for 10 seconds

WAITFOR DELAY '00:00:10'

--Statement 2

SELECT * FROM HumanResources.Department

WHERE DepartmentID = 1

COMMIT

GO

--Transaction B

USE AdventureWorks2008

GO

BEGIN TRANSACTION

--Statement 1

UPDATE HumanResources.Department

SET Name = Name + ' added text'

WHERE DepartmentID = 1

--Hold for 10 seconds

WAITFOR DELAY '00:00:10'

--Statement 2

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

COMMIT

GO

כמצופה, בחלון השני בוטלה הטרנזקציה והתהליך כולו נבחר כ-victim עם הודעת השגיאה מס' 1205 המודיעה על deadlock.

נבחן כעת את הפלט ב-Error Log בעקבות הפעלת דגל המעקב 1222. את המידע הזה ניתן לראות בשני אופנים:

1. בחלון הצגת רשומות הלוג של SQL Server ב-SSMS כמו בתמונה הבאה (ניתן למיין בסדר תאריך עולה רק בגרסה 2012):

2. בקובץ הלוג האחרון (Current = ERROROG) עצמו הנמצא בספרייתLOG  של SQL Server (שם הקובץ ומיקום ברירת המחדל:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\ERRORLOG

באפשרות זאת, אפשר לראות את המידע במלואו ולהעתיקו למקום אחר. במקרה שלנו מתקבל הפלט הבא על אירוע ה-deadlock כתוצה מהפעלת דגל המעקב 1222 (בצירוף עריכה קלה והדגשות שונות לצורך תצוגה ברורה):

deadlock-list

 deadlock victim=process89bc78

  process-list

   process id=process89bc78 taskpriority=0 logused=992 waitresource=KEY: 41:72057594059096064 (88d989970b21) waittime=1295 ownerId=1461972 transactionname=user_transaction lasttranstarted=2012-02-01T14:16:17.460 XDES=0x6e68280 lockMode=S schedulerid=1 kpid=9792 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-02-01T14:16:17.330 lastbatchcompleted=2012-02-01T14:16:17.330 clientapp=Microsoft SQL Server Management Studio - Query hostname=COMP hostpid=8648 loginname=COMP\User isolationlevel=read committed (2) xactid=1461972 currentdb=41 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

    executionStack

     frame procname=adhoc line=12 stmtstart=38 sqlhandle=0x0200000062bf1a367c223b2515295d4ab887a612948fe8f8

SELECT * FROM [Sales].[SalesOrderDetail] WHERE [SalesOrderID]=@1 AND [SalesOrderDetailID]=@2    

     frame procname=adhoc line=12 stmtstart=390 stmtend=578 sqlhandle=0x02000000081dbe017f1c3ce264b5c71a5af5ba8fc624e486

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1    

    inputbuf

BEGIN TRANSACTION

--Statement 1

UPDATE HumanResources.Department

SET Name = Name + ' added text'

WHERE DepartmentID = 1

--Hold for 10 seconds

WAITFOR DELAY '00:00:10'

--Statement 2

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

COMMIT

   process id=process5d89558 taskpriority=0 logused=3292 waitresource=KEY: 41:72057594044612608 (e1784bd73cba) waittime=4120 ownerId=1461715 transactionname=user_transaction lasttranstarted=2012-02-01T14:16:14.063 XDES=0x5797b30 lockMode=S schedulerid=2 kpid=6612 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-02-01T14:16:13.963 lastbatchcompleted=2012-02-01T14:16:13.963 clientapp=Microsoft SQL Server Management Studio - Query hostname=COMP hostpid=8648 loginname=COMP\User isolationlevel=read committed (2) xactid=1461715 currentdb=41 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

    executionStack

     frame procname=adhoc line=12 stmtstart=24 sqlhandle=0x02000000f1566f31cd4b8116cb213bf6a38d6f62e123f693

SELECT * FROM [HumanResources].[Department] WHERE [DepartmentID]=@1    

     frame procname=adhoc line=12 stmtstart=438 stmtend=570 sqlhandle=0x0200000083fb582b676b2b8e21b5289e7168a5a5e6b45d03

SELECT * FROM HumanResources.Department

WHERE DepartmentID = 1    

    Inputbuf

BEGIN TRANSACTION

--Statement 1

UPDATE Sales.SalesOrderDetail

SET OrderQty = OrderQty * 2

WHERE SalesOrderID = 43659 and SalesOrderDetailID = 1

--Hold for 10 seconds

WAITFOR DELAY '00:00:10'

--Statement 2

SELECT * FROM HumanResources.Department

WHERE DepartmentID = 1

COMMIT

  resource-list

   keylock hobtid=72057594059096064 dbid=41 objectname=AdventureWorks2008.Sales.SalesOrderDetail indexname=PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID id=lock55ddf40 mode=X associatedObjectId=72057594059096064

    owner-list

     owner id=process5d89558 mode=X

    waiter-list

     waiter id=process89bc78 mode=S requestType=wait

   keylock hobtid=72057594044612608 dbid=41 objectname=AdventureWorks2008.HumanResources.Department indexname=PK_Department_DepartmentID id=lockadd2380 mode=X associatedObjectId=72057594044612608

    owner-list

     owner id=process89bc78 mode=X

    waiter-list

     waiter id=process5d89558 mode=S requestType=wait

הפלט מציג נתונים מפורטים על התהליכים המעורבים באירוע ה-deadlock באופן הירארכי כמו שמוצג בטבלה הבאה:

אם נבחן את הפלט על פי המבנה הזה נראה אכן שיש לנו שני תהליכים המעורבים באירוע (ואחד מהם הוא victim) ושני משאבים הנעולים על ידם. ננתח זאת כך:

·         את המעגליות של הנעילה הגורמת ל-deadlock נוכל לראות בסעיף resource-list המציג את המשאבים הנעולים ביחס לכל אחד משני התהליכים.

·         את מספרי התהליכים המופיעים בסעיף זה נשווה למידע המופיע למעלה יותר בסעיף process-list ונוכל לשייך כל אחד מהם לקוד המקורי שלנו.

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

מהטבלה נחשפים בבירור מקורותיו של אירוע ה-deadlock כפי שתיארנו אותם בראשית הדברים:

·         טרנזקציה A, בתהליך process5d89558, לאחר שביצעה UPDATE בטבלה SalesOrderDetails ועל כן נועלת את המפתח שלה (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID) בנעילת X, ממתינה כעת בשאילתת SELECT מהטבלה Department המבקשת נעילת S על המפתח שלה (PK_Department_DepartmentID).

ולעומתה, בדיוק המקרה ההפוך:

·         טרנזקציה B, בתהליך process89bc78, לאחר שביצעה UPDATE בטבלה Department ועל כן נועלת את המפתח שלה (PK_Department_DepartmentID) בנעילת X, ממתינה כעת בשאילתת SELECT מהטבלה SalesOrderDetails המבקשת נעילת S על המפתח שלה (PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID).

כלומר, כל אחד מהתהליכים נועל משאב מסוים המבוקש ע"י התהליך האחר, והוא עצמו ממתין למשאב התפוס כבר ע"י התהליך האחר, וכך שני התהליכים ממתינים זה לזה ומופיע deadlock.

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

סיכום

במאמר הזה ראינו כיצד לנטר מידע על אירועי deadlock באמצעות דגל המעקב (trace flag) מספר 1222. המידע המופק מדגל המעקב מספר 1204 איננו שונה באופן עקרוני, אך הוא פחות מפורט, וישDBA  המעדיפים להשתמש בו. בכל אופן, ניתוח המידע וההתחקות אחר מקורות ה-deadlock איננו פשוט כל כך,  אך הכרת מבנה פלט המידע על האירוע וניתוח מסודר ומובנה שלו יחשוף בפנינו בסופו של דבר את גורמי התופעה.

במאמר הבא בסדרה, נראה כיצד לתחקר אירוע deadlock באמצעות SQL Server Profiler.

נעילה באמצעות Application Lock

שמוליק כהן

DBA  בפרוייקט ממשלתי מטעם נאיה טכנולוגיות.

רובנו מכירים את מנגנון הנעילות של SQL Server. זהו המנגנון המונע קונפליקטים בזמן הגישה לנתונים. כך למשל, כל זמן שמשתמש מעדכן רשומה מסוימת, משתמש אחר לא יוכל לעדכן אותה או למחוק אותה, וברוב המקרים גם לא יוכל לקרוא אותה. מנגנון זה נועל ישויות בבסיס הנתונים:  רשומה,key  באינדקס, page, extent, טבלה ובסיס נתונים. 

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

הקוד יראה כך:

declare @NewCustomer int

set @NewCustomer = 12345678

if not exists (select 1

               from customers

               where ID = 12345678)

begin

      insert customers

      values (@NewCustomer)

end

כאשר האפליקציה רצה פעם אחת בלבד בו זמנית, אין שום בעיה בקטע הקוד הזה.  אולם כאשר האפליקציה רצה בו זמנית על ידי מספר מפעילים, בהחלט תיתכן בעיה.  אם הלקוח אינו קיים, אנו עלולים להגיע למצב ששני מפעילים המריצים את קטע הקוד הזה בו זמנית, ינסו להכניס את אותו הערך לטבלת customers.   מנגנון נעילת הנתונים של SQL Server לא יעזור לי, משום שהוא אינו מסוגל לנעול שורה שאיננה קיימת (נכון שבמצבים מסויימים SQL Server מסוגל לנעול טווח ערכים בטבלה (Range Lock), אבל זה לא רלוונטי לעניינינו).

הפתרון במקרה שכזה שונה: במקום לנעול נתונים, ננעל את קטע הקוד האפליקטיבי המדובר, ואז אפשר להריץ אותו רק פעם אחת בו זמנית.  זוהי המשמעות של נעילת application lock, והיא מתבצעת באמצעות פקודת sp_getapplock. בסיום הרצת הקוד יש צורך לשחרר את הנעילה באמצעות sp_releaselock. 

נעילת קטע הקוד

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

declare @LockResults int

exec @LockResults = sp_getapplock  @Resource =  'CodeName', @LockMode =  'Exclusive',  @LockOwner =  'Session',  @LockTimeout =  '3000'

נפרט את הפרמטרים לפרוצדורה:

LockResults@ - משתנה מסוג int, שמקבל את תוצאות הנעילה. אם ערכו 1 או 0 – הנעילה הצליחה. אם ערכו שלילי – הנעילה נכשלה.  העצלנים שבחבורה יכולים פשוט לבדוק אם הערך שהתקבל הוא שלילי ולטפל בכל סוגי השגיאות באותו אופן. המשקיענים יכולים לוודא את הסיבה הספציפית, ואם הסיבה היא time out הערך שיתקבל יהיה -1. במקרה זה, אפשר לנסות ולבצע את הנעילה מספר פעמים נוספות.  (כמובן שכדאי להגביל את מספר הפעמים שמנסים להשיג את הנעילה...) .

Resource@ - משתנה מסוג nvarchar(255) המגדיר את שם קטע הקוד, ועליו להיות בעל שם יחודי.

LockMode@  - משתנה מסוג nvarchar(32) המגדיר סוג הנעילה.  סוגי הנעילה האפשריים הם:  Shared, Update, IntentShared, IntentExclusive, Exclusive. ל- LockMode@ אין ברירת מחדל וחייבים לקבוע ערך עבורו.

LockOwner@ – משתנה מסוג nvarchar(32) שיכול לקבל אחד משני ערכים:  Transaction או Session.  במקרה שהוא מוגדר כ- Transaction, ניתן לקבל את הנעילה רק בתוך טרנזקציה.  ברירת המחדל של הערך היא Transaction.

LockTimout@ – משתנה מסוג nvarchar הקובע את הזמן (טקסט המציין אלפיות שניה) ש- sp_getapplock ימתין לפני שיחזיר timout. ערך ברירת המחדל הוא ערכו של משתנה המערכת LOCK_TIMEOUT@@. אם ערך זה לא אותחל הוא יקבע על -1, שמשמעותו המתנה ללא הגבלת זמן להשגת הנעילה.

שחרור קטע הקוד

לאחר ביצוע הקוד יש לזכור לשחרר אותו, אחרת הוא נשאר נעול כל זמן שה- session אינו מתנתק.  שחרור הנעילה מתבצע באמצעות הפרוצדורה sp_releaselock.  הפרוצדורה מקבלת כפרמטר את שם ה- application lock שאותה צריך לשחרר. 

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

create proc InserNewCustomer

      @CustomerID int

as

begin

      declare @LockResults int

      begin  tran

            exec @LockResults = sp_getapplock 

                   @Resource =  'InsertNewCustomer',

                   @LockMode =  'Exclusive', 

                   @LockOwner =  'transaction', 

                   @LockTimeout =  '3000'

            if @LockResults<0

            begin

                  rollback

                  return

            end

            if not exists (select 1

                           from customers

                           where ID = 12345678)

            begin

                  insert customers

                  values (@NewCustomer)

                  exec sp_releaseapplock @Resource =  'InsertNewCustomer'

            end

      commit

end

ניתן להריץ יותר מפקודת sp_getapplock אחת בפרוצדורה, אך צריך להקפיד להפעיל את sp_realeaselock כמספר פעמים שהריצו את פקודת sp_getapplock. 

שימוש בנעילה אפליקטיבית בריבוי משתמשים.

כדי לפתור את בעיית הקונפליקט שבה פתחנו נוכל להשתמש ב- sp_getapplock באופן מיוחד שיתן מענה לתסריט מורכב כזה.

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

במקרה שכזה אנו נתחיל את הפרוצדורה בהפעלת  sp_getapplockכדי לקבל נעילה מסוג shared. בהתקיים התנאי הרלוונטי, נשדרג את הנעילה ל- Exclusive (באמצעות שחרור הנעילה ונעילה מחדש כ- exclusive תחת אותו השם שהוגדר במשתנה Resource@). אם יש על קטע הקוד נעילה מסוג Shared (בשל הרצת אותה הפרוצדורה בו-זמנית ע"י משתלשים אחרים), הבקשה לנעילת ה- Exclusive. 

קוד הפרוצדורה המממש את המנגנון הזה יראה אם כן כך:

create proc InserNewCustomer

      @CustomerID int

as

begin

      declare @LockResults int

      begin  tran

            exec @LockResults = sp_getapplock 

                   @Resource =  'InsertNewCustomer',

                   @LockMode =  'Shared', 

                   @LockOwner =  'transaction', 

                   @LockTimeout =  '3000'

            if @LockResults<0

            begin

                  rollback

                  return

            END

            /*

            write non critical code here

            */

           --Releasing Shared lock and aquiring exclusive app

           --lock for critical code

            exec sp_releaseapplock @Resource =  'InsertNewCustomer'

            exec @LockResults = sp_getapplock 

                   @Resource =  'InsertNewCustomer',

                   @LockMode =  'Exclusive', 

                   @LockOwner =  'transaction', 

                   @LockTimeout =  '3000'

            if @LockResults<0

            begin

                  rollback

                  return

            END

            if not exists (select 1

                           from customers

                           where ID = 12345678)

            begin

                  insert customers

                  values (CustomerID)

                  exec sp_releaseapplock @Resource =  'InsertNewCustomer'

            end

      commit

end

כדאי, כמובן, לנסות ולמנוע כישלון של הנעילה על ידי הגדרת timeout ארוך מעט או ע"י מספר נסיונות חוזרים להשיג את הנעילה. לאחר קבלת נעילת ה- Exclusiveבהצלחה , כל נסיון להריץ את הפרוצדורה בו זמנית ע"י משתמשים אחרים יכשל, משום שלא ניתן יהיה לקבל נעילת Shared כל עוד לא שחררנו את נעילת ה- Exclusive. 

מה נראה בפרוצדורת sp_lock

sp_lock מראה את מצב הנעילות הקיימות כרגע במערכת.  בין סוגי הנעילות שניתן לראות באמצעות הפרוצדורה, ניתן לראות גם application lock.  בעמודת ה- Type יופיע הקיצור APP.

נקודות נוספות

1.       לנעילה אפליקטיבית אין מנגנון deadlock, ולכן האחריות עלינו, המפתחים, למנוע אפשרות של deadlock.

2.       ROLLBACK אינו משחרר נעילה אפליקטיבית. במידה שהגענו למצב בו עלינו לבצע ROLLBACK חובה עלינו גם לטפל בשחרור הנעילה.

3.       אם בנעילה קובעים את הערך session עבור הפרמטר LockOwner@, אזי יש להוסיף את הפרמטר הזה גם בפקודת השחרור, משום שברירת המחדל היא הערך Transaction וללא קביעת הערך המתאים בפרמטר זה, השחרור יכשל.

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

שימוש בפרוצדורות המערכת sp_msforeach

עדי כהן

מומחה SQL Server בעל כ-15 שנות ניסיון מגוון בתחום. ר"צ DBA בחברה למסחר פיננסי באינטרנט מטעם נאיה טכנולוגיות.

כמו שכולנו יודעים, יש הרבה דברים שלא מתועדים ע"י מייקרוסופט, אבל קיימים ב SQL Server. אובייקטים לא מתועדים יכולים להשתנות ללא אזהרה בגרסת שרת חדשה, בהתקנה של Service pack או בהתקנה של CU. מבחינת מייקרוסופט מי שמשתמש ב-undocumented object לוקח על עצמו את הסיכון שהקוד שלו יפסיק לעבוד ללא שום אזהרה. זאת סיבה מעולה לא להשתמש בשום אובייקט לא מתועד בקוד אפליקטיבי. 

לעומת זאת בסקריפטים אדמיניסטרטיבים, יש לנו הרבה מקרים שכדאי להשתמש בהם. הסיבה להבדל בגישה היא, שכאשר אפליקציה נופלת בגלל שימוש באוביקט לא מתועד שהשתנה, או שאפילו לא קיים יותר לאחר ביצוע שינוי בשרת (upgrade, התקנת SP וכד'), משתמשי הקצה מושפעים מכך ישירות. תיקון המצב דורש הפעלה של מפתח, או של DBA, או של אנשי QA ולא תמיד ניתן להעלות במהירות גרסה מתוקנת.  כמובן, שעד שיוצאת גרסה מתוקנת העבודה של משתמשי הקצה נפגעת.  לעומת זאת נפילה של סקריפט אדמיניסטרטיבי שלנו, לרוב לא ישפיע על משתמש קצה, הוא לא מצריך עבודה של צוות אחר חוץ מצוות ה-DBA וניתן לבצע שינוי במהירות.

שתי פרוצדורות לא מתועדות שקיימות כבר הרבה שנים  הן sp_msforeachdb ו-sp_msforeachtable.  שתי הפרוצדורות מקבלות כפרמטר מחרוזת טקסט (String) שמכיל קוד SQL, ומריצות אותו על כל בסיסי הנתונים בשרת (במקרה של sp_msforeachdb) או על כל טבלה בבסיס נתונים ספציפי (במקרה של sp_msforeachtable).  מאחר ששתי הפרוצדורות רצות על מספר אובייקטים (או על כל בסיס נתונים או על כל טבלה) הדרך שלנו לטפל באובייקט הנוכחי היא להשתמש בסימן שאלה (?) אשר יחליף בתוך הקוד את שם בסיס הנתונים או הטבלה. 

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

לדוגמא הפקודה:

exec sp_spaceused 'MyTable'

מחזירה פרטים על הטבלה MyTable.

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

exec sp_MSforeachtable 'exec sp_spaceused ''?'''

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

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

בדוגמא הבאה אין צורך לעטוף את סימן השאלה בגרשיים, כי פשוט מבצעים select top 1 מכל טבלה:

exec sp_MSforeachtable 'select top 1 * from ?'

סימן השאלה הוא שומר מקום (place holder) לשם הטבלה. מאחר שבמשפט select אין צורך לעטוף את שם הטבלה בגרשיים,  לא עטפתי במקרה זה את שם הטבלה בגרשיים.

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