Transcript ISV Proposition

ISV Proposition
Keith Burns Data Architect
[email protected]
Microsoft UK
Transparent Data Encryption
SQL Server Change Tracking
Data Profiling
External Key Management
Synchronized Programming Model
Star Join
Data Auditing
Visual Studio Support
Pluggable CPU
SQL Server Conflict Detection
Enterprise Reporting
Engine
Transparent Failover for
Database Mirroring
FILESTREAM data type
Internet Report
Deployment
Integrated Full Text Search
Block Computations
Sparse Columns
Scale out Analysis
Large User Defined Types
BI Platform Management
Date/Time Data Type
Export to Word and Excel
LOCATION data type
Author reports in Word
and Excel
Policy Management
Server Group Management
Streamlined Installation
Enterprise System Management
Performance Data Collection
System Analysis
Data Compression
Query Optimization Modes
Resource Governor
Entity Data Model
LINQ
Visual Entity Designer
Entity Aware Adapters
SPATIAL data type
Virtual Earth Integration
Partitioned Table Parallelism
Query Optimizations
Persistent Lookups
Report Builder
Enhancements
TABLIX
Rich Formatted Data
Change Data Capture
Personalized
Perspectives
Backup Compression
… and many more
MERGE SQL Statement
Top ISV Features in SQL Server 2008
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Lock Escalation
Plan Freezing
Optimise for Ad-hoc Workloads Option
Resource Governor
Logging enhancements
Sparse Data and Indexes
Hierarchy Datatype
Spatial Data
Filestream
Security (Auditing & Transparent Database
Encryption (TDE)
Lock Escalation
Pain Points
• Lock escalation causes performance problems
(blocking); cannot be controlled on per table basis
• Traceflags -T1211 & -T1224 used to disable lock
escalation can help, but often result in other
problems
SQL Server 2008
• disable lock escalation at a table level
ALTER TABLE TabA SET (LOCK_ESCALATION = DISABLE);
• For partitioned tables, results in lock being
escalated to the partition, instead of to the table
level as in SQL Server 2005
Plan Freezing
Pain Points
Difficult to tune query plan when T-SQL code not accessible
No easy way of ensuring plan consistency across upgrades
SQL Server 2005 introduced Plan Guides and USE PLAN
hint; but these were difficult to use
SQL Server 2008
• Plan Freezing builds on plan guides framework and
introduces easier creation process based on plan cache
entries (sp_create_plan_guide_from_cache)
• Full DML support
INSERT, DELETE, UPDATE supported
• New function sys.fn_validate_plan_guide introduced to
validate existing SQL 2005 plan guides
Optimize for Ad hoc Workloads Option
Pain Points
• Single use ad hoc batches can flood the procedure cache
• Using Forced Parameterization option for such scenarios can
result in adverse side-effects
SQL Server 2008
• New ‘optimize for ad hoc workloads’ server option
• Set using sp_configure, or SQL Server Management Studio
• Stores stub for adhoc compiled plan after first execution,
replaces with actual query plan if the query is re-executed
• Improves efficiency of plan cache
Resource Governor
Connections
SQL Server 2008
RG is designed for workload
balance in database engine
Configurable at connection
level; Online change of
group/pool allowed
Classifier Function
SQL Server
Backup
Admin Tasks
Definition of group/pool
transparent to application
Users can control server
level setting (MAXDOP) at
workgroup level
Prevent run-away queries
Executive
Reports
Ad-hoc
Reports
OLTP
Activity
High
Admin Workload
OLTP Workload
Min Memory 10%
Max Memory 20%
Max CPU 20%
Admin Pool
Report Workload
Max CPU 90%
Application Pool
Logging Enhancements
SQL Server 2008
• INSERT into table supports minimal logging
• 3X-5X Performance Boost over fully logged INSERT
Heap Insert
SQL Server 2008
SQL Server
Index Insert
Run Time
Sparse Column Storage
The problem
•
•
•
Need to store sparse data
Possibly 100’s of columns
Typically only few % are populated
Typical Solution
Desired schema
PK
Q1
Q2
1
C
1
2
B
3
C
4
Q3
Q4
4
Q5
E
6
C
Q7
6
9
A
Q10
ID
Column
Value
9
1
Q1
C
1
Q2
1
1
Q10
9
2
Q1
B
2
Q3
4
2
Q5
Low
3
Q1
C
3
Q7
6
3
Q8
5
5
Hig
h
Blu
e
8
9
5
A
Q9
5
7
8
Q8
Low
1
5
Q6
7
2
Red
3
6
Sparse Columns
“Sparse” as a storage attribute on a column
• 0 bytes for a NULL, 4 byte overhead for non-NULL
• No change in Query/DML behavior
• Same limitations as normal tables eg 1024 columns
Wide Table / defining a “Sparse Column Set”
• Column set columns can still be individually specified in SQL statements
• Select * returns all non-sparse-columns + sparse column set as XML
• 30,000 sparse columns allowed in a table (2Gb), 1000 indexes
// Sparse as a storage attibute in Create/Alter table statements
Create Table Products(Id int, Type nvarchar(16)…,
Resolution int SPARSE, ZoomLength int SPARSE);
// Create a sparse column set
Create Table Products(Id int, Type nvarchar(16)…,
Resolution int SPARSE, ZoomLength int SPARSE,
Properties XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);
Filtered Indexes
Filtered Indexes and Statistics
•
•
•
•
Indexing a portion of the data in a table
Filtered/co-related statistics creation and usage
Query/DML Optimization to use Filtered indexes and Statistics
Restricted to non-clustered indexes
Benefits
• Lower storage and maintenance costs for large number of indexes
• Query/DML Performance Benefits: IO only for qualifying rows
// Create a Filtered Indexes
Create Index ZoomIdx on Products(ZoomLength) where Type = ‘Camera’;
// Optimizer will pick the filtered index when query predicates match
Select ProductId, Type, Resolution, ZoomLength where Type = ‘Camera’’
HierarchyID
• Key properties
•
•
•
•
•
Logically encodes the path from the
root of the tree to a node
/
Rich built-in methods for manipulating
hierarchies
/1/
/2/
/3/
Simplifies storage and querying of
hierarchical data
/1/1/
/1/2/
/3/1/
/3/2/
Comparison a<b is in depth-first order
/1/1/1/
Support for arbitrary insertions and
deletions
/1/1/2/
• Potential Applications
•
•
•
•
•
Forum and mailing list threads
Business organization charts
Content management categories
Product categories
Files/folders management
• Storage and retrieval of spatial data using standard SQL
syntax
– New Spatial Data Types (geometry, geography)
– New Spatial Methods (intersects, buffer, etc.)
– New Spatial Indexes
• Offers full set of Open Geospatial Consortium components
(OGC/SQL MM, ISO 19125)
• Integration with Virtual Earth
Storage Attribute on VARBINARY(MAX)
Store BLOBs in
DB + File System
•
Works with integrated FTS
Unstructured data stored directly in the file
system (requires NTFS)
Application
Dual Programming Model
BLOB
•
•
TSQL (Same as SQL BLOB)
Win32 Streaming APIs with T-SQL
transactional semantics
Data Consistency
DB
Integrated Manageability
•
•
Back Up / Restore
Administration
Size limit is the file system volume size
SQL Server Security Stack
TRUSTED,
SCALABLE PLATFORM
IT & DEVELOPER
EFFICIENCY
MANAGED
SELF-SERVICE BI
PowerPivot /
SharePoint 2010
Data Warehouse Edition
Report Builder 3.0
Master Data Services
Stream Insight
Data Tier Applications
Master Data Management
CRM
Purchasing DB
ERP
HR Doc
Asset Mgmt
Stream Insight
Scenarios for Event Processing
Latency
Months
CEP Target Scenarios
Days
Relational Database Applications
hours
Operational Analytics
Applications, e.g., Logistics, etc.
Data Warehousing
Applications
Web Analytics Applications
Minutes
Seconds
100 ms
Monitoring
Applications
Manufacturing Applications
< 1ms
0
10
100
1000
10000
Aggregate Data Rate (Events/sec.)
Financial trading
Applications
100000
~1million
Stream Insight
Complex Event Processing (CEP) is the continuous and incremental
processing of event streams from multiple sources based on
declarativequery and pattern specifications with near-zero latency.
Database Applications
Event-driven Applications
Query
Paradigm
Ad-hoc queries or
requests
Continuous standing queries
Latency
Seconds, hours, days
Milliseconds or less
Data Rate
Hundreds of events/sec
Tens of thousands of
events/sec or more
request
response
Event
input
stream
output
stream
18
StreamInsight
Increasing Availability
Additional SQL Server 2008 R2 report features
o Mapping support
o Report Builder 3.0
o Report Parts
o Export to data feed
Summary
 SQL Server 2008 has many features designed
specifically for ISV workloads; zero or minimal
application change required
 There are literally 100s of new and improved features
– this session only highlights some of the ones most
popular with ISVs
 Understanding what problem each feature resolves
as well as it’s performance, insights and limitations
helps more appropriate usage