כולנו (או לפחות, כל מי שיצר טבלה ב- DB) מכיר את הרגע שבו הוא מגיע לקבוע data type של עמודה טקסטואלית, בוחר varchar/nvarchar וצריך לקבוע את הגודל המקסימלי. האם לשים 10? 20? 100? הרי אף אחד לא יודע מה יילד יום. וכשלא יודעים מה יקרה – יש כאלה שפשוט בוחרים ללכת על המקסימום. וככה נולדות להן הרבה עמודות nvarchar(max) / varchar(max), כי הרי למה להגביל היום ולחטוף את השגיאה של string or binary data would be truncated מתישהו בעתיד?
דרך נוספת שבה עמודות כאלה נולדות – היא משימוש ב- Entity Framework Code First ושאר ה-ORM-ים. אם מגדירים string property במודל, ומייצרים ממנו באמצעות EF Code First טבלה, אז Entity Framework שואף לשמר את ההתנהגות של System.String, שאינו מוגבל באורכו, ולכן מגדיר את העמודה בתור nvarchar(max) (ניתן כמובן להשתמש ב- StringLengthAttribute כדי להגדיר אורך שונה, אבל זאת פעולה נוספת, שהרבה מפתחים לא עושים).
לכאורה, באמת לא ברור מה המשמעות של ההגבלה. הרי כשאני כותב קוד ב- C#, אף אחד לא מבקש ממני להגיד מה גודל ה-string המקסימלי שאני מחזיק בזמן ההצהרה. למה שאני אדרש לכזה דבר בהגדרת הטבלה? ואם אני כבר נדרש, מה רע בלשים את ה- max?
אז ללבחור max יש כמה חסרונות, והמטרה של הפוסט הזה היא להתמקד בעיקריים שבהם.

אתם מקשים על MSSQL לייצר Execution Plan טוב

שאילתות ב- SQL מגדירות איך אנחנו רוצים שהתוצאה תיראה, ולא כיצד צריך לבצע את הפעולות כדי להגיע לתוצאה. מי שמחליט בסופו של דבר מבין שלל הדרכים להריץ את השליפה, באיזה דרך לבחור, הוא המנוע DB עצמו.
כדי להגיע להחלטה הזאת, הוא נעזר בשלל כלים – אחד הבסיסיים בהם הוא הסכימה של הטבלה, והמידע שקיים כחלק מהסכימה. למשל, אם מוגדרת על עמודה מסויימת unique constraint, זה מידע ש- SQL Server יכול להשתמש בו כדי לייצר execution plan. מידע נוסף שהוא משתמש בו, הוא הערכה של גודל הנתונים שיהיו מעורבים בביצוע. יש הבדל בין הדרכים האפקטיביות לבצע, למשל, JOIN של טבלה קטנה עם טבלה קטנה, טבלה קטנה עם גדולה או שתי טבלאות גדולות.
כדי להעריך את כמות הנתונים, בנוסף לסטטיסטיקות שונות, ה- query optimizer (הרכיב שבוחר את ה- execution plan) מסתמך גם על הגדרת הטבלה עצמה, כדי להעריך מה גודל שורה שמוחזר מכל אופרטור ב- execution plan. במקרה של ערכים שהגודל שלהם הוא קבוע (כמו int, bigint, nchar וכו’) – הוא יודע להגיד את הגודל המדוייק. במקרה של עמודות עם אורך משתנה, הוא נדרש לנחש, כאשר הבסיס לניחוש הוא הגודל המקסימלי המוצהר של העמודה.

לשם הדוגמא, לקחתי את טבלת Posts של StackOverflow, עם הנתונים מ- 2016 בלבד, (ניתן להוריד מכאן)  – והשוויתי בין שתי סכימות של הטבלה: בראשונה העמודה Title מוגדרת בתור nvarchar(250) ובשנייה בתור nvarchar(max).

הרצתי שתי שליפות:

SET STATISTICS IO ON

SET STATISTICS TIME ON

SELECT COUNT(DISTINCT Title)

FROM PostsMax a

GO

SELECT COUNT(DISTINCT Title)

FROM Posts a

השליפה הראשונה לקחה קצת יותר מ-29 שניות (90 שניות של CPU Time, ה-execution plan כלל parallelism), בעוד שהשליפה השנייה לקחה כ-5 שניות (18 שניות של CPU Time). למה הפער הכ”כ מהותי הזה?  כי ה- execution plan היה שונה. הסיבה לשוני היא  ה- estimation לגבי גודל השורה. הפער אפילו ברור יותר כאשר עושים SELECT פשוט מהטבלאות:

כלומר – אם אתם מגדירים סתם עמודות nvarchar(max) ו- varchar(max), או עמודות עם גדלים שמנותקים מהגודל האמיתי של הנתונים, אתם מקשרים על ה- query optimizer להביא execution plan טוב כי אתם מכריחים אותו להסתמך על נתונים שגויים. אם יודעים מה הגודל הצפוי – רצוי פשוט להגדיר אותו.

Compression

בפוסט הקודם נגעתי קצת ב- compression מזווית של דחיסת נתונים גדולים. בפרט, ציינתי ששימוש ב- row compression או page compression לא עובד באותה הצורה עבור עמודות שמוגדרות max (למשל, עמודות nvarchar(max) לא עוברות unicode compression). הנ”ל יכול לגרום לכם לאכסן סתם יותר מידע בדיסק, ולבזבז סתם יותר זיכרון ב- buffer pool – ולחינם (אם הגודל של המידע לא באמת מצדיק את זה).

והערה כללית לגבי שמירת ערכים גדולים ב- DB

על אף שלפעמים זה נדרש, כאשר רואים שיש טבלה שבמסגרתה יישמרו שורות גדולות ב- DB (בין אם בעמודות max, ובין אם אוסף של עמודות מוגבלות יותר בגודלן) – צריך לחשוב האם זה הפיתרון הנכון. לטבלה כזאת יש מס’ חסרונות –

• הן נוטות יותר לפרגמנטציה (למשל בגללעדכונים שבהם משתנה גודל הטקסט, או בגלל ששורות חדשות ממוקמות ב- page נפרד כדי שיהיה מקום לכל השורה בתוך ה- page)
• ה- data access pattern בשורות כאלה הוא פחות יעיל – כאשר הגודל של השורה עובר את ה- 8KB (הגודל של page בודד), MSSQL יפצל את השורה, ויעביר חלק מהמידע ל- overflow pages, כאשר בשורה עצמה יישמר pointer ל- overflow page. הקפיצות הללו יוצרות IO pattern לא יעיל, שרצוי להימנע ממנו
• full table scans יהיו יותר איטיים – בגלל שהטבלה יותר גדולה, שליפות שדורשות full table scan (למשל, לצרכי reporting, או שליפות ad-hoc), יצטרכו לעבור על יותר data, גם אם ה- data המעניין הוא יחסית קטן. כמובן שיש לזה פתרונות שונים (בין אם לפצל את העמודות הגדולות לטבלאות נפרדות, אינדקסים על הערכים של העמודות הקטנות יותר, שימוש ב- columnstore indexes ועוד)
• הערכים הגדולים, שנטענים לזיכרון ל- buffer pool, מגיעים על חשבון data שאולי חשוב לנו יותר שיישמר ב- cache, וגורמים לזה שמידע יפונה יותר מהר לדיסק – ולכן יידרש יותר IO.

במקרה כזה, כדאי לשקול פתרונות אחרים – שמירה של המידע בדיסק עם הפנייה מה- DB, שימוש ב- FILESTREAM tables, שמירה של המידע דחוס ב- DB ועוד.

מזה מספר שנים, SQL Server מציע באופן מובנה יכולת של Data Compression. מדור ביכולת לדחיסת המידע בטבלאות ואינדקסים, בין אם “רגילים” (row store) או clustered / nonclusted columnstore indexes.
כאשר מדובר על דחיסה של אינדקסים “רגילים” – אנחנו יכולים לבחור בין דחיסה ברמת השורה – שהיא בעיקרה רלוונטית ל- fixed length data types, שהשמירה שלהם נעשית יותר יעילה במקום,  או ב- page level compression שמבצע בפועל row level compression של כלל השורות באותו ה- page, ומעל זה מוסיף רובד של prefix compression (שמקטין את הנפח במידה שיש ערכים מייצגים בעמודות, או לחלופין תחיליות משותפות) ו- dictionary compression (שמחליף ערכים חוזרים בהפנייה למילון שנשמר כחלק מה- metadata, ומקטין את הנפח במידה שיש חזרתיות גבוהה של ערכים ברמת ה- page).
בין אם משתמשים ב- row level compression או ב- page level compression, מופעלת גם היכולת של unicode compression שמקטינה את הנפח במידה שהעמודה מוגדרת בתור nvarchar או nchar, אולם בפועל עיקר הטקסט שבו נעשה שימוש לא באמת מתפרס על ה-2 בתים שמוקצים ב- UCS-2 לכל תו (כלומר, לאחר הדחיסה מתקבלת התנהגות שמזכירה יותר את UTF-16 במובן שלא נדרשים בפועל תמיד 2 בתים לכל תו, אלא ניתן להסתפק בתו בודד). זה יכול במקרים רבים לחסוך עד 50% (כי עבור הרבה מאד שפות, לא צריך להתפרס על 2 בתים כדי לייצג את התו).

אולם, יש מס’ חסרונות ל- page/row level compression, בעיקר כשבאמת רוצים לדחוס כמות גדולה של data. החיסרון הראשון הוא אופי הדחיסה – האלגוריתם של מיקרוסופט נבחר (ככה”נ, אני מין הסתם לא יודע את זה בוודאות) מתוך מחשבה על איזון בין חיסכון במקום לבין צריכת CPU בזמן ה- compression וה- decompression. מדובר באיזון חשוב, מאחר שחלק מהיתרונות של ה- compression הוא שהמידע נשמר דחוס גם ב- buffer pool, מה שנותן חיסכון בזיכרון – אבל שכפול של עלות ה-decompression בכל שימוש במידע. אולם, בהשוואה לאלגוריתמי דחיסה אחרים (DEFLATE), הוא מכיל פחות שלבים שיכולים לשפר את האפקטיביות של הדחיסה.
חיסרון נוסף, שנובע ככה”נ מאותו השיקול שהזכרתי קודם – מיקרוסופט בכלל לא מתיימרים לדחוס כמות גדולה של מידע- הדחיסה מתבצעת רק ל- in-row data, כלומר למידע שנשמר ב-page של השורה עצמה. מידע שנשמר ב- row-overflow page לא נדחס כלל. אגב, ה- unicode compession (ודחיסה באופן כללי) לא מופעל בכלל עבור עמודות שמוגדרות בתור varchar(max), nvarchar(max).

במרבית המקרים, ה- trade-off של מיקרוסופט בין העלות CPU של ה-compression/decompression ב- row/page level compression בהחלט נשמע הגיוני. אולם, יש מקרים שבהם אנחנו נעדיף דחיסה טובה משמעותית. מקרה כזה לדוגמא, הוא טבלאות שמכילות מידע שנשמר “אם יהיה צורך”. מדובר למשל בטבלאות, שמכילות עמודות nvarchar(max) עם איזשהו JSON גדול, שאמנם כל המידע המעניין ממנו כבר מחולץ ונשמר בטבלאות אחרות בצורה רלציונית סטנדרטית, אולם שומרים את ה- JSON קלט המלא למקרה שיהיה צורך לתשאל בדיוק איזשהו שדה שאולי לא חולץ, או לעשות פעולה אחרת על המידע שלא תוכננה מראש. אלה עמודות שאול אפשר היה לשמור אותן אפילו כקבצים על הדיסק, ולשמור רק את המיקום ב-DB, אבל מכיוון שיש את היכולת שליפות על ה- JSON-ים באופן מובנה בצורה נוחה ב- SQL Server, מעדיפים בכל זאת לשמור את המידע בטבלה – ולחסוך את הסקריפט שיאסוף את המידע מהדיסק במידת הצורך.
כלומר, מדובר במידע שאנחנו כנראה לא מעוניינים לתשאל (למעט אולי במקרים נדירים), ולכן לא אכפת לנו ממש מה-CPU – אבל כן רוצים לשמור. בדיוק עבור מקרים כאלה, יש לנו (החל מ-SQL Server 2016) את הפונקציות COMPRESS ו- DECOMPRESS.

הפונקציה COMPRESS מקבלת data ומחזירה אותו אחרי דחיסת GZIP, ו-DECOMPRESS מקבלת varbinary שמכיל את ה- GZIP, ומחזירה את ה- plain data שבתוכו.

Benchmark

לטובת ה- Benchmark, לקחתי את ה-DB לדוגמא שמבוסס על ה-DB של Stackoverflow וכולל את כל השאלות שנשאלו, והתשובות שנענו, בשנת 2016. כדי לייצג את הטבלה שלנו, זאת עם ה- JSON הגדול, אני אייצר טבלה שכוללת עבור כל tag שקיים באתר עמודה עם ה- Id של ה- tag ו-JSON שכולל את כל השאלות שנשאלו באותו ה- tag, ולכל שאלה עוד JSON פנימי שכולל את כל התשובות שנענו לאותה השאלה. כלומר, בסופו של דבר נקבל JSON-ים בגודל מכובד למדי.

ה-SQL כדי לייצר את הטבלה ולמלא אותה בנתונים נראה כך:

SET STATISTICS TIME ON

SET STATISTICS IO ON

CREATE TABLE [dbo].[TagsExtendedInfoUncompressed](

    [TagId] [int] NOT NULL,

    [TagName]  nvarchar(100) NOT NULL,

    [ExtendedInfoJson] nvarchar(max) NULL,

 CONSTRAINT [PK_TagsExtendedInfoUncompressed] PRIMARY KEY CLUSTERED (TagId)

) 

;with data as (

    SELECT

        TagId = t.Id,

        TagName = t.TagName,

        QuestionsJson =    (

                            SELECT 

                                    q.*,

                                    RepliesJson =    (

                                                        SELECT *

                                                        FROM Posts a WITH(FORCESEEK)

                                                        WHERE a.ParentId = q.Id

                                                        FOR JSON AUTO

                                                    )

                            FROM Posts q WITH(FORCESEEK)

                            JOIN PostsToTags ON PostsToTags.PostId = q.Id AND PostsToTags.TagId = t.Id

                            WHERE q.ParentId IS NULL

                            FOR JSON AUTO

                        ) 

    FROM Tags t

)

INSERT INTO [TagsExtendedInfoUncompressed](TagId, TagName, ExtendedInfoJson)

SELECT TagId, TagName,QuestionsJson

FROM data

זמן הריצה שלו היה 308 שניות, ה- CPU time היה 285 שניות  וגודל הטבלה שנוצרה בסוף הוא 8978MB (והיא כוללת 47,392 שורות – מקרה שבו ה- JSON-ים באמת מאד גדולים).

הרצה נוספת של אותו ה- SQL רק עם הוספה של DATA_COMPRESSION = PAGE להגדרת ה- primary key בטבלה, הסתיימה לאחר 281שניות (ה- CPU time היה 270 שניות) וגודל הטבלה 8766MB– שזה כמעט זהה לחלוטין לגודל הטבלה המקורית. למה זה המצב? כי הטבלה שלנו מכילה BLOB-ים גדולים, שה- page compression של SQL Server בכלל לא מיועד לעבוד עליהם, ולא נוגע בהם כלל. כלומר, ה-page compression של SQL Server לחלוטין לא אפקטיבי במקרה הזה.
למעשה, עבור כל JSON שהוא גדול מספיק כדי שיוגדר nvarchar(max), varchar(max) הדחיסה של SQL Server תהיה מאד לא אפקטיבית. לכן, אם יש לכם JSON-ים שאתם שומרים ואתם יודעים שהגודל יהיה פחות מ-8000 תווים עבור טקסט ללא יוניקוד, או 4000 תווים לטקסט עם יוניקוד – אל תגדירו את העמודות כ- max, כי יהיו לזה השפעות משמעותיות על הדחיסה שתקבלו.

אולם, במקרים כמו המקרה שבחרתי ל- test case הנ”ל – יש לנו באמת JSON-ים שעוברים את הגודל הזה (אפילו באופן משמעותי). כאמור, החל מ- SQL Server 2016 אנחנו יכולים לבחור לבצע GZIP Compression ל- data באמצעות הפונקציה COMPRESS, וכך להקטין את הגודל של ה- data.

נעשה עכשיו את אותה הבדיקה עם הפונקציה COMPRESS, ונריץ את ה- SQL הבא (שימו לב שהעמודה בטבלה מוגדרת כ- varbinary(max) במקרה הזה, וכמובן שאנחנו לא מפעילים גם page compression – אין היגיון בלנסות לדחוס מידע שהוא כבר דחוס):

SET STATISTICS TIME ON

SET STATISTICS IO ON

CREATE TABLE [dbo].[TagsExtendedInfoGzipCompressed](

    [TagId] [int] NOT NULL,

    [TagName]  nvarchar(100) NOT NULL,

    [ExtendedInfoJson] varbinary(max) NULL,

 CONSTRAINT [PK_TagsExtendedInfoGzipCompressed] PRIMARY KEY CLUSTERED (TagId) WITH(DATA_COMPRESSION=PAGE)

) 

;with data as (

    SELECT

        TagId = t.Id,

        TagName = t.TagName,

        QuestionsJson =    (

                            SELECT 

                                    q.*,

                                    RepliesJson =    (

                                                        SELECT *

                                                        FROM Posts a WITH(FORCESEEK)

                                                        WHERE a.ParentId = q.Id

                                                        FOR JSON AUTO

                                                    )

                            FROM Posts q WITH(FORCESEEK)

                            JOIN PostsToTags ON PostsToTags.PostId = q.Id AND PostsToTags.TagId = t.Id

                            WHERE q.ParentId IS NULL

                            FOR JSON AUTO

                        ) 

    FROM Tags t

)

INSERT INTO [TagsExtendedInfoGzipCompressed](TagId, TagName, ExtendedInfoJson)

SELECT TagId, TagName,COMPRESS(QuestionsJson)

FROM data

זמן הריצה במקרה הזה היה ארוך משמעותית בהשוואה להכנסת המידע ללא דחיסה, או עם page compression (שלא מייצר overhead במקרה הזה, מאחר שהוא לא נוגע ב-LOB, ולכן גם לא דוחס לנו כמעט בכלל).  הריצה לקחה 608 שניות, כמעט פי 2 מהריצה עם page compression. אולם, הדחיסה הייתה גם משמעותית יותר טובה – נפח הטבלה שהתקבלה הוא 908MB, שזה חיסכון של כ-90% בנפח המידע.

מבחינת השליפות, אם נריץ את השליפה הבאה:

SET STATISTICS IO ON

SET STATISTICS TIME ON 

SELECT JSON_VALUE(ExtendedInfoJson, '$[0].Id')

FROM [SO-2016].[dbo].[TagsExtendedInfoPageCompressed]

נגלה שעבודה כלשהי מול הטבלה עם ה- page compression עולה לנו (במקרה הזה) 5.3 שניות (מתוכן 1.5 זמן CPU).

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

SET STATISTICS IO ON

SET STATISTICS TIME ON 

SELECT JSON_VALUE(CAST(DECOMPRESS(ExtendedInfoJson) as nvarchar(max)), '$[0].Id')

FROM [SO-2016].[dbo].[TagsExtendedInfoGzipCompressed]

אז נצטרך להמתין משמעותית יותר זמן – 86 שניות – פי 16 יותר זמן.

Offloading של ה- Compression וה- Decompression

ראינו שהפונקציות COMPRESS ו- DECOMPRESS אינן זולות (כי דחיסת GZIP אינה זולה). אולם, העובדה שאנחנו עושים שימוש בשיטת דחיסה סטנדרטית, מאפשר לנו לעשות offload של הדחיסה (שהיא פעולה שיכולה להיות CPU intensive) מהשרת MSSQL שלנו, ל- client שמכניס או שולף. זאת בניגוד ל-page compression, שחייב להתבצע בצד של ה- MSSQL. ביצוע offload ל- client יכול להוריד עומס מה- MSSQL, לבזר את התהליך (אם יש מס’ תהליכים במס’ שרתים שמבצעים את ההכנסה), ולהקטין את התעבורה של המידע שעוברת על הקו (כי הוא כבר יעבור דחוס).

את הדחיסה ניתן לבצע מקוד .NET באמצעות שימוש ב- GZipStream. הדבר היחיד שחשוב לזכור הוא שבמידה שרוצים לדחוס טקסט יוניקודי, כדי לקבל את ה- byte array עליו נבצע את ה- compression יש להשתמש ב- Encoding.Unicode.GetBytes. שימוש ב- UTF-8, למשל, לא יאפשר cast של ה- data ל- nvarchar(max).
ביצוע offload של הדחיסה לאפליקציות שמבצעות את טעינת החומר, יכול להקל את ה- performance penalty שמתלווה לשימוש ב- GZIP Compression, ולמנוע מה- MSSQL להפוך ל- bottleneck באיזור הזה.

סיכום

ראינו שיש מקרים שבהם row/page compression הם פחות אפקטיביים – בין אם כי מדובר ב- LOB-ים, ובין אם כי נדרשת דחיסה טובה יותר מהדחיסה שהם מספקים. במקרים האלה, כאשר ה- data הוא “ארכיוני” באופיו (כזה שאין שליפות קבועות שמתבססות עליו), ניתן לעשות שימוש ב- GZIP Compression ב- SQL Server באמצעות הפונקציות COMPRESS/DECOMPRESS.