en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אפר9

Written by: ronen ariely
09/04/2014 20:46 RssIcon

הקדמה

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

מקרה מבחן

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

/*************************************************************** DDL + DML */
-- Creating New Database
CREATE DATABASE SameQueryDifferentServerVersion
GO
-- Creating New Table
use SameQueryDifferentServerVersion
GO
-- Creating New Dataase
CREATE TABLE SourceTbl(
    ID INT,
    FName NVARCHAR(100),
    MName NVARCHAR(100),
    LName NVARCHAR(100)
)
GO
-- Insert sample data (DML)
insert SourceTbl (FName,MName,LName)
select 'a','e','r' UNION ALL
select 's','w','t' UNION ALL
select 'd','q','y' UNION ALL
select 'f',',','u' UNION ALL
select 'g','m','i' UNION ALL
select 'h','n','o' UNION ALL
select 'j','b','p' UNION ALL
select 'k','v','l' UNION ALL
select 'l','c','k' UNION ALL
select 'z','x','j'
GO
-- Creating New Table using our exist table
select ID, FName, MName, LName
into #T1
from SourceTbl
order by FName
GO

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

select ID, FName, MName, LName
from SourceTbl;
select ID, FName, MName, LName
from #T1;
GO

האם התוצאות של שתי השאילתות חזרו כמצופה?

אני מניח שלרוב הקוראים בשרתים הישנים יותר כמו SQL 2005 התוצאות חזרו בסדר שונה. בשאילתה השנייה הנתונים חזרו ככל הנראה מסודרים לפי עמודת ה Name ובשאילתה הראשונה הנתונים חזרו ככל הנראה כפי שהם הוכנסו לטבלה לפי סדר ה ID. לעומת זאת בשרתים החדשים יותר כמו SQL 2012 הנתונים חזרו באותו סדר של הנתונים המקוריים ככל הנראה בשתי השאילתות, ז"א מסודרים לפי עמודת ה ID. מדוע נוצר ההבדל הזה?

* הערה: על מנת לקבל את התוצאות שאנחנו רוצים לדון בהם במאמר מומלץ לבצע את הבדיקה על מכונה שאינה עמוסה בפעילות נוספת!

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

הסבר

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

בואו נחקור את השאילתה הבאה בכל השרתים:

select ID, FName, MName, LName
from SourceTbl
order by FName

עתה נסתכל על תכנית ההרצה שלנו (התמונות לקוחות מגרסת 2008r2 ומגרסת 2012, אני אבדוק גרסאות נוספות בהמשך)

ronen ariely

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

נחזור לשאילתה המקורית של הדיון, ונבחן את תכנית ההרצה

בשרת 2012 קיבלנו את התכנית הבא:


אבל שימו לב לתכנית ההרצה בשרתים היותר ישנים (2005,2008,2008r2)

ניתן לראות בברור, שגרסאות שונות של השרת מריצות את השאילתות בצורה שונה לחלוטין!

בגרסאות הישנות כמו 2005,2008,2008r2 הכנסת הנתונים נעשתה אחרי סידור שלהם, אבל בגרסת 2012, אנו לא רואים את פעולת הסידור בתכנות ההרצה.

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

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

select ID, FName, MName, LName
from SourceTbl;
select ID, FName, MName, LName
from #T1;
GO

קריאה מהנה :-)

Tags: SQL , T-SQL
Categories: SQL