שרת SQL מגדיל באופן משמעותי את שטח לא מנוצל עבור טבלאות מסוימות

סיכום

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

מאפייני הבעיה

שקול את התרחיש הבא ב- SQL Server:
  • מופע של SQL Server יש מסדי נתונים של משתמש אחד או יותר.
  • מספר הטבלאות במסדי נתונים אלה המצטברת היא גדולה מסף מסוים המופיעה בטבלה בסוף סעיף זה. טבלאות אלה כוללות טבלת המערכת, הטבלה המשתמש ואת הטבלה הזמנית.
  • יישומים המחוברים למופע של SQL Server הפניה רוב טבלאות אלה.
בתרחיש זה, ייתכן שתבחין בתופעות הבאות:
  • הרווח שצורכות מסדי נתונים אלה משתמש גדלה בקצב מהיר הרבה יותר התעריף הרגיל. בהתאם להגדרות Autogrowth עבור מסדי נתונים של משתמש אלה, קבצי מסד הנתונים עשוי לגדול בתדירות גבוהה יותר מאשר הם צומחים במקרים אופייני.
  • החלק שאינו בשימוש של הרווח שצורכות מסדי נתונים אלה המשתמש יהיה גדול יותר החלק שאינו בשימוש טיפוסי.
  • בעת הצגת המאפיינים של מבני אחסון עבור מסדי נתונים אלה משתמש, כגון מבנה אחסון ערימה, עץ תמונה של טקסט אינדקס מקובץ באשכולות, אתה רואה כמות גדולה של שטח לא מנוצל.
  • הגדלת השטח השמור עבור ערכי אינדקס בטבלה sysindexes בכפולות של 8. עם זאת, מגדילה השטח בשימוש עבור ערכי אינדקס בטבלה sysindexes מספר קטן בלבד, כגון 1 או 2. כלומר, עבור כל שמונה עמודים המוקצות בשטח הרציף חדשות, עמודים בודדים בלבד אי פעם משמשים מתוך במידה זו.
סף שנידונו מוקדם יותר במאמר זה משתנה בהתאם לתנאים הבאים:
  • מהדורה ספציפית של SQL Server בה אתה משתמש
  • זיכרון שתצורתם נקבעה עבור שרת SQL
הטבלה הבאה מפרטת את אלה הסף עבור מהדורות שונות של שרת SQL.
מהדורהזיכרון (בתים)סף
Enterprise/Standard/Developerיותר מ- 671,088,6408192
Enterprise/Standard/Developerפחות מ- 671,088,6402048
Enterprise/Standard/Developerפחות מ- 67,108,864512
Enterprise/Standard/Developerפחות מ- 8,388,608128
Personal/MSDEלא ישים128
הזיכרון תואמת להגדרה זיכרון מרבי של השרת עבור המופע של SQL Server בה אתה משתמש. באפשרותך להשתמש בהליך מערכת המאוחסנים sp_configure כדי לקבוע את התצורה של הגדרות הזיכרון שרתים לכל היותר .

הערה ב- SQL Server 2005, באפשרותך לבצע שאילתה על תצוגת קטלוג sys.dm_db_partition_stats כדי להשיג את המידע שטח בשימוש ואת המידע השמור. ב- SQL Server 2000, באפשרותך לבצע שאילתה על הטבלה sysindexes כדי להשיג מידע זה.

הגורם

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

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

פתרון הבעיה

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

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

הערה לאחר שתמיר את הטבלאות ערימה לטבלאות המשתמשות אינדקסים clustered, באפשרותך לבצע פעולות תחזוקה אינדקס מעת לעת כדי לפנות שטח דיסק הנמצא בשימוש ללא צורך. לדוגמה, באפשרותך להפעיל את הפקודות הבאות:
DBCC DBREINDEXDBCC INDEXDEFRAG 
הבאה היא רצף פעולות המתרחשות בעת הוספת רשומה בטבלה ערימה אופייני:
  • 1.נסה להוסיף שורה לטבלה.
  • 2.ראה את המטמון שטח פנוי עבור מזהה אינדקס 0 של טבלה זו.
  • 3.לקבוע אם קיימות חוקי עמודים במטמון שטח פנוי.
  • 4.אם כן:
    • 4.1.אם יש די מקום בעמוד, להוסיף נתונים בעמוד.
  • 5.אם לא:
    • 5.1.שרת SQL חייב להקצות דף חדש עבור שורה זו.
    • 5.2.ראה את המטמון הקצאה עבור מזהה אינדקס 0 של טבלה זו.
    • 5.3.לקבוע אם קיימות הרחבות חוקי במטמון הקצאה.
    • 5.4. אם כן:
      • 5.4.1.לקבוע אם כל אחד מהעמודים שמונה בשטח הרציף יכול לשמש עבור בקשת הקצאה זה דף חדש.
      • 5.4.2. אם כן:
        • 5.4.2.1.Allocate עמוד אחד מתוך זה במידה שהוקצה כבר.
        • 5.4.2.2.עבור לשלב 5.5.5.
      • 5.4.3.אם לא:
        • 5.4.3.1.עבור לשלב 5.5.1.
    • 5.5.אם לא:
      • 5.5.1.להשתמש בנתונים מתוך מפת הקצאת הכללית (GAM) וכן את משני כללי הקצאה מפת (SGAM) כדי למצוא את ההיקף חדש שיש להקצות.
      • מופיע בתבנית 5.5.2.להקצות מידה חדשה.
      • 5.5.3.השתמש בדף אחד מתוך ההיקף חדשה זו כדי לענות על הבקשה הקצאת דף חדש משלב 5.1.
      • 5.5.4.לאכלס את המידע אודות זה במידה שהוקצה לאחרונה במטמון הקצאה.
      • 5.5.5.אכלס המידע אודות נושא זה עתה להקצות את הדף במטמון שטח פנוי.
אם המטמון הקצאה ומטמון שטח פנוי ריקות בין פעולות הוספה מאוחר יותר, ל- SQL Server להקצות דפים חדשים מתוך הרחבות חדשות כך תוכל להצליח פעולות הוספה. המטה-נתונים של טבלה מוסר מן הזיכרון, המטמון הקצאה ומטמון שטח פנוי גם מוסרים. לכן, בפעם הבאה שתבצע פעולה הוספה שמפנה את הטבלה, מטמוני אלה הן ריקות. במצב זה, שרת SQL חייב לבצע שלב 5 ולאחר מכן שלב 5.5. התנהגות זו גורמת הרחבות לאחרונה שהוקצה להצגת שמונה עמודים מוקצים כאשר נעשה שימוש על-ידי עמוד אחד בלבד. במקרה הגרוע ביותר, ייתכן 56 קילו-בתים (KB) של שטח מבוזבז עבור כל פעולת הוספת שאתה מבצע על הטבלה.

הרשימה הבאה היא רצף אופייני של פעולות המתרחשות בעת הוספת נתונים לטבלה בעלת אינדקס מקובץ באשכולות.
  • 1.נסה להוסיף שורה בטבלה.
  • 2. העבר את העץ-B כדי לחפש את דף הנתונים שבו שרת SQL עליך לאחסן את מפתח קיבוץ באשכולות.
  • 3. קבע אם אין די שטח פנוי בהדף עבור השורה החדשה.
  • 4. אם כן:
    • 4.1. הוספת נתונים בדף זה.
  • 5. אם לא:
    • 5.1. שרת SQL חייב להקצות דף חדש עבור שורה זו.
    • 5.2. ראה את המטמון הקצאה עבור מזהה אינדקס 1 של טבלה זו.
    • 5.3. לקבוע אם קיימות הרחבות חוקי במטמון הקצאה.
    • 5.4. אם כן:
      • 5.4.1.לקבוע אם באפשרותך להשתמש בכל אחד שמונה עמודים בשטח הרציף עבור בקשת הקצאה זה דף חדש.
      • 5.4.2. אם כן:
        • 5.4.2.1. Allocate עמוד אחד מתוך זה במידה שהוקצה כבר.
      • 5.4.3. אם לא:
        • 5.4.3.1. עבור לשלב 5.5.1.
    • 5.5. אם לא:
      • 5.5.1. להשתמש בנתונים מתוך GAM ו- SGAM כדי למצוא את ההיקף חדש שיש להקצות.
      • מופיע בתבנית 5.5.2. להקצות מידה חדשה.
      • 5.5.3. השתמש בדף אחד מתוך במידה זו כדי לענות על הבקשה הקצאת דף חדש משלב 5.1.
      • 5.5.4. לאכלס את המידע אודות זה במידה שהוקצה לאחרונה במטמון הקצאה.
אם המטמון הקצאה ומטמון שטח פנוי מסומנת מסיבות המתוארות בסעיף 'סיבה', אין צורך מיידי כדי להקצות דף חדש בין פעולות הוספה מאוחר יותר עבור טבלה זו. הדבר נכון כל עוד הנתונים שנוספו ניתן לכלול בדף קיים בו המפתח קיבוץ באשכולות מסוים חייב לשכון פיזית. כאשר הדף נתונים מתמלא, ואם מטמוני הן ריקות, שרת SQL חייב לבצע שלב 5, ולאחר מכן למעבר 5.5. כפי מאמר זה מציין, אם תשתמש אינדקס מקובץ באשכולות, תרחיש שבו מוקצים דפים חדשים מתרחש לעיתים קרובות הרבה פחות מאשר התרחיש היכן להוסיף רשומה בטבלה ערימה.

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

הגדר האובייקטים הפתוחים אפשרות התצורה של ערך גבוה

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

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

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

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

מצב

Microsoft אישרה כי מדובר בבעיה במוצרי Microsoft הרשומים במקטע 'חל על'.

מידע נוסף

מדוע הגדרות האפשרויות של האובייקטים הפתוחים להוביל לבעיה זו

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

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

מצב 1

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

מצב 2

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

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


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

הפניות

לקבלת מידע נוסף אודות אופן השימוש הפרוצדורה המאוחסנת sp_configure כדי לשנות את אפשרויות התצורה, בקר באתר האינטרנט הבא של Microsoft מפתח רשת (MSDN):לקבלת מידע נוסף אודות האפשרות לפתוח אובייקטים , בקר באתר האינטרנט של MSDN הבא:לקבלת מידע נוסף אודות האפשרות תצורת זיכרון שרתים לכל היותר , בקר באתר האינטרנט של MSDN הבא:לקבלת מידע נוסף אודות ארכיטקטורת טבלה ואינדקס ב- SQL Server, בקר באתר האינטרנט של MSDN הבא:
מאפיינים:

מזהה פריט: 924947 - סקירה אחרונה: 15 בינו׳ 2017 - תיקון: 1

משוב