SQL Server Storage and Index Structures .

Uploaded on:
SQL Server Storage and Index Structures. Physical Data Organization Indexes B-Trees SQL Server Data Access Clustered and Non-Clustered Creating, Altering, Dropping Indexes Choosing your Indexes Maintaining your Indexes. SQL Server Storage Hierarchy. Database Extent
Slide 1

SQL Server Storage and Index Structures Physical Data Organization Indexes B-Trees SQL Server Data Access Clustered and Non-Clustered Creating, Altering, Dropping Indexes Choosing your Indexes Maintaining your Indexes

Slide 2

SQL Server Storage Hierarchy Database Extent 8 adjoining 64K information pages Once degree full, next record will take up an entire extra degree. Pre-distributing space spares time.

Slide 3

SQL Server Storage Hierarchy Page 64K bytes # of records/page changes with bytes/record Types of Pages: Data and Index pages Page Split When page turns out to be full, it parts New page assigned: ½ information from old page moved to new Rows 8060 bytes and 1024 segments

Slide 4

B-tree Key Points to Remember Tree divide incorporates key qualities just requested as in make list explanation Keys pressed in file pages Fewer bytes per key - > more keys per page/degree - > less page shortcomings per get to. Grouped files have records at leafs Records are in information pages Data pages consecutively connected Non-Clustered files point into store or tree segment of bunched file

Slide 5

Create Index Statement Create [unique] [clustered | nonclustered] file <indexName> on <table or view> (col. Name> [asc|desc] [,… ]) incorporate (<col name> [,… ]) (with … pad_index, fillfactor, ignore_dup_key drop_existing, statistics_norecompute sort_in_tempDB, on the web, allow_row_locks, allow_page_locks, maxdop

Slide 6

Create Index Details Asc/Desc Ascending & plummeting sort arrange for file Include (cool!) Includes col in leaf hubs of grouped file Allows quick access to non-key characteristic Useful with substantial record – less page shortcomings

Slide 7

Create Index "with" Details Pad_Index= (on|off) Initial fill-consider for list\'s non-leaf pages Fill Factor = <1% – 100%> Default is list pages are as full as could be expected under the circumstances less two records Fill variable is the way full after list is made Once part goes to half Ignore_dup_key Circumvent special key imperative to some degree Still get blunder message, yet no rollback helpful for putting away extraordinary values yet destroying exchanges

Slide 8

Create Index "with" Details Drop_Existing Any current file with same name is dropped with this make proclamation More productive than drop list took after by make for bunched list as no compelling reason to touch non-bunched files or information pages Statistics_nonrecompute Default: sql server robotizes the way toward redesigning the measurements on tables/lists This choice says you will keep up details DON\'T USE THIS!

Slide 9

Create Index "with" Details Sort_In_tempdb Only helpful when tempdb on physically isolate drive Reads/compose for sort contend with read/composes to compose information and file pages This bode well if and just on the off chance that you comprehend circle composes - examination Online Keeps table accessible to clients while making record – sounds great, yet … .!!

Slide 10

Create Index "with" Details Allow push/page locks Don\'t utilize unless better than average MAXDOP Overrides framework setting for max level of parallelism while building file what number procedures are utilized to develop a list. MAXDOP sets confine on what number of processors per operation. Thoroughly analyze these terms

Slide 11

Create Index "with" Details ON Can store record independently from information Space for file spread crosswise over drives I/O for lists not contend with physical information recovery

Slide 12

XML Indexes into XML information Xml VERY unstructured Column can be of sort xml in sql server Create file on xml segment Page 276 for more points of interest

Slide 13

Implied lists made by a few limitations Primary Key Unique Can undoubtedly wind up with copy requirements and not understand it

Slide 14

Deciding what files go where? Lists speed get to, yet expensive to keep up Almost every upgrade to table requires adjusting both information pages and each list. All additions and cancellations influence all records Many overhauls will influence non-grouped files Sometimes toning it down would be best Not making a file now and then might be best Code for tranasaction have where statement? What sections utilized? Sort requried?

Slide 15

Deciding what files go where? Selectivity Indexes, especially non-bunched lists, are basically useful in circumstances where there is a sensibly HIGH LEVEL of Selectivity inside the record. % of qualities in section that are novel Higher rate of special values, the higher the selectivity If 80% of parts are either "red" or "green" not exceptionally particular

Slide 16

Choosing Clustered Index Only one for every table! - Choose astutely Default, essential key makes bunched file Do you truly need your prime key to be grouped record? Alternative: make table foo myfooExample (column1 int distinguish essential key nonclustered column2 … . ) Changing grouped record can be expensive How long? Do I have enough space?

Slide 17

Clustered Indexes Pros & Cons Pros Clustered lists best for inquiries where sections being referred to will every now and again be the subject of RANGE question (e.g., between) Group by with max, min, tally Search can go straight to specific point in information and simply continue perusing consecutively from that point. Bunched files accommodating with request by in view of grouped key

Slide 18

Clustered Indexes Pros & Cons The Cons – two circumstances Don\'t utilize bunched list on section in light of the fact that appears thing to do (e.g., essential key default) Lots of additions in non-consecutive request Constant page parts, incorporate information page and also record pages Choose grouped key that will be successive embeddings Don\'t utilize a grouped list at all maybe?

Slide 19

Column Order Matters (P#, S#, Qty) P# S# together are essential key One list that incorporates all segments is not valuable in all circumstances! Just wind up putting away information a second time. Grouped file of P#S# not same as S#P# P#S# can query P# decently effectively, however turning upward S# requires a straight inquiry. S#P# can query S# reasonably effortlessly, however not P#. Take note of that despite the fact that key of S#P# means can\'t query P# rapidly, are a few focal points in incorporate P# in key.

Slide 20

Dropping Indexes Sometimes bodes well to always re-break down circumstance and add lists DON\'T FORGET TO DROP INDEXES!! Enormous overhead for supplements and erases Always solicit yourself: "Would I be able to get free from any of these?" Drop INDEX <TABLE NAME> <Index name>

Slide 21

Index Tuning Wizard Hopefully you will develop to the direct you don\'t require toward utilize this contraption But still can be entirely convenient Uses workload record produced utilizing sql server profiler (ch 19) Not perfect to rely on upon this device, yet it might make a few proposals that you have not considered.

Slide 22

Maintaining Indexes Page Splits Insert/erase request and rate basic Fragmentation Not OS fragementation – e.g. defrag instrument Happens when database develops, pages split, and after that information inevitably erased. Btrees extraordinary on keeping up adjust on inclusions, however with erases, can wind up with numerous pages containing little # of records.

Slide 23

Fragmentation Problems Wasted space Sql server designates a stretch out at once Could wind up with a degree, containing single page, with single record. Whipping (much excessively numerous plate hits) Could wind up with page 1 of information on one augment, page 2 on another, page 3 on the primary, page 4 on another, … . Records everywhere Bit better for supplements in any case awful for peruses!

Slide 24

Identifying Fragmentation versus page parts DBCC SHOWCONTIG Page 283 Demo with northwind

Slide 25

DBREINDEX & Fillfactor DBCC DBREINDEX Can drop list and reconstruct Usually best to utilize drop-existing Completely modifies the file If supply table name, remakes all lists on table. Re-sets up base fillfactors and so on. Firmly suggest forbid exchanges while doing this. Reconstructing is most likely better.

Slide 26

Summary Clustered lists generally speedier than non-grouped Only place non-bunched files on segments with high selectivity (>95% of lines are one of a kind on that section) All information control dialect proclamations can profit, from files, yet embeds, erases, and upgrades are moderated. Lists consume up room and require page hits.

Slide 27

Summary Index utilized just if first section as a part of list is significant to question Indexes can sting as much as they Make beyond any doubt don\'t include one coincidentally. Lists can gave organized information execution to unstructured XML, yet overhead included.

Slide 28

Summary Is there an abnormal state of selectivity on the information? on the off chance that yes and is habitually focus of where condition, then include list Have I dropped lists I do not require anymore? Why not? Do I have an upkeep system set up? Why not?

Slide 29

Critical Questions Are there loads of supplements of adjustments to this table? On the off chance that yes, keep records to least Is this a reporting table? E.g. relatively few embeds yet loads of reports run a wide range of ways If yes, more files are fine. Is there an abnormal state of selectivity on the information? In the event that yes and is every now and again focus of where provision, then include list

View more...