‫פונקציות החלון החדשות‬
‫‪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