Read Microsoft PowerPoint - partitioning-sql-server tables viws indexed views.ppt text version

Partitioning Tables, Views, and Indexed Views in SQL Server

Andrew Novick www.NovickSoftware.com

Agenda

§ Partitioned Views § Partitioned Tables § Hash Partitioning for OLTP § Partition Aligned Indexed Views

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Introduction

§ Andrew Novick ­ Novick Software, Inc. § Business Application Consulting

­ SQL Server ­ .Net

§ www.NovickSoftware.com § Books:

­ Transact-SQL User-Defined Functions ­ SQL 2000 XML Distilled

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Architecture

What do we have to work with?

SQL Server Storage Architecture

SQL Server Storage Table1 Table2

FileGroupA FileA1

FileGroupB FileB1 FileB2

Logical Disk System ­ Windows Drives Drive C: Drive D: Physical IO - subsystem Disk Disk Disk Disk Disk Disk Drive E:

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

At 3 PM on the 1st of the month: Where do you want your data to be?

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Spread to as many disks as possible

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

I/O Performance

§ Little has changed in 50 years (YET! But it will soon)

I/O throughput is a function of the number of disk drives.

§ Watch out for bottlenecks in the I/O Path § Memory reduces the need for I/O

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioning

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioning Goals

§ Adequate Import Speed § Maximize Query Performance

­ Make use of all available resources

§ Data Management

­ Migrate data to cheaper resources ­ Delete old data easily ­ Minimize maintenance tasks

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Sliding Window

Always There Data

Temporal Data 2008-01

Temporal Data 2008-02

Temporal Data 2008-03

Temporal Data 2008-04

Temporal Data 2008-05

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioned Views

Created like any view

CREATE VIEW Fact AS SELECT * FROM Fact_20080405 UNION ALL SELECT * FROM Fact_20080406

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioned Views: Check Constraints

§ Check constraints tell SQL Server which data is in which

table

ALTER TABLE Fact_20080405 ADD CONSTRAINT CK_FACT_20080405_Date CHECK (FactDate >= `2008-04-05' and FactDate < `2008-04-06')

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioned View - 2

§ Looks to a query like any table or view

SELECT FactDate, ..... FROM Fact WHERE CustID=334343 AND FactDate = `2008-04-05'

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioned View

SQL Server Storage Table1 Table2 Fact_20080330 View Fact Fact_20080401 Fact_20080401 FGF3 F3 FGF4 F4

Fact_20080331 FileGroupA FileA1 FileGroupB FileB1 FileB2 FGF1 F1 FGF2 F2

Logical Disk System ­ Windows Drives Drive C: Drive D: Physical IO - subsystem Disk Disk Disk Disk Disk Disk Drive E:

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partition Elimination

§ The query compiler can eliminate partitions from

consideration in the plan

§ Partition elimination happens at query compile time. § It is not necessary to make parameters strings,

but it looks like it does.

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Demo 1 ­ Partitioned Views

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioned Tables

§ SQL Server Enterprise/2005 § Require a non-null partitioning column § Check constraints tell SQL Server what data is in each

parturition

§ All tables are partitioned!

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioned Function

§ Defines how to split data

CREATE PARTITION FUNCTION Fact_PF(smalldatetime) RANGE RIGHT FOR VALUES (`2001-07-01', `2001-07-02')

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partition Scheme

§ Defines where to store each range of data

CREATE PARTITION SCHEME Fact_PS AS PARTITION Fact_pf TO (PRIMARY, FG_20010701, FG_20010702)

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Creating a Partitioned Table

§ The table is created ON the

Partitioned Scheme

CREATE TABLE Fact (Fact_Date smalldatetime , all my other columns) ON Fact_PS (Fact_Date)

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partitioned Table

SQL Server Storage Table1 Table2 Table Fact Fact.$Partition=1 Fact.$Partition=3 Fact.$Partition=2 FileGroupA FileA1 FileGroupB FileB1 FileB2 FGF1 F1 FGF2 F2 Fact.$Partition=4 FGF4 F4

FGF3 F3

Logical Disk System ­ Windows Drives Drive C: Drive D: Physical IO - subsystem Disk Disk Disk Disk Disk Disk Drive E:

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Demo 2 ­ Partitioned Tables

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Issues with Partitioning

§ No foreign keys can reference the Partitioned Table

(if you want to switch in partitions)

§ Can't use and identity column § UPDATES on partitioned tables with part of the table in

READ_ONLY filegroups must have partition elimination that restricts the updates to READ_WRITE filegroups.

§ INSERTs into partitioned views require all columns and

face additional restrictions.

Copyright 2008-2009 Noviick Software, Inc. All rights reserved. Partitioning: Tables, Views, and Indexed Views In SQL Server

Hash Partitioning for OLTP

§ Not directly supported § Use a NOT NULL PERSISTED computed column

§ Reduces index depth and contention.

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Demo 3 ­ Hash Partitioning

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partition Aligned Indexed Views

§ What are indexed Views § Why indexed views on partitioned tables aren't very useful § What do partition aligned indexed views do?

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Partition Aligned Indexed Views

§ Allows ALTER TABLE .... SWITCH

when indexed view is defined

§ Uses same filegroups as the Partitioned Table § No special syntax

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Demo 4 ­ Partition Aligned Indexed Views

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

SQL Server 2008 ­ What's New

§ Partition Aligned Indexed Views § Partition Level Lock Escalation § More than one processor allocated to searching a partition § Filegroup Read/Write transitions don't require exclusive

access to the database.

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

New England Visual Basic Pro

§ Feb 5th Joe Hill of Novell on Visual Basic for Linux, Boo § March 5 Igor Moochnick on ASP.Net Data Services

(Astoria)

§ April 2nd : Tom Schwartzman, ESRI § MPR C - 6:15 PM to 8:30

GIS in .Net

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

SQL Server Loadfest

§ Full day of free instruction on the SQL Server install process

§ March 27th

9:00 to 5:00 Microsoft Office 201 Jones Rd., Waltham, MA

§ Regististration at

https://www.clicktoattend.com/invitation.aspx?code=134471

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Thanks for Coming

Andrew Novick

§

[email protected]

§

www.NovickSoftware.com

Copyright 2008-2009 Noviick Software, Inc. All rights reserved.

Partitioning: Tables, Views, and Indexed Views In SQL Server

Information

Microsoft PowerPoint - partitioning-sql-server tables viws indexed views.ppt

33 pages

Report File (DMCA)

Our content is added by our users. We aim to remove reported files within 1 working day. Please use this link to notify us:

Report this file as copyright or inappropriate

216241


Notice: fwrite(): send of 201 bytes failed with errno=104 Connection reset by peer in /home/readbag.com/web/sphinxapi.php on line 531