פונקציות החלון החדשות SQL Server 2012 גרי רשף DBAאפליקטיבי – בנק הפועלים בלוגים ,פורומים ,לינקדאין ,כישורים וקישורים: http://About.me/GeriReshef 1 פונקציות החלון החדשות SQL Server 2012 • • • • • מה בתוכנית? מהן פונקציות החלון ומדוע הן מעניינות כל כך? הגל הראשון :פונקציות החלון בSQL Server 2005- הגל השני :פונקציות החלון בSQL Server 2012- הגל השלישי :למה אנו מצפים בעתיד? שאלות ,הערות ,סיכומים.. פונקציות החלון החדשות SQL Server 2012 כלי פיתוח • שפות פרוצדורליות )איך?(VB, C, Java - • שפות דקלרטיביות )מה?(SQL - • מה הקשר לפונקציות החלון? 2 פונקציות החלון החדשות SQL Server 2012 2012 פונקציות חדשות First_Value 2012 ארגומנטים חדשים Frame הגלים השונים 2005 פונקציות צבירה Sum Count Max/Min Avg 2005 פונקציות ספירה Row_Number פונקציות החלון החדשות SQL Server 2012 Window Vs. Frame Customer Month Quantity Adva 1 … Adva 2 … Adva 4 … Adva 5 … Beni 4 … Carmel 2 … Carmel 3 … Carmel 5 … 3 2012 פונקציות חדשות Lag פונקציות החלון החדשות SQL Server 2012 Row Vs. Range Running Total Range Running Total Row Quantity Month Year Customer 130 80 80 1 2011 Dana 130 130 50 1 2011 Dana Dana 320 220 90 2 2011 320 240 20 2 2011 Dana 320 270 30 2 2011 Dana 320 320 50 2 2011 Dana 360 360 40 3 2011 Dana פונקציות החלון החדשות SQL Server 2012 • • • • נקודות לסיכום – יתרונות וחסרונות פתרונות פשוטים לבעיות מסובכות פתרונות מקוריים ביצועים בעייתיים טיפול לקוי ב"חורים" 4 ?שאלות !תודה רבה http://About.me/GeriReshef 5 Introducing SQL Server 2012 Extended Events Enhancements Keren Bartal Tzahi Hakikat 888 holdings Agenda • • • • • • About us Introduction to Extended Events Extended Events 2008 Extended Events Practical Terminology Extended Events 2012 Enhancements Summary 6 Agenda • • • • • • About us Introduction to Extended Events Extended Events 2008 Extended Events Practical Terminology Extended Events 2012 Enhancements Summary About 888 • 888.com is a global online gaming company. • Our purpose is to provide quality entertainment for people who enjoy gambling. • Giving them the opportunity to do so in a safe, fun, fair, regulated and secure environment. 7 888 Database Environment 50 Production Instances 300 Development Instances 400 Databases 250 TB Of Data 24*7 Availability 99.95 Uptime 8 Agenda • • • • • • About us Introduction to Extended Events Extended Events 2008 Extended Events Practical Terminology Extended Events 2012 Enhancements Summary Extended Events • General event-handling system for windows servers • Used for problem diagnosis and info gathering and auditing • The Extended Events infrastructure supports the correlation of data from SQL Server and OS 9 Extended Events • Support 7 different types of targets • Event and consumer agnostic – Any event can be processed by any consumer – New events can be added, immediately useable • Rich predicate system for filtering • Less overhead than server-side trace queues – 10,000 events processed will consume 1% of single 2GHz processor Agenda • • • • • • About us Introduction to Extended Events Extended Events 2008 Extended Events Practical Terminology Extended Events 2012 Enhancements Summary 10 Extended Events 2008 אוהבים לכתוב בסינטקס שנראה כמו כתב ?סתרים כנעני לא נותן לכם מה שאתםProfiler-מרגישים שה ?צריכים . מה אכפת לי, תירו לעצמכם ברגל,קדימה ? של מי הציטוט הנ"ל Extended Events 2008 drawbacks • XE required extensive understanding of system catalog views and DMVs • Event Sessions could only be managed through the use of DDL commands • Reading target data requires the use of XQuery 11 • • • • Extended Event Metadata • Catalog views for defined session info – – – – – • DMVs for Event System Metadata – – – – • server_event_sessions server_event_session_target server_event_session_fields server_event_session_actions server_event_session_events dm_xe_package dm_xe_objects dm_xe_object_columns dm_xe_map_values DMVs for currently active session info – – – – – dm_xe_sessions dm_xe_session_targets dm_xe_events dm_xe_event_actions dm_xe_object_columns Demo Capture errors with XE 2008 • Find events and actions • Create a new event session • View the output 12 Agenda • • • • • • About us Introduction to Extended Events Extended Events 2008 Extended Events Practical Terminology Extended Events 2012 Enhancements Summary Extended Events Objects Module Packages Events Targets Actions Types 13 Predicates Maps Packages • Packages are metadata containers • Packages register at module load time • 9 available packages • package0 - XE system objects (default) • sqlserver - SQL Server related objects • sqlos - SQL Server Operating System (SQLOS) related objects • SQL audit uses private XE package Events • • • • • • An event is a well known point in code Unique schema for each event Supports optional fields Events fire synchronously 264 events in 2008 R2 618 events in 2012 14 Actions • programmatic response or series of responses to an event • Can be added to any event • Adds data to the event payload • Actions are invoked synchronously • Trigger a memory dump Demo Capture errors using the XE UI • Create an event session • Configure action • Watch live data 15 Targets • Target is an event consumer – Can be synchronous or asynchronous • Target types – – – – – – – event_file event_counter histogram etw_classic_sync_target pair_matching ring_buffer event_stream Demo Monitor locks Present different types of targets • • • • • • Ring buffer Event file Event counter Histogram Pair Matching Etw_classic_sync_target 16 Predicates • Predicates are a set of logical rules that are used to evaluate events when they are processed. • Boolean expressions using flexible operators • Event data • Action data • Global State Demo Activity Tracking Present different types of Predicates • Event Predicates • Action Predicates • Global Predicates 17 Event Session • The materialization of combination of metadata elements of XE architecture • Multiple targets per session • Event can be in many sessions – Actions/Predicates are per event • Event Session can specify what to do if target can't keep up • Event Session defines data retention • Event session can add or remove events on runtime Event Session 18 Event life cycle Pre-Collect Collection Predicate evaluation Customizable attribute check Predicate evaluation IsEnabled check Event data collected Publish Actions executed Synchronous targets served Event data buffered for asynchronous targets Agenda • • • • • • About us Introduction to Extended Events Extended Events 2008 Extended Events Practical Terminology Extended Events 2012 Enhancements Summary 19 Extended Events 2012 Enhancements • User Interface – Advanced & Wizard UI for creating and managing – Display & Analysis • Expanded to other systems – Analysis Services, Replication, PDW • Managed code – Powershell object model for runtime and meta data – Reader API for XEL files and near real time stream User Interface • Event Session list – Provides a list of Event Sessions • New Session Wizard – Provides a simplified experience for creating an Event Session • Extended Events display – Tabbed windows that display Extended Events trace data 20 Demo Capture queries and group by query hash • Grouping • Aggregation • Save XE to a table Extended Events Management API • Management API provides the ability to create and modify event sessions • Provides a complete object model for XE usage by managed applications • Provides a XEReader API for reading event files and event streams coming from a running event session on a server 21 Agenda • • • • • • About us Introduction to Extended Events Extended Events 2008 Extended Events Practical Terminology Extended Events 2012 Enhancements Summary Extended Event Use Cases • Proactive monitoring – Application errors – Errors log – Event grouping • Troubleshooting – Page Split – blocking • Audit – Monitor the access of privileged and non privileged users 22 The Profiler’s grave Summary • SQL Server 2012 offers simplified diagnostic tracing with Extended Events – Management Studio integration provides SQL Server Profiler functionality for Extended Events allowing Event Sessions to be created, modified, and scripted – Management API allows managed applications to be developed that leverage Extended Events 23 24 סיפורו של חוזר בתשובה :איך השתכנעתי ש- DACהולך לשנות את חיי הDBA- מה בתוכנית? • מה זה DACולמה שלחתי אותו לישון בסלון • מה קרה בשנה האחרונה • הדגמה 25 Challenges Development • No uniform DB project system and workflow Deployment Management • DBAs and Developers work in silos • Time consuming and error prone • Difficult to centrally control the environment Investments Development • Data-tier Application project • Integrated editor, debugger, IntelliSense, policy designer • Static code analysis, build service Deployment Management • Easier to deploy and upgrade Datatier Apps from VS and SSMS (and now SSDT) • Manage @ Scale: SQL Server Control Point • Visualize resource utilization at the instance & application level 26 Concepts .dacpac = unit of deployment (datatier application + developer intent) Data-tier Application Component • Improves collaboration between developer and DBA • Moves developers from a procedural model to a declarative model V1 is targeted at small apps Schema LOGICA L Tables, Views, Procs, UDFs PHYSIC AL Users, Logins, Indexes DAC Deployment Profile Deployment Requirements, Management Policies Data-Tier Applications Data-Tier Applications • Often referred to as the DAC Framework (or DAC Fx) • Collection of APIs, file and data formats, and services • Dramatically simplify database deployment and versioning • Fully integrated into SSDT and essential for developers! • Allows a focus on the data model rather than on scripts • Target on-premises or cloud 27 הרהורי כפירה (UCP- )כחלק מEnterprise Edition Only • • לא היתה תמיכה בכל האובייקטים ...• וכל האובייקטים התלויים בהם AdventureWorks-• אפילו לא ב Side by Side : DAC • שדרוג באמצעות Data-• אין תמיכה ב • אין תמיכה בגרסאות ישנות ?מה השתנה v2 DAC Upgrade • Side-by-side process • Data migrated to new database • Retained copy of previous database v3 DAC Upgrade • • • • • Shipped with SQL Server 2012 Supports in-place upgrades No need to copy/migrate all the data (#1 requested feature) Support for SQL Server 2005 SP4 (or later) and SQL Azure Need to ensure sufficient transaction log space is available for upgrade process • PowershellIncrementalUpgrade() method added 28 Enhanced Object Support Significant enhancement to list of supported objects • 98% SQL Azure parity • Support added for permissions and roles Enhanced database object support: Newly Supported Objects SYNONYM SEQUENCE SPATIAL INDEX GEOMETRY GEOGRAPHY HIERARCHYID Cursor Parameters in Stored Procedures STATISTICS Platform Targeting Single project can target multiple platforms • Versions of SQL Server • SQL Azure Build prevents use of unsupported features for platform Easy migration of databases to SQL Azure • Create down-level .dacpac file 29 Demo Database Project vs. DAC Project Database Project (Mission Critical, Business Critical) Data-tier Application Project V1 (Small Apps) Visual Studio Visual Studio Generate scripts Build Build .dbschema .dacpac Deplo y Deploy 30 DAC Exports Option to export both schema and data to a single file • Exports logins, users, tables, columns, constraints, indexes, views, stored procedures, functions and triggers • First added in DAC v2 Feature Pack CTP .bacpac files • Uses JSON format for data • Much easier than using (and configuring) bcp • Avoids issues with code pages and precision Not intended as a backup mechanism • No transaction log or history • Not transactionally consistent (where concurrent access is occurring) DACImportExportCLI.exe –s localhost-d RetailDB -f C:\EXPORT\RetailDB.bacpac -x -e DAC Import Imports schema and data • Registers Data-Tier Application Greatly simplifies migrations • SQL Server -> SQL Azure DACImportExportCLI.exe –s sdf23sdf.database.windows.net -d RetailDB -f C:\EXPORT\RetailDB.bacpac -i -u Username -p Password 31 SQL Azure Integration BACPAC can also be used in SQL Azure portal • Databases export .bacpac to Windows Azure storage • Create one or more databases based on importing .bacpac from storage Extract/Deploy Data-Tier Applications to/from local filesystem • Use SSMS directly connected to SQL Azure Call To Action! Consider using DAC Fx on your next project • Focus on the data model, not on the schema changes Simplify your application deployment • Move on from error-prone scripts • Distribute .dacpac or .bacpac files with your application 32 33 Spatial או מי צריך את זה בכלל מי משתמש ב?Spatial- 34 כולנו! תוכן • • • • • • • קצת על רפאל ועליי מה זה מערכת מידע גיאוגרפית? – אתגרי מערכת מידע גיאוגרפית סוגי מידע – גיאומטרי – גיאוגרפי אינדקסים Tessellation Spatial Aggregations חידושים ושיפורים בSQL 2012- 35 קצת על רפאל • מפתחת ,מייצרת ומספקת מגוון מערכות לחימה מתקדמות אשר תורמות למערכת הביטחון יכולות: – בים – באוויר – ביבשה • רווחים בשנת – 2010למעלה מ 1.8מיליארד ₪ • למעלה מ 5000-עובדים קצת עליי • בוגר החוג למערכות מידע באוניברסיטת חיפה • DBAתשתיתי כ 6.5-שנים: – MS SQL 2000-2012 – Oracle 9i-11g – SQL Anywhere 10 – Sybase 36 תחומי אחריות • • • • • תחזוקה שוטפת של למעלה מ 100-שרתים במערכות מרכזיות. פיתוח ,הדרכה והטמעה של מגוון שירותים וכלים: – Reporting Services – Integration Services – Spatial – PowerShell – Mirroring, Replicationועוד... שיפור ביצועים וכיוונון שאילתות ייעוץ וליווי פרוייקטים ברפא"ל אבטחת מידע בבסיסי נתונים אז מה זה בעצם מערכת מידע גיאוגרפית? • • • מערכות מידע גיאוגרפיות ) (GISהן מערכות מידע המטפלות בהיבטים המרחביים של המידע .למשל ,ממ"ג תשמור ברשומותיה את כתובת העסק והפוליגון המרחבי התוחם אותו באופן שיאפשר את ניצול המידע הזה. ממ"ג יודעת לענות על שאלות בנוגעות ליחס המיקומים בין ישוייות שונות כגון: – מה המרחק בין כתובת Xלכתובת ?Y – האם הכתובת Xנמצאת בשכונה ) Yפוליגון(? – האם כביש Xוכביש Yנחתכים? – הבא לי את כל הכבישים )קווים( הנכנסים או יוצאים אל/משכונה מסויימת )פוליגון( בנוסף ,ממ"ג שומרת גם נתונים נוספים ורגילים .למשל שווי נכס ,מספר דיירים וכו'. 37 אתגרי מערכת המידע הגאוגרפית • מורכבות אלגוריתמית – יש צורך באלגוריתמים מתוחכמים על מנת לענות ביעילות על שאילתות גיאוגרפיות • נפח בסיסי הנתונים – יש צורך בשמירת מידע בהיקף רב .מספר הרשומות )כבישים, צינורות ,קווי חשמל וכו'( של העיר ניו יורק מגיע למיליוני רשומות • בעיות תצוגה – איך להציג את הנתונים בצורה נכונה )אמינות המידע( ,מפורטת )חדות( ונעימה לעין ללא עיוותים )גיאודיזה( סוגי המידע המרחבי הנשמרים בDB- • ב SQL-נשמרים 2סוגי מידע מרחבי: • גיאומטרי – מוכר לכולנו משיעורי ההנדסה ביסודי – מערכת צירים x,y,zלציון אורך,רוחב ,גובה – מוגדרת ע"י קווים ומצולעים • גיאוגרפי – מוכר בעיקר מאפליקציות ניווט ,מפות באינטרנט וכו' – מערכת ייחוס גיאודזית )לרוב משתמשים ב(WGS84- – כל משבצת מייצגת תא שטח ברזולוציה מסויימת 38 מידע גיאומטרי מייצג אובייקטים גיאומטריים שונים כגון: – נקודה )(Point • • • • משמשת כיחידה הבסיסית ביותר )אבן הביניין( ליצירת אובקייטים מורכבים יותר מייצגת מיקום מדוייק מוגדרת ע"י נקודו ציון במערכת צירים x,yכאשר ניתן להוסיף גם zלציון גובה ו M-לציון יחידת מידה .למשלPOINT(10 10) / POINT(10 10 10 1) : אין לה מאפיינים כגון אורך ,שטח ,היקף וכו' – קו )(Linestring • • • • מציין נתיב בין מספר נקודות )LINESTRING(0 0, 10 10 נחשב כצורה פשוטה כאשר הוא לא חותך את עצמו נחשב כטבעת )מעגל( כאשר הוא יוצר צורה סגורה תמיד נחשב כצורה חד מימדית ,יש לו אורך אבל אין לו שטח )אפילו כאשר מוגדר כמעגלי( פוליגון )(Polygon • צורה סגורה ,דו מימדית • בעלת שטח ואורך • יכולה להכיל "חורים" בתוכה – אוסף )(Collection • מקביל למערך ברוב שפות התכנות המקובלות • מכיל אוסף של נקודות ,קווים או פוליגונים • האוסף הגנרי ביותר נקרא GeomCollection • ממנו נגזרים MultiPoints,MultiPolygon,MultiLineString 39 עבודה עם מידע גיאומטרי • על מנת לעבוד עם אובקייטים גיאומטריים יש להשתמש בפונקציות שונות כגון: • –STGeomFromTextהפיכת מחרוזת לאובייקט גיאומטרי תקין )SET @g = Geometry::STGeomFromText('LINESTRING(0 0, 10 10, 21 2)',0 • – STEnvelopeיצירת פוליגון מרובע מסביב לצורה נתונה • – STConvexHullיצירת פוליגון המקיף את קווי המתאר של צורה נתונה • )( – STDifferenceמחזירה את השטח השונה של צורה הנחתכת ע"י צורה אחרת • )(– STIntersectionמחזירה את השטח החופף בלבד בין 2צורות נחתכות • )( – STUnionמחזירה את השטח החופף של 2צורות נחתכות דוגמאות 40 מידע גיאוגרפי מייצג אובייקטים גיאוגרפיים במערכת ייחוס: • נקודה נשמרת כ longitude -וlatitude- • מכיוון שכדור הארץ אינו עגול ,יש צורך במערכת ייחוס )דאטום( אשר מקרבת את מערכת הקורדינאטות שלנו אל הגיאואיד )המשטח המייצג את "גובה פני הים" סביב כדור הארץ .הדאטום הנפוץ במערכות GPSהינו WGS84מכיון שהוא קרוב יחסית למשטח הגיאואיד ברוב כדור הארץ מידע גיאוגרפי חשוב להקפיד על שימוש בדאטום הנכון עבור המערכת שלנו!! • שימוש בדאטום לא נכון גורם לטעויות חישוביות: – בשנות ה 60-נבנה גשר בין 2האיים הלא נכונים בהוואי – במלחמת המפרץ ,המפציצים האמריקאים פגעו בקביעות בסטייה של 750מטרים מהמטרה 41 Spatial indexes • נסתכל על הדוגמא הבאה: – במערכת ציונים באוניברסיטה ,נשמרים בטבלה לכל סטודנט פרטיו האישיים וציונו הממוצע. – בהנחה שיש 20,000רשומות ,אם נרצה לשלוף רק את אלו עם ממוצע 90ומעלה ,השליפה תיקח זמן רב )יחסית( – לצורך שיפור השליפה ,נגדיר אינדקס על שדה הציון הממוצע ונשפר את יעילות הריצה )).(O(n)O(logn • בעוד ששאילתות כלליות מבצעות השוואות על פי יחס סדר )=><( בממ"ג השאילתות כוללות אופרטורים מרחביים. • בממ"ג נרצה בנוסף לענות גם על שאילתות כגון "מי הסטודנטים המתגוררים בשכונת קרית אליעזר בחיפה?" • אפשר לפתור את הבעיה ללא אינדקס -מעבר על כל הטבלה ולכל רשומה לבדוק האם היא בתוך הפוליגון של קרית אליעזר או לא 42 • כדי להתגבר על חוסר היעילות הנ"ל נשתמש באינדקסים גיאוגרפיים .קיימים מספר סוגי אינדקסים גיאוגרפיים ולכל אחד יתרונות וחסרונות. • האינדקס הנפוץ והפשוט מכונה Fixed quadtree index • בשיטה זו מחלקים את העולם לריבועים בגודל קבוע ,ממספרים את הריבועים ולכל צורה במרחב שומרים את הריבוע/ים בו היא נמצאת. • כעת החיפוש יבוצע בשני שלבים: – חיפוש גס • תחילה נחשב את כל הריבועים התוחמים את שכונת קריית אליעזר • נשלוף את כל הרשומות אשר מספר האריח שלהן נמצא ברשימה .בשלב זה כבר פסלנו חלק גדול מהרשומות – חיפוש עדין • עבור הרשומות שנותרו נבדוק האם הן בתוך הפוליגון המגדיר את השכונה .ע"י כך נסנן רשומות מרחובות סמוכים שלא נכללות בשכונה המבוקשת 43 • ב SQL-האינדקסים נשמרים ע"י שימוש ב.B-trees- • לצורך שיטוח המידע הדו-מימדי של האינדקס לתוך עצי ה ,B-ה SQL-משתמש בfour-level grid - hierarchy • כל שכבה נוספת מפרידה את השכבה שמעליה ומציגה אותה בפירוט נוסף 44 צפיפות האינדקס רמות צפיפות3 • לכל שכבה באינדקס ניתן להגדיר Level Grid configuration Number of cells Low 4x4 16 Medium 8x8 64 High 16x16 256 :• יצירת האינדקס מתבצעת באופן הבא • CREATE SPATIAL INDEX SIndx_SpatialTable_geography_col3 ON SpatialTable2(object) WITH ( GRIDS = ( LEVEL_3 = HIGH, LEVEL_2 = HIGH ) ); 45 Tessellation • לאחר בניית הרשת השטוחה ,האינדקס קורא את העמודה הגיאוגרפית שורה אחר שורה ומבצע "פסיפוס" של הנתונים השייכים לאותה משבצת. • ה"פסיפוס" קורה בכל ארבע השכבות ,שכבה אחת בכל פעם. • בתהליך מוכנסים לאינדקס touched cellsשבהם יש אובייקטים ש"נוגעים" לאובייקט המאונדקס באותו רגע. Tessellation Rules • על מנת להגביל את המידע המוכנס לאינדקס ,משתמשים ב. tessellation rules • The covering rule – אם האובייקט מכסה תא לגמרי הוא נחשב כתא מכוסה – תא מכוסה נספר אבל לא נחשב בתהליך ה"פסיפוס" – החוק הזה תקף בכל 4השכבות – מפשט את התהליך וחוסך בגודל האינדקס 46 • The cells-per-object rule החוק שולט בכמות המידע הנשמרת עבור כל אובייקט בשכבות הנמוכות ,ע"י הגבלה של מספר התאים הנספרים עבור כל אובייקט )למעט בשכבה הראשונה( 47 • The deepest-cell rule מאנדקס בצורה הטובה ביותר את האובייקט ע"י שמירה של המידע בשכבה התחתונה ביותר. התאים בשכבות העליונות לא נכללים בספירת כמות התאים לאובייקט ולכן לא נשמרים באינדקס 48 Spatial Aggregation • הצגת מאוחדת של שכבות מידע שונות • SELECT Geography::UnionAggregate(geog) FROM Counties WHERE name = 'Washington'; • SELECT Geography::EnvelopeAggregate(geog) FROM Counties WHERE name = Washington'; • • SELECT Geography::CollectionAggregate(geog) FROM Counties WHERE name = 'Washington'; • SELECT Geography::ConvexHullAggregate(geog) FROM Counties WHERE name = 'Washington'; 49 SQL 2012 -שיפורים וחידושים ב :Circular Arc-• תמיכה ב – CircularString – CompoundCurve – CurvePolygon – כמות מידע מצומצמת באופן משמעותי הנדרשת על .מנת לשמור מידע על קווים מעוגלים – יכול להשתלב עם מקטעי קווים ישרים DECLARE@CircularSegmentGEOGRAPHY =GEOGRAPHY::STGeomFromText('CIRCULARSTRING(0 -30, 0 0, 0 30)',4326); SELECT @CircularSegmentAS Shape; Full Globe Support היה מוגבל כדור בכל פעםSQL 2008 • • תמיכה במתודות חדשות – EnvelopeAngle() method returns 180 for objects larger than a logical hemisphere and < 90 for smaller objects – ReorientObject() reverses the ring orientation DECLARE @Globe GEOGRAPHY=GEOGRAPHY::STGeomFromText('FULLGLOBE',4326); SELECT @Globe.STArea();-- calculate the area of the WGS84 ellipsoid -- Result: 510,065,621,710,996 square meters 50 Spatial Index Enhancements • • • • מהירות בנייה פי 4-5מהירה יותר Auto-grid – מחליף את הפרמטר GRIDS – משתמש ב 8-רמות – נותן דיוק רב יותר דחיסה של האינדקס – ניתן לחסוך 40-60%בשטח האינדקס Hintsחדשים לשיפור ביצועים מה למדנו? • • • • • • מה זו מערכת מידע גיאוגרפית האתגרים בבניית מערכת כזו הבדל בין סוגי המידע הנשמרים )גיאומטרי/גיאוגרפי( אינדקסים גיאוגרפיים -כיצד בנויים ואיך עובדים איחוד שכבות מידע גיאוגרפיות שיפורים וחידושים ב2012- 51 52 Boosting performance with Columnstore Indexes Michael Zilberstein DBArt Ltd [email protected] DEMO 53 History • Column-oriented databases: – Sybase IQ – Vertica – Aster Data – Greenplum –… • Excel PowerPivot. • VertiPaq. • xVelocity Columnstore index. C1 C2 C3 Uses VertiPaq compression 54 C4 C5 C6 Reduced IO SELECT C2, SUM (C3) … C2 C1 C3 C4 C5 C6 Fetches only needed columns from disk Columns are compressed Less IO Better buffer hit rates New query execution technology • Batch mode execution of some operations – processes rows in batches – groups of batch operations in query plan • Better parallelism, better algorithms 55 Dictionary-based compression Year of Birth 1996 Year of Birth Code 1996 1 1975 15 1948 50 1932 58 … 60 Internal Dictionary On-the-fly build dictionary with all distinct value. Substitute non-selective values with ID. Index in our example – 6 bits per row. 1975 1975 1948 Year of Birth Code 1932 Compressed Fact 1 … 15 15 50 58 60 Segments C1 C2 C3 C4 C5 C6 Set of about 1M rows Column Segment 56 Column segment contains values from one column for a set of about 1M rows Column segments are compressed Each column segment stored in separate LOB Column segment is unit of transfer from disk Data Dictionary Views New execution plan elements 57 Best practices / worst practices • Best practices: – Put columnstore indexes on large tables only. – Include every column of the table in the columnstore index. – Structure your queries as star joins with grouping and aggregation as much as possible. • Worst practices: – Avoid JOIN and/or filter on string columns in the table with columnstore index. – Avoid OUTER JOIN, UNION ALL, IN/NOT IN. – Avoid JOIN between 2 Fact tables. Datatype Limitations • • • • • • • Decimal > 18 digits (Var)Binary BLOB (N)Varchar(max) Uniqueidentifier Date/Time types > 8 bytes (Datetime2 etc) CLR 58 Issues and Workarounds: good, bad and ugly • • • • • • Outer Joins NOT IN Scalar Aggregates Multiple DISTINCT UNION ALL IN and EXISTS Data loading and columnstore index • Columnstore index makes table read-only. DEMO 59 Data loading and columnstore index • 2 ways to load data: – Partition switching – Disable/drop index -> load data -> create/rebuild index. References • http://channel9.msdn.com/Events/TechEd/Nor thAmerica/2011/DBI312 • http://social.technet.microsoft.com/wiki/cont ents/articles/4995.sql-server-columnstoreperformance-tuning.aspx 60 Q&A Michael Zilberstein Tel: 052-4767219 E-Mail: [email protected] 61
© Copyright 2024