Relational Engine Enhancements

1

• Relational Engine Enhancements
• James Hamilton
• Language Processing Exec Dev Manager
• SQL Server
• Microsoft Corp
• JamesRH_at_microsoft.com
• 5-206

2
(No Transcript)
3
Storage Market/Requirements
Business Operations Availability Scalability Perfo
rmance Programmability ISV driven Internet/Commerc
e
Data Warehousing Large Databases Complex Queries
Closed Loop Analysis Partitions OLAP Data
Transformation
Core Reliability Auto Tuning Security Base
Storage Replication Meta Data Devices
Knowledge Management Content Indexing/Search Conte
nt/Web Server Tracking Workflow
4
SQL Server 7.0
Business Operations Row-Level Locking MSCS
Failover Clusters Prepare/Execute Dynamic DDL FFO
Cursors
Data Warehousing OLAP Services Intra-query
Parallelism Advanced Join Techniques Star-Schema
Optimizations Data Transformation DW Framework
Core Reliability Dynamic Memory Auto Statistics
WinNT-based Security VLDB Utilities New On-Disk
Structure Merge Replication Unicode
Support Distributed Query
Knowledge Management Full-Text Indexing Access
Workflow Designer
5
SQL Server 7.0

• Best TCO in the industry
• Most Complete DW offering
• Handle 95 of all ERP customers needs(SAP, Baan,
  Psoft)
• gt50 share on Internet Shopping Sites
• Largest database on the Internet
• http//terraserver.microsoft.com
• Best SAP POS B-mark on ANY platform
• Best TPC-C price/perf in Industry
• Best TPC-C per/processor perf in industry
• Achieving 99.9x availability TODAY!!!(Pennzoil,
  Barnes Noble, E-Plus, Terraserver, more)
• Over 8.5M seats sold in FY 99

6
SQL Server 7.0 Relational Engine

• State of the art QP for OLTP, DSS
• Intra-Query Parallelism
• Advanced join technology
• Caching of query plans
• Unicode
• Distributed queries
• Full-Text
• Auto Management (memory, stats)

7
SQL Server 2000 Relational Engine (Customer
Feedback)

• Programmability
• XML support
• Manageability
• Multiple instances/machine
• Security
• Scalability, performance
• Availability

8
Programmability

• Cascaded DRI
• User Defined Functions
• Column Level Collations
• Instead Of Triggers
• New Datatypes
• FullText Enhancements
• Extended Properties
• Checksum
• Session context

9
Cascaded DRI

• Automatic cascading of Deletes and Updates from
  PK to FK tables
• ANSI Standard Restrict and Cascade semantics
• CREATE TABLE country (
• country_name NVARCHAR(75) NOT NULL PRIMARY KEY
  )
• CREATE TABLE employee (
• employee_name NVARCHAR(75) NOT NULL,
• nationality NVARCHAR(75) NOT NULL REFERENCES
  country
• ON UPDATE CASCADE
• ON DELETE NO ACTION,
• passport_number VARCHAR(25) NOT NULL,
• PRIMARY KEY (nationality, passport_number))
• CREATE TABLE renewal_reminder (
• nationality NVARCHAR(75) NOT NULL,
• passport_number VARCHAR(25) NOT NULL,
• date_of_expiry datetime NOT NULL,
• FOREIGN KEY (nationality, passport_number)
  REFERENCES employee

10
Shiloh User Defined Functions

• Multi-statement T-SQL routines
• Scalar-valued
• Select f(c1)
• Select where f2(c2)
• In expressions
• Table-valued (also called relational)
• Select c1 from f(arg)
• Strongly typed input arguments with return value
• No output parameters
• Inline relational functions
• Effectively a parameterized view

11
Scalar UDFs Example

• CREATE FUNCTION ExtractNamePart(_at_InName
  varchar(100), _at_part Varchar(10))
• RETURNS varchar(30)
• BEGIN
• DECLARE _at_offset tinyint ,_at_return varchar(30)
• SET _at_offset charindex(' ', _at_InName)
• select _at_return case _at_part
• WHEN 'First' then substring(_at_InName, 1,
  _at_offset-1)
• WHEN 'Last' then substring(_at_InName,
  _at_offset1,
  
  len(_at_InName))
• ELSE NULL
• END
• return _at_return
• END
• select dbo.ExtractNamePart('Fadi Fakhouri','Last')

12
Column Level Collations

• Multi-lingual applications, application hosting,
  server consolidation
• Per-database collations
• Multiple apps with different collations
• Per-column collations
• Deeper multi-lingual applications
• Attach, restore databases with different
  collations from server
• Full backwards compatibility

13
Column Level Collations Syntax

• -- Database-level collation
• CREATE DATABASE MyDB
• COLLATE Latin1_General_CS_AI
• -- Column-level collation
• CREATE TABLE Products (
• ProductId char(20), -- db collation
• ProductName char(20)
• COLLATE French_CS_AS,
• Description char(20)
• COLLATE French_CI_AI)

14
Column Level Collations Examples

• SELECT
• FROM PRODUCTS
• ORDER BY ProductName, Description
• SELECT
• FROM PRODUCTS
• ORDER BY ProductName COLLATE French_CI_AI
• SELECT
• DISTINCT ProductName COLLATE French_CI_AI
• FROM Products

15
Instead Of Triggers

• Application
• User-defined view updatability
• Before triggers
• Trigger executed INSTEAD of Insert, Delete,
  Update action
• Trigger can be created on view or table
• Inserted/Deleted tables still available
• Statement Level

16
Instead Of Trigger Example

• CREATE TRIGGER AuIns on AuthorsView INSTEAD OF
  INSERT
• AS
• BEGIN
• INSERT authors(au_id,au_fname,au_lname,)
• SELECT au_id,
• dbo.ExtractNamePart(au_name, 'First'),
• dbo.ExtractNamePart(au_name, 'last'),
  phone, address, city, state, zip,
• contract
• FROM inserted
• END
• INSERT AuthorsView VALUES(John Doe, )

17
New Datatypes

• Usage Columns, Parameters, Variables, Return
  type
• Table Type
• Table valued UDFs
• BigInt
• 8 byte integer
• SQL_Variant
• Can store any base type (except LOB)
• Useful for open schema, annotations

18
Full Text Enhancements

• Change Tracking
• Maintain list of changes to indexed data
• Image Filtering
• Support for MS Office documents, HTML
• Custom filters (3rd party)
• Failover Clustering
• Column Level Linguistic analysis
• Columns can have associated language
• Top-N-By-Rank
• Containstable, Freetexttable
• Scalability Improvements

19
Extended Properties

• Associate user-defined metadata with SQL objects
• Richer UI, client-side validation
• Objects ? (property name, value)
• Sp_setextendedproperty,
• Sp_drop extendedproperty,
  Fn_listextendedproperty
• Example
• exec sp_addextendedproperty 'caption', 'Employee
  Id', 'user', dbo, 'view', foo_view, 'column', id
• select from fn_listextendedproperty
  ('caption', 'user', 'dbo', 'view', 'foo_view',
  'column', NULL)
• Properties retained on a rename

20
Other Developer Features

• CheckSum
• Intrinsic for computing a checksum over a set of
  columns
• Session context
• Middle-tier apps can set/get user supplied
  context
• Tools
• Profiler (larger workloads, performance analysis,
  OLAP events)
• Query Analyzer
• Index Tuning Wizard (Indexed Views)

21
SQL Server 2000 Relational Engine (Customer
Feedback)

• Programmability
• XML support
• Manageability
• Multiple instances/machine
• Security
• Scalability, performance
• Availability

22
Relational To XML

• SELECT FOR XML
• FOR XML RAW (return an XML rowset)
• FOR XML AUTO (exploit RI, name matching, etc.)
• FOR XML EXPLICIT (maximal control)
• Annotated Schema
• Maps between XML and relational schema expressed
  in XML
• Templates
• Encapsulated parameterized query
• XSL/T support
• XPATH support
• Direct from DOM
• MSXML component hosted in SQL Server kernel
• Direct URL access (SQL owned virtual root)
• SELECT FOR XML
• Annotated schema
• Templates

23
XML To Relational

• Bulk load using XML
• Updategram (web delivery post Shiloh)
• Templates and Annotated Schema
• DOM
• Insert document into SQL Server DOM
• Select from DOM rowset and insert directly into
  SQL tables

24
XML Example

• http//SRV1/nwind?sqlSELECT'ltROOTgt'SELECTDISTI
  NCTContactTitleFROMCustomersWHEREContactTitle
  LIKE'Sa25'ORDERbYContactTitleFORXMLAUTOS
  ELECT'lt/ROOTgt'
• Result set
• ltROOTgt
• ltCustomers ContactTitle"Sales Agent" /gt
• ltCustomers ContactTitle"Sales Associate" /gt
• ltCustomers ContactTitle"Sales Manager" /gt
• ltCustomers ContactTitle"Sales Representative"
  /gt
• lt/ROOTgt

25
SQL Server 2000 Relational Engine (Customer
Feedback)

• Programmability
• XML support
• Manageability
• Multiple instances/machine
• Security
• Scalability, performance
• Availability

26
Multi-Instance

• Support multiple SQL Servers instances on single
  machine
• Applications
• Application hosting
• Server consolidation
• SQL Runtime (MSDE)
• Used internally in implementation of shared disk
  Failover

27
Multi-Instance

• One default Instance (typically server name)
• Multiple named instances (server_name\instance)
• Named instances are all version 8.0 or above
• 7.0 or 8.0 supported as default database

28
SQL Server 2000 Relational Engine (Customer
Feedback)

• Programmability
• XML support
• Manageability
• Multiple instances/machine
• Security
• Scalability, performance
• Availability

29
Security

• Initial Setup Secure
• Auditing
• Encryption
• Logins (SSL)
• Internal Server (Crypto APIs)
• Windows 2000 Kerberos support, delegation
  (linked servers)
• Enhancements to Server roles
• C2 Security Evaluation

30
SQL Server 2000 Relational Engine (Customer
Feedback)

• Programmability
• XML support
• Manageability
• Multiple Instances/Machine
• Security
• Scalability, Performance
• Availability

31
Scalability Desktop

• Multiple Instances of server per system
• Database level collations
• Microsoft installer based setup
• Reduced start-up time, working set
• Simplified licensing
• Access integration

32
Partitioned Views

• A partitioned view is a UNION ALL view over two
  or more tables which have partitioning columns
  with disjoint intervals
• Example
• CREATE TABLE CUSTOMER_1K (, C_W_ID INT NOT NULL
  CHECK (C_W_ID BETWEEN 1 AND 1000), PRIMARY
  KEY (C_W_ID, )
• CREATE TABLE CUSTOMER_10K (, C_W_ID INT NOT
  NULL CHECK (C_W_ID BETWEEN 9001 AND 10000),
  PRIMARY KEY (C_W_ID, )
• CREATE VIEW CUSTOMER AS
• SELECT , C_W_ID FROM CUSTOMER_1K UNION ALL
• SELECT , C_W_ID FROM CUSTOMER_1OK

33
Distributed Partitioned Views
Each node in the cluster has a single common
logical view of the tables across all other
nodes
34
Distributed Partitioned Views

1. Each table in the application is split across the
   cluster
2. Each table is partitioned into disjoint intervals
   using a CHECK constraint
3. All nodes are linked using OLEDB linked
   servers sp_addlinkedserver node1 sp_setnetname
   node1, MACHINE NAME
4. Views are created for each table on each node
   to link together the partitions stored across all
   nodes

35
Performance

• Index Enhancements
• Indexed Views
• Other Index Improvements
• Parallelism
• Query Optimizer enhancements
• Distributed Queries
• Large memory support
• Lots of other enhancements

36
Indexed Views

• Decision support workloads
• Content of view is persisted, maintained
• View may contain joins, aggregations (some
  restrictions)
• Allows storage of partial results
• Automatically used by optimizer
• Example
• Create View Store_Sales as Select StoreId,
  SUM(qty) From Sales Group By StoreId
• Create Clustered Index I1 on Store_Sales
• Select StoreId, Qty from Sales where StoreId
  100
• Index Tuning Wizard Support

37
Index Enhancements

• Indexes on Computed Columns (PK, Unique
  constraints)
• Indexes with Asc, Desc columns
• Example Select a, b, c
• From T
• Where a gt 10
• Order By a asc, b desc
• RE creates, maintains indexes
• Indexes supported on bit columns

38
Parallelism

• Enhancements to query parallelism
• More efficient plans
• Bitmap filtering
• Multi-threaded access to spools
• Reduced parallel sort stalls
• Elapsed time costing
• Integrated deadlock detection
• Insert/Update/Delete
• Index creation
• DBCC

39
Query Optimizer

• Cardinality estimation
• Exploit new statistics
• Improve many computations
• Handle extreme skew
• More granular statistics
• Dynamic bucket boundaries
• Star Joins
• Snowflake Schema
• More trivial plan cases

40
Distributed Queries

• New providers Active Directory, Exchange 2000
• OLEDB Interfaces (Statistics, Constraints)
• Cost based optimization
• Obtains statistics from remote source
• Smarter remoting logic
• More SQL dialects
• Minimal SQL
• More granular capabilities
• Heterogeneous partitioning views

41
Large Memory Support

• Scale-up memory limited applications
• Up to 64GB on Intel x86 CPUs (PAE)
• Uses Windows 2000 AWE Support
• Single Buffer Cache
• Physical/Virtual Re-Mapping of Buffers
• sp_configure option

42
SQL Server 2000 Relational Engine (Customer
Feedback)

• Programmability
• XML support
• Manageability
• Multiple Instances/Machine
• Security
• Scalability, Performance
• Availability

43
Availability

• Built on top of
• Microsoft Cluster Server
• Multi-Instance (no virtual layer)
• Fixed known issues with 7.0
• Installation (Setup)
• Perfmon counters
• Tools
• Licensing information
• Bugs

44
Availability

• Enhancements
• Multiple IP addresses
• Rolling upgrades
• Full-Text
• Disaster Recovery
• Windows 2000 Data Center support
• All components are cluster aware
• EASY to add/remove nodes

45
Availability
46
SQL Server 2000
Business Operations 4-Node Failover Log
Shipping Het. Partitioning Views User-Defined
Functions XML
Data Warehousing Decision Trees Large
Dimensions Web Hit Analysis English Query for
OLAP Indexed Views Server-less Backup
Core Reliability Delegation Encryption Schema
Replication Multi-Instance SS2K CE
Knowledge Management Content Filters Exchange
2000 Integration
47
Questions/Feedback
?
48
(No Transcript)