SQL Server 2008 Beyond Relational - PowerPoint PPT Presentation

Loading...

PPT – SQL Server 2008 Beyond Relational PowerPoint presentation | free to view - id: 9ba52-OGI0N



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

SQL Server 2008 Beyond Relational

Description:

They are Not used in any spatial method. Coordinate order is Latitude /Longitude ... How Can We Model property bags? Products Catalog. Lab tests with different ... – PowerPoint PPT presentation

Number of Views:111
Avg rating:3.0/5.0
Slides: 66
Provided by: downloadM
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Server 2008 Beyond Relational


1
SQL Server 2008 Beyond Relational
Yoni Okun DBA Team Manager SRL Group Yonio_at_srl.co.
il
2
(No Transcript)
3
Data Storage Explosion!
New Digital Information Created
  • 55 in personal PCs
  • 16 in corporate data warehouses
  • Internet only 21 TB
  • Email 500x more than Internet/year (400TB)

4
Data Storage Explosion!
Storage of New Information
  • 1 Megabyte
  • 2 250 page novels
  • 1 Gigabyte
  • a pickup truck filled with books
  • 2 Terabyte
  • 100,000 trees made into paper and printed.
  • 2 Petabytes
  • All U.S. academic research libraries
  • 2 Exabytes
  • Total volume of information generated in 1999

Hard Drive Prices
1980… 40,000!
5
Data Storage Explosion!
6
Relational and Non-Relational Data
Relational Data
Docs Multimedia
Applications
XML
Spatial
7
Agenda
  • Spatial Data Types
  • Filestream Data Storage
  • HierarchyID Data Type
  • Sparse Design Columns Stats
  • Filtered Indexes
  • Integrated Full Text Search
  • Administrative Features

8
Spatial Data Type
  • Answers to location-based queries
  • Which roads intersect campus?
  • Does my land claim overlap yours?
  • All Italian restaurants within 5 km
  • Does Your Database Include Address?
  • Where are all SQL Server Geeks?

SELECT FROM roads WHERE roads.geom.Intersects(_at_
ms)1
9
Spatial Data Type
  • The Open Geospatial Consortium (OGC) defines a
    hierarchy of spatial data types

10
Spatial Data Type
Geometry
Geography
A geodetic model
A planar model (Created by the Mercator
projection)
11
Spatial Data Type
  • Supports two dimension Only
  • While Points can hold members
  • Z (for elevation) and M (for measure)
  • They are Not used in any spatial method
  • Coordinate order is Latitude /Longitude
  • Longitude/Latitude In RTM

12
Working With Spatial Data Type
  • 1. Use with DDL

CREATE TABLE Hotels (HotelName nvarchar(200),
HotelLocation )
geography
2. Insert Values
INSERT INTO Hotels VALUES ('Dan',
), ('Herdos'

)
geographyPoint(29.548306566591655,34.96617794036
865,4326)
, geographySTGeomFromWKB(0x0101000000B699
452D3C8C3D4000000082 877B4140,4326)
3. Use Methods Properties to work with data
DECLARE _at_x geography, _at_y geography select _at_x
HotelLocation from Hotels where HotelName
'Dan' select _at_y HotelLocation from Hotels where
HotelName 'Herdos'
select _at_x.ToString(), _at_y.ToString() select
_at_x.STDistance(_at_y)
13
Working With Spatial Data Type
  • 1. Use with DDL

CREATE TABLE Hotels (HotelName nvarchar(200),
HotelLocation )
geography
2. Insert Values
INSERT INTO Hotels VALUES ('Dan,

), ('Herdos

)
geographyPoint(29.548306566591655,34.96617794036
865,4326)
, geographySTGeomFromWKB(0x0101000000B699
452D3C8C3D4000000082 877B4140,4326)
3. Use Methods Properties to work with data
DECLARE _at_x geography, _at_y geography select _at_x
HotelLocation from Hotels where HotelName
'Dan' select _at_y HotelLocation from Hotels where
HotelName 'Herdos
select _at_x.ToString(), _at_y.ToString() select
_at_x.STDistance(_at_y)
14
Spatial Data Type
  • Input
  • Binary - STTypeFromWKB
  • Text - STTypeFromText
  • GML GeomFromGml
  • Output
  • Binary - STAsBinary
  • Text - STAsText
  • GML - AsGml
  • Text with Z and M AsTextZM
  • SRID
  • Represent different assumption around what is the
    earth elipse.
  • SRID 4326 GPS
  • sys.spatial_reference_systems

15
Spatial Data Type
Demonstration
  • Using Spatial Viewer
  • Using Spatial Data Type

15
16
Useful Methods / Properties
  • Descriptive
  • STArea
  • STLength
  • STCentroid
  • Relation between two instances
  • STIntersects
  • STDistance
  • Collections
  • STGeometryN - geography element in a
    GeometryCollection
  • STPointN Nth point of a geometry

17
Extension Methods
  • SQL Server 2008 extends OGC methods
  • MakeValid Converts to OGC valid instance
  • You cant use the Methods unless Type is valid
  • For example 8- shape is not valid
  • use when there is user input of shapes
  • BufferWithTolerence - similar to STBuffer
  • , allows approximation and variation.
  • Allows adding buffer around my location or , for
    example, around a poligon.
  • Reduce - Simplify a complex geography or
    geometry.
  • Opposite of buffer. (donut)
  • NumRings, RingN - polygons with multiple rings
  • GML support
  • Z and M properties and AsTextZM method
  • Filter - provides a quick intersection set but
    with false positives
  • EnvelopeCentre,EnvelopeAngle for Geography types

18
Spatial Indexes
  • Spatial Is Indexed With An
  • Adaptive Multi- level Grid
  • Index Is Integrated Into The SQL
  • Server
  • Index Consists Of A Grid- based
  • Hierarchy
  • Each Level Subdivides The Grid
  • Sector That Is Defined In The
  • Level Above

Spatial Index - Conceptual Model
19
Spatial Indexes
  • 1. Decomposing space into Grid Hierarchy
  • Grids parameter determines density
  • Low 4x4
  • Medium 8x8 (Default)
  • High 16x16
  • 4x4x4 65,536 cells !!
  • 2. Tessellation
  • Fitting Objects Into Grid (Touched Cells).

20
Tessellation Process
Level 3 4 Intersections 85 matching Cells
Level 1 Intersections
Level 2 Intersections
Cells Per Object Stops Tessellation At Limit
(Per Object 15, Cells 13)
Complete Match Cells Arent Broken To Lower
Level (42 Cells)
21
Creating Spatial Indexes
  • CREATE SPATIAL INDEX Sindx_col2
  • ON SpatialTable (geometry_col)
  • USING GEOMETRY_GRID
  • WITH
  • (
  • BOUNDING_BOX ( xmin0, ymin0, xmax500,
    ymax200 ),
  • GRIDS (LOW, LOW, MEDIUM, HIGH),
  • CELLS_PER_OBJECT 64
  • )
  • BOUNDING_BOX - GEOMETRY index only
  • GRIDS - 4 Grid Levels, Grid Densities For
    Level - Low, Medium, High
  • CELLS_PER_OBJECT - number of cells recorded for
    matching

22
Spatial Data Type
Demonstration
  • Integration With Maps
  • App Code by Dan Morgenstern
  • SRL Group

22
23
Agenda
  • Spatial Data Types
  • FILESTREAM Data Storage
  • HierarchyID Data Type
  • Sparse Design Columns Stats
  • Filtered Indexes
  • Integrated Full Text Search
  • Administrative Features

24
FILESTREAM Storage
  • To Blob Or Not To Blob?
  • Mgmt Complexities Vs. Streaming Performance
  • Cons
  • LOBS take Memory buffers
  • Updating LOBS cause fragmentation
  • Per GB
  • However, File system "update" is delete and
    insert
  • Pros
  • Transactional consistency
  • Point-in-time backup restore
  • Single storage and query vehicle

?
25
FILESTREAM Storage
  • Low cost per GB
  • Streaming Performance
  • Integrated management
  • Data-level consistency
  • Lower cost per GB at scale
  • Scalability Expandability

Advantages
  • Complex application development deployment
  • Separate data management
  • Enterprise-scales only
  • Complex application development deployment
  • Integration with structured data

Challenges
  • Poor data streaming support
  • File size limitations
  • Highest cost per GB
  • Windows File Servers
  • NetApp NetFiler
  • EMC Centera
  • Fujitsu Nearline
  • SQL Server VARBINARY(MAX)

Example
26
FILESTREAM Storage
  • FILESTREAM Combines The Best Of 2 Worlds
  • Integrates DB Engine With NTFS
  • Storing BLOB Data As Files
  • FEATURES
  • Uses NT Cache For Caching File Data.
  • SQL Bpool Not Used And Is Available To Query
    Processing
  • Win 32 File System Interface Provide Streaming
    Access To Data
  • Compressed volumes are supported

27
FILESTREAM Storage
  • Its not only about Storing But About Working
    With BLOBS
  • Image Analysis
  • Voice Interpretation Scripting
  • Mixing Satellite Feeds Spatial Data Type For
  • Weather Reports
  • Etc..

28
FILESTREAM Implementation
  • At Database Level
  • Declare A Filegroup Map To Directory
  • At Table Level
  • Define On VARBINARY(MAX)
  • Must Have UNIQUEIDENTIFIER Column
  • Integrated Security Management
  • Permissions On FILESTREAM Implied On Files.
  • Tools And Functions Work For Filestream Data.
    (Backup)

29
FILESTREAM Programming
  • Dual Programming Model
  • TSQL (Same as SQL BLOB)
  • Win32 Streaming File IO APIs
  • Begin a SQL Server Tran
  • Obtain a symbolic PATH NAME TRANSACTION CONTEXT
  • Open a handle using sqlncli10.dll -
    OpenSqlFilestream
  • Use Handle Within System.IO Classes
  • Commit Tran

30
FILESTREAM Programming
// 1. Start up a database transaction -
SqlTransaction txn cxn.BeginTransaction()
// 2. Insert a row to create a handle for
streaming. new SqlCommand("INSERT ltTablegt VALUES
( _at_mediaId, _at_fileName, _at_contentType)", cxn, txn)
// 3. Get a filestream PathName transaction
context. new SqlCommand("SELECT PathName(),
GET_FILESTREAM_TRANSACTION_CONTEXT() FROM
ltTablegt", cxn, txn)
// 4. Get a Win32 file handle using SQL Native
Client call. SafeFileHandle handle
SqlNativeClient.OpenSqlFilestream(...)
// 5. Open up a new stream to write the file to
the blob. FileStream destBlob new
FileStream(handle, FileAccess.Write)
// 6. Loop through source file and write to
FileStream handle while ((bytesRead
sourceFile.Read(buffer, 0, buffer.Length)) gt 0)
destBlob.Write(buffer, 0, bytesRead)
// 7. Commit transaction, cleanup connection. -
txn.Commit()
31
FILESTREAM Programming
// 1. Start up a database transaction -
SqlTransaction txn cxn.BeginTransaction()
// 2. Insert a row to create a handle for
streaming. new SqlCommand("INSERT ltTablegt VALUES
( _at_mediaId, _at_fileName, _at_contentType)", cxn, txn)
// 3. Get a filestream PathName transaction
context. new SqlCommand("SELECT PathName(),
GET_FILESTREAM_TRANSACTION_CONTEXT() FROM
ltTablegt", cxn, txn)
// 4. Get a Win32 file handle using SQL Native
Client call. SafeFileHandle handle
SqlNativeClient.OpenSqlFilestream(...)
// 5. Open up a new stream to write the file to
the blob. FileStream destBlob new
FileStream(handle, FileAccess.Write)
// 6. Loop through source file and write to
FileStream handle while ((bytesRead
sourceFile.Read(buffer, 0, buffer.Length)) gt 0)
destBlob.Write(buffer, 0, bytesRead)
// 7. Commit transaction, cleanup connection. -
txn.Commit()
32
Initial FILESTREAM Limitations
  • Remote FILESTREAM storage - Not supported
  • DB Snapshot and Mirroring Not supported
  • Features not integrated
  • SQL Encryption
  • Table Value Parameters

33
FILESTREAM Storage
Demonstration
  • Administrating FILESTREAM
  • Programming FILESTREAM

33
34
Agenda
  • Spatial Data Types
  • FILESTREAM Data Storage
  • HierarchyID Data Type
  • Sparse Design Columns Stats
  • Filtered Indexes
  • Integrated Full Text Search
  • Administrative Features

35
HierarchyID
36
HierarchyID
  • Scenarios
  • List Forum Threads
  • Business Organization Charts
  • Product Categories
  • Files/Folders Management
  • Features
  • Compact - 100,000 Nodes, 6 Level 5 Bytes /
    Node
  • Available To Clr Clients As The Sqlhierarchyid
    Data Type

37
2k5 Alternatives - Adjacency Model
  • Pros
  • Understandable
  • 2k5 Recursive CTE
  • Cons
  • De-Normalized (PersonnelChart)
  • Not Set-Based

38
2k5 Alternatives Path Enumeration
  • Holds Path As A String Concatenation
  • Pros
  • Logical Representation
  • Cons
  • Searches Done With String Functions
  • And Predicates On Those Path Strings

39
2k5 Alternatives Nested Sets
  • "Left" And "Right" Columns Represent Edges
  • Pros
  • Predictable , Set Based Results
  • Cons
  • Must Be Maintained Separately

40
Demo Structure
1. Insert Root
2. Insert 1st Subordinate
3. Enter Rest of Tree
4. Query Hierarchical Data
5. Reparent Employee
6. Add Subordiante
7. Reparent Node
41
HierarchyID
Demonstration
  • Trees and Hierarchies

41
42
HierarchyID Methods
  • GetRoot() - root of hierarchy tree
  • ToString() - Logical string representation of
    Value
  • GetDescendant()- a child node x of this
  • GetAncestor() - hierarchyid of the nth ancestor
    of this
  • IsDescendantOf() - true if child is a descendant
    of this
  • GetLevel() integer representing depth of the
    node this
  • GetReparentedValue() -path to newRoot, followed
    by oldRoot to this

43
HierarchyID Indexes
Depth-first Index
Breadth-first Index
Employees that Report Directly To The Same
Manager
Employees That Report Through A Manager
44
Agenda
  • Spatial Data Types
  • FILESTREAM Data Storage
  • HierarchyID Data Type
  • Sparse Design Columns Stats
  • Filtered Indexes
  • Integrated Full Text Search
  • Administrative Features

45
Sparse Properties
  • How Can We Model property bags?
  • Products Catalog
  • Lab tests with different readings per test
  • Because they don't appear on each row
  • they are difficult to model

46
Modeling Sparse Properties
  • Entity-Attribute-Value
  • Non Relational
  • Value Column Issues
  • Need PIVOT to Make Sparse
  • Xml
  • Non Relational
  • Complex Updates
  • Sparse Columns
  • Hit 1,024 Limit
  • Storage

47
Sparse Columns
  • Efficient Way Of Managing Empty Data
  • Null Data ? No Physical Space
  • 30,000 Column Limit (RTM)
  • 1024 For "Non-sparse" Columns
  • Column Set - Xml Of All Sparse Values
  • For Web Site That Needs To Show The Properties

48
Sparse Columns
  • Sparse require more storage for nonnull values
    then regular Columns
  • NULL Percentage
  • percent of the data that must be NULL for savings
    40 space

49
Sparse Columns
  • Sparse Columns Benefits
  • To any external application the column will
    behave the same
  • Combination with filtered indexes
  • Combination with column set
  • Change Data Capture and Transactional replication
    both work
  • Sparse Columns Restrictions
  • Some data types cant be sparse
  • computed columns can't be sparse
  • You can't apply rules / default values.
  • cannot form part of a clustered index. 
  • Merge replication, Data Compression don't work.
  • Access (read and write) to sparse columns is more
    expensive

50
Sparse Columns
Demonstration
  • Build Sparse Design
  • Sparse Storage

50
51
Agenda
  • Spatial Data Types
  • FILESTREAM Data Storage
  • HierarchyID Data Type
  • Sparse Design Columns Stats
  • Filtered Indexes
  • Integrated Full Text Search
  • Administrative Features

52
Filtered Indexes
  • Do your queries relate to subsets of Data??
  • Dramatic Effect on Performance Storage
  • Smaller. Stats is more accurate
  • Reduced Management Costs (on Changes)
  • Different indexes on frequently \ infrequently
    changed columns.
  • Storage-wise is the same.

53
Filtered Indexes
  • Candidate Columns
  • Heterogeneous Categories Of Values
  • Columns With Distinct Ranges Of Values
  • Partitioned Tables
  • sparse columns
  • Can keep track of only non-null value distribution

54
Filtered Indexes
Create Index Ind1 on t(c1) where C1A or C1
D Create index Ind2 on t(sc7) where sc7 is
not null
55
Filtered Indexes
Demonstration
  • Filtered Indexes Performance
  • Filtered Indexes - Storage

55
56
Agenda
  • Spatial Data Types
  • FILESTREAM Data Storage
  • HierarchyID Data Type
  • Sparse Design Columns Stats
  • Integrated Full Text Search
  • Administrative Features

57
Full-Text Search Overview pre-SQL Server 2008 (1)
  • Full-text search allows fast querying over
    textual data
  • One or more full-text catalogs created for a
    database
  • Stored in the file system, path to root specified
    on create
  • Part of a filegroup (either specified or defaults
    to PRIMARY)
  • One full-text index per table, stored in any of
    the full-text catalogs over specified
    character-based columns, and documents stored in
    binary/varbinary columns
  • Index is queried using full-text predicates
  • Uses an external process to do the parsing,
    implementing the MSSearch

58
FTS Overview pre-SQL Server 2008 (2)
  • BACKUP and RESTORE work as expected for FULL
    backups only
  • Full-text service paused while backup is being
    taken
  • Differential backups backs up all full-text files
    that have been changed or added since the last
    full backup, in their entirety!!
  • Full-text index changes are NOT logged
  • Recovery to a point in time requires a full-text
    repopulate
  • Failover to a database mirror requires a
    full-text repopulate
  • Failover to a log shipping secondary requires a
    full-text repopulate
  • In SQL Server 2000, full-text catalogs could not
    be attached/detached with the rest of the
    database they had to be moved manually

59
Integrated Full Text
  • Integrated In The Engine
  • Stored As Internal Table
  • Index Changes Are Now Fully Logged
  • Log Backup, Log Shipping, Db Mirroring
  • New Dmvs To Access Full-text Data
  • Sys.Dm_fts_index_keywords
  • Sys.Dm_fts_index_keywords_by_document
  • DBCC CHECKDB Validates Full-text Index Structures

60
Integrated Full Text
  • Noise Words Now Stop Lists
  • Altered On The Fly
  • Configurable For Column, Or Query Time
  • Query Semantics Transparent
  • Dm_fts_parser Returns Query Terms
  • Query Improvements
  • All Relational Operators Can Be Used
  • Filters Can Be Applied Anywhere
  • No Surrogate Keys In Use (If PK Is Integer)

61
Integrated Full Text
Demonstration
  • Integrated Full Text

61
62
Agenda
  • Spatial Data Types
  • FILESTREAM Data Storage
  • HierarchyID Data Type
  • Sparse Design Columns Stats
  • Integrated Full Text Search
  • Administrative Features

63
Resource Governor
  • Quotas on SQL Server
  • Scenarios
  • Run-away queries
  • Unpredictable workload execution
  • Setting workload priority
  • Usages
  • Consolidation Servers
  • Moving Test To Prod

64
Resource Governor Limitations
  • Database Engine only
  • Each instance controlled individually
  • I/O controls are planned for V2
  • Certain workloads may not be entirely suited
  • short-lived OLTP queries

65
Resource Governor Concepts
  • User connects
  • Connection is
  • Classified
  • Assigned to a workload group
  • Workload group is already bound to a pool with
    limits
  • Queries execute within the limits of the pool

66
Resource Governor Resource Pools
  • MAX settings are only enforced when contention
    occurs
  • E.g. If a pool has a max CPU of 10 and another
    at 90, why can a query from pool1 exceed 10
    CPU?
  • Two possibilities multiple CPUs or the second
    pool is not using its max CPU
  • Image and scenario taken from the PSS blog

67
Resource Governor Dynamic Control
  • ALTER RESOURCE GOVERNOR DISABLE RECONFIGURE
  • RECONFIGURE starts the governor if its currently
    disabled

68
Resource Governor Tools Support
  • Fully integrated into SSMS under Management

69
Resource Governor
Demonstration
  • Resource Governor In Action

69
70
Resources
  • SQL Server 2008 Home Page
  • http//www.microsoft.com/sqlserver/2008/en/us/defa
    ult.aspx
  • SQL Server 2008 Demos and Videos
    http//www.microsoft.com/sqlserver/2008/en/us/demo
    s.aspx
  • Microsoft Jump Start http//sqlserver2008jumpstart
    .microsofttraining.com/content/secure/AttendeeLogi
    n.asp?CcpSubsiteID69
  • Microsoft Developer Network (MSDN) TechNet
    http//microsoft.com/msdn http//microsoft.com/
    technet
  • Trial Software and Virtual Labs http//www.microso
    ft.com/technet/downloads/trials/default.mspx

71
Thank you for listening
yonio_at_srl.co.il
About PowerShow.com