none
תופעה מוזרה בהכנת רשומה ל sql server 2012 בעזרת c# RRS feed

  • שאלה

  • שלום לכולם,

    אני מפעיל פרוצדורת INSERT מאוד מאוד פשוטה דרך C# (הפרוצדורה יושבת בתוך ה-SQL SERVER).

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

    (לא הצלחתי להבין מתי בדיוק זה קורה - זה לא עקבי) המספר הרץ עולה ב-1000.

    תראו בהכנסות האחרונות שלי את ערכי המפתח:

    146
    147
    148
    150
    151
    152
    153
    154
    155
    1155
    1156
    2156
    2157
    3156
    3157
    3158
    3159
    3160
    3161
    3162
    3163
    4163

    לא הצלחתי להבין מתי בדיוק הקפיצות מתרחשות, לפני זה השתמשתי באותה הפרוצדורה ב SQL SERVER 2008

    ושם התופעה לא התרחשה.

    למישהו יצא להתקל בדבר כזה? יודע ממה זה נובע?

    הנה תוכן פרוצדורת ה INSERT:

    	@a	date,
    	@b      date,
    	@c      varchar(100),
    	@d      int,
    	@e      int,
    	@f      varchar(150),
    	@g 	varchar(350),
    	@p_IsOK	INT OUTPUT,
    	@x	INT OUTPUT
    	)
    	-- Add the parameters for the stored procedure here
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET @p_IsOK = @@ERROR
    	
    	INSERT INTO dbo.table(aa, bb, cc, dd, ee, ff, gg)
    	VALUES (@a, @b, @c, @d,@e, @f, @g)
    	
    	SET @x= @@IDENTITY

    שיניתי את שמות הפרמטרים מאחר ואיני יכול לפרסם את תוכן הקוד.

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

    של המספר הרץ.

    תודה מראש על העזרה

    אופיר


    • נערך על-ידי ofir_bl יום שני 14 מאי 2012 12:54
    • הועבר על-ידי ipdd יום שני 14 מאי 2012 18:30 שייך לפורום SQL (מאת:C# and .NET Framework)
    יום שני 14 מאי 2012 12:52

תשובות

  • הי אופיר,

    הפונקציה IDENTITY@@ מחזירה את ערך ה-IDENTITY האחרון שנוצר ב-Session שלך. הפונקציה SCOPE_IDENTITY מחזירה את ערך ה-IDENTITY האחרון שנוצר ב-Batch שלך. ההבדל הוא טריגרים. אם יש לך טריגר על הטבלה, והטריגר מבצע INSERT על טבלה אחרת, שגם בה יש IDENTITY, אז הפונקציה IDENTITY@@ תחזיר את הערך מהטבלה השניה, ואילו הפונקציה SCOPE_IDENTITY תחזיר את הערך מהטבלה שלך.

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

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי pituachMVP, Editor יום שישי 18 מאי 2012 22:53
    יום שני 14 מאי 2012 19:13
    מנחה דיון
  • סיבה הגיונית לבעיה כזו יכולה להיות תוצאה ישירה של נסיון להכניס 1000 רשומות למשל לשדה identity בטרנזקציה שלא מסיימת. אם הטרזקציה נכשלה המספרים כבר נתפסו והמספור לא מתאפס כשמבוצע למשל . rollback . דבר זה יכול לקרות גם בפעולות על כמות לא קבועה כפי שאסביר בהמשך.

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

    use QQ
    go
    
    CREATE TABLE ValueTable ([id] int identity,[value] int )
    GO
    
    DECLARE @TransactionName varchar(20) = 'Transaction1';
    BEGIN TRAN @TransactionName
           INSERT INTO ValueTable VALUES(1), (2);
    ROLLBACK TRAN @TransactionName;
    go
    
    select * from ValueTable
    GO
    
    INSERT INTO ValueTable VALUES(1),(2);
    select * from ValueTable
    go
    
    DROP TABLE ValueTable;

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

    אם אתה למשל מכניס את הנתונים בעזרת BULK INSERT והפעולה מבוצעת בכפולות של 1000 אז קיבלת דוגמה מעשית לכך (החלוקה ל 1000 יכולה להיות על ידך או על ידי ההגדרה של כמה דטא להעביר בכל batch ישנם הרבה פרמטרים שקובעים סופו של דבר את הכמות כמו BATCHSIZE, KILOBYTES_PER_BATCH, ROWS_PER_BATCH )

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

    * זה מתאים לאפיון שאתה מספר והנפילות יכולות להיות תוצאה של deadlock או כל דבר אחר שהוא אקראי אבל הנזק יהיה תמיד קבוע (1000 רשומות למשל)

    - דוגמה נוספת היא כאשר אתה מריץ כמה פעולות במקביל של INSERT של מספר רשומות גדול. הפעם אני לא מדבר על פעולות בכמות מדוייקת של 1000 למשל. אפילו אם הפעולה מבוצעת בטרנזקציה אחת הרי שאם הם מבוצעות במקביל ה ID לא יהיו בהכרח רציפים. למשל אם תריץ 2 טרנזקציות שכל אחת מכניסה מליון רשומות(או פחות) הרי שהרשומות לא יהיו מליון ID רציפים מטרנזקציה אחת ומליון הבאים מטרנזקציה שנייה. אם אחת הפעולות נופלת כאמור תקבל מצב של חורים. החורים יהיו בכמות הנתונים של ה ID שהשרת משך לעצמו עבור הפעולה. זה פחות מתאים לכמות הקבועה במדוייק של 1000 ויותר למצב של "כמעט תמיד כמות קבועה" :-), אבל גם את זה צריך לבדוק כי במצב זה יותר קשה לאתר את הבעיה. במצב הקודם דיברנו על פעולות ידועות שפועלות בכפולות של 1000 וכאן זה יותר גמיש.

    * זה מתאים לאפיון שאני רואה כאן שהוא לא בדיוק 1000 בכל פעם אלא קרוב מאוד ל 1000. למשל יש יוצא אחד מהכלל בדוגמה שלך : 2157->3156 שזה רק 999 :-)

    צריך לדייק באפיון ובבדיקות ולנסות לנטר את המקור לבעיה בהתאם


    signature

    • נערך על-ידי pituachMVP, Editor יום שני 14 מאי 2012 21:29
    • סומן כתשובה על-ידי ofir_bl יום שישי 18 מאי 2012 19:59
    יום שני 14 מאי 2012 20:29
    מנחה דיון
  • אופיר אני לא בטוח אם נצליח לנטר את הבעיה מ"רחוק" בפורום. הבעיה ברורה ודוגמאות אמיתיות למתי היא נוצרת אפשר למצוא (כמו שרשמתי מעל) אבל למרות שהבעיה פשוטה הניטור (למציאת המקור) יכול להיות משהו מורכב מאוד ככל שהמערכת מורכבת יותר :-)
    * יצא לי כבר לנטר בעיה במשך זמן יותר ארוך מזמן הפיתוח של האפליקציה (כשמנכ"ל החברה לא מבין הרבה ומשנה את הקוד בשרת החי בפגישה עם הלקוח כדי להראות שהוא יודע מה הוא עושה, ועוד מדווח למנהל הפיתוח שלא נגע בכלום אז אתה עלול לשבת כמה ימים לנטר בעיה בשרת הפיתוח כשאתה יוצא מנקודת הנחה מוטעה... ניטור הוא חיה מוזרה)

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

    אין לנו מספיק נתונים לעלות על הבעיה מרחוק כרגע אלא רק לזרוק עוד רעיונות ונקודות :-(

    למשל: אתה אומר שאין עבודה במקביל... האם אתם מפתחים עם TPL במקרה? או תכנות מקבילי בכלל? האם יכול להיות שמה שנראה לך כמו פעולה בודדת מעשית מבוצע במקביל על ידי מנהל ה THREAD-ים של dot.net ? אני בטוח שאם הייתי רואה את האפליקציה הייתי עולה על זה דיי מהר אבל זה מעבר למסגרת הפורום :-)

    אתה אומר שהאפליקציה מכניסה רשומות בודדות בכל יום ולכן נראה על פני השטח שהבעיה אינה קשורה באפליקציה (אנחנו מחפשים תופעה של אלפים ושדה identity אמור להיות מנוהל על ידי מסד הנתונים ואל האפליקציה) אלא במשהו אחר. האם יכול להיות שאתה עושה שימוש בשדה שאינו identity או יש לך שימוש ב SET IDENTITY_INSERT (ראה קוד למטה)? כמו כן ניטור של פעולות תחזוקה זה רעיון טוב, ניטור של טריגרים מאחורי הקלעים במסד הנתונים, כמובן בדיקה של מבנה המסד נתונים (הדברים שכתב בהתחלה tetitu)

    נתונים שכדאי לספר לנו ויכולים להיות קשורים: איזה גרסה אתה עובד? ב 2012 הדברים מעט שונים כי נוסף Sequence שיכול לעשות דברים כאלה. האם אנחנו מדברים בכלל על שדה identity ?

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

    כמו שאמרתי ניטור יכול bitchy :-)

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

    USE AdventureWorks2012;
    GO
    
    -- Create Test table.
    CREATE TABLE dbo.Tool(
       ID INT IDENTITY NOT NULL PRIMARY KEY, 
       Name VARCHAR(40) NOT NULL
    )
    GO
    -- Inserting values into products table.
    INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
    INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
    INSERT INTO dbo.Tool(Name) VALUES ('Saw')
    INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
    GO
    
    SELECT * FROM dbo.Tool
    GO
    
    /**************************************************/
    /***** נניח שיש לך אפליקציה שמבצעת רק הכנסה של נתון בודד ********/
    -- SET IDENTITY_INSERT to ON.
    SET IDENTITY_INSERT dbo.Tool ON
    GO
    -- Insert an explicit ID value of 30.
    INSERT INTO dbo.Tool (ID, Name) VALUES (30, 'Garden shovel')
    GO
    -- SET IDENTITY_INSERT to OFF.
    SET IDENTITY_INSERT dbo.Tool OFF
    GO
    /***************************************/
    /***************************************/
    
    INSERT INTO dbo.Tool(Name) VALUES ('NewAfter30')
    GO
    
    -- lets look at the data
    SELECT * FROM dbo.Tool
    GO
    
    -- Drop Test table.
    DROP TABLE dbo.Tool
    GO


    signature

    • נערך על-ידי pituachMVP, Editor יום שלישי 15 מאי 2012 08:58
    • סומן כתשובה על-ידי ofir_bl יום שישי 18 מאי 2012 19:59
    יום שלישי 15 מאי 2012 08:49
    מנחה דיון

כל התגובות

  • זה יותר לפורום של ה SQL

    תשתמש ב scope_identity במקום @@identity   !!!!!!!

    ואם זה עדיין מתנהג מוזר אז:

    תעבור על כל אחד מהסעיפים ותראה אם זה נכון.

    א. השדה שלך מסוג int  ו identity

    ב. ה seed של ה identity  הוא 1

    ג. יש לך מפתח על השדה

    ד. תמחק את כל הרשומות ותאפס את ה IDENTITY ע"י DBCC CHECKIDENT('mytable', RESEED, 0)

    ה. אם זה עדיין דפוק תבנה בסיס נתונים חדש

    ו. אם עדיין אז תדווח על BUG

    יום שני 14 מאי 2012 15:02
  • אעדכן תוך שבועיים אם זה פתר את הבעיה או שלא.

    למה לא מומלץ להשתמש ב-@@Identity?

    למה scope_identity עדיף?

    תודה

    אופיר

    יום שני 14 מאי 2012 18:34
  • הי אופיר,

    הפונקציה IDENTITY@@ מחזירה את ערך ה-IDENTITY האחרון שנוצר ב-Session שלך. הפונקציה SCOPE_IDENTITY מחזירה את ערך ה-IDENTITY האחרון שנוצר ב-Batch שלך. ההבדל הוא טריגרים. אם יש לך טריגר על הטבלה, והטריגר מבצע INSERT על טבלה אחרת, שגם בה יש IDENTITY, אז הפונקציה IDENTITY@@ תחזיר את הערך מהטבלה השניה, ואילו הפונקציה SCOPE_IDENTITY תחזיר את הערך מהטבלה שלך.

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

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי pituachMVP, Editor יום שישי 18 מאי 2012 22:53
    יום שני 14 מאי 2012 19:13
    מנחה דיון
  • סיבה הגיונית לבעיה כזו יכולה להיות תוצאה ישירה של נסיון להכניס 1000 רשומות למשל לשדה identity בטרנזקציה שלא מסיימת. אם הטרזקציה נכשלה המספרים כבר נתפסו והמספור לא מתאפס כשמבוצע למשל . rollback . דבר זה יכול לקרות גם בפעולות על כמות לא קבועה כפי שאסביר בהמשך.

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

    use QQ
    go
    
    CREATE TABLE ValueTable ([id] int identity,[value] int )
    GO
    
    DECLARE @TransactionName varchar(20) = 'Transaction1';
    BEGIN TRAN @TransactionName
           INSERT INTO ValueTable VALUES(1), (2);
    ROLLBACK TRAN @TransactionName;
    go
    
    select * from ValueTable
    GO
    
    INSERT INTO ValueTable VALUES(1),(2);
    select * from ValueTable
    go
    
    DROP TABLE ValueTable;

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

    אם אתה למשל מכניס את הנתונים בעזרת BULK INSERT והפעולה מבוצעת בכפולות של 1000 אז קיבלת דוגמה מעשית לכך (החלוקה ל 1000 יכולה להיות על ידך או על ידי ההגדרה של כמה דטא להעביר בכל batch ישנם הרבה פרמטרים שקובעים סופו של דבר את הכמות כמו BATCHSIZE, KILOBYTES_PER_BATCH, ROWS_PER_BATCH )

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

    * זה מתאים לאפיון שאתה מספר והנפילות יכולות להיות תוצאה של deadlock או כל דבר אחר שהוא אקראי אבל הנזק יהיה תמיד קבוע (1000 רשומות למשל)

    - דוגמה נוספת היא כאשר אתה מריץ כמה פעולות במקביל של INSERT של מספר רשומות גדול. הפעם אני לא מדבר על פעולות בכמות מדוייקת של 1000 למשל. אפילו אם הפעולה מבוצעת בטרנזקציה אחת הרי שאם הם מבוצעות במקביל ה ID לא יהיו בהכרח רציפים. למשל אם תריץ 2 טרנזקציות שכל אחת מכניסה מליון רשומות(או פחות) הרי שהרשומות לא יהיו מליון ID רציפים מטרנזקציה אחת ומליון הבאים מטרנזקציה שנייה. אם אחת הפעולות נופלת כאמור תקבל מצב של חורים. החורים יהיו בכמות הנתונים של ה ID שהשרת משך לעצמו עבור הפעולה. זה פחות מתאים לכמות הקבועה במדוייק של 1000 ויותר למצב של "כמעט תמיד כמות קבועה" :-), אבל גם את זה צריך לבדוק כי במצב זה יותר קשה לאתר את הבעיה. במצב הקודם דיברנו על פעולות ידועות שפועלות בכפולות של 1000 וכאן זה יותר גמיש.

    * זה מתאים לאפיון שאני רואה כאן שהוא לא בדיוק 1000 בכל פעם אלא קרוב מאוד ל 1000. למשל יש יוצא אחד מהכלל בדוגמה שלך : 2157->3156 שזה רק 999 :-)

    צריך לדייק באפיון ובבדיקות ולנסות לנטר את המקור לבעיה בהתאם


    signature

    • נערך על-ידי pituachMVP, Editor יום שני 14 מאי 2012 21:29
    • סומן כתשובה על-ידי ofir_bl יום שישי 18 מאי 2012 19:59
    יום שני 14 מאי 2012 20:29
    מנחה דיון
  • זו לא סיבה לדיווח באג כל כך מהר :-)

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


    signature

    יום שני 14 מאי 2012 20:33
    מנחה דיון
  • תודה רבה על תשובתך,

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

    היא יושבת רק על עמדה אחת (ככה שאין הכנסות מקביליות) ומכניסים רשומות בודדות ביום.

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

    חשבתי אולי זה קשור ל-MAINTENCE PLAN, האם יש סיכוי שפעולות כמו REBULID INDEXES, CHECK DB INTEGRITY  או UPDATE STATISTICS גורמים לכזאת תופעה?

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

    יום שלישי 15 מאי 2012 04:13
  • אופיר אני לא בטוח אם נצליח לנטר את הבעיה מ"רחוק" בפורום. הבעיה ברורה ודוגמאות אמיתיות למתי היא נוצרת אפשר למצוא (כמו שרשמתי מעל) אבל למרות שהבעיה פשוטה הניטור (למציאת המקור) יכול להיות משהו מורכב מאוד ככל שהמערכת מורכבת יותר :-)
    * יצא לי כבר לנטר בעיה במשך זמן יותר ארוך מזמן הפיתוח של האפליקציה (כשמנכ"ל החברה לא מבין הרבה ומשנה את הקוד בשרת החי בפגישה עם הלקוח כדי להראות שהוא יודע מה הוא עושה, ועוד מדווח למנהל הפיתוח שלא נגע בכלום אז אתה עלול לשבת כמה ימים לנטר בעיה בשרת הפיתוח כשאתה יוצא מנקודת הנחה מוטעה... ניטור הוא חיה מוזרה)

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

    אין לנו מספיק נתונים לעלות על הבעיה מרחוק כרגע אלא רק לזרוק עוד רעיונות ונקודות :-(

    למשל: אתה אומר שאין עבודה במקביל... האם אתם מפתחים עם TPL במקרה? או תכנות מקבילי בכלל? האם יכול להיות שמה שנראה לך כמו פעולה בודדת מעשית מבוצע במקביל על ידי מנהל ה THREAD-ים של dot.net ? אני בטוח שאם הייתי רואה את האפליקציה הייתי עולה על זה דיי מהר אבל זה מעבר למסגרת הפורום :-)

    אתה אומר שהאפליקציה מכניסה רשומות בודדות בכל יום ולכן נראה על פני השטח שהבעיה אינה קשורה באפליקציה (אנחנו מחפשים תופעה של אלפים ושדה identity אמור להיות מנוהל על ידי מסד הנתונים ואל האפליקציה) אלא במשהו אחר. האם יכול להיות שאתה עושה שימוש בשדה שאינו identity או יש לך שימוש ב SET IDENTITY_INSERT (ראה קוד למטה)? כמו כן ניטור של פעולות תחזוקה זה רעיון טוב, ניטור של טריגרים מאחורי הקלעים במסד הנתונים, כמובן בדיקה של מבנה המסד נתונים (הדברים שכתב בהתחלה tetitu)

    נתונים שכדאי לספר לנו ויכולים להיות קשורים: איזה גרסה אתה עובד? ב 2012 הדברים מעט שונים כי נוסף Sequence שיכול לעשות דברים כאלה. האם אנחנו מדברים בכלל על שדה identity ?

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

    כמו שאמרתי ניטור יכול bitchy :-)

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

    USE AdventureWorks2012;
    GO
    
    -- Create Test table.
    CREATE TABLE dbo.Tool(
       ID INT IDENTITY NOT NULL PRIMARY KEY, 
       Name VARCHAR(40) NOT NULL
    )
    GO
    -- Inserting values into products table.
    INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
    INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
    INSERT INTO dbo.Tool(Name) VALUES ('Saw')
    INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
    GO
    
    SELECT * FROM dbo.Tool
    GO
    
    /**************************************************/
    /***** נניח שיש לך אפליקציה שמבצעת רק הכנסה של נתון בודד ********/
    -- SET IDENTITY_INSERT to ON.
    SET IDENTITY_INSERT dbo.Tool ON
    GO
    -- Insert an explicit ID value of 30.
    INSERT INTO dbo.Tool (ID, Name) VALUES (30, 'Garden shovel')
    GO
    -- SET IDENTITY_INSERT to OFF.
    SET IDENTITY_INSERT dbo.Tool OFF
    GO
    /***************************************/
    /***************************************/
    
    INSERT INTO dbo.Tool(Name) VALUES ('NewAfter30')
    GO
    
    -- lets look at the data
    SELECT * FROM dbo.Tool
    GO
    
    -- Drop Test table.
    DROP TABLE dbo.Tool
    GO


    signature

    • נערך על-ידי pituachMVP, Editor יום שלישי 15 מאי 2012 08:58
    • סומן כתשובה על-ידי ofir_bl יום שישי 18 מאי 2012 19:59
    יום שלישי 15 מאי 2012 08:49
    מנחה דיון
  • תודה על התשובה המפורטת, מחר, כשאגיע למיקום שבו ה-SQL SERVER יושב אעלה עוד נתונים.

    אופיר

    יום שלישי 15 מאי 2012 09:58
  • האם אני צריך להניח שההסברים כאן עזרו ופתרת את הבעיה?

    ה"מחר" עובר עוד מעט :-)

    אנא ידע אותנו היכן אנחנו עומדים בנושא


    signature

    יום רביעי 16 מאי 2012 19:30
    מנחה דיון
  • אופיר אני לא בטוח אם נצליח לנטר את הבעיה מ"רחוק" בפורום. הבעיה ברורה ודוגמאות אמיתיות למתי היא נוצרת אפשר למצוא (כמו שרשמתי מעל) אבל למרות שהבעיה פשוטה הניטור (למציאת המקור) יכול להיות משהו מורכב מאוד ככל שהמערכת מורכבת יותר :-)
    * יצא לי כבר לנטר בעיה במשך זמן יותר ארוך מזמן הפיתוח של האפליקציה (כשמנכ"ל החברה לא מבין הרבה ומשנה את הקוד בשרת החי בפגישה עם הלקוח כדי להראות שהוא יודע מה הוא עושה, ועוד מדווח למנהל הפיתוח שלא נגע בכלום אז אתה עלול לשבת כמה ימים לנטר בעיה בשרת הפיתוח כשאתה יוצא מנקודת הנחה מוטעה... ניטור הוא חיה מוזרה)

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

    אין לנו מספיק נתונים לעלות על הבעיה מרחוק כרגע אלא רק לזרוק עוד רעיונות ונקודות :-(

    למשל: אתה אומר שאין עבודה במקביל... האם אתם מפתחים עם TPL במקרה? או תכנות מקבילי בכלל? האם יכול להיות שמה שנראה לך כמו פעולה בודדת מעשית מבוצע במקביל על ידי מנהל ה THREAD-ים של dot.net ? אני בטוח שאם הייתי רואה את האפליקציה הייתי עולה על זה דיי מהר אבל זה מעבר למסגרת הפורום :-)

    אתה אומר שהאפליקציה מכניסה רשומות בודדות בכל יום ולכן נראה על פני השטח שהבעיה אינה קשורה באפליקציה (אנחנו מחפשים תופעה של אלפים ושדה identity אמור להיות מנוהל על ידי מסד הנתונים ואל האפליקציה) אלא במשהו אחר. האם יכול להיות שאתה עושה שימוש בשדה שאינו identity או יש לך שימוש ב SET IDENTITY_INSERT (ראה קוד למטה)? כמו כן ניטור של פעולות תחזוקה זה רעיון טוב, ניטור של טריגרים מאחורי הקלעים במסד הנתונים, כמובן בדיקה של מבנה המסד נתונים (הדברים שכתב בהתחלה tetitu)

    נתונים שכדאי לספר לנו ויכולים להיות קשורים: איזה גרסה אתה עובד? ב 2012 הדברים מעט שונים כי נוסף Sequence שיכול לעשות דברים כאלה. האם אנחנו מדברים בכלל על שדה identity ?

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

    כמו שאמרתי ניטור יכול bitchy :-)

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

    USE AdventureWorks2012;
    GO
    
    -- Create Test table.
    CREATE TABLE dbo.Tool(
       ID INT IDENTITY NOT NULL PRIMARY KEY, 
       Name VARCHAR(40) NOT NULL
    )
    GO
    -- Inserting values into products table.
    INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
    INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
    INSERT INTO dbo.Tool(Name) VALUES ('Saw')
    INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
    GO
    
    SELECT * FROM dbo.Tool
    GO
    
    /**************************************************/
    /***** נניח שיש לך אפליקציה שמבצעת רק הכנסה של נתון בודד ********/
    -- SET IDENTITY_INSERT to ON.
    SET IDENTITY_INSERT dbo.Tool ON
    GO
    -- Insert an explicit ID value of 30.
    INSERT INTO dbo.Tool (ID, Name) VALUES (30, 'Garden shovel')
    GO
    -- SET IDENTITY_INSERT to OFF.
    SET IDENTITY_INSERT dbo.Tool OFF
    GO
    /***************************************/
    /***************************************/
    
    INSERT INTO dbo.Tool(Name) VALUES ('NewAfter30')
    GO
    
    -- lets look at the data
    SELECT * FROM dbo.Tool
    GO
    
    -- Drop Test table.
    DROP TABLE dbo.Tool
    GO


    signature

    שלום ומצטער על האיחור,

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

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

    USE [Shopping_Managment]
    GO
    
    /****** Object:  Table [dbo].[ShoppingInfo]    Script Date: 17/05/2012 07:47:09 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[ShoppingInfo](
    	[RowID] [int] IDENTITY(1,1) NOT NULL,
    	[BoughtDate] [date] NULL,
    	[Warrenty] [date] NULL,
    	[ItemName] [varchar](100) NULL,
    	[CostPrice] [int] NULL,
    	[Category] [int] NULL,
    	[BoughtFrom] [varchar](150) NULL,
    	[Notes] [varchar](350) NULL,
     CONSTRAINT [PK_ShoppingInfo] PRIMARY KEY CLUSTERED 
    (
    	[RowID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[ShoppingInfo]  WITH CHECK ADD  CONSTRAINT [FK_ShoppingInfo_CategoriesDesc] FOREIGN KEY([Category])
    REFERENCES [dbo].[CategoriesDesc] ([CategoryID])
    GO
    
    ALTER TABLE [dbo].[ShoppingInfo] CHECK CONSTRAINT [FK_ShoppingInfo_CategoriesDesc]
    GO
    

     הפרוצדורות שמתעסקות איתה הן הפרוצדורות הבאות:

    ALTER PROCEDURE [dbo].[InsertShopping] (
    	@p_BoughtDate	date,
    	@p_Warrenty		date,
    	@p_ItemName		varchar(100),
    	@p_CostPrice	int,
    	@p_Category		int,
    	@p_BoughtFrom	varchar(150),
    	@p_Notes		varchar(350),
    	@p_IsOK			INT OUTPUT,
    	@p_ShoppingID	INT OUTPUT
    	)
    	-- Add the parameters for the stored procedure here
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET @p_IsOK = @@ERROR
    	
    	INSERT INTO dbo.ShoppingInfo (BoughtDate, Warrenty, ItemName, CostPrice, Category, BoughtFrom, Notes)
    	VALUES (@p_BoughtDate, @p_Warrenty, @p_ItemName, @p_CostPrice,@p_Category, @p_BoughtFrom, @p_Notes)
    	
    	SET @p_ShoppingID = SCOPE_IDENTITY()
    END

    ALTER PROCEDURE [dbo].[UpdateShopping] (
    	@p_RowID		int,
    	@p_BoughtDate	date,
    	@p_Warrenty		date,
    	@p_ItemName		varchar(100),
    	@p_CostPrice	int,
    	@p_Category		int,
    	@p_BoughtFrom	varchar(150),
    	@p_Notes		varchar(350),
    	@p_ShoppingFilesTbl		dbo.ShoppingFilesTypes READONLY,
    	@p_IsOK			INT OUTPUT
    	)
    	-- Add the parameters for the stored procedure here
    AS
    BEGIN
    	SET NOCOUNT ON;
    	SET @p_IsOK = @@ERROR
    	
    	UPDATE dbo.ShoppingInfo
    	SET
    		BoughtDate = @p_BoughtDate,
    		Warrenty = @p_Warrenty,
    		ItemName = @p_ItemName,
    		CostPrice = @p_CostPrice,
    		Category = @p_Category,
    		BoughtFrom = @p_BoughtFrom,
    		Notes = @p_Notes
    	WHERE RowID = @p_RowID;
    	
    	MERGE dbo.ShoppingFilesByID(@p_RowID) AS TARGET
    	USING @p_ShoppingFilesTbl AS SOURCE
    	ON (TARGET.RowID = SOURCE.RowID AND TARGET.[FileName] = SOURCE.[FileName])
    	WHEN NOT MATCHED BY TARGET THEN
    	INSERT (RowID, [FileName], FilePath) 
    	VALUES (@p_RowID, SOURCE.[FileName], SOURCE.FilePath)
    	WHEN NOT MATCHED BY SOURCE THEN DELETE;	
    END

    DELETE FROM dbo.ShoppingFiles
    WHERE ShoppingFiles.RowID = @p_FileID;
    
    DELETE FROM dbo.ShoppingInfo
    WHERE RowID = @p_FileID;
    
    SET @p_IsOK = @@ERROR	

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

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

    הוא לתקשר עם ה-DB, במקביל אין האפליקציה עושה עוד דברים.

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

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

    check db integrity, rebulid indexess, update statistics, shrink db.

    בהתאם להמלצות אחרות כאן בפורום שיניתי קצת את התוכנית וביטלתי את פעולת ה-SHRINK.

    מעבר לזה ישנו עוד JOB בשם syspolicy_purge_history שאיני יודע בדיוק מה תפקידו, הוא לא התווסף על ידי.

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

    תודה

    אופיר


    • נערך על-ידי ofir_bl יום חמישי 17 מאי 2012 05:01 שגיאת כתיב
    יום חמישי 17 מאי 2012 04:59
  • האם יש טריגרים על הטבלה ?
    יום חמישי 17 מאי 2012 06:46
  • שלילי.
    יום חמישי 17 מאי 2012 06:49
  • היי

    הרעיון ב DDL+DML זה שנוכל לשחזר את המצב כמה שיותר קרוב למצב אצלך וככה אולי לקבל תובנות על הנושא :-)

    בכל מקרה לא יכולתי למשל לשחזר את ה מגבלות(CONSTRAINT) מכיוון שלא סיפקת את ה DDL של הטבלה המקושרת. אבל נזכרתי בעוד מקרה מאוד נפוץ ומאוד פשוט שיכול להיות וקורה הרבה פעמים בפועל וזה שאם יש לך CONSTRAINT ואתה מנסה להכניס רשומה לא חוקית אז למעשה קורה בדיוק מה שכתבתי בדוגמה הראשונה שהצגתי: המספר של ה ID נתפס -> הפעולה נכשלת בגלל המגבלה (CONSTRAINT) -< ז"א מבוצע ROLLBACK => והתוצאה שוב מצב של קפיצה ב ID :-)

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

    נחזור לניטור הבעיה: אתה רושם "ישנו עוד JOB בשם syspolicy_purge_history שאיני יודע בדיוק מה תפקידו"

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

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

    * הערה: על פני השטח השאילתות שצירפת בפני עצמן אינן נראות לי פוטניאל ישיר לבעיה כזו (אם כי גם בהם יכול להתקיים מקרים כמו שהצגתי קודם)


    signature

    יום חמישי 17 מאי 2012 22:52
    מנחה דיון
  • הי,

    ה-Job שנקרא "syspolicy_purge_history" הוא Job של המערכת, שנוצר באופן אוטומטי בכל התקנה או שדרוג של Instance של SQL Server 2008 ומעלה. תפקידו של ה-Job הזה למחוק לוגים ישנים שקשורים ל-Policy Management מ-msdb. אין לו שום קשר לבסיס הנתונים שלך או לטבלה שלך, והוא לא הסיבה לקפיצות ב-Identity.

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום שישי 18 מאי 2012 05:04
    מנחה דיון
  • הנה תמיד טוב ללמוד :-)
    לא הכרתי את השם.

    * הרצתי בדיקה כל ה JOB-ים בשרת 2012 חדש שעדיין אין בו כלום ואכן הוא נמצא שם

    מה שאומר שגיא חסך לך את הבדיקה ונשאר רק להמתין לראות אם הבעיה נמשכת

    יום שישי 18 מאי 2012 07:30
    מנחה דיון
  • המצב כרגע הוא שסידרתי את ה-ID עם DBCC CHECKIDENT('mytable', RESEED, x)

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

    אני יוצא למילואים בקרוב עד סוף החודש, כשאחזור אבדוק מה מצב ה-DB ואעדכן - לחיוב או לשלילה.

    בואו נקווה שזה יהיה לחיוב.

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

    אופיר

    יום שישי 18 מאי 2012 08:17
  • אופיר אני מציע שתסגור את השירשור אם מדובר בתקופה שהנושא יהיה בהמתנה (זמן את כל התשובות כתשובות וסמן דברים מועילים כדברים מועילים והשירשור יקבל סמון V במקום סימון סימן שאלה), וכשתחזור לטיפול בו תפתח שירשור חדש (עם קישור לכאן אם צריך או עם סיכום המצב)

    מילואים מהנים
    אין כמו מילואים לחופש וניקוי הראש מהכל :-)


    signature

    יום שישי 18 מאי 2012 18:17
    מנחה דיון