ايندکس ها

1. مباحث اين جلسه • B-Tree ها ، Page ها و Extent ها • مفهوم Page Split و پيشگيري از آن • ايجاد ، استفاده و تغيير انواع ايندکس ها • ايندکسهايXML • نگهداري و به روز رسانيIndex ها ايندکس ها

7. NON-CLUSTERED INDEX On a Heap

8. NON-CLUSTERED INDEX On a CLUSTERED INDEX

10. انواع سيستم هاي ديتابيس • OLTP (Online Transaction Processing) • OLAP (Online Analytical Processing)

11. CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] • INDEX <index name> ON <table or view name>(<column name> [ASC|DESC] [,...n]) • INCLUDE (<column name> [, ...n]) [WITH • [PAD_INDEX = { ON | OFF }] • [[,] FILLFACTOR = <fillfactor>] • [[,] IGNORE_DUP_KEY = { ON | OFF }] • [[,] DROP_EXISTING = { ON | OFF }] • [[,] STATISTICS_NORECOMPUTE = { ON | OFF }] • [[,] SORT_IN_TEMPDB = { ON | OFF }] • [[,] ONLINE = { ON | OFF } • [[,] ALLOW_ROW_LOCKS = { ON | OFF } • [[,] ALLOW_PAGE_LOCKS = { ON | OFF } • [[,] MAXDOP = <maximum degree of parallelism>] • [ON {<filegroup> | <partition scheme name> | DEFAULT }]

12. NONCLUSTERED INDEXمثال از يک • CREATE TABLE MyTableKeyExample • ( • Column1 int IDENTITY • PRIMARY KEY NONCLUSTERED, • Column2 int • )

13. XML Indexes • The table containing the XML you want to index must have a clustered index on it. • A “primary” XML index must exist on the XML data column before you can create “secondary” indexes • XML indexes can be created only on columns of XML type (and an XML index is the only kind of index you can create on columns of that type). • The XML column must be part of a base table — you cannot create the index on a view.

14. XML INDEXنحوه ايجاد يک • USE [databaseName] GO • SET ARITHABORT ON GO • SET CONCAT_NULL_YIELDS_NULL ON GO • SET QUOTED_IDENTIFIER ON GO • SET ANSI_NULLS ON GO • SET ANSI_PADDING ON GO • SET NUMERIC_ROUNDABORT OFF GO

15. XML INDEXادامه ايجاد • CREATE PRIMARY XML INDEX [test] ON [dbo].[test] • ( [xmldata] ) • WITH (PAD_INDEX = OFF, • SORT_IN_TEMPDB = OFF, • DROP_EXISTING = OFF, • ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, • FILLFACTOR = 100) • GO

16. ساخت مجدد ايندکس • ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID • ON Sales.SalesOrderDetail • REBUILD WITH (FILLFACTOR = 100)

17. ALTER INDEX { <name of index> | ALL } • ON <table or view name> • { REBUILD • [ [ WITH ( • [ PAD_INDEX = { ON | OFF } ] • | [[,] FILLFACTOR = <fillfactor> • | [[,] SORT_IN_TEMPDB = { ON | OFF } ] • | [[,] IGNORE_DUP_KEY = { ON | OFF } ] • | [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ] • | [[,] ONLINE = { ON | OFF } ] • | [[,] ALLOW_ROW_LOCKS = { ON | OFF } ] • | [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ] • | [[,] MAXDOP = <max degree of parallelism> • ) ]

18. | [ PARTITION = <partition number> • [ WITH ( <partition rebuild index option> • [ ,...n ] ) ] ] ] • | DISABLE • | REORGANIZE • [ PARTITION = <partition number> ] • [ WITH ( LOB_COMPACTION = { ON | OFF } ) ] • | SET ([ ALLOW_ROW_LOCKS= { ON | OFF } ] • | [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ] • | [[,] IGNORE_DUP_KEY = { ON | OFF } ] • | [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ] • ) • } [ ; ]

19. DBCC SHOWCONTIG ([table name], [index name]) • Pages Scanned • Extents Scanned • Extent Switches • Avg. Pages per Extent • Scan Density [Best Count: Actual Count] • Logical Scan Fragmentation • Extent Scan Fragmentation • Avg. Bytes Free per Page • Avg. Page Density

20. DBCC DBREINDEX(db.owner.table[,index name[,fill factor]])) • DBCC DBREINDEX(OrderDetails,indexName,65]]))

21. انتخاب ايندکس مناسب • Use Indexes (either CI or NCI) in Foreign Keys • Range Selects (BETWEEN, <,>,Aggregate, Sortes)  Clustered Indexes • High Selectivity  NON-CLUSTERED Indexes • Increase Index Count  Decrease Data Altering Instructions • Clustered Indexes decrease Alter Instructions Speed • So Always Keep Balance in Using Indexes

22. INDEXبدست آوردن اطلاعات مربوط به يک • DECLARE @db_id SMALLINT; • DECLARE @object_id INT; • SET @db_id = DB_ID(N'AdventureWorks2008'); • SET @object_id = OBJECT_ID ( N'AdventureWorks2008.Sales.SalesOrderDetail'); • SELECT database_id, object_id, index_id, index_depth, avg_fragmentation_in_percent, page_count • FROM sys.dm_db_index_physical_stats(@db_id,@object_id,NULL,NULL,NULL);

23. نتيجه اجراي اسکريپت قبل