שימוש מתקדם בפונקציות אנליטיות של SQL Server 2012 - חלק 1 - ilDBA Portal

שימוש מתקדם בפונקציות אנליטיות של SQL Server 2012 – חלק 1

05/06/2012 | פורסם על ידי

בRDBMS כמו Oracle, Sybase ו- DB2 יש תמיכה מלאה בפונקציות חלון מזה מספר שנים.
עד עכשיו ל- SQL Server 2008 היה מימוש חלקי בלבד שלהן.
גרסת SQL Server 2012 הרחיבה את התמיכה לתמיכה ב-windows aggregate function ע"י שימוש בביטויים order ו – frame, תמיכה בפונקציות offset ותמיכה בפונקציות התפלגות .

במאמר זה, שהוא ראשון מתוך שניים, אדגים שימוש מתקדם בפונקציות אלה.
הדוגמאות שאביא במאמר זה, נלקחות מניסיוני במערכות RDBMS אחרות (Oracle ו- Sybase).
בנוסף לכך, אציג מה לדעתי עדיין חסר ב- SQL server 2012 וכיצד הייתי מצפה מ-Microsoft לממש זאת , בתקווה, אולי בגרסה הבאה .

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

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

הכנת טבלאות הדוגמא:

טבלת הדוגמא נקראת total_sales והיא מכילה 5 עמודות ע"פ הפירוט הבא :

  • YEAR – שנת המכירה
  • MONTH – חודש המכירה ( 1-12).
  • PRD_TYPE_ID – מספר מזהה של המוצר .
  • EMP_ID – מספר מזהה של העובד שמכר את המוצר .
  • AMOUNT – מכיל את סכום המכירה הכולל .

השאילתה הבאה מביאה את 12 השורות הראשונות מהטבלה total_sales . הפלט בשרטוט 1 .

select  top (12) * from [dbo].[total_sales]

בשאילתה הבאה נשתמש בפונקציות RANK() ו- DENSE_RANK() אשר כבר נתמכו ב- SQL Server 2005 .
השאילתה הבאה מחזירה דירוג של המכירות ע"פ מוצרים בשנת 2012 .
במקרה שלנו אין לולאות (ties) ולכן RANK() ו- DENSE_RANK() מחזירות את אותו דירוג .

SELECT
  prd_type_id, SUM(amount),
  RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank
FROM total_sales
WHERE year = 2012
AND amount IS NOT NULL
GROUP BY prd_type_id
ORDER BY prd_type_id;

הפלט מוצג בשרטוט 2:

הטבלה total_sales מכילה Nulls בעמודה AMOUNT, עבור כל השורות שבהן PRD_TYPE_ID=5.
הדוגמא הקודמת מתעלמת מהשורות הללו בגלל השימוש בשורה “AND amount IS NOT NULL” בביטוי WHERE.
הדוגמא הבאה כוללת את השורות הללו ע"י הורדת שורה זו מהביטוי WHERE .

SELECT
 prd_type_id, SUM(amount) AS  SUM_Amount,
 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank
FROM total_sales
WHERE year = 2012
GROUP BY prd_type_id
ORDER BY prd_type_id;

הפלט הוא בשרטוט 3 .

prd_type_id                       SUM_Amount           rank   dense_rank
1                                 905081.84            1             1
2                                 186381.22            4             4
3                                 478270.91            2             2
4                                 402751.16            3             3
5                                 NULL                 5             5

שימו לב שהשורה האחרונה כוללת NULLS עבור סכום של העמודה AMOUNT ו- RANK() ו- DENSE_RANK() מחזירות 5 עבור השורה הזו .
זה נובע מכך שכבררת מחדל RANK() ו- DENSE_RANK() מדרגים ערכי NULLS לערך הגבוה ברשימת הערכים כאשר משתמשים ב- DESC בביטוי OVER,
ואת הדרוג הכי נמוך 1 לערכי NULLS כאשר משתמשים ב- ASC בביטוי OVER בשאילתה עצמה..

————————————–

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

בדוגמא הבאה יש שאילתה היפותטית (השאילתה לא תעבוד, כי ה-Attributes המדוברים עדיין לא נתמכים) שימוש ב NULLS LAST בכדי לציין שערכי NULLs יהיו הנמוכים .

SELECT
 prd_type_id, SUM(amount),
 RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank,
 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS dense_rank
FROM total_sales
WHERE year = 2012
GROUP BY prd_type_id
ORDER BY prd_type_id
הפלט הצפוי (ההיפותטי) להלן בשרטוט 4 :

לפעמים, חלוקה לקבוצות לא מספיקה לנו.
כאשר נדרש לחלק את הקבוצות לתת קבוצות, יש להשתמש בביטוי PARTITION BY עם פונקציות אנליטיות.
בדוגמא הבאה נחלק את סכום המכירות ע"פ חודש יש להשתמש בביטוי Partition By month ע"פ ההסבר הבא :

Using the PARTITION BY Clause with Analytic Functions When you need to divide the groups into subgroups Using the PARTITION BY Clause with Analytic Functions
– Example : Subdivide the sales amount by month, use PARTITION BY month as follows

SELECT
 prd_type_id, month, SUM(amount) AS  SUM_Amount ,
 RANK() OVER (PARTITION BY month ORDER BY  SUM(amount) DESC) AS rank
FROM total_sales
WHERE year = 2012
AND amount IS NOT NULL
GROUP BY prd_type_id, month
ORDER BY prd_type_id, month;

פלט חלקי בשרטוט 5 :

בחלק הבא אני אדגים שימוש ב SETS GROUPING , CUBE ו- ROLLUP עם פונקציות אנאליטיות.
השאילתה הבאה משתמשת ב- ROLLUP ו- RANK() כדי לקבל דרוג של המכירות ע"פ product type ID.

SELECT
 prd_type_id, SUM(amount) SUM_Amount,
 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM total_sales
WHERE year = 2012
GROUP BY ROLLUP(prd_type_id)
ORDER BY prd_type_id;

הפלט בשרטוט 6 .

השאילתה הבאה משתמשת ב CUBE ו- RANK() בשביל לדרג את כל המכירות ע"פ product type ID ו – employee ID.

SELECT
 prd_type_id, emp_id, SUM(amount)as SUM_Amount,
 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM total_sales
WHERE year = 2012
GROUP BY CUBE(prd_type_id, emp_id)
ORDER BY prd_type_id, emp_id;

פלט חלקי בשרטוט 7 .

ניתן להשתמש ב- GROUPING SETS ו- RANK() בשביל לקבל רק את סיכומי הביניים (subtotal) של סכום המכירות.
GROUPING SETS מחזיר רק את השורות של סיכומי הביניים.
בדוגמא הבאה אני משתמש ב GROUPING SETS בשביל לקבל רק את סיכומי הבינים עבור העמודות product type id ו- . employee id

You can use GROUPING SETS and RANK() to get just the sales amount subtotal . GROUPING SETS gets just the subtotal rows. In following example GROUPING SETS is used to get subtotals for product type id and employee id columns.

SELECT
 prd_type_id, emp_id, SUM(amount) SUM_Amount,
 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM total_sales
WHERE year = 2012
GROUP BY GROUPING SETS(prd_type_id, emp_id)
ORDER BY prd_type_id, emp_id;

פלט חלקי בשרטוט 8 :

אדגים שימוש של הפונקציות CUME_DIST() ו- PERCENT_RANK() עם פונקציות אנליטיות ע"פ הבא :

  • CUME_DIST() מחשב את המיקום של ערך מסוים יחסית לקבוצה של ערכים . CUME_DIST() הוא קיצור ל cumulative distribution . כלומר התפלגות מצטברת .
  • PERCENT_RANK() מחשב את האחוזון של ערך יחסית לקבוצת ערכים .

בדוגמא הבאה יש שימוש ב CUME_DIST() ו- PERCENT_RANK() בשביל לקבל את פונקציית ההתפלגות והאחוזונים של המכירות .

SELECT
 prd_type_id, SUM(amount) SUM_Amount,
 CUME_DIST() OVER (ORDER BY SUM(amount) ASC) AS  cume_dist,
 PERCENT_RANK() OVER (ORDER BY SUM(amount) ASC) AS percent_rank
FROM total_sales
WHERE year = 2012
GROUP BY prd_type_id
ORDER BY prd_type_id

הפלט בשרטוט 9 .

קצת הסבר על הפלט.
SUM_Amount הוא משתנה אקראי בדיד.
יש לנו 5 משתנים כאלה כולל הערך NULL עבור prd_type_id 5 .
לכן לכל משתנה כזה יש הסתברות של 0.2 .
ההסתברות שמשתנה אקראי יהיה קטן מ- 402751.16 שהוא סכום המכירות עבור prd_type_id 4 היא ההתפלגות המצטברת של כל המשתנים האקראיים הקטנים או השווים לו ( הערך NULL , הערך 186381.22 עבור prd_type_id 2 והערך 402751.16 עבור prd_type_id 4 ).
לכן ההתפלגות המצטברת (CUME_DIST()) עבור prd_type_id 4 הוא 3*0.2 . סה"כ 0.6 .

הקורא המעמיק מוזמן באותו אופן להבין כיצד PERCENT_RANK() עובד .

——————————–

מה שחסר לטעמי ב- SQL Server 2012 הן 2 הפונקציות ההופכיות PERCENTILE_DISC(x) ו PERCENTILE_CONT(x). ל- CUME_DIST() ו PERCENT_RANK() ע"פ הבא :

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

נתראה בחלק הבא!

ד. יצחק , DBA אפליקטיבי ותשתיתי של SQL Server, Oracle, Sybase, Sybase Anywhere מזה 16 שנה .
מהנדס מערכות מידע ושני תארים שניים ב MBA ו- ME (הנדסת מערכת)
מרצה בפורום משתמשי SQL Server , משתמשי Oracle (IOUG) , Oracle Open World 2010 (Active data Guard ו- .net) , Microsoft HPC והנדסת מערכת .

The following two tabs change content below.

דוד יצחק

דוד יצחק , DBA אפליקטיבי ותשתיתי של SQL Server, Oracle ,Sybase Anywhere מזה 17 שנה עם התמחות בשיפור ביצועים, שרידות, זמינות גבוהה, רפלקציה, תוספים גיאוגרפיים, אבטחת מידע ו-Business Intelligence. הוא מרכז את פורום DBA צפון של ilOUG ומרצה בפורום משתמשי SQL Server.

13 תגובות ל- “שימוש מתקדם בפונקציות אנליטיות של SQL Server 2012 – חלק 1”

commenter

אהבתי את הביטוי
מזה מספר שנים
בבאורקל פונקציות אנליטיות קיימות מהמילניום הקודם
משהו כמו לפני 13 שנה…

commenter

שלום רב
אני DBA של oracle ו- MSSQL מזה 16 שנה
במקrה זה לא צוין
במאמר אבל הדוגמאות נלקחו מהניסיון שלי ב- Oracle
תוכה לקרוא על זה בגרסה האנגלית של המאמר מ- SQL Server Magazine
ההערות הועברו גם לצוות הפיתוח של MSSQL ולא סתם בכדי שבגרסה הבאה הם יוכנסו
ראה תחת ה- Link הבא :

 

http://blogs.technet.com/b/dataplatforminsider/archive/2012/02/06/recommended-sql-server-2012-virtual-launch-event-sessions-for-dbas-and-it-professionals.aspx?CommentPosted=true#commentmessage

http://www.slideshare.net/…/fast-transition-to-sql-server-2012-from-mssql-2005-2008-for-developers-david-yitzhak

 

ד יצחק

commenter

 בהמשך למעלה ….
BI ו- TOOLS USER BI: SELF-SERVICE הוא התחום ה"רותח" בשוק בכלל וב- SQL 2012 בפרט. אז הנה סיכום קליל של ה- Feature הללו ב- SQL  Server 2012 מתוך מטרה לתת מבט ממעוף הציפור . משם כל אחד יוכל לצלול פנימה ….
 
Columnstore Indexes
SQL Server 2012’s killer feature ל- BI
משתמש ב Vertipaq engine
ארגון המידע על הדיסק ע"פ עמודות
סיכומים ו- joins רצים פי 1000 מהר יותר
כרגע פקודות INSERT,UPDATE,MERGE ו- DELETE לא נתמכות
 
Service Application Architecture
חדש ב – . SharePoint 2010דוגמאות :
Excel Services
PerformancePoint Services
Visio Services
PowerPivot
 
 
Excel Services – Service Application שמאפשר לטעון , לחשב ולהציג Excel workbooks ב
 SharePoint 2012.
 
 
Report Builder 3.0
יצירת דוחות ad-hoc "מקוסטמים" למשתמש קצה . כרגע לא השתנה מגרסה SQL 2008 R2 .
יתרונות :
·         חלק מההתקנה של SSRS
·         לא מצריך Office ואינטגרציה עם SharePoint .
 
 
 
PowerPivot for ExcelBI "קליל" עם SharePoint ו- Excel
גרסה חינם להורדה החל מ SQL Server 2008 R2, דורש Excel 2010
הרחבה היכולות של Excel
סיכומים מהירים וניתוח של Data Sets גדולים (2G בדיסק , 4G בזיכרון , מיליארדי רשומות) .
משתמש ב Vertipaq engine (כמו של SSAS’s tabular ו- columnstore)
שימוש ב- DAX כשפת שאילתות – נוסחאות excel –  עקומת לימוד מהירה .
אינטגרציה עם SharePoint לאחסון ושיתוף מסמכים .
 
Power View
עבור  משתמשי קצה עם אוריינטציה ל- Power Point יותר מ- Excel
אפליקציה מסוג point-and-click למודל BISM מעל SharePoint
ביצוע דוחות בזמן אמת – הוספת אלמנטים של Data מציגה באופן מידי את התוצאה .
יצירת בדוחות אחסונם ב- SharePoint או יצוא ל- Power Point .
דורש  SharePoint גרסת Enterprise Edition ו-  SQL Server .
 
מידול SSAS) SQL Server Analysis Server)
Unified Dimensional Model (UDM)
– חדש ב- 2012The BI Semantic Model (BISM)
 
 
 
Unified Dimensional Model (UDM)
טוב עבור :
·         מודלים מורכבים
·         יישומים גדולים
·         MOLAP (star Schema)
·         שפת שאילתות מורכבת  MDX בלבד
 
 
 
The BI Semantic Model (BISM)
 
התקנה של Analysis Services ב- Tabular Mode
משתמש ב Vertipaq engine
שפת שאילתות :
·         DAX – קלה ללימוד, מבוססת Excel.
·         MDX- מורכבת.
 
טוב עבור :
·         פיתוח מהיר
·         משתמשי קצה
·         יישומים קטנים עד בינוניים
·         גישה ל- SharePoint ו- Excel
·         עבור סכימה מנורמלת (ROLAP)  ומימדי snowflake . (לא בהכרח מצריך DW) .
 
 
 ד יצחק

commenter

  שלום רב   מוזמנים להרצאה השלישית שלי במספר במסגרת ILOUG – DBA Forum – North. הנושא מרתק וכולל חשיפה של ה- DBA לשימוש מתקדם בפונקציות חלון של Oracle  , מבוא ל BI  :  MDX ו- DAX ומה הקשר לעזאזל עם פונקציות חלון .       ‏‏מועד: ‏‏יום שני 03 ספטמבר 2012, 23:00 עד יום שלישי 04 ספטמבר 2012. 03:00. ‏‏מיקום: מיקום מדויק ישלח בהמשך           שם ההרצאה   Advanced use ofAnalytic & Windows SQLfunctionin Oracle 11G andwhat the hell isMDX and DAX ?       תוכן העניינים :   SQL WindowingIntroduction   Window Functions in details :   ·Using Window RankingFunctions   ·Using the ROLLUP, CUBE, and GROUPING SETS Operators   ·Using WindowDistribution Functions   ·Using WindowAggregate Functions   ·Using Window OffsetFunctions   Ordered Set Functions: Hypothetical Set Functions , Inverse Distribution Functions , Offset Functions , String Concatenation   Hypothetical SetFunctions   Optimization of Window Functions : indexing Guidelines   Solutions Using Window Functions :   ·Paging   ·RemovingDuplicates   ·Pivoting & Unpivoting   SQL and BI   Introduction to MDXandDAX   What should I use: Analytic & Windows SQLfunctionor MDX or DAX   רקע   שימוש מתקדם בפונקציות חלון ופונקציות אנליטיות ב- Oracle עבור DW , BI ו- Data Mining   מתי להשתמש בפונקציות האלה ומתי להשתמש ב- MDX (כמו בהיפריון) או בהרחבה של מיקרוסופט ל- Excel :   Power Pivot  ו- DAX   החומר מבוסס על מערכת סימולציה ותחקור שעובדת ע"ג Oracle 11GR2 ע"ג Win 2008 R2 בחוות עיבוד מקבילי מבוססת HPC של מיקרוסופט . ה-Instance הוא אחד הגדולים בעולם ע"ג WIN : 4 טרה של data .  

commenter

יום שלישי, 4 בספטמבר 2012
במדיאטק הייטק ברחוב שד' ההסתדרות 46 חיפה
שלום לכולם

פורום DBA צפון הקרוב יערך בתאריך 4.9.2012 במדיאטק הייטק ברחוב שד' ההסתדרות 46 חיפה (מול צומת לב המפרץ) הכניסה דרך: רח' מרקוני – פינת הקורנס.

הנושא המוביל בו יעסוק הכנס : שאילתות ו-Tuning אפליקטיבי

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

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

אני מזמין אתכם להגיע, לראות ולהראות.

בברכה,
עירד דויטש,
מנהל פורום DBA צפון

14:00-14:30

התכנסות ודברי פתיחה

14:30-15:15

מתודולוגיות מתקדמות לביצוע Tuning אפליקטיבי בעזרת OEM
רפי בלבירסקי, PerformanceArt

15:15-16:00

שימוש מתקדם בשאילתות חלון
דוד יצחק, רפאל

16:00-16:30

הפסקה

16:30-17:15

שימושים מתקדמים באינדקסים לשיפור שאילתות
עירד דויטש , Veracity

17:15-18:00

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

commenter

  שלום רב   אתם מוזמנים לפורום DBA צפון       ההרצאה כוללת : שימוש מתקדם בפונקציות חלון שכולל ממוצעים נעים , שימוש בפונקציות אנליטיות  למציאת פונקציות הסתברות וצפיפות של משתנים דיסקרטיים  ,  פתרון בעיות מתקדם (חלקו השראת הספר האחרון של איציק בן- גן על פונקציות חלון) כמו : isolated islands, השלמת רצפי מספרים , מציאת שורות חסרות   והשוואה ל- MDX ו- DAX . הפעם בסביבת ORACLE .   למה Oracle ? הסיבה מאוד פשוטה . מיקרוסופט ב- SQL 2012 ישרה קו  עם Oracle שתומך בפונקציות חלון בצורה כמעט מלאה למעלה מ 10 שנים . כך שלמעשה הפתרונות כמעט זהים לחלוטין       הטכניקות שיודגמו משמשות לסימולציה של Clients מסוג Oracle Data provider for .NET בחוות HPC של מיקרוסופט מול Oracle RDBMS 11GR1 (שהוא אגב אחד הגדולים בעולם ע"ג Win 2008 R2 – 6 טרה) . צפו להפתעות !       פורום DBA צפון הקרוב יערך בתאריך 4.9.2012 במדיאטק הייטק ברחוב שד' ההסתדרות 46 חיפה (מול צומת לב המפרץ) הכניסה דרך: רח' מרקוני – פינת הקורנס.       ד יצחק  

commenter

  אז הנה פרטי המאמר ב- SQL Server מגאזין :   August 21, 2012 09:38 AM   Take Advantage of SQL Server 2012's Window Functionality    Examples of how to use the window ranking, distribution, aggregate, and offset functions    D. Yitzhak    SQL Server Pro    InstantDoc ID #143309   http://www.sqlmag.com/content1/topic/sql-server-2012-window-functionality-143309/catpath/sql-server-2012   ד יצחק  

David Itshak | 05/02/2013 בשעה 16:12
commenter

שלום לכולם

פורום DBA צפון הקרוב יערך בתאריך 5.3.2013 במדיאטק הייטק ברחוב שד' ההסתדרות 46 חיפה (מול צומת לב המפרץ) הכניסה דרך: רח' מרקוני – פינת הקורנס.

הנושא המוביל בו יעסוק הכנס :
·         אופטימיזציה של Oracle DB וה- Storage.
·         Index Organized Tables and Clustered Tables- הרצאת המשך לשבוע Oracle.
·         פתרונות רפלקציה מתקדמים של Oracle.
מבני נתונים שונים נתמכים ע"י אורקל לצורך שמירת טבלאות.הנפוץ שבהם (באופן כמעט בלעדי) הוא Heap Organized Table, אולם במקרים שונים ניתן להפיק יותר תועלת דווקא ממבני נתונים אחרים.נכיר מספר מבני נתונים כאלה, מתי כדאי להשתמש בהם וכיצד, תוך שימוש בדוגמאות.

בפורום הקרוב נציג סיפור לקוח של תהליך הערכה ובחירה של כלי להפצת נתונים מתוך Multi Master Replication Oracle Streams , NoSQL, ו-Oracle Golden Gate (OGG). נציג את המימוש הגדול בעולם של רפלקציה דו כיוונית עם OGG עם עשרות צמתים.

גם בפורום זה, ננצל את הזמן לצורך חיבור בין החברים בקהילה ושיתוף הידע. בפרום הפתוח , במידה ויתיר הזמן וכהמשך ישיר להרצאות , נחליף רעיונות וניתן טיפים על שימוש ב- RMAN ומחיצות גיבוי רשת (NFS) , טכניקות לעבודה יעילה של Oracle מול Storage של NetAPP וקצת גם על NoSQL.

אני מזמין אתכם להגיע, לראות ולהראות.

בברכה,
דוד יצחק ,
מנהל פורום DBA צפון

דוד יצחק DBA | 12/02/2013 בשעה 07:41
commenter

חברים יקרים
מספר ההערות מהאתר הגדול בארץ של MSSQL על ההרצאה של מתן בפורום SQL Server בנושא cache  :
כללי עבודה עם כוונון
דוגמא השאילתה הבאה
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SUM(qs.total_logical_reads) AS [Total Reads]
, SUM(qs.total_logical_writes) AS [Total Writes]
, DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE DB_NAME(qt.dbid) = 'AdventureWorks'
GROUP BY DB_NAME(qt.dbid)
צמצום ה- DMV ל- DB ספציפי ע"י הפונקציה DB_NAME .
 
 
חובה שכל ה- SQL לטובת הכוונון יתחילו עם הבא :
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
בצורה כזו אני מבטיח שלא אגרום לנעילה או אחכה לשחרור נעילות . חובה . ניסיון ממערכות יצור עמוסות .
גבי שאילתות adhoc  
כאשר עובדים עם SP , מידע על ה- DB נשמר בתוך ה- SP .כאשר עובדים עם שאילתת adhoc ה- DB מופיעה כ- NULL ב sys.dm_exec_sql_text.את שם ה- DB מיתן לקחת מ- DMF בשם sys.dm_exec_plan_attributes. הקלט הוא מזהה ה plan מ DMV sys.dm_exec_cached_plans.sys.dm_exec_plan_attribute מכיל מידע רב לגבי ה- DBs . בדוגמא הבאה נבחר ב- DBId באופן הבא :
WHERE pa.attribute = 'dbid'
השאילתה השלמה תראה כך
SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = 'dbid'
ORDER BY cp.usecounts DESC;
הסבר לגבי הטכניקה :
אנו מנסים למצוא את שם ה- DB ע"י הפונקציה COALESCE , שמחזירה את הערך הראשון ששונה מ- NULL מתוך רשימה של ערכים .DBID מתוך DMF sys.dm_exec_sql_text  (DB_NAME(st.dbid),) . אם הערך הוא NULL אז DBID מתוך sys.dm_exec_plan_attributes . אם זה NULL אז מניחים שזה רץ מתוך RESOURCE DB .
שימו לב בדוגמא זיהנו שאילתות ad hoc ע"פ התוספת של * לשם ה- DB . ניתן לבצע זאת גם ע"י העמודה objtype .
בברכה
דוד יצחק – DBA
BSC Information system , ME System Engineering ,MBA
מנהל פורום DBA צפון
http://www.iloug.org.il/Event_Page.php?EventID=159&BannID=1907

[…] זה הוא המשכו של מאמר שפורסם בשנה שעברה והתייחס לפונקציות אנליטיות. בחלק השני זה של המאמר אני […]

[…] זה הוא המשכו של מאמר שפורסם בשנה שעברה והתייחס לפונקציות אנליטיות. בחלק השני זה של המאמר אני […]

[…] זה הוא המשכו של מאמר שפורסם בשנה שעברה והתייחס לפונקציות אנליטיות. בחלק השני זה של המאמר אני […]

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

קהילת

קהילת ה- BI וה- BIG DATA מתכנסת ב- 28-10 !

שלום רב, למי שלא ידע 🙂 קהילת ה- BI וה- BIG DATA בעולמות Microsoft מתכנסת מידי חודש על מנת להפגש, להכיר ולשמוע הרצאות במגוון נושאים טכנולוגיים מרתקים בתחום. במפגש הקרוב (מספר 63) שיתקיים ב- 28-10-2015, יום רביעי [...]
הזמנה

הזמנה ל-SQL Saturday #481 – Israel 2016

שלום רב, בקרוב יתקיים בישראל כנס טכנולוגי מרכזי קהילת ה-DB וה-BI בתחום ה-SQL Server – ה-SQL Saturday! הכנס אשר מאורגן בהתנדבות על ידי אנשי הקהילה יכלול במהלכו מספר מסלולי לימוד בתחומים טכנולוגיים שונים. [...]

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]

תיעוד בסיס הנתונים – למה זה טוב?

ישנם מספר נושאים חשובים שיש לתת עליהם את הדעת בפיתוח ותכנון בסיס נתונים. כמובן שצריך לשים לב לנירמול נכון של הטבלאות, וכמובן שצריך להגדיר סטנדרט לכתיבה נכונה של הקוד, וכמובן שאי אפשר בלי [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: