Transcript What’s New in SQL Server 2005?

XML and XML Query Plans
Martin Bell
SQL Server - MVP
XML Enhancements







New XML Datatype
XQUERY Support
XML Data Manipulation Language
FOR XML Enhancements
XML Schema (XSD) Support
MSXML 6.0 (Native)
.Net XML Framework
New XML Datatype
 Store XML data, reducing the need to
shred it
 Stored as binary (underlying
varbinary(MAX) 2GB maximum)
 Compressed and tokenized – improved
storage (large documents) and speed
 Index XML columns
 Untyped (no schema) and typed XML
(data must conform to given schema)
 Use a datatype for variables
OPENROWSET Changes
 Can now be used for BULK loading
with BULK keyword
 Can use parameters similar to BULK
INSERT command
– FIRSTROW, LASTROW, FIELDTERMINATOR,
ROWTERMINATOR, CODEPAGE, DATAFILETYPE,
FORMATFILE, ERRORFILE, MAXERRORS,
ROWS_PER_BATCH
 Hints
– KEEPIDENTITY, KEEPDEFAULTS,
IGNORE_CONSTRAINTS, IGNORE_TRIGGERS,
TABLOCK
 Type of Data
– SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB
Using OPENROWSET to
populate a XML column
CREATE TABLE XMLAddress (
XMLAddress XML NOT NULL )
GO
INSERT INTO dbo.XMLAddress (XMLAddress )
SELECT *
FROM OPENROWSET ( BULK 'C:\demos\XML and
XML Query Plans\XML Examples\Address.xml',
SINGLE_BLOB ) AS b
GO
XQUERY Support
Method
Description
query() Method
Use this method to query over an
XML instance.
value() Method
Use this method to retrieve a value of
SQL type from an XML instance.
exist() Method
Use this method to determine
whether a query returns a nonempty
result.
modify() Method
Use this method to specify XML DML
statements to perform updates.
nodes() Method
Use this method to shred XML into
multiple rows to propagate parts
XML Data Manipulation
Language
 Update, Insert and Deletion of XML data using XQUERY
though modify method
UPDATE dbo.XMLAddress
SET XMLAddress.modify(
'replace value of (/Address/AddressID[text()="1"]/text())[1]
with "2"')
GO
XML Data Manipulation
Language
 Deletion of XML data using
XQUERY though modify method
UPDATE dbo.XMLAddress
SET XMLAddress.modify(
'delete /Address/AddressLines')
GO
XML Data Manipulation
Language
insert
Expression1
( {as first | as last} into | after |
before
Expression2
)
XML Data Manipulation
Language
 Inserting of XML data using XQUERY
though modify method
UPDATE dbo.XMLAddress
SET XMLAddress.modify(
'insert <AddressLines/>
before (/Address/City)[1]'
)
XML Schema (XSD) Support
 CREATE XML SCHEMA
COLLECTION
 ALTER XML SCHEMA COLLECTION
 DROP XML SCHEMA COLLECTION
 xml_schema_namespace function –
reconstructs a schema
xml_schema_namespace(
Relational_schema ,
XML_schema_collection_name , [ Namespace ] )
Schema Limitations
 Not supported:
<xsd:include>, <xsd:key>, <xsd:keyref>,
<xsd:unique>, <xsd:redefine>, xs:ID, xs:IDREF,
xs:IDS, union types, NOTATION type, lax
validation…
 Limitation of long (2^31) for value of length,
minLength, and maxLength
 Types derived from xs:date, xs:time, and
xs:dateTime are required to have time zones
XML Schema DMVs








sys.xml_schema_collections
sys.xml_schema_namespaces
sys.xml_schema_components
sys.xml_schema_elements
sys.xml_schema_attributes
sys.xml_schema_types
sys.xml_indexes
plus others
Using a schema to validate a
XML Column
CREATE TABLE [dbo].[Addresses](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[AddressXML] XML (DOCUMENT AddressXMLSchema),
CONSTRAINT [PK_Addresses_AddressID] PRIMARY KEY
CLUSTERED
(
[AddressID]
)
)
Using a schema to
validate a XML Column
Schema definition for Postalcode has the restriction
pattern "[0-9]{5}“
<xs:element name="PostalCode">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:pattern value="[0-9]{5}"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
Using a schema to
validate a XML Column
-- Insert Data this works OK!
INSERT INTO [dbo].[Addresses] ( AddressID, AddressLine1,
AddressLine2, City, PostalCode, AddressXML )
SELECT 0,'1970 Napa Ct.','Parkside','Bothell','98011',
'<?xml version="1.0" encoding="UTF-8"?>
<Address xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance">
<AddressID>0</AddressID>
<AddressLine1>1970 Napa Ct.</AddressLine1>
<AddressLine2>Parkside</AddressLine2>
<City>Bothell</City>
<PostalCode>98011</PostalCode>
</Address>'
Using a schema to
validate a XML Column
-- Insert Data, this will give an error on PostalCode validation
INSERT INTO [dbo].[Addresses] ( AddressID, AddressLine1,
AddressLine2, City, PostalCode, AddressXML )
SELECT 1,'Threadneedle Street',NULL,'London','EC2R 8AH',
'<?xml version="1.0" encoding="UTF-8"?>
<Address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<AddressID>1</AddressID>
<AddressLine1>Threadneedle Street</AddressLine1>
<City>London</City>
<PostalCode>EC2R 8AH</PostalCode>
</Address>‘
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: 'EC2R 8AH'. Location:
/*:Address[1]/*:PostalCode[1]
Using a schema to
validate a XML Column
<xs:element name="ZipCode">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:pattern value="[0-9]{5}"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="Postcode">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:pattern value="[A-Za-z]{1,2}[\d]{1,2}([A-Za-z])?\s?[\d][A-Za-z]{2}"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
Using a schema to
validate a XML Column
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Address">
<xs:complexType>
<xs:sequence>
<xs:element ref="AddressID"/>
<xs:element ref="AddressLine1"/>
<xs:element ref="AddressLine2" minOccurs="0"/>
<xs:element ref="City"/>
<xs:choice>
<xs:element ref="ZipCode"/>
<xs:element ref="Postcode"/>
</xs:choice>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Using a schema to validate
an XML Variable
DECLARE @AddressXML XML (CONTENT AddressXMLSchema)
SET @AddressXML = '<?xml version="1.0" encoding="UTF-8"?>
<Address xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<AddressID>0</AddressID>
<AddressLine1>1970 Napa Ct.</AddressLine1>
<AddressLine2>Parkside</AddressLine2>
<City>Bothell</City>
<State>WA</State>
<ZipCode>98011</ZipCode>
</Address>'
Using a schema to
validate a XML Variable
-- Wrong element
DECLARE @AddressXML XML (CONTENT AddressXMLSchema)
SET @AddressXML = '<?xml version="1.0" encoding="UTF-8"?>
<Address xmlns="UK Address" xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance">
<AddressID>2</AddressID>
<AddressLine1>Threadneedle Street</AddressLine1>
<City>London</City>
<ZipCode>98011</ZipCode>
</Address>'
Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: 'EC2R 8AH'. Location:
/*:Address[1]/*:PostalCode[1]
Multiple Schema Validation
DECLARE @XmlSchema varbinary(max)
SELECT @XmlSchema = BulkColumn
FROM OPENROWSET (
BULK 'C:\demos\XML and XML Query Plans\XML Examples\XML SPY Schema
Validate US Address.xsd',
SINGLE_BLOB ) AS b
SELECT @XmlSchema = @XmlSchema + BulkColumn
FROM OPENROWSET (
BULK 'C:\demos\XML and XML Query Plans\XML Examples\XML SPY Schema
Validate UK Address.xsd',
SINGLE_BLOB ) AS b
CREATE XML SCHEMA COLLECTION AddressXMLSchema AS @XmlSchema
Multiple Schema Validation
SET @AddressXML = '<?xml version="1.0" encoding="UTF-8"?>
<Address xmlns="UK Address">
<AddressID>1</AddressID>
<AddressLine1>Threadneedle Street</AddressLine1>
<City>London</City>
<Postcode>EC2R 8AH</Postcode>
</Address>
<Address xmlns="US Address">
<AddressID>0</AddressID>
<AddressLine1>1970 Napa Ct.</AddressLine1>
<AddressLine2>Parkside</AddressLine2>
<City>Bothell</City>
<State>WA</State>
<ZipCode>98011</ZipCode>
</Address>'
XML Indexes
 XML Primary indexes
– Based on ORDPATH (reverse materialised path! Data is
shredded so be careful of space required)
– Requires a separate Clustered index on the table
– Creates a Node table in the sys schema
 XML Secondary Indexes
– PATH
 Useful if most of usage is .query() or .exist() using path
based searches
– PROPERTY
 Useful if most of usage is .value() to get property values
– VALUE
 Useful if wildcard searching
XML Primary indexes
CREATE TABLE [dbo].[Addresses](
[AddressID] [int] NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[AddressXML] XML NOT NULL,
CONSTRAINT [PK_Addresses_AddressID] PRIMARY KEY
CLUSTERED
(
[AddressID]
)
)
CREATE PRIMARY XML INDEX idx_Addresses ON [Addresses] (
AddressXML )

Creates a Node table in the sys schema
XML Secondary indexes
CREATE XML INDEX idx_Addresses_PATH ON [Addresses] (
AddressXML )
USING XML INDEX idx_Addresses FOR PATH
GO
CREATE XML INDEX idx_Addresses_PROPERTY ON
[Addresses] ( AddressXML )
USING XML INDEX idx_Addresses FOR PROPERTY
GO
CREATE XML INDEX idx_Addresses_VALUE ON [Addresses] (
AddressXML )
USING XML INDEX idx_Addresses FOR VALUE
GO
Creating XML Query Plans
 Permissions needed to see plans
– SHOWPLAN permission is a database-level
permission which
– dbo, sysadmin, dbcreator roles have this
permission and can grant, deny and revoke it
– Supports ownership chaining
– Security Implications (can see definitions or
imply data distributions)
Creating XML Query Plans
 How to generate XML Query Plans
– Set Commands
 SHOWPLAN_XML (Display Estimated Execution Plan)
 STATISTICS_XML (Include Actual Execution Plan)
– SQL Profiler event classes
 Showplan XML
 Showplan XML for Query Compile,
 Showplan XML Statistics Profile
– From Query Cache
 sys.dm_exec_query_plan (only method which will return
XML datatype!)
Set Commands
 Set Commands
– SHOWPLAN_XML (Display Estimated
Execution Plan)
– STATISTICS_XML (Include Actual Execution
Plan)
Set Commands
Command
Execute
Query?
Display
Plan
Text?
Display
Estimated
Row Counts
& Stats
Display
Actual
Row Counts
set showplan_xml on No
Yes
Yes
No
set statistics xml on
Yes
Yes
Yes
Yes
SQL Profiler
 SQL Profiler event classes
– Showplan XML
– Showplan XML for Query Compile
– Showplan XML Statistics Profile
SQL Profiler
SQL Profiler
Query Cache
 Dynamic Management Functions
– sys.dm_exec_query_plan
– sys.dm_exec_cached_plans
 Permissions
– VIEW SERVER STATE
– sysadmin (sys.dm_exec_query_plan)
Query Cache
Note:
– sys.dm_exec_query_plan does not always return a
query plan
 If the plan has been evicted from the cache
 If they query contains a statement that is not cached
 If they query contains a call to a user-defined function or a
call to dynamic SQL
 If the number of nested elements exceed 128 levels (limit in
XML data type)
 If a ad-hoc query used simple or forced parameterised only the
query text is returned
Query Cache
Example:
Return all plans currently in the Query Cache
SELECT *
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
Query Cache
Example:
Return all plans that have statistics
SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
XML Query Plan Schema
 Schema
– C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\schemas\sqlserver\2004\0
7\showplan\showplanxml.xsd
Loading XML Query Plans




Openrowset
XMLBulkload
SSIS
CLR
SQLXML BulkLoad Component
 SQLXML BulkLoad Component
– SQLXMLBulkLoad4
 Written to make use of XML datatype and CLR UDTs
– Examples in Books Online
Using SSIS to load XML files
Using SSIS to load XML files
Using SSIS to load XML files
Using SSIS to load XML files
Using SSIS to load XML files
Loading XML Showplan from
Trace
CREATE TABLE [dbo].[XMLShowplans](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[XML] [xml] NOT NULL
)
INSERT INTO [dbo].[XMLShowplans] ( XML )
SELECT TextData
FROM fn_trace_gettable ( 'C:\demos\XML and XML Query Plans\XML
Examples\GlasgowShowplanTrace.trc' , default)
WHERE EventClass = 122
AND ApplicationName <> 'SQLAgent - Alert Engine'
GO
Using CLR to load XML Query
Plans
 Processing XML Showplans Using SQLCLR
in SQL Server 2005
http://msdn2.microsoft.com/enus/library/ms345130.aspx
Analysing XML Query Plans
-- Get Each Query Cost
SELECT id, XML.value
('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
(//p:RelOp)[1]/@EstimatedTotalSubtreeCost', 'float') AS QueryCost
FROM [dbo].[XMLShowplans]
Analysing XML Query Plans
-- Get Showplans with warnings
SELECT id, XML.query(
'declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
for $pc in //p:Warnings
return $pc
')
FROM [dbo].[XMLShowplans]
Analysing XML Query Plans
-- Exclude those which don't have warnings
SELECT id, XML.query(
'declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
for $pc in //p:Warnings
return $pc
')
FROM [dbo].[XMLShowplans]
WHERE XML.exist(
'declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//p:Warnings
') = 1
Analysing XML Query Plans
-- Output Column Names for missing index
SELECT s.id,
cols.value('@Database','varchar(128)') AS [Database],
cols.value('@Schema','varchar(128)') AS [Schema],
cols.value('@Table','varchar(128)') AS [Table],
cols.value('@Column','varchar(128)') AS [Column]
FROM [dbo].[XMLShowplans] S
CROSS APPLY XML.nodes(
'declare namespace
ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
for $pc in //ns:ColumnsWithNoStatistics/ns:ColumnReference
return $pc
') as sp(cols)
Resources
 Home page
http://www.microsoft.com/sql/default.mspx
 Technet
http://www.microsoft.com/technet/prodtechn
ol/sql/default.mspx
 Virtual Labs
http://msdn.microsoft.com/virtuallabs/sql/def
ault.aspx
 Webcasts
http://www.microsoft.com/events/series/msd
nsqlserver2005.mspx
Resources
 perfectxml.com
http://www.perfectxml.com/
 sqlxml.org http://sqlxml.org/
Blogs
 Simon Sabin
http://sqlblogcasts.com/blogs/simons
 Craig Freedman
http://blogs.msdn.com/craigfr
 Jeni Tennison
http://www.jenitennison.com/blog
 Denis Ruckebusch
http://blogs.msdn.com/denisru
 Mike Rorke http://blogs.msdn.com/mrorke
 Microsoft XML Team
http://blogs.msdn.com/xmlteam
 Michael Rys http://blogs.msdn.com/mrys
Books
 Professional SQL Server 2005 XML, Scott
Klein ISBN 0764597922
 Pro SQL Server 2005, Thomas Rizzo et al.
ISBN 1590594770
 Professional SQL Server 2005 Integration
Services, Brian Knight et al. ISBN 0764584359
 Inside SQL server 2005 T-SQL Programming
Itzik Ben-Gan ISBN 0735621977
 A Developers Guide to SQL Server 2005 Bob
Beauchemin ISBN 0321382188