14-Progettazione Fisica in SQL Server

8/14/2019 14-Progettazione Fisica in SQL Server

1/26

Physical Database DesignPhysical Database Design

in SQL Server 2005in SQL Server 2005

2

CREATE TABLECREATE TABLE

CREATE TABLE [database_name.... [schema_name] ....

| schema_name....] table_name

(((( { |

}

[ ] [ ,,,,...n ] ))))

[ ON { partition_scheme_name

((((partition_column_name)))) | filegroup| """"default"""" } ]

[ { TEXTIMAGE_ON { filegroup| """"default"""" } ]

3

CREATE TABLECREATE TABLE

Creates table table_name

[ON { partition_scheme_name(partition_column_name) | filegroup| "default" } ]

Specifies the partition scheme or filegroup onwhich the table is stored.

If"default" is specified, or if ON is not specified atall, the table is stored on the default filegroup.

The storage mechanism of a table as specified inCREATE TABLE cannot be subsequently altered.

4

TEXTIMAGE_ONTEXTIMAGE_ON

[ { TEXTIMAGE_ON { filegroup| "default" } ]

indicate that the text, ntext, image, xml, varchar(max),nvarchar(max), varbinary(max), and CLR user-defined

type columns are stored on the specified filegroup. TEXTIMAGE_ON is not allowed if there are no large value

columns in the table.

TEXTIMAGE_ON cannot be specified if is specified.

If"default" is specified, or if TEXTIMAGE_ON is notspecified at all, the large value columns are stored in thedefault filegroup. The storage of any large value column dataspecified in CREATE TABLE cannot be subsequentlyaltered.


2/26

5

::=column_name

[ COLLATE collation_name]

[ NULL | NOT NULL ]

[

[ CONSTRAINT constraint_name] DEFAULTconstant_expression

| IDENTITY [ ( seed ,increment) ]

[ NOT FOR REPLICATION ]

]

[ ROWGUIDCOL ]

[ [ ...n] ]

6

NULL | NOT NULL

Determine whether null values are allowed in thecolumn.

7

IDENTITYIDENTITY

When a new row is added to the table, the Database Engineprovides a unique, incremental value for the column.

Identity columns are typically used with PRIMARY KEY

constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned totinyint, smallint,

int, bigint, decimal(p,0), or numeric(p,0) columns.

Only one identity column can be created per table.

DEFAULT constraints cannot be used with an identity column.

Both the seed and increment or neither must be specified. Ifneither is specified, the default is (1,1).

NOT FOR REPLICATION: values are not incremented in

identity columns when replication agents perform inserts

8

ROWGUIDCOLROWGUIDCOL

Although the IDENTITY property automates rownumbering within one table, separate tables, eachwith its own identifier column, can generate the same

values.

If an application must generate an identifier columnthat is unique across the database, or everydatabase on every networked computer in the world,use the ROWGUIDCOL property, theuniqueidentifier data type, and the NEWID function.


3/26

9

ROWGUIDCOLROWGUIDCOL

When you use the ROWGUIDCOL property to define a GloballyUnique Identifier (GUID) column, consider the following:

A table can have only one ROWGUIDCOL column, and thatcolumn must be defined by using the uniqueidentifier datatype.

The Database Engine does not automatically generatevalues for the column. To insert a globally unique value,create a DEFAULT definition on the column that uses theNEWID function to generate a globally unique value.

Because the ROWGUIDCOL property does not enforce

uniqueness, the UNIQUE constraint should be used toguarantee that unique values are inserted into theROWGUIDCOL column.

10

::=[ CONSTRAINT constraint_name]

{ { PRIMARY KEY | UNIQUE }[ CLUSTERED | NONCLUSTERED ][ WITH FILLFACTOR = fillfactor

| WITH ( < index_option > [ , ...n] ) ][ ON { partition_scheme_name(partition_column_name)| filegroup| "default" } ]

| [ FOREIGN KEY ]REFERENCES [ schema_name. ] referenced_table_name

[ (ref_column) ][ ON DELETE { NO ACTION | CASCADE | SET NULL | SET

DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SETDEFAULT } ]

[ NOT FOR REPLICATION ]| CHECK [ NOT FOR REPLICATION ] (logical_expression) }

11

PRIMARY KEY

Is a constraint that enforces entity integrity for a specifiedcolumn or columns through a unique index. Only one

PRIMARY KEY constraint can be created per table. UNIQUE

Is a constraint that provides entity integrity for a specifiedcolumn or columns through a unique index. A table can havemultiple UNIQUE constraints.

CLUSTERED | NONCLUSTERED

Indicate that a clustered or a nonclustered index is createdfor the PRIMARY KEY or UNIQUE constraint. PRIMARYKEY constraints default to CLUSTERED, and UNIQUEconstraints default to NONCLUSTERED.

12

In a CREATE TABLE statement, CLUSTERED can bespecified for only one constraint. If CLUSTERED is specifiedfor a UNIQUE constraint and a PRIMARY KEY constraint is

also specified, the PRIMARY KEY defaults toNONCLUSTERED.

The clause ON indicates where the index will be stored

CHECK

Is a constraint that enforces domain integrity by limiting thepossible values that can be entered into a column orcolumns.

logical_expression

Is a logical expression that returns TRUE or FALSE.


4/26

13

NOT FOR REPLICATIONNOT FOR REPLICATION

If this clause is specified for a constraint, theconstraint is not enforced when replication agentsperform insert, update, or delete operations

14

ExamplesExamplesof

EmployeeID int

PRIMARY KEY CLUSTERED

SalesPersonID int NULL

REFERENCES SalesPerson(SalesPersonID)

Name nvarchar(100) NOT NULL

UNIQUE NONCLUSTERED

15

ComputedComputedColumnsColumns

A computed column is computed from an expressionthat uses other columns in the same table.

It is not physically stored in the table, unless thecolumn is marked PERSISTED.

For example, a computed column can have thedefinition: cost AS price * qty. The expression canbe a noncomputed column name, constant, function,variable, and any combination of these connected byone or more operators. The expression cannot be asubquery.

16


Computed columns can be used in select lists, WHERE clauses,ORDER BY clauses, or any other locations in which regularexpressions can be used, with the following exceptions: A computed column can be used as a key column in an

index or as part of any PRIMARY KEY or UNIQUEconstraint, if the computed column value is defined by adeterministic expression and the data type of the result isallowed in index columns.

For example, if the table has integer columnsa and b,the computed columna+b may be indexed, but thecomputed columna+DATEPART(dd, GETDATE())cannot be indexed because the value may change insubsequent invocations.

A computed column cannot be the target of an INSERT orUPDATE statement.


5/26

17

::=column_nameAScomputed_column_expression

[ PERSISTED [ NOT NULL ] ]

[ [ CONSTRAINT constraint_name]{ PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ][ WITH FILLFACTOR = fillfactor

| WITH ( < index_option > [ , ...n] ) ]| [ FOREIGN KEY ]

REFERENCES [ schema_name. ] referenced_table_name[ (ref_column) ]

[ ON DELETE { NO ACTION | CASCADE } ][ ON UPDATE { NO ACTION } ][ NOT FOR REPLICATION ]

| CHECK [ NOT FOR REPLICATION ] (logical_expression) }[ ON { partition_scheme_name(partition_column_name) | filegroup| "default" } ]]

18

PERSISTEDPERSISTED

Specifies that the SQL Server Database Engine willphysically store the computed values in the table, andupdate the values when any other columns on whichthe computed column depends are updated. .

Any computed column that is used as a partitioningcolumn of a partitioned table must be explicitlymarked PERSISTED.

computed_column_expressionmust be deterministicwhen PERSISTED is specified

19

< table_constraint > ::=[ CONSTRAINT constraint_name]{ { PRIMARY KEY | UNIQUE }

[ CLUSTERED | NONCLUSTERED ]

(column[ ASC | DESC ] [ ,...n ] )[ WITH FILLFACTOR = fillfactor

|WITH ( [, ...n] ) ][ ON { partition_scheme_name(partition_column_name) | filegroup|

"default" } ]| FOREIGN KEY ( column [ ,...n ] )

REFERENCES referenced_table_name[ (ref_column[ ,...n ] ) ][ ON DELETE { NO ACTION | CASCADE | SET NULL |

SET DEFAULT}][ ON UPDATE { NO ACTION | CASCADE | SET NULL |

SET DEFAULT } ][ NOT FOR REPLICATION ]| CHECK [ NOT FOR REPLICATION ] (logical_expression) }

20

Used for defining multicolumn constraints

column: is a column or list of columns, inparentheses, used to indicate the columns used inthe constraint definition.

[ ASC | DESC ]: specifies the order in which thecolumn or columns participating in table constraintsare sorted. The default is ASC.

NOT FOR REPLICATION: the constraint is notenforced when replication agents perform insert,update, or delete operations


6/26

21

ExampleExampleof

FOREIGN KEY (SalesPersonID) REFERENCESSalesPerson(SalesPersonID)

CONSTRAINTFK_SpecialOfferProduct_SalesOrderDetailFOREIGN KEY (ProductID, SpecialOfferID)

REFERENCES SpecialOfferProduct (ProductID,SpecialOfferID)

22

CompleteComplete ExampleExample

CREATE TABLE [dbo].[PurchaseOrderDetail]( [PurchaseOrderID] [int] NOT NULL

REFERENCESPurchasing.PurchaseOrderHeader(PurchaseOrderID),

[LineNumber] [smallint] NOT NULL,

[ProductID] [int] NULLREFERENCES Production.Product(ProductID),

[DueDate] [datetime] NULL,[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL

CONSTRAINT [DF_PurchaseOrderDetail_rowguid] DEFAULT(newid()),

[ModifiedDate] [datetime] NOT NULLCONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate] DEFAULT

(getdate()),[LineTotal] AS (([UnitPrice]*[OrderQty])),[StockedQty] AS (([ReceivedQty]-[RejectedQty])),

CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber]PRIMARY KEY CLUSTERED ([PurchaseOrderID], [LineNumber])

) ON [PRIMARY]

23

PartitionsPartitions

A large table or index can be divided into partitions

A partition is an horizontal portion of a table or index

A column is chosen on the basis of which performingpartitioning

The partitions can be spread across more than onefilegroup in a database.

The table or index is treated as a single logical entitywhen queries or updates are performed on the data.

All partitions of a single index or table must reside in

the same database.

24

PartitionsPartitions

Example: A transaction table

The current month of data is primarily used for INSERT,UPDATE, and DELETE operations

Previous months are used primarily for SELECT queries Partitioning by month can be useful for

Maintenance operations: index rebuilds

Moving one month of read-only data from this table to a datawarehouse table for analysis. With partitioning, subsets ofdata can be separated quickly from a table and then addedas partitions to another existing partitioned table, assumingthese tables are all in the same database instance.

Improving query performance


7/26

25

PartitioningPartitioning

In SQL Server 2005, all tables and indexes in adatabase are considered partitioned, even if they aremade up of only one partition.

Essentially, partitions form the basic unit oforganization in the physical architecture of tables andindexes.

26

Partitioned IndexesPartitioned Indexes

Partitioned indexes can be implementedindependently from their base tables

However, it generally makes sense to partition theindex in the same way as the underlying table. Wesay the index isalignedwith the table

When you design a partitioned table and then createan index on the table, SQL Server automaticallypartitions the index by using the same partitionscheme and partitioning column as the table.

27

PartitioningPartitioning

Before partitioning a table or index you need tocreate the following database objects:

Partition function: determines the values of thethresholds on the partitioning column

Partition scheme: determines in which filegroupeach partition is stored

28

ExampleExample

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES (1, 100, 1000);

GO

CREATE PARTITION SCHEME myRangePS1AS PARTITION myRangePF1

TO (test1fg, test2fg, test3fg, test4fg);

The partitions of a table that uses partition function myRangePF1 on

partitioning columncol1 would be assigned as shown in the followingtable

test4fgtest3fgtest2fgtest1fgFilegroup

col1 > 1000col1>100 ANDcol1 1 ANDcol1


8/26

29

ExampleExample

CREATE TABLE PartitionTable (col1 int, col2 char(10))

ON myRangePS1 (col1) ;

GO

30

ViewsViews

A view can be thought of as either a virtual table or astored query.

Unless a view is indexed, its data is not stored in thedatabase as a distinct object. What is stored in thedatabase is a SELECT statement. The result set ofthe SELECT statement forms the virtual tablereturned by the view.

A user can use this virtual table by referencing theview name in Transact-SQL statements in the same

way in which a table is referenced.

31

TypesTypesofof ViewsViews

Standard views

Indexed Views:

it is a view that has been materialized. This meansit has been computed and stored. You index aview by creating a unique clustered index on it.

Indexed views dramatically improve theperformance of some types of queries. Indexedviews work best for queries that aggregate manyrows. They are not well-suited for underlying datasets that are frequently updated.

32

CREATE VIEWCREATE VIEW

CREATE VIEW [ schema_name. ] view_name[(column[ ,...n ] ) ]

[ WITH [ ,...n ] ]

AS select_statement[ ; ]

[ WITH CHECK OPTION ]

::= {

[ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }


9/26

33

columncolumn

column

Ifcolumnis not specified, the view columnsacquire the same names as the columns in theSELECT statement.

If it is specified, it is the name to be used for acolumn in a view. A column name is required onlywhen a column is derived from an arithmeticexpression, a function, or a constant; when two ormore columns may otherwise have the same

name, typically because of a join; or when acolumn in a view is given a name different fromthat of the column from which it is derived.

34

WITH CHECK OPTIONWITH CHECK OPTION

Forces all data modification statements executedagainst the view to follow the criteria set within

select_statement. When a row is modified through aview, the WITH CHECK OPTION makes sure thedata remains visible through the view after themodification is committed.

35

SCHEMABINDINGSCHEMABINDING

When SCHEMABINDING is specified, the base tableor tables cannot be modified in a way that wouldaffect the view definition.

The view definition itself must first be modified ordropped to remove dependencies on the table that isto be modified, otherwise an error is returned

When you use SCHEMABINDING, theselect_statementmust include the two-part names(schema.object) of tables, views, or user-definedfunctions that are referenced. All referenced objects

must be in the same database.

36

UpdatableUpdatableViewsViews

You can modify the data of an underlying base table through a view, aslong as the following conditions are true:

Any modifications, including UPDATE, INSERT, and DELETEstatements, must reference columns from only one base table.

The columns being modified in the view must directly reference theunderlying data in the table columns. The columns cannot bederived in any other way, such as through the following:

An aggregate function: AVG, COUNT, SUM, MIN, MAX,GROUPING, STDEV, STDEVP, VAR, and VARP.

A computation. The column cannot be computed from anexpression that uses other columns.

The columns being modified are not affected by GROUP BY,HAVING, or DISTINCT clauses.

TOP is not used anywhere in the select_statementof the view

together with the WITH CHECK OPTION clause.


10/26

37

ViewViewExampleExample

USE AdventureWorks ;

GO

CREATE VIEW hiredate_view

AS

SELECT c.FirstName, c.LastName, e.EmployeeID,e.HireDate

FROM HumanResources.Employee e JOINPerson.Contact c on e.ContactID = c.ContactID ;

GO

An UPDATE that modifies an employee LastName andHierDate returns an error

38

ViewViewExampleExample

USE AdventureWorks ;

GO

CREATE VIEW SeattleOnly

AS

SELECT p.LastName, p.FirstName, p.City,

FROM Person p WHERE p.City = 'Seattle'

WITH CHECK OPTION ;

GO

An UPDATE that changes the city of a Person returnsan error.

39

IndexIndexTypesTypes

A unique index ensures that the index key contains noduplicate values and therefore every row in the table or

view is in some way unique.Both clustered and nonclustered indexes can be unique.

Unique

A nonclustered index can be defined on a table or viewwith a clustered index or on a heap. Each index row inthe nonclustered index contains the nonclustered keyvalue and a row locator.

Nonclustered

A clustered index sorts and stores the data rows of the

table or view in order based on the clustered index key.Clustered

DescriptionIndex type

40

IndexIndexTypesTypes

A persisted, representation of the XML binary large objects

(BLOBs) in the xml data type column.XML

A special type of token-based functional index that is built andmaintained by the Microsoft Full-Text Engine for SQL Server(MSFTESQL) service. It provides efficient support forsophisticated word searches in character string data.

Full-text

An index on a view materializes the view and the result set is

permanently stored in a unique clustered index in the sameway a table with a clustered index is stored. Nonclusteredindexes on the view can be added after the clustered index iscreated.

Indexed views

A nonclustered index that is extended to include nonkeycolumns in addition to the key columns.

Index withincludedcolumns


11/26

41

IndexIndexCreationCreation

Indexes are automatically created when PRIMARY KEY andUNIQUE constraints are defined on table columns.

A unique index is created to enforce the uniquenessrequirements of a PRIMARY KEY or UNIQUE constraint.

By default, a unique clustered index is created to enforce aPRIMARY KEY constraint, unless a clustered index alreadyexists on the table, or you specify a nonclustered index.

By default, a unique nonclustered index is created to enforce aUNIQUE constraint unless a unique clustered index is explicitlyspecified and a clustered index on the table does not exist.

An index created as part of a PRIMARY KEY or UNIQUEconstraint is automatically given the same name as theconstraint name.

42


An index can be built with

CREATE INDEX of T-SQL or

New Index of Management Studio

43

LimitsLimits

Does not pertain to XML indexes.900 bytesMaximum index keyrecord size

Clustered index is limited to 15 columnsif the table also contains a primary XMLindex.

16Number of key columnsper index

Includes primary and secondary XMLindexes on xml data type columns.

249XML indexes per table

Includes nonclustered indexes createdby PRIMARY KEY or UNIQUEconstraints, but not XML indexes.

249Nonclustered indexes pertable

1Clustered indexes pertable

Additional informationValueMaximum index limits

44

RelationalRelationalIndexIndexCreationCreation

CREATE [ UNIQUE ] [ CLUSTERED |

NONCLUSTERED ] INDEX index_name

ON ((((column [ ASC | DESC ] [ ,,,,...n] ))))

[ INCLUDE ((((column_name[ ,,,,...n] )))) ]

[ WITH (((( [ ,,,,...n] )))) ]

[ ON { partition_scheme_name((((column_name))))

| filegroup_name

| default }

]

::= table_or_view


12/26

45

UniqueUniqueIndexesIndexes

The benefits of unique indexes include the following:

Data integrity of the defined columns is ensured.

Additional information helpful to the queryoptimizer is provided

There are no significant differences between creatinga UNIQUE constraint and creating a unique indexindependent of a constraint. Data validation occurs inthe same manner and the query optimizer does notdifferentiate between a unique index created by aconstraint or manually created.

Create a UNIQUE or PRIMARY KEY constraint onthe column when data integrity is the objective.

46

UniqueUniqueIndexesIndexes

For indexing purposes, NULL values compare asequal. Therefore, you cannot create a unique index,

or UNIQUE constraint, if the key values are NULL inmore than one row. Select columns that are definedas NOT NULL when you choose columns for aunique index or unique constraint.

47

IncludedIncludedColumnsColumns

You can add nonkey columns to the leaf level of thenonclustered index.

Included columns can be data types not allowed as index

key columns. Included columns are not considered by the Database

Engine when calculating the number of index key columns orindex key size.

When an index contains all the columns referenced by the queryas key or nonkey columns it is typically referred to ascoveringthe query

The performance of a query covered by an index is increasedbecause all the columns values can be located in the index

without accessing the table or clustered index data

48


Assume that you want to index the following columns in theDocument table in the AdventureWorks sample database:

Title nvarchar(50) Revision nchar(5) FileName

nvarchar(400) An index that contains these three columns would exceed the

900 byte size limitation by 10 bytes (455 * 2).

By using the INCLUDE clause of the CREATE INDEXstatement, the index key could be defined as (Title, Revision)and FileName defined as a nonkey column. In this way, theindex key size would be 110 bytes (55 * 2), and the index wouldstill contain all the required columns.


13/26

49


USE AdventureWorks;

GO

CREATE INDEX IX_Document_Title

ON Production.Document (Title, Revision)

INCLUDE (FileName);

Nonkey columns can only be defined on nonclusteredindexes on tables or indexed views.

50

SortSortOrderOrder

You should consider whether the data for the indexkey column should be stored in ascending or

descending order. Ascending is the default Keywords: ASC (ascending)

and DESC (descending)

Specifying the order in which key values are stored inan index is useful when queries referencing the tablehave ORDER BY clauses that specify orderdirections for the key column or columns in thatindex.

In these cases, the index can remove the need for aSORT operator in the query plan; therefore, thismakes the query more efficient.

51

SortSortOrderOrderExampleExample

The buyers in the Adventure Works Cyclespurchasing department have to evaluate the qualityof products they purchase from vendors. The buyers

are most interested in finding products sent by thesevendors with a high rejection rate.

Retrieving the data to meet this criteria requires theRejectedQty column in thePurchasing.PurchaseOrderDetail table to be sorted indescending order (large to small) and the ProductIDcolumn to be sorted in ascending order (small to

large).

52


USE AdventureWorks;

GO

SELECT RejectedQty, ((RejectedQty/OrderQty)*100)AS RejectionRate, ProductID, DueDate

FROM Purchasing.PurchaseOrderDetail

ORDER BY RejectedQty DESC, ProductID ASC;


14/26

53


The following execution plan for this query shows thatthe query optimizer used a SORT operator to return

the result set in the order specified by the ORDERBY clause.

54


If an index is created with key columns that matchthose in the ORDER BY clause in the query, the

SORT operator can be eliminated in the query planand the query plan is more efficient:

CREATE NONCLUSTERED INDEXIX_PurchaseOrderDetail_RejectedQty

ON Purchasing.PurchaseOrderDetail

(RejectedQty DESC, ProductID ASC);

55


After the query is executed again, the followingexecution plan shows that the SORT operator hasbeen eliminated and the newly created nonclustered

index is used.

56

SortSortOrderOrder

SQL Server 2005 can move equally efficiently ineither direction. An index defined as (RejectedQtyDESC, ProductID ASC) can still be used for a query

in which the sort direction of the columns in theORDER BY clause are reversed.

For example, a query with the ORDER BY clauseORDER BY RejectedQty ASC, ProductID DESC canuse the index.

A query with the ORDER BY clause ORDER BYRejectedQty ASC, ProductID ASC can not use the

index.


15/26

57

DataData TypesTypes

The index key of a clustered index cannot contain varcharcolumns that have existing data in theROW_OVERFLOW_DATA allocation unit. If a clustered indexis created on a varchar column and the existing data is in theIN_ROW_DATA allocation unit, subsequent insert or updateactions on the column that would push the data off-row will fail.

Varchar columns pushed off-row

Can be indexed. Computed columns derived from LOB datatypes can be indexed either as a key or nonkey column as longas the computed column data type is allowed as an index keycolumn or nonkey column.

Computed columns

Cannot be an index key column. However, an xml column canbe a key column in a primary or secondary XML index on atable.Can participate as nonkey (included) columns in anonclustered index except for image, ntext, and text..

Large object (LOB) datatypes: image, ntext, text,varchar(max),nvarchar(max),varbinary(max), and xml

Can be indexed if the type supports binary ordering.CLR user-defined type

Index participationData type

58


You can define indexes on computed columns as long as thecomputed_column_expressionis deterministic and precise

Expressions are deterministic if they always return the sameresult for a specified set of inputs.computed_column_expressionis deterministic when one ormore of the following is true:

All functions referenced by the expression are deterministic.This includes both user-defined and built-in functions.

All columns referenced in the expression come from thetable that contains the computed column.

No column reference pulls data from multiple rows. Forexample, aggregate functions such as SUM or AVG.

Has no system data access or user data access.

59


A computed column expression is precise when one or more ofthe following is true:

It is not an expression of the float or real data types

It does not use a float or real data type in its definition. Forexample, in the following statement, column y isint anddeterministic but not precise

CREATE TABLE t2 (a int, b int, c int, x float,

y AS CASE x

WHEN 0 THEN a

WHEN 1 THEN b

ELSE c

END)

60


You can create an index on a computed column thatis defined with a deterministic, but imprecise,expression if the column is marked PERSISTED in

the CREATE TABLE or ALTER TABLE statement. The Database Engine uses these persisted values

when it creates an index on the column, and whenthe index is referenced in a query.


16/26

61

RelationalRelationalIndexIndexOptionsOptions

Specifies the error response to duplicate keyvalues in a multiple-row INSERT transactionon a unique clustered or unique nonclusteredindex.

IGNORE_DUP_KEY

Determines where the intermediate sortresults, generated during index creation, arestored. When ON, the sort results are stored intempdb. When OFF, the sort results are storedin the filegroup or partition scheme in whichthe resulting index is stored.

SORT_IN_TEMPDB

Sets the percentage of free space in the leaflevel of each index page during index creation.

FILLFACTOR

Sets the percentage of free space in the

intermediate level pages during index creation.PAD_INDEX

DescriptionIndex option

Sets the maximum number of processors thequery processor can use to execute a singleindex statement. Fewer processors may beused depending on the current systemworkload.

MAXDOP

Determines whether page locks are used inaccessing index data.

ALLOW_PAGE_LOCKS

Determines whether row locks are used in

accessing index data.ALLOW_ROW_LOCKS

Determines whether concurrent user access tothe underlying table or clustered index dataand any associated nonclustered indexes isallowed during index operations.

ONLINE

Indicates the existing index should be dropped

and recreated.

DROP_EXISTING

Specifies whether out-of-date index statisticsshould be automatically recomputed.

STATISTICS_NORECOMPUTE

DescriptionIndex option

63

RelationalRelationalIndexIndexOptionsOptions

When IGNORE_DUP_KEY is set to OFF (thedefault), SQL Server 2005 rejects all rows in theINSERT statement when one or more rows contain

duplicate key values. When set to ON, only the rowsthat contain duplicate key values are rejected; thenonduplicate key values are added.

64

IndexIndexPositionPosition

ON filegroup_name

Creates the specified index on the specified filegroup. If nolocation is specified and the table or view is not partitioned,the index uses the same filegroup as the underlying table orview. The filegroup must already exist.

ON "default"

Creates the specified index on the default filegroup.

The term default, in this context, is not a keyword. It is anidentifier for the default filegroup and must be delimited, asin ON "default" or ON [default]. If "default" is specified, theQUOTED_IDENTIFIER option must be ON for the currentsession. This is the default setting.


17/26

65

CREATE INDEXCREATE INDEX ExampleExample

The following example creates a nonclustered indexon the VendorID column of the

Purchasing.ProductVendor table.

USE AdventureWorks;

GO

CREATE INDEX IX_ProductVendor_VendorID

ON Purchasing.ProductVendor (VendorID);

GO

66


The following example creates a nonclusteredcomposite index on the SalesQuota and SalesYTD

columns of the Sales.SalesPerson table.

USE AdventureWorks

GO

CREATE NONCLUSTERED INDEXIX_SalesPerson_SalesQuota_SalesYTD

ON Sales.SalesPerson (SalesQuota, SalesYTD);GO

67


The following example creates a unique nonclusteredindex on the Name column of theProduction.UnitMeasure table.

USE AdventureWorks;

GO

CREATE UNIQUE INDEX AK_UnitMeasure_Name

ON Production.UnitMeasure(Name);

GO

AK stands for Alternate Key

68


Attempting to insert a row with the same value as that inan existing row.

INSERT INTO Production.UnitMeasure

(UnitMeasureCode, Name, ModifiedDate) VALUES('OC', 'Ounces', GetDate());

GO

Result:

Server: Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'UnitMeasure'with unique index 'AK_UnitMeasure_Name'. Thestatement has been terminated.


18/26

69


Included columns:

USE AdventureWorks;

GOCREATE NONCLUSTERED INDEX IX_Address_PostalCode

ON Person.Address (PostalCode)

INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID,PostalCode

FROM Person.Address

WHERE PostalCode BETWEEN N'98000' and N'99999';

GO

70

IndexesIndexesonon ViewsViews

The first index created on a view must be a uniqueclustered index. After the unique clustered index has

been created, you can create additional nonclusteredindexes.

The naming conventions for indexes on views are thesame as for indexes on tables. The only difference isthat the table name is replaced with a view name.

The view must be defined with SCHEMABINDING tocreate an index on it

An indexed view is stored in the database in thesame way a table with a clustered index is stored.

71


Whether the index will be created on an empty tableor one that contains data is an important factor toconsider.

Creating an index on an empty table has noperformance implications at the time the index iscreated; however, performance will be affected whendata is added to the table.

Creating indexes on large tables should be plannedcarefully so database performance is not hindered.The preferred way to create indexes on large tables

is to start with the clustered index and then build anynonclustered indexes.

72


If a clustered index is created on a heap with severalexisting nonclustered indexes, all the nonclusteredindexes must be rebuilt so that they contain the

clustering key value instead of the row identifier(RID). Similarly, if a clustered index is dropped on atable that has several nonclustered indexes, thenonclustered indexes are all rebuilt as part of theDROP operation. This may take significant time onlarge tables.


19/26

73


In SQL Server 2005 Enterprise Edition, you can create, rebuild,or drop indexes online with the ONLINE option set to ON.

It allows concurrent user access to the underlying table orclustered index data and any associated nonclustered indexesduring index operations.

For example, while a clustered index is being rebuilt by oneuser, that user and others can continue to update and query theunderlying data.

When you perform DDL operations offline, such as building orrebuilding a clustered index; these operations hold long-termexclusive locks on the underlying data and associated indexes.

This prevents modifications and queries to the underlying datauntil the index operation is complete.

74

FragmentationFragmentation

Over time insert, update, or delete operations cancause the information in the index to become

scattered in the database (fragmented). Fragmentation exists when indexes have pages in

which the logical ordering, based on the key value,does not match the physical ordering inside the datafile.

Heavily fragmented indexes can degrade queryperformance and cause your application to respondslowly.

You can remedy index fragmentation by eitherreorganizing an index or by rebuilding an index.

75

DetectingDetectingFragmentationFragmentation

By using the system functionsys.dm_db_index_physical_stats, you can detectfragmentation in a specific index, all indexes on a

table or indexed view, all indexes in a database, or allindexes in all databases.

For partitioned indexes,sys.dm_db_index_physical_stats also providesfragmentation information for each partition.

The result set returned by this function includes thefollowing column:

avg_fragmentation_in_percent: the percent of

logical fragmentation (out-of-order pages in theindex).

76

CorrectingCorrectingFragmentationFragmentation

After the degree of fragmentation is known, use thefollowing table to determine the best method tocorrect the fragmentation:

ALTER INDEX REBUILD> 30%

ALTER INDEX REORGANIZE> 5% and < = 30%

Corrective statementavg_fragmentation_in_percent

value


20/26

77

ExampleExample

Rebuilding online all indexes on tableProduction.Product

USE AdventureWorks;

GO

ALTER INDEX ALL ON Production.Product

REBUILD WITH (ONLINE = ON);

78

DroppingDroppingIndexesIndexes

When a clustered index is dropped, the data rowsthat were stored in the leaf level of the clustered

index are stored in an unordered table (heap). Dropping a clustered index can take time because all

nonclustered indexes on the table must be rebuilt toreplace the clustered index keys with row pointers tothe heap.

When dropping all indexes on a table, drop thenonclustered indexes first and the clustered index

last.

79

DroppingDroppingIndexesIndexesExampleExample

The following example drops the indexIX_ProductVendor_VendorID in the ProductVendortable.

USE AdventureWorks;

GO

DROP INDEX IX_ProductVendor_VendorID

ON Purchasing.ProductVendor;

GO

80

DroppingDroppingIndexesIndexesExampleExample

The following example drops a clustered index withthe ONLINE option set to ON. The resultingunordered table (heap) is stored in the samefilegroup as the index was stored.

USE AdventureWorks;

GO

DROP INDEXAK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate

ON Production.BillOfMaterials WITH (ONLINE = ON,MAXDOP = 2);

GO


21/26

81

StatisticsStatistics

Statistical information can be created regarding thedistribution of values in a column.

The query optimizer uses this statistical informationto determine the optimal query plan by estimating thecost of using an index to evaluate the query.

A statistics on a column consist of anhistogramdividing the values in the column in up to 200intervals.

The histogram specifies how many rows exactlymatch each interval value, how many rows fall within

an interval, and a calculation of the density of values,or the incidence of duplicate values, within aninterval.

82

StatisticsStatistics

Statistics can be created in three ways:

Automatically by creating an index

Automatically when a column is used in apredicate when the AUTO_CREATE_STATISTICSdatabase option is set to ON (default),

Explicitly with CREATE STATISTICS

83

Visualizing StatisticsVisualizing Statistics

DBCC SHOW_STATISTICS( {'table_name' |'view_name'},target )

[ WITH [ NO_INFOMSGS ] < option > [ ,n] ]

:: = STAT_HEADER | DENSITY_VECTOR| HISTOGRAM

targetis the name of the object (index name, statisticsname or column name) for which to display statisticsinformation.

STAT_HEADER | DENSITY_VECTOR | HISTOGRAM

Specifying one or more of these options limits theresult sets returned by the statement.

84

VisualingVisualingStatisticsStatistics

Alternatively, in Management Studio right click on astatistic and Properties


22/26

85

ExampleExample

DBCC SHOW_STATISTICS ('Person.Address',IX_Address_StateProvinceID);

Returns 3 tables:

STAT_HEADER

DENSITY_VECTOR

HISTOGRAM

86

ColumnsColumnsofof STAT_HEADERSTAT_HEADER

Yes indicates that the statistics contain a string summary indexto support estimation of result set sizes for LIKE conditions.Applies only to leading columns of char, varchar, nchar, andnvarchar, varchar(max), nvarchar(max), text, and ntext datatypes.

String Index

Average length of all the index columns.Average key

length

Selectivity of the first index column prefix excluding theEQ_ROWS, which are described in the section about theHISTOGRAM option result set.

Density

Number of distribution steps.Steps

Number of rows sampled for statistics information.Rows Sampled

Number of rows in the table.Rows

Date and time the statistics were last updated.Updated

Name of the statistic.Name

DescriptionColumn name

87

Columns ofColumns of HISTOGRAMHISTOGRAM

Average number of duplicate values within a

histogram step, excluding the upper bound(RANGE_ROWS / DISTINCT_RANGE_ROWS).

AVG_RANGE_ROWS

Estimated number of distinct values within ahistogram step, excluding the upper bound.

DISTINCT_RANGE_ROWS

Estimated number of rows from the table that areequal in value to the upper bound of thehistogram step.

EQ_ROWS

Estimated number of rows from the table that fallwithin a histogram step, excluding the upperbound.

RANGE_ROWS

Upper bound value of a histogram step.RANGE_HI_KEY

DescriptionColumn name

88

Example ofExample of HISTOGRAMHISTOGRAM

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS

------------ ------------- ------------- -------------------- --------------

1 0 25 0 1

3 0 7 0 1

6 0 18 0 1

7 0 1579 0 1

8 0 231 0 19 0 4564 0 1

10 0 11 0 1

11 0 9 0 1

14 0 1954 0 1

15 0 31 0 1

17 0 17 0 1

......


23/26

89

Statistics UpdateStatistics Update

As the data in a column changes, statistics canbecome out-of-date and cause the query optimizer to

make less-than-optimal decisions on how to processa query.

Out-of-date or missing statistics are indicated aswarnings (table name in red text) when the executionplan of a query is graphically displayed using SQLServer Management Studio

90

Statistics UpdateStatistics Update

When the AUTO_UPDATE_STATISTICS databaseoption is set to ON (the default), the query optimizer

automatically updates this statistical informationperiodically as the data in the tables changes.

Unless the statistic has been created with anindex for which theSTATISTICS_NORECOMPUTE option wasspecified in the CREATE INDEX statement

Almost always, statistical information is updated

when approximately 20 percent of the data rows haschanged.

91

UPDATE STATISTICSUPDATE STATISTICS

You can manually update the statistics with

UPDATE STATISTICS table| view

[

{ { index| statistics_name}

| ( { index|statistics_name } [ ,...n] ) }

]

[ WITH

[ FULLSCAN

| SAMPLE number{ PERCENT | ROWS }

]

92

UPDATE STATISTICSUPDATE STATISTICS

The FULLSCAN clause specifies that all data in thetable is scanned to gather statistics,

The SAMPLE clause can be used to specify either

the percentage of rows to sample or the number ofrows to sample.


24/26

93

Physical DesignPhysical Design

In order to chose the physical structures to use(clustered indexes, nonclustered indexes, indexed

views, partitions) we can use the Database EngineTuning Advisor

Two interfaces are available

A standalone graphical user interface tool

A command-line utility program, dta.exe, forDatabase Engine Tuning Advisor functionality insoftware programs and scripts.

94

Database Engine Tuning AdvisorDatabase Engine Tuning Advisor

It analyzes the performance effects of workloadsrunagainst one or more databases.

A workload is a set of Transact-SQL statements thatexecutes against databases you want to tune.

After analyzing the effects of a workload on yourdatabases, Database Engine Tuning Advisorprovides recommendations to add, remove, or modifyphysical design structures in databases in order toreduce the execution time of the workload

95

WorkloadsWorkloads

A workload consists of a Transact-SQL script (.sql), aSQL Server Profiler trace saved to a file (.trc) or tableor an XML file (.xml) containing the statements plus

configuration information

96

RecommendationsRecommendations

A recommendation consists of Transact-SQLstatements

After Database Engine Tuning Advisor has

suggested a recommendation, you can optionally: Implement it immediately.

Save it to a Transact-SQL script and implement itlater

Modify it so to apply only a subset ofrecommendations


25/26

97

ReportsReports

Database Engine Tuning Advisor also returns anumber of reports

Improvement in percent

Use of existing physical structures

98

Exploratory AnalysisExploratory Analysis

The user can also investigate the impact on the execution timeof hypothetical structures

Example: An administrator just finished using Database Engine Tuning

Advisor to tune a database and received therecommendation (R).

After reviewing R, the administrator would like to fine tune Rby modifying it.

After modifying R, the administrator uses the modifiedrecommendation as input to Database Engine TuningAdvisor and tunes again to measure the performance impact

of her modifications.

99

TuningTuningLoadLoad

Tuning a large workload can create significantoverhead on the server that is being tuned.

The overhead results from the many calls made by

Database Engine Tuning Advisor to the queryoptimizer during the tuning process.

We can eliminate this overhead problem by using atest server in addition to the production server.

Production server: the server used by clients in dayto day real operations

Test server: a server used by developers for testing

new database configurations

100

Test ServerTest Server

Database Engine Tuning Advisor creates a shelldatabase on the test server. To create this shelldatabase and tune it, Database Engine TuningAdvisor extracts from the production server thefollowing information:

metadata on the production database. Thismetadata includes empty tables, indexes, views,stored procedures, triggers, and so on.

statistics

hardware parameters specifying the number ofprocessors and available memory on the

production server


26/26

101

TuningTuning

After Database Engine Tuning Advisor finishes tuningthe test server shell database, it generates a tuning

recommendation. You can apply the recommendation received from

tuning the test server to the production server.

102

HowHowtotoTuneTunewithwitha Test Servera Test Server

To tune a workload on a test server, you must use anXML input file with the dta command-line utility.

In the XML input file, specify the name of your testserver with the TestServer subelement in addition tospecifying the values for the other subelements underthe TuningOptions parent element.

103

ShowingShowingthethe ExecutionExecutionPlan of aPlan of a QueryQuery

SET SHOWPLAN_XML ONThis statement causes SQL Server not to executeTransact-SQL statements. Instead, Microsoft SQL

Server returns execution plan information about howthe statements are going to be executed in a well-formed XML document.

SET SHOWPLAN_TEXT ONAfter this SET statement is executed, SQL Serverreturns the execution plan information for each queryin text. The Transact-SQL statements or batches arenot executed.

104

ShowingShowingthethe ExecutionExecutionPlan of aPlan of a QueryQuery

SET SHOWPLAN_ALL ONThis statement is similar to SET SHOWPLAN_TEXT,except that the output is in a format more verbose

than that of SHOWPLAN_TEXT

14-Progettazione Fisica in SQL Server

Documents

Transcript of 14-Progettazione Fisica in SQL Server