Leitfaden zur Architektur und zum Design von SQL Server-IndizesSQL Server Index Architecture and Design Guide

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance JaAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics JaParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data WarehouseAnwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database JaVerwaltete Azure SQL-InstanzAzure SQL Managed InstanceYesVerwaltete Azure SQL-InstanzAzure SQL Managed Instance JaAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics JaParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

Schlecht entworfene oder fehlende Indizes sind die Hauptquellen für Engpässe der Datenbankanwendung.Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. Ein effizienter Indexentwurf ist zum Erzielen einer guten Datenbank- und Anwendungsleistung unabdinglich.Designing efficient indexes is paramount to achieving good database and application performance. Dieser Leitfaden zum Entwerfen von SQL ServerSQL Server-Indizes enthält Informationen zur Indexarchitektur und bewährte Methoden, die Sie beim Entwerfen effektiver Indizes unterstützen sollen, die den Anforderungen Ihrer Anwendung entsprechen.This SQL ServerSQL Server index design guide contains information on index architecture, and best practices to help you design effective indexes to meet the needs of your application.

In diesem Handbuch wird davon ausgegangen, dass der Leser grundsätzlich mit den in SQL ServerSQL Serververfügbaren Indextypen vertraut ist.This guide assumes the reader has a general understanding of the index types available in SQL ServerSQL Server. Eine allgemeine Beschreibung zu Indextypen finden Sie unter Indextypen.For a general description of index types, see Index Types.

Dieses Handbuch behandelt folgende Typen von Indizes:This guide covers the following types of indexes:

  • GruppiertClustered
  • Nicht gruppiertNonclustered
  • EindeutigUnique
  • FilteredFiltered
  • columnstoreColumnstore
  • HashHash
  • Speicheroptimiert, nicht gruppiertMemory-Optimized Nonclustered

Weitere Informationen zu XML-Indizes finden Sie unter XML-Indizes (SQL Server).For information about XML indexes, see XML Indexes Overview.

Weitere Informationen zu räumlichen Indizes finden Sie unter Übersicht über räumliche Indizes.For information about Spatial indexes, see Spatial Indexes Overview.

Weitere Informationen zu Volltextindizes finden Sie unter Auffüllen von Volltextindizes.For information about Full-text indexes, see Populate Full-Text Indexes.

Grundlagen des IndexentwurfsIndex Design Basics

Am Ende eines guten Sachbuchs befindet sich meist ein Index, über den bestimmte Informationen schnell und zielsicher innerhalb des Buchs ausfindig gemacht werden können.Think about a regular book: at the end of the book there is an index which helps to quickly locate information within the book. Ein Index ist eine sortierte Liste von Schlüsselwörtern, neben denen jeweils eine oder mehrere Seitenzahlen auf die Seiten verweisen, auf denen das jeweilige Schlüsselwort gefunden werden kann.The index is a sorted list of keywords and next to each keyword is a set of page numbers pointing to the pages where each keyword can be found. Der SQL Server-Index macht da keinen Unterschied: Er stellt eine sortierte Liste mit Werten dar, bei der jeweils Zeiger auf die Datenseiten hinweisen, auf denen sich diese Werte befinden.A SQL Server index is no different: it is an ordered list of values and for each value there are pointers to the data pages where these values are located. Der Index selbst wird auf Seiten gespeichert, die die Indexseiten in SQL Server bilden.The index itself is stored on pages, making up the Index Pages in SQL Server. In einem Buch umfasst der Index meist mehrere Seiten. Wenn Sie nun beispielsweise nach Verweisen auf alle Seiten mit dem Wort „SQL“ suchen, müssen Sie den Index bis zu der Seite durchblättern, die das Schlüsselwort „SQL“ enthält.In a regular book, if the index spans multiple pages and you have to find pointers to all the pages that contain the word "SQL" for example, you would have to leaf through until you locate the index page that contains the keyword "SQL". Von dort gelangen Sie dann über die Verweise zu den angegebenen Buchseiten.From there you follow the pointers to all the book pages. Dies ließe sich durch Hinzufügen einer Seite vor dem Index optimieren, auf der die Seitenzahlen für die einzelnen Buchstaben des Alphabets angegeben werden.This could be optimized further if at the very beginning of the index, you create a single page that contains an alphabetical list of where each letter can be found. Beispiel: „A bis D: Seite 121“, „E bis G: Seite 122“ usw.For example: "A through D - page 121", "E through G - page 122" and so on. Mit dieser zusätzlichen Seite entfiele der Schritt, den Index nach dem Ausgangspunkt durchsuchen zu müssen.This additional page would eliminate the step of leafing through the index to find the starting place. Eine solche Seite existiert in einem Buch meist nicht, in einem SQL Server-Index jedoch schon.Such page does not exist in regular books, but it does exist in a SQL Server index. Sie wird als Stammseite des Index bezeichnet.This single page is referred to as the root page of the index. Die Stammseite stellt die Startseite der Baumstruktur dar, die von einem SQL Server-Index verwendet wird.The root page is the starting page of the tree structure used by a SQL Server index. Folgt man der Baumanalogie, so werden die Seiten mit den Zeigern auf die jeweiligen Daten als „Blattseiten“ der Baumstruktur bezeichnet.Following the tree analogy, the end pages which contain pointers to the actual data are referred to as "leaf pages" of the tree.

Ein SQL Server-Index ist eine Struktur auf dem Datenträger oder im Arbeitsspeicher, die einer Tabelle oder einer Sicht zugeordnet ist und durch die das Abrufen von Zeilen aus der Tabelle oder Sicht beschleunigt wird.A SQL Server index is an on-disk or in-memory structure associated with a table or view that speeds retrieval of rows from the table or view. Ein Index enthält Schlüssel, die aus einer oder mehreren Spalten in der Tabelle oder Sicht erstellt werden.An index contains keys built from one or more columns in the table or view. Bei Indizes auf Datenträgern werden diese Schlüssel in einer Baumstruktur (B-Struktur) gespeichert, die es SQL Server ermöglicht, die den Schlüsselwerten zugeordnete(n) Zeile(n) schnell und effizient zu finden.For on-disk indexes, these keys are stored in a tree structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

In einem Index werden Daten logisch in Form einer Tabelle mit Zeilen und Spalten gespeichert und physisch in einem zeilenbezogenen Format namens Rowstore1 oder in einem spaltenbezogenen Format namens Columnstore .An index stores data logically organized as a table with rows and columns, and physically stored in a row-wise data format called rowstore 1, or stored in a column-wise data format called columnstore.

Das Auswählen der richtigen Indizes für eine Datenbank und ihre Arbeitsauslastung ist ein komplexer Vorgang, bei dem ein ausgewogenes Verhältnis zwischen gewünschter Abfragegeschwindigkeit und vertretbaren Updatekosten erzielt werden muss.The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. Schmale Indizes (Indizes mit wenigen Spalten im Indexschlüssel) erfordern weniger Speicherplatz und Wartungsaufwand.Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. Breite Indizes decken jedoch eine größere Anzahl an Abfragen ab.Wide indexes, on the other hand, cover more queries. Möglicherweise müssen Sie mit verschiedenen Entwürfen experimentieren, bevor Sie den effizientesten Index ermitteln.You may have to experiment with several different designs before finding the most efficient index. Indizes können ohne Auswirkungen auf das Datenbankschema oder den Anwendungsentwurf hinzugefügt, geändert und gelöscht werden.Indexes can be added, modified, and dropped without affecting the database schema or application design. Daher sollten Sie in jedem Fall mit verschiedenen Indizes experimentieren.Therefore, you should not hesitate to experiment with different indexes.

Der Abfrageoptimierer von SQL ServerSQL Server wählt in der Mehrzahl der Fälle zuverlässig den effektivsten Index aus.The query optimizer in SQL ServerSQL Server reliably chooses the most effective index in the vast majority of cases. Indexentwurfsstrategie sollte sein, dem Abfrageoptimierer eine Vielzahl von Indizes zur Auswahl bereitzustellen und sich darauf zu verlassen, dass dieser die richtige Entscheidung trifft.Your overall index design strategy should provide a variety of indexes for the query optimizer to choose from and trust it to make the right decision. Auf diese Weise wird die Analysezeit verkürzt und ein gutes Leistungsverhalten in vielen unterschiedlichen Situationen erzielt.This reduces analysis time and produces good performance over a variety of situations. Wenn Sie anzeigen möchten, welche Indizes der Abfrageoptimierer für eine bestimmte Abfrage verwendet, wählen Sie in SQL Server Management StudioSQL Server Management Studiodie Option Tatsächlichen Ausführungsplan einschließen aus dem Menü Abfrageaus.To see which indexes the query optimizer uses for a specific query, in SQL Server Management StudioSQL Server Management Studio, on the Query menu, select Include Actual Execution Plan.

Setzen Sie Indexverwendung aber nicht stets mit gutem Leistungsverhalten bzw. gute Leistung mit effizienter Indexverwendung gleich.Do not always equate index usage with good performance, and good performance with efficient index use. Würde durch die Verwendung eines Indexes in jedem Fall die beste Leistung erzielt, so wäre die Arbeit des Abfrageoptimierers sehr einfach.If using an index always helped produce the best performance, the job of the query optimizer would be simple. Tatsächlich kann die Auswahl eines falschen Indexes eine Leistung bewirken, die nicht optimal ist.In reality, an incorrect index choice can cause less than optimal performance. Daher besteht die Aufgabe des Abfrageoptimierers darin, einen Index oder eine Kombination aus Indizes nur dann auszuwählen, wenn die Leistung verbessert wird, und den indizierten Abruf zu vermeiden, wenn die Leistung negativ beeinflusst wird.Therefore, the task of the query optimizer is to select an index, or combination of indexes, only when it will improve performance, and to avoid indexed retrieval when it will hinder performance.

1 Rowstore stellte die herkömmliche Vorgehensweise beim Speichern von Daten aus relationalen Tabellen dar.1 Rowstore has been the traditional way to store relational table data. In SQL ServerSQL Server bezieht Rowstore sich auf die Tabelle, deren zugrunde liegendes Datenspeicherformat ein Heap, eine B-Struktur (d.h. ein gruppierter Index) oder eine speicheroptimierte Tabelle ist.In SQL ServerSQL Server, rowstore refers to table where the underlying data storage format is a heap, a B-tree (clustered index), or a memory-optimized table.

Aufgaben beim IndexentwurfIndex Design Tasks

Die folgenden Aufgaben fassen die empfohlene Strategie beim Entwerfen von Indizes zusammen:The follow tasks make up our recommended strategy for designing indexes:

  1. Verstehen der Merkmale der Datenbank selbst.Understand the characteristics of the database itself.

  2. Verstehen der Merkmale der am häufigsten verwendeten Abfragen.Understand the characteristics of the most frequently used queries. Wenn Sie z. B. wissen, dass eine häufig verwendete Abfrage zwei oder mehr Tabellen verknüpft, unterstützt Sie dieses Wissen beim Ermitteln der zu verwendenden effizientesten Indextypen.For example, knowing that a frequently used query joins two or more tables will help you determine the best type of indexes to use.

  3. Verstehen der Merkmale der in den Abfragen verwendeten Spalten.Understand the characteristics of the columns used in the queries. Ein Index eignet sich z. B. ideal für Spalten, die einen ganzzahligen Datentyp besitzen und außerdem eindeutige oder Nicht-NULL-Spalten sind.For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. Für Spalten, die klar definierte Teilmengen von Daten enthalten, können Sie in SQL Server 2008SQL Server 2008 und höheren Versionen einen gefilterten Index verwenden.For columns that have well-defined subsets of data, you can use a filtered index in SQL Server 2008SQL Server 2008 and higher versions. Weitere Informationen finden Sie unter Richtlinien für den Entwurf gefilterter Indizes in diesem Handbuch.For more information, see Filtered Index Design Guidelines in this guide.

  4. Ermitteln, welche Indexoptionen die Leistung steigern können, wenn der Index erstellt oder gewartet wird.Determine which index options might enhance performance when the index is created or maintained. Für das Erstellen eines gruppierten Indexes für eine vorhandene Tabelle ist z.B. die ONLINE-Indexoption nützlich.For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. Die ONLINE-Option ermöglicht, dass gleichzeitige Aktivitäten für die zugrunde liegenden Daten fortgesetzt werden können, während der Index erstellt oder neu erstellt wird.The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt. Weitere Informationen finden Sie unter Festlegen von Indexoptionen.For more information, see Set Index Options.

  5. Angeben des optimalen Speicherorts für den Index.Determine the optimal storage location for the index. Ein nicht gruppierter Index kann in derselben Dateigruppe wie die zugrunde liegende Tabelle oder in einer anderen Dateigruppe gespeichert werden.A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. Der Speicherort von Indizes kann die Abfrageleistung durch Optimieren der Datenträger-E/A-Leistung verbessern.The storage location of indexes can improve query performance by increasing disk I/O performance. Wenn Sie z. B. einen nicht gruppierten Index in einer Dateigruppe speichern, die sich auf einem anderen Datenträger als die Tabellendateigruppe befindet, kann dies die Leistung verbessern, weil mehrere Datenträger gleichzeitig gelesen werden können.For example, storing a nonclustered index on a filegroup that is on a different disk than the table filegroup can improve performance because multiple disks can be read at the same time.
    Alternativ können gruppierte und nicht gruppierte Indizes ein dateigruppenübergreifendes Partitionsschema verwenden.Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups. Durch die Partitionierung sind große Tabellen oder Indizes leichter zu verwalten, denn Sie können dadurch schnell und effizient auf Datenteilmengen zugreifen und sie verwalten, während die Integrität der gesamten Sammlung erhalten bleibt.Partitioning makes large tables or indexes more manageable by letting you access or manage subsets of data quickly and efficiently, while maintaining the integrity of the overall collection. Weitere Informationen finden Sie unter partitionierte Tabellen und Indizes.For more information, see Partitioned Tables and Indexes. Wenn Sie Partitionierung in Erwägung ziehen, müssen Sie festlegen, ob der Index ausgerichtet sein soll, d. h., ob er weitgehend wie die Tabelle oder unabhängig davon partitioniert werden soll.When you consider partitioning, determine whether the index should be aligned, that is, partitioned in essentially the same manner as the table, or partitioned independently.

Allgemeine Richtlinien zum IndexentwurfGeneral Index Design Guidelines

Erfahrene Datenbankadministratoren sind in der Lage, einen geeigneten Satz an Indizes zu entwerfen. Es handelt sich jedoch selbst bei gemäßigt komplexen Datenbanken und Arbeitsauslastungen um eine sehr komplexe, zeitintensive und fehleranfällige Aufgabe.Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. Das Verständnis der Merkmale der Datenbank, Abfragen und Datenspalten kann Sie beim Entwerfen optimaler Indizes unterstützen.Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

Überlegungen zu DatenbankenDatabase Considerations

Beachten Sie beim Entwerfen eines Indexes die folgenden Datenbankrichtlinien:When you design an index, consider the following database guidelines:

  • Eine große Anzahl von Indizes für eine Tabelle beeinträchtigt die Leistung von INSERT-, UPDATE-, DELETE-, und MERGE-Anweisungen, da alle Indizes entsprechend angepasst werden müssen, sobald Daten in der Tabelle geändert werden.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. Wenn eine Spalte beispielsweise in mehreren Indizes verwendet wird und Sie eine UPDATE-Anweisung ausführen, durch die Daten in dieser Spalte geändert werden, müssen alle Indizes, die diese Spalte enthalten, sowie die Spalte in der zugrunde liegenden Basistabelle (Heap oder gruppierter Index) ebenfalls aktualisiert werden.For example, if a column is used in several indexes and you execute an UPDATE statement that modifies that column's data, each index that contains that column must be updated as well as the column in the underlying base table (heap or clustered index).

    • Vermeiden Sie die zu starke Indizierung häufig aktualisierter Tabellen, und halten Sie die Indizes schmal, d. h., verwenden Sie so wenig Spalten wie möglich.Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible.

    • Verwenden Sie viele Indizes, um die Abfrageleistung für Tabellen zu verbessern, die geringe Updateanforderungen und große Datenmengen aufweisen.Use many indexes to improve query performance on tables with low update requirements, but large volumes of data. Eine große Anzahl an Indizes kann die Leistung von Abfragen steigern, durch die keine Daten geändert werden (z. B. SELECT-Anweisungen), da der Abfrageoptimierer aus einer größeren Anzahl an Indizes auswählen kann, um die beste Methode für den schnellstmöglichen Zugriff zu ermitteln.Large numbers of indexes can help the performance of queries that do not modify data, such as SELECT statements, because the query optimizer has more indexes to choose from to determine the fastest access method.

  • Das Indizieren kleiner Tabellen ist häufig nicht die optimale Methode, da der Abfrageoptimierer in diesem Fall mitunter mehr Zeit benötigt, um die Daten über einen Index zu suchen, als für die Durchführung eines einfachen Tabellenscans erforderlich wäre.Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. Aus diesem Grund werden Indizes für kleine Tabellen möglicherweise niemals verwendet, müssen jedoch trotzdem verwaltet werden, wenn sich Daten in der Tabelle ändern.Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

  • Indizes für Sichten können zu erheblichen Leistungsverbesserungen führen, wenn die Sicht Aggregationen, Tabellenjoins oder eine Kombination aus Aggregationen und Joins enthält.Indexes on views can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregations and joins. Es ist nicht erforderlich, dass in der Abfrage explizit auf die jeweilige Sicht verwiesen wird, damit der Abfrageoptimierer die Sicht verwendet.The view does not have to be explicitly referenced in the query for the query optimizer to use it.

  • Verwenden Sie den Datenbankoptimierungsratgeber, um die Datenbank zu analysieren und Indexempfehlungen zu erhalten.Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. Weitere Informationen finden Sie unter Database Engine Tuning Advisor.For more information, see Database Engine Tuning Advisor.

Überlegungen zu AbfragenQuery Considerations

Beachten Sie beim Entwerfen eines Indexes die folgenden Abfragerichtlinien:When you design an index, consider the following query guidelines:

  • Erstellen Sie nicht gruppierte Indizes für die Spalten, die häufig in Prädikaten und Joinbedingungen in Abfragen verwendet werden.Create nonclustered indexes on the columns that are frequently used in predicates and join conditions in queries. Dies sind Ihre SARGable1-Spalten.These are your SARGable1 columns. Sie sollten jedoch keine unnötigen Spalten hinzufügen.However, you should avoid adding unnecessary columns. Wenn Sie zu viele Indexspalten hinzufügen, kann sich dies negativ auf den Speicherplatz und die Indexverwaltungsleistung auswirken.Adding too many index columns can adversely affect disk space and index maintenance performance.

  • Abdeckende Indizes können die Abfrageleistung steigern, weil alle Daten im Index selbst enthalten sind, die die Anforderungen der Abfrage erfüllen.Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. Auf diese Weise muss nur auf die Indexseiten und nicht auf die Datenseiten der Tabelle oder des gruppierten Indexes verwiesen werden, um die abgefragten Daten abzurufen, wodurch der Umfang der E/A-Operationen des Datenträgers verringert wird.That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. Eine Abfrage der Spalten a und b für eine Tabelle, die einen zusammengesetzten Index besitzt, der für die Spalten a, bund c erstellt wurde, kann die angegebenen Daten ausschließlich aus dem Index abrufen.For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

    Wichtig

    Abdeckende Indizes sind die Bezeichnung für einen nicht gruppierten Index, der ein oder mehrere ähnliche Abfrageergebnisse direkt ohne Zugriff auf seine Basistabelle auflöst, und ohne dass Suchen erforderlich sind.Covering indexes are the designation for a nonclustered index that resolves one or several similar query results directly with no access to its base table, and without incurring in lookups. Derartige Indizes verfügen über alle erforderlichen Nicht-SARGable-Spalten auf Blattebene.Such indexes have all the necessary non-SARGable columns in its leaf level. Das bedeutet, dass die von der SELECT-Klausel zurückgegebenen Spalten und alle WHERE- und JOIN-Argumente durch den Index abgedeckt werden.This means that the columns returned by either the SELECT clause and all the WHERE and JOIN arguments are covered by the index. Es gibt potenziell viel weniger eingehenden und ausgehenden Datenverkehr, um die Abfrage auszuführen, wenn der Index im Vergleich zu den Zeilen und Spalten in der Tabelle selbst nah genug gefasst ist, was bedeutet, dass es sich um eine echte Teilmenge der gesamten Spalten handelt.There is potentially much less I/O to execute the query, if the index is narrow enough when compared to the rows and columns in the table itself, meaning it is a real sub-set of the total columns. Ziehen Sie abdeckende Indizes in Betracht, wenn Sie einen kleinen Teil einer großen Tabelle auswählen, und wenn dieser kleine Teil durch ein festes Prädikat definiert ist. Ein Beispiel hierfür sind Sparsespalten, die nur wenige Werte enthalten, die nicht NULL sind.Consider covering indexes when selecting a small portion of a large table, and where that small portion is defined by a fixed predicate, such as sparse columns that contain only a few non-NULL values, for example.

  • Schreiben Sie Abfragen, die möglichst viele Zeilen in einer einzigen Anweisung einfügen oder ändern, anstatt mehrere Abfragen zum Aktualisieren der gleichen Zeilen zu verwenden.Write queries that insert or modify as many rows as possible in a single statement, instead of using multiple queries to update the same rows. Wenn nur eine Anweisung verwendet wird, kann der Index optimal verwaltet werden.By using only one statement, optimized index maintenance could be exploited.

  • Werten Sie den Abfragetyp sowie die Art der Verwendung von Spalten in der Abfrage aus.Evaluate the query type and how columns are used in the query. Eine Spalte, die in einem Abfragetyp für genaue Übereinstimmung verwendet wird, ist z. B. ein guter Kandidat für einen nicht gruppierten oder gruppierten Index.For example, a column used in an exact-match query type would be a good candidate for a nonclustered or clustered index.

1 Der Begriff „SARGable“ in relationalen Datenbanken bezieht sich auf ein suchargumentfähiges Prädikat (Search ARGument-able), das mithilfe eines Indexes die Ausführung der Abfrage beschleunigen kann.1 The term SARGable in relational databases refers to a Search ARGument-able predicate that can leverage an index to speed up the execution of the query.

Überlegungen zu SpaltenColumn Considerations

Beachten Sie beim Entwerfen eines Indexes die folgenden Spaltenrichtlinien:When you design an index consider the following column guidelines:

  • Wählen Sie für gruppierte Indizes einen kurzen Indexschlüssel aus.Keep the length of the index key short for clustered indexes. Gruppierte Indizes bieten darüber hinaus den Vorteil, dass sie für eindeutige oder Nicht-NULL-Spalten erstellt werden.Additionally, clustered indexes benefit from being created on unique or nonnull columns.

  • Die Datentypen ntext, text, image, varchar(max) , nvarchar(max) und varbinary(max) können nicht als Indexschlüsselspalten angegeben werden.Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. varchar(max) , nvarchar(max) , varbinary(max) und xml -Datentypen können jedoch als Nichtschlüssel-Indexspalten in einen nicht gruppierten Index aufgenommen werden.However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. Weitere Informationen finden Sie im Abschnitt Index mit eingeschlossenen Spaltenin diesem Handbuch.For more information, see the section 'Index with Included Columns' in this guide.

  • Ein xml -Datentyp kann nur in einem XML-Index eine Schlüsselspalte sein.An xml data type can only be a key column only in an XML index. Weitere Informationen finden Sie unter XML-Indizes (SQL Server).For more information, see XML Indexes (SQL Server). Mit SQL Server 2012 SP1 wird ein neuer XML-Indextyp eingeführt, der als selektiver XML-Index bezeichnet wird.SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. Durch diesen neuen Index kann die Abfrageleistung bei Daten verbessert werden, die als XML in SQL Server gespeichert sind. Das sorgt für eine schnellere Indizierung großer XML-Datenmengen und für höhere Skalierbarkeit, indem die Speicherkosten des Indexes gesenkt werden.This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself. Weitere Informationen finden Sie unter Selektive XML-Indizes (SXI).For more information, see Selective XML Indexes (SXI).

  • Überprüfen Sie die Eindeutigkeit der Spalten.Examine column uniqueness. Ein eindeutiger Index anstelle eines nicht eindeutigen Indexes für dieselbe Kombination von Spalten stellt zusätzliche Informationen für den Abfrageoptimierer bereit, die den Index wertvoller machen.A unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that makes the index more useful. Weitere Informationen finden Sie unter Richtlinien zum Entwerfen eindeutiger Indizes in diesem Handbuch.For more information, see Unique Index Design Guidelines in this guide.

  • Überprüfen Sie die Datenverteilung in der Spalte.Examine data distribution in the column. Häufig dauert eine Abfrage deshalb sehr lange, weil eine indizierte Spalte mit wenigen eindeutigen Werten verwendet wird oder weil sie einen Join mit einer solchen Spalte durchführt.Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. Hierbei handelt es sich um ein grundlegendes Problem von Daten und Abfragen, das in der Regel nicht gelöst werden kann, ohne die betreffende Situation zu identifizieren.This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. Beispielsweise wird ein physisches Telefonbuch, das alphabetisch nach dem Nachnamen sortiert ist, das Suchen eines Teilnehmers nicht vereinfachen, wenn alle Teilnehmer des Ortsnetzes Smith oder Jones heißen.For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones. Weitere Informationen zur Datenverteilung finden Sie unter Statistics.For more information about data distribution, see Statistics.

  • Verwenden Sie für Spalten mit fest definierten Teilmengen, z. B. Sparsespalten, Spalten, die größtenteils NULL-Werte enthalten, Spalten mit Wertekategorien und Spalten mit verschiedenen Wertebereichen, gefilterte Indizes.Consider using filtered indexes on columns that have well-defined subsets, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. Ein gut entworfener gefilterter Index kann die Abfrageleistung verbessern, die Indexwartungskosten reduzieren und den Speicheraufwand verringern.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs.

  • Berücksichtigen Sie die Reihenfolge der Spalten, wenn der Index mehrere Spalten enthalten soll.Consider the order of the columns if the index will contain multiple columns. Die Spalte, die in der WHERE-Klausel in einer Gleich- (=), Größer als- (>), Kleiner als- (<) oder BETWEEN-Suchbedingung verwendet oder in einen Join eingeschlossen wird, sollte an erster Stelle stehen.The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Die Reihenfolge zusätzlicher Spalten sollte basierend auf dem Grad ihrer Eindeutigkeit, d. h. von der eindeutigsten Spalte absteigend zu der am wenigsten eindeutigen Spalte, ausgewählt werden.Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

    Wenn der Index z. B. als LastName, FirstName definiert ist, ist der Index hilfreich, wenn das Suchkriterium WHERE LastName = 'Smith' oder WHERE LastName = Smith AND FirstName LIKE 'J%'lautet.For example, if the index is defined as LastName, FirstName the index will be useful when the search criterion is WHERE LastName = 'Smith' or WHERE LastName = Smith AND FirstName LIKE 'J%'. Der Abfrageoptimierer verwendet den Index jedoch nicht für eine Abfrage, die nur nach FirstName (WHERE FirstName = 'Jane')sucht.However, the query optimizer would not use the index for a query that searched only on FirstName (WHERE FirstName = 'Jane').

  • Ziehen Sie das Indizieren berechneter Spalten in Betracht.Consider indexing computed columns. Weitere Informationen finden Sie unter Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

IndexmerkmaleIndex Characteristics

Wenn sich herausgestellt hat, dass ein Index für eine Abfrage geeignet ist, können Sie den Indextyp auswählen, der für die jeweilige Situation am besten geeignet ist.After you have determined that an index is appropriate for a query, you can select the type of index that best fits your situation. Die Indexmerkmale beziehen sich z. B. auf Folgendes:Index characteristics include the following:

  • Gruppiert im Vergleich zu nicht gruppiertClustered versus nonclustered
  • Eindeutig im Vergleich zu nicht eindeutigUnique versus nonunique
  • Einspaltig im Vergleich zu mehrspaltigSingle column versus multicolumn
  • Aufsteigende oder absteigende Reihenfolge in den Spalten des IndexesAscending or descending order on the columns in the index
  • Tabellenindizes im Vergleich zu gefilterten für nicht gruppierte IndizesFull-table versus filtered for nonclustered indexes
  • Vergleich von Columnstore und RowstoreColumnstore versus rowstore
  • Vergleich von Hashindizes und nicht gruppierten Indizes für speicheroptimierte TabellenHash versus nonclustered for Memory-Optimized tables

Um die Indexleistung- oder -wartung zu optimieren, können Sie durch das Festlegen einer Option wie z. B. FILLFACTOR auch die Ausgangsspeichermerkmale des Indexes anpassen.You can also customize the initial storage characteristics of the index to optimize its performance or maintenance by setting an option such as FILLFACTOR. Zudem können Sie den Speicherort des Indexes mithilfe von Dateigruppen oder Partitionsschemas festlegen, um die Leistung zu optimieren.Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

Indexplatzierung in Dateigruppen oder PartitionsschemasIndex Placement on Filegroups or Partitions Schemes

Bei der Entwicklung einer Indexentwurfsstrategie sollten Sie die Platzierung der Indizes in den Dateigruppen berücksichtigen, die der Datenbank zugeordnet sind.As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database. Das sorgfältige Auswählen der Dateigruppe oder des Partitionsschemas kann die Abfrageleistung verbessern.Careful selection of the filegroup or partition scheme can improve query performance.

Standardmäßig werden Indizes in derselben Dateigruppe wie die Basistabelle gespeichert, für die der Index erstellt wird.By default, indexes are stored in the same filegroup as the base table on which the index is created. Ein nicht partitionierter, gruppierter Index und die Basistabelle befinden sich immer in der gleichen Dateigruppe.A nonpartitioned clustered index and the base table always reside in the same filegroup. Sie können jedoch folgende Aktionen ausführen:However, you can do the following:

  • Erstellen nicht gruppierter Indizes in einer anderen Dateigruppe als der Dateigruppe der Basistabelle oder des gruppierten Indexes.Create nonclustered indexes on a filegroup other than the filegroup of the base table or clustered index.
  • Partitionieren von gruppierten und nicht gruppierten Indizes, damit diese mehrere Dateigruppen umfassen.Partition clustered and nonclustered indexes to span multiple filegroups.
  • Verschieben einer Tabelle aus einer Dateigruppe in eine andere durch Löschen des gruppierten Index und Angeben einer neuen Dateigruppe oder eines Partitionsschemas in der MOVE TO-Klausel der DROP INDEX-Anweisung oder durch Verwenden der CREATE INDEX-Anweisung mit der DROP_EXISTIN-Klausel.Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.

Wird der nicht gruppierte Index in einer anderen Dateigruppe erstellt, können Leistungsvorteile erzielt werden, wenn die Dateigruppen verschiedene physische Laufwerke mit eigenen Controllern verwenden.By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. Daten und Indexinformationen können dann parallel von mehreren Leseköpfen gelesen werden.Data and index information can then be read in parallel by the multiple disk heads. Werden beispielsweise Table_A in Dateigruppe f1 und Index_A in Dateigruppe f2 von derselben Abfrage verwendet, können Leistungsvorteile erzielt werden, da beide Dateigruppen konfliktfrei vollständig verwendet werden können.For example, if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used without contention. Wenn Table_A von der Abfrage durchsucht wird, ohne dass auf Index_A verwiesen wird, wird jedoch nur Dateigruppe f1 verwendet.However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. In diesem Fall ist kein Leistungsgewinn zu verzeichnen.This creates no performance gain.

Da jedoch nicht vorhersehbar ist, welche Zugriffsart wann erfolgt, ist die Entscheidung für das Verteilen der Tabellen und Indizes auf alle Dateigruppen häufig die bessere Lösung.Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. So ist sichergestellt, dass unabhängig von der Art des Datenzugriffs auf alle Datenträger zugegriffen wird, da alle Daten und Indizes gleichmäßig auf alle Datenträger verteilt sind.This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. Diese Lösung ist auch aus Systemadministratorensicht einfacher.This is also a simpler approach for system administrators.

Partitionen über mehrere DateigruppenPartitions across multiple Filegroups

Sie können auch das Partitionieren von gruppierten und nicht gruppierten Indizes über mehrere Dateigruppen hinweg in Betracht ziehen.You can also consider partitioning clustered and nonclustered indexes across multiple filegroups. Partitionierte Indizes werden horizontal oder nach Zeile basierend auf einer Partitionsfunktion partitioniert.Partitioned indexes are partitioned horizontally, or by row, based on a partition function. Die Partitionsfunktion definiert, wie jede einzelne Zeile einer Gruppe von Partitionen auf der Grundlage der Werte bestimmter Spalten zugeordnet wird, die als Partitionierungsspalten bezeichnet werden.The partition function defines how each row is mapped to a set of partitions based on the values of certain columns, called partitioning columns. Ein Partitionsschema gibt die Zuordnung dieser Partitionen zu einer Sammlung von Dateigruppen an.A partition scheme specifies the mapping of the partitions to a set of filegroups.

Das Partitionieren eines Indexes kann die folgenden Vorteile bieten:Partitioning an index can provide the following benefits:

  • Bereitstellen skalierbarer Systeme, die die Verwaltbarkeit großer Indizes vereinfachen.Provide scalable systems that make large indexes more manageable. OLTP-Systeme können z. B. partitionsabhängige Anwendungen implementieren, die große Indizes verwalten können.OLTP systems, for example, can implement partition-aware applications that deal with large indexes.

  • Schnellere und effizientere Ausführung von Abfragen.Make queries run faster and more efficiently. Wenn Abfragen auf mehrere Partitionen eines Indexes zugreifen, kann der Abfrageoptimierer einzelne Partitionen gleichzeitig verarbeiten und Partitionen ausschließen, die nicht von der Abfrage betroffen sind.When queries access several partitions of an index, the query optimizer can process individual partitions at the same time and exclude partitions that are not affected by the query.

Weitere Informationen finden Sie unter partitionierte Tabellen und Indizes.For more information, see Partitioned Tables and Indexes.

Entwurfsrichtlinien zur Sortierreihenfolge von IndizesIndex Sort Order Design Guidelines

Beim Definieren von Indizes sollten Sie berücksichtigen, ob die Daten für die Indexschlüsselspalte in aufsteigender oder absteigender Reihenfolge gespeichert werden sollen.When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. Die Standardreihenfolge ist aufsteigend. Hierbei wird auch die Kompatibilität mit früheren Versionen von SQL ServerSQL Serverbeibehalten.Ascending is the default and maintains compatibility with earlier versions of SQL ServerSQL Server. Die Syntax der CREATE INDEX-, CREATE TABLE- und ALTER TABLE-Anweisungen unterstützt die Schüsselwörter ASC (aufsteigend) und DESC (absteigend) für einzelne Spalten in Indizes und Einschränkungen:The syntax of the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements supports the keywords ASC (ascending) and DESC (descending) on individual columns in indexes and constraints.

Das Angeben der Reihenfolge, in der die Schlüsselwerte in einem Index gespeichert werden, ist sinnvoll, wenn Abfragen, die auf die Tabelle verweisen, über ORDER BY-Klauseln verfügen, die verschiedene Richtungen für die Schlüsselspalten in dem entsprechenden Index angeben.Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. In diesen Fällen kann der Index dafür sorgen, dass kein SORT-Operator mehr im Abfrageplan benötigt wird, wodurch die Abfrage effizienter wird.In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient. Die Mitarbeiter der Einkaufsabteilung von Adventure Works CyclesAdventure Works Cycles müssen beispielsweise die Qualität der Produkte, die sie von den Anbietern kaufen, bewerten.For example, the buyers in the Adventure Works CyclesAdventure Works Cycles purchasing department have to evaluate the quality of products they purchase from vendors. Die Einkäufer sind vor allem interessiert daran, Produkte zu finden, die von diesen Anbietern gesendet wurden und die eine hohe Ablehnungsrate aufweisen.The buyers are most interested in finding products sent by these vendors with a high rejection rate. Wie in der folgenden Abfrage gezeigt, muss zum Abrufen der Daten, die diese Anforderung erfüllen, die RejectedQty -Spalte in der Purchasing.PurchaseOrderDetail -Tabelle in absteigender Reihenfolge (von groß nach klein) sortiert werden, und die ProductID -Spalte muss in aufsteigender Reihenfolge (von klein nach groß) sortiert werden.As shown in the following query, retrieving the data to meet this criteria requires the RejectedQty column in the Purchasing.PurchaseOrderDetail table to be sorted in descending order (large to small) and the ProductID column to be sorted in ascending order (small to large).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;  

Der folgende Ausführungsplan für diese Abfrage zeigt, dass der Abfrageoptimierer einen SORT-Operator verwendet hat, um das Resultset in der durch die ORDER BY-Klausel angegebenen Reihenfolge zurückzugeben.The following execution plan for this query shows that the query optimizer used a SORT operator to return the result set in the order specified by the ORDER BY clause.

IndexSort1

Falls ein Index mit Schlüsselspalten erstellt wird, die mit jenen in der ORDER BY-Klausel in der Abfrage übereinstimmen, kann der SORT-Operator im Abfrageplan gelöscht werden, wodurch der Abfrageplan effizienter wird.If an index is created with key columns that match those in the ORDER BY clause in the query, the SORT operator can be eliminated in the query plan and the query plan is more efficient.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);  

Nachdem die Abfrage erneut ausgeführt wurde, zeigt folgender Ausführungsplan, dass der SORT-Operator gelöscht wurde und der neu erstellte nicht gruppierte Index verwendet wird.After the query is executed again, the following execution plan shows that the SORT operator has been eliminated and the newly created nonclustered index is used.

InsertSort2

Datenbank-EngineDatabase Engine bewegt sich in beide Richtungen gleichermaßen effizient.The Datenbank-EngineDatabase Engine can move equally efficiently in either direction. Ein als (RejectedQty DESC, ProductID ASC) definierter Index kann nach wie vor für eine Abfrage verwendet werden, in der die Sortierreihenfolge der Spalten in der ORDER BY-Klausel reserviert sind.An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. Eine Abfrage mit der ORDER BY-Klausel ORDER BY RejectedQty ASC, ProductID DESC kann den Index beispielsweise verwenden.For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

Die Sortierreihenfolge kann nur für die Schlüsselspalten im Index angegeben werden.Sort order can be specified only for the key columns in index. Die Katalogsicht sys.index_columns und die INDEXKEY_PROPERTY-Funktion melden, ob eine Indexspalte in aufsteigender oder absteigender Reihenfolge gespeichert wird.The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

MetadatenMetadata

Verwenden Sie diese Metadatenansichten, um die Attribute des Indizes anzuzeigen.Use these metadata views to see attributes of indexes. Weitere Informationen zur Architektur sind in einigen Ansichten eingebettet.More architectural information is embedded in some of these views.

Hinweis

Bei Columnstore-Indizes werden alle Spalten in den Metadaten als eingeschlossene Spalten gespeichert.For columnstore indexes, all columns are stored in the metadata as included columns. Der Columnstore-Index weist keine Schlüsselspalten auf.The columnstore index does not have key columns.

Richtlinien für den Entwurf gruppierter IndizesClustered Index Design Guidelines

Gruppierte Indizes sortieren und speichern die Datenzeilen in den Tabellen basierend auf ihren Schlüsselwerten.Clustered indexes sort and store the data rows in the table based on their key values. Pro Tabelle kann nur ein gruppierter Index vorhanden sein, da die Datenzeilen nur in einer Reihenfolge sortiert werden können.There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. Mit wenigen Ausnahmen sollte für jede Tabelle ein gruppierter Index für die Spalte(n) definiert werden, auf die Folgendes zutrifft:With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  • Sie kann für häufig verwendete Abfragen verwendet werden.Can be used for frequently used queries.

  • Sie stellt einen hohen Grad an Eindeutigkeit bereit.Provide a high degree of uniqueness.

    Hinweis

    Wenn Sie eine PRIMARY KEY-Einschränkung erstellen, wird automatisch ein eindeutiger Index für die Spalte(n) erstellt.When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. Standardmäßig ist dieser Index gruppiert; Sie können jedoch auch einen nicht gruppierten Index angeben, wenn Sie die Einschränkung erstellen.By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

  • Sie kann in Bereichsabfragen verwendet werden.Can be used in range queries.

Wenn der gruppierte Index nicht mit der UNIQUE-Eigenschaft erstellt wird, fügt die Datenbank-EngineDatabase Engine der Tabelle automatisch eine 4 Byte große Uniqueifier-Spalte hinzu.If the clustered index is not created with the UNIQUE property, the Datenbank-EngineDatabase Engine automatically adds a 4-byte uniqueifier column to the table. Falls erforderlich, fügt das Datenbank-EngineDatabase Engine einer Zeile automatisch einen uniqueifier-Wert hinzu, um jeden Schlüssel eindeutig zu machen.When it is required, the Datenbank-EngineDatabase Engine automatically adds a uniqueifier value to a row to make each key unique. Diese Spalte und ihre Werte werden intern verwendet und können durch Benutzer nicht angezeigt werden. Der Zugriff durch Benutzer auf diese ist ebenfalls nicht möglich.This column and its values are used internally and cannot be seen or accessed by users.

Architektur gruppierter IndizesClustered Index Architecture

In SQL ServerSQL Server sind Indizes in Form von B-Strukturen aufgebaut.In SQL ServerSQL Server, indexes are organized as B-Trees. Jede Seite in der B-Struktur eines Indexes wird als Indexknoten bezeichnet.Each page in an index B-tree is called an index node. Der oberste Knoten der B-Struktur wird als Stammknoten bezeichnet.The top node of the B-tree is called the root node. Die unteren Knoten im Index werden als Blattknoten bezeichnet.The bottom nodes in the index are called the leaf nodes. Alle anderen Indexebenen zwischen dem Stamm- und den Blattknoten werden zusammenfassend als Zwischenebenen bezeichnet.Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In einem gruppierten Index enthalten die Blattknoten die Datenseiten der zugrunde liegenden Tabelle.In a clustered index, the leaf nodes contain the data pages of the underlying table. Die Stamm- und Zwischenebenenknoten enthalten Indexseiten, in denen Indexzeilen enthalten sind.The root and intermediate level nodes contain index pages holding index rows. Jede Indexzeile enthält einen Schlüsselwert und einen Zeiger auf eine Seite einer Zwischenebene in der B-Struktur oder auf eine Datenzeile in der Blattebene des Indexes.Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. Die Seiten auf jeder Ebene des Indexes sind durch eine doppelt verknüpfte Liste miteinander verknüpft.The pages in each level of the index are linked in a doubly-linked list.

Gruppierte Indizes besitzen eine Zeile in sys.partitions, wobei index_id = 1 für jede Partition, die vom Index verwendet wird.Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. Standardmäßig besitzt ein gruppierter Index eine Partition.By default, a clustered index has a single partition. Wenn ein gruppierter Index über mehrere Partitionen verfügt, besitzt jede Partition eine B-Struktur, die die Daten für diese bestimmte Partition enthält.When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. Wenn ein gruppierter Index z. B. vier Partitionen besitzt, sind vier B-Strukturen vorhanden, eine in jeder Partition.For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

Abhängig von den Datentypen im gruppierten Index weist jede gruppierte Indexstruktur eine oder mehrere Zuordnungseinheiten auf, in denen die Daten für eine bestimmte Partition gespeichert und verwaltet werden.Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. Jeder gruppierte Index weist mindestens eine IN_ROW_DATA-Zuordnungseinheit pro Partition auf.At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. Der gruppierte Index besitzt außerdem eine LOB_DATA-Zuordnungseinheit pro Partition, wenn LOB-Spalten (Large Object) vorhanden sind.The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. Außerdem ist eine ROW_OVERFLOW_DATA-Zuordnungseinheit pro Partition vorhanden, wenn der Index Spalten variabler Länge aufweist, die die Zeilengrößenbeschränkung von 8.060 Byte übersteigen.It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

Die Seiten in der Datenkette und die darin enthaltenen Zeilen werden anhand des Werts des Schlüssels des gruppierten Indexes angeordnet.The pages in the data chain and the rows in them are ordered on the value of the clustered index key. Jede Einfügung wird an der Position vorgenommen, die der Schlüsselwert der eingefügten Zeile in der Reihenfolge vorhandener Zeilen einnimmt.All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

Die folgende Abbildung veranschaulicht die Struktur eines gruppierten Indexes in einer einzelnen Partition.This illustration shows the structure of a clustered index in a single partition.

bokind2

Überlegungen zu AbfragenQuery Considerations

Bevor Sie gruppierte Indizes erstellen, sollten Sie sich überlegen, wie der Zugriff auf die Daten erfolgt.Before you create clustered indexes, understand how your data will be accessed. Einen gruppierten Index können Sie für Abfragen verwenden, die die folgenden Aktionen durchführen:Consider using a clustered index for queries that do the following:

  • Sie geben einen Wertebereich zurück, indem z.B. folgende Operatoren verwendet werden: BETWEEN, >, >=, < und <=.Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

    Sobald mithilfe des gruppierten Indexes die Zeile mit dem ersten Wert gefunden wird, ist sichergestellt, dass Zeilen mit nachfolgenden Indexwerten physisch benachbart sind.After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. Wenn mit einer Abfrage z. B. Datensätze aus einem Bereich von Verkaufsauftragsnummern abgerufen werden, bietet ein gruppierter Index für die SalesOrderNumber -Spalte die Möglichkeit, schnell die Zeile zu finden, die die Start-Verkaufsauftragsnummer enthält, und dann alle nachfolgenden Zeilen in der Tabelle abzurufen, bis die letzte Verkaufsauftragsnummer erreicht ist.For example, if a query retrieves records between a range of sales order numbers, a clustered index on the column SalesOrderNumber can quickly locate the row that contains the starting sales order number, and then retrieve all successive rows in the table until the last sales order number is reached.

  • Zurückgeben umfangreicher Resultsets.Return large result sets.

  • Sie verwenden JOIN-Klauseln. In der Regel handelt es sich dabei um Fremdschlüsselspalten.Use JOIN clauses; typically these are foreign key columns.

  • Sie verwenden ORDER BY- oder GROUP BY-Klauseln.Use ORDER BY or GROUP BY clauses.

    Durch einen Index für Spalten, die in der ORDER BY- oder GROUP BY-Klausel angegeben werden, entfällt ggf. die Notwendigkeit für Datenbank-EngineDatabase Engine , die Daten zu sortieren, da die Zeilen bereits sortiert sind.An index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Datenbank-EngineDatabase Engine to sort the data, because the rows are already sorted. Die Abfrageleistung wird somit verbessert.This improves query performance.

Überlegungen zu SpaltenColumn Considerations

Die Definition des gruppierten Indexschlüssels sollte im Allgemeinen so wenig Spalten wie möglich umfassen.Generally, you should define the clustered index key with as few columns as possible. Ziehen Sie Spalten in Betracht, auf die ein oder mehrere der folgenden Merkmale zutreffen:Consider columns that have one or more of the following attributes:

  • Sie sind eindeutig oder enthalten zahlreiche unterschiedliche Werte.Are unique or contain many distinct values

    Eine Mitarbeiter-ID identifiziert einen Mitarbeiter z. B. eindeutig.For example, an employee ID uniquely identifies employees. Ein gruppierter Index oder eine PRIMARY KEY-Einschränkung für die EmployeeID-Spalte verbessert die Leistung von Abfragen, durch die Mitarbeiterinformationen anhand der Mitarbeiter-ID gesucht werden.A clustered index or PRIMARY KEY constraint on the EmployeeID column would improve the performance of queries that search for employee information based on the employee ID number. Alternativ kann ein gruppierter Index für LastName, FirstNameund MiddleName erstellt werden, weil Mitarbeiterdatensätze häufig auf diese Weise gruppiert und abgefragt werden und die Kombination dieser Spalten jedoch trotzdem einen hohen Differenzierungsgrad bietet.Alternatively, a clustered index could be created on LastName, FirstName, MiddleName because employee records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

    Tipp

    Wenn dies nicht anders angegeben ist, erstellt SQL ServerSQL Server beim Erstellen einer PRIMARY KEY-Einschränkung einen gruppierten Index, um diese Einschränkung zu unterstützen.If not specified differently, when creating a PRIMARY KEY constraint, SQL ServerSQL Server creates a clustered index to support that constraint. Obwohl ein uniqueidentifier -Datentyp verwendet werden kann, um Eindeutigkeit als PRIMARY KEY zu erzwingen, handelt es sich dabei nicht um einen effizienten Gruppierungsschlüssel.Although a uniqueidentifier can be used to enforce uniqueness as a PRIMARY KEY, it is not an efficient clustering key. Wenn Sie einen uniqueidentifier-Datentyp als PRIMARY KEY verwenden, wird empfohlen, diesen als nicht gruppierten Index zu erstellen und eine andere Spalte (z.B. IDENTITY) zu verwenden, um den gruppierten Index zu erstellen.If using a uniqueidentifier as PRIMARY KEY, the recommendation is to create it as a nonclustered index, and use another column such as an IDENTITY to create the clustered index.

  • Der Zugriff auf sie erfolgt sequenziell.Are accessed sequentially

    Durch eine Produkt-ID werden Produkte in der Production.Product -Tabelle der AdventureWorks2012AdventureWorks2012 -Datenbank beispielsweise eindeutig identifiziert.For example, a product ID uniquely identifies products in the Production.Product table in the AdventureWorks2012AdventureWorks2012 database. Abfragen, in denen eine sequenzielle Suche angegeben wird, z. B. WHERE ProductID BETWEEN 980 and 999, profitieren von einem gruppierten Index für ProductID.Queries in which a sequential search is specified, such as WHERE ProductID BETWEEN 980 and 999, would benefit from a clustered index on ProductID. Die Ursache liegt darin, dass die Zeilen für diese Schlüsselspalte in sortierter Reihenfolge gespeichert werden.This is because the rows would be stored in sorted order on that key column.

  • Sie sind als IDENTITY definiert.Defined as IDENTITY.

  • Sie wird häufig verwendet, um die aus einer Tabelle abgerufenen Daten zu sortieren.Used frequently to sort the data retrieved from a table.

    Es kann sinnvoll sein, die Tabelle für die betreffende Spalte zu gruppieren (d. h., physisch zu sortieren), damit nicht bei jeder Abfrage der Spalte Kosten für einen Sortiervorgang entstehen.It can be a good idea to cluster, that is physically sort, the table on that column to save the cost of a sort operation every time the column is queried.

Die Verwendung eines gruppierten Indexes ist nicht empfehlenswert, wenn die Spalten die folgenden Merkmale aufweisen:Clustered indexes are not a good choice for the following attributes:

  • Spalten, die häufig geändert werden.Columns that undergo frequent changes

    In diesem Fall würde die gesamte Zeile verschoben, weil Datenbank-EngineDatabase Engine die physische Reihenfolge der Datenwerte der Zeile aufrechterhalten muss.This causes in the whole row to move, because the Datenbank-EngineDatabase Engine must keep the data values of a row in physical order. Dieser Aspekt sollte insbesondere bei Systemen berücksichtigt werden, in denen Transaktionsverarbeitung in großem Umfang erfolgt und Daten nur selten von Dauer sind.This is an important consideration in high-volume transaction processing systems in which data is typically volatile.

  • Ausführliche Schlüssel.Wide keys

    Ausführliche Schlüssel sind aus mehreren Spalten oder mehreren großen Spalten zusammengesetzt.Wide keys are a composite of several columns or several large-size columns. Die Schlüsselwerte aus dem gruppierten Index werden von allen nicht gruppierten Indizes als Suchschlüssel verwendet.The key values from the clustered index are used by all nonclustered indexes as lookup keys. Alle nicht gruppierten Indizes, die für dieselbe Tabelle definiert werden, sind erheblich größer, da die Einträge des nicht gruppierten Indexes den Gruppierungsschlüssel sowie die Schlüsselspalten enthalten, die für diesen nicht gruppierten Index definiert wurden.Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.

Entwurfsrichtlinien für einen nicht gruppierten IndexNonclustered Index Design Guidelines

Ein nicht gruppierter Index enthält Indexschlüsselwerte sowie Zeilenlokatoren, die auf den Speicherort der Tabellendaten verweisen.A nonclustered index contains the index key values and row locators that point to the storage location of the table data. Sie können mehrere nicht gruppierte Indizes für eine Tabelle oder eine indizierte Sicht erstellen.You can create multiple nonclustered indexes on a table or indexed view. Im Allgemeinen sollten nicht gruppierte Indizes so entworfen werden, dass sich die Leistung häufig verwendeter Abfragen verbessert, die nicht vom gruppierten Index abgedeckt werden.Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

Vergleichbar mit der Art und Weise wie Sie einen Index in einem Buch verwenden, sucht der Abfrageoptimierer nach einem Datenwert, indem er den nicht gruppierten Index durchsucht, um so den Speicherort des Datenwerts in der Tabelle zu ermitteln. Anschließend werden die Daten direkt von diesem Speicherort abgerufen.Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. Aus diesem Grund sind nicht gruppierte Indizes optimal für Abfragen geeignet, die nach genauen Übereinstimmungen suchen, da der Index Einträge enthält, die in der Tabelle den genauen Speicherort der Datenwerte beschreiben, die in den Abfragen gesucht werden.This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. Wenn beispielsweise die HumanResources. Employee -Tabelle nach sämtlichen Mitarbeitern abgefragt werden soll, die einem bestimmten Abteilungsleiter unterstehen, verwendet der Abfrageoptimierer möglicherweise den nicht gruppierten Index IX_Employee_ManagerID; hier ist ManagerID die Schlüsselspalte.For example, to query the HumanResources. Employee table for all employees that report to a specific manager, the query optimizer might use the nonclustered index IX_Employee_ManagerID; this has ManagerID as its key column. Der Abfrageoptimierer kann schnell alle Einträge im Index finden, die mit der angegebenen ManagerIDübereinstimmen.The query optimizer can quickly find all entries in the index that match the specified ManagerID. Jeder Indexeinträg verweist auf die genaue Seite und Zeile in der Tabelle bzw. im gruppierten Index, in der die entsprechenden Daten zu finden sind.Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. Nachdem der Abfrageoptimierer sämtliche Einträge im Index gefunden hat, kann er sich direkt zu der genauen Seite und Zeile begeben, um die Daten abzurufen.After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

Architektur nicht gruppierter IndizesNonclustered Index Architecture

Nicht gruppierte Indizes weisen dieselbe B-Baumstruktur auf wie gruppierte Indizes, mit Ausnahme der beiden folgenden signifikanten Unterschiede:Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

  • Die Datenzeilen der zugrunde liegenden Tabelle werden nicht auf der Grundlage ihrer nicht gruppierten Schlüssel sortiert und gespeichert.The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

  • Die Blattebene eines nicht gruppierten Indexes besteht aus Indexseiten anstelle von Datenseiten.The leaf level of a nonclustered index is made up of index pages instead of data pages.

Zeilenlokatoren in nicht gruppierten Indexzeilen bestehen entweder aus Zeigern, die auf jeweils eine Zeile zeigen, oder aus einem Schlüssel eines gruppierten Indexes für eine Zeile, wie im Folgenden erläutert:The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

  • Wenn es sich bei der Tabelle um einen Heap handelt (d. h. sie hat keinen gruppierten Index), entspricht der Zeilenlokator einem Zeiger auf die Zeile.If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. Der Zeiger setzt sich aus dem Dateibezeichner (ID), der Seitennummer und der Nummer der Zeile auf der Seite zusammen.The pointer is built from the file identifier (ID), page number, and number of the row on the page. Der ganze Zeiger wird als Zeilen-ID (RID) bezeichnet.The whole pointer is known as a Row ID (RID).

  • Wenn die Tabelle einen gruppierten Index besitzt oder der Index für eine indizierte Sicht erstellt wurde, ist der Zeilenlokator der Schlüssel des gruppierten Indexes für die Zeile.If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

Nicht gruppierte Indizes verfügen in sys.partitions für jede vom Index verwendete Partition über eine Zeile, deren index_id >1 ist.Nonclustered indexes have one row in sys.partitions with index_id > 1 for each partition used by the index. Standardmäßig besitzen nicht gruppierte Indizes nur eine Partition.By default, a nonclustered index has a single partition. Wenn ein nicht gruppierter Index mehrere Partitionen besitzt, weist jede Partition eine B-Baumstruktur auf, die die Indexzeilen der entsprechenden Partition enthält.When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. Wenn ein nicht gruppierter Index beispielsweise vier Partitionen besitzt, gibt es vier B-Baumstrukturen – jeweils eine in jeder Partition.For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

Abhängig von den Datentypen des nicht gruppierten Indexes erhält jede Struktur mindestens eine Zuordnungseinheit, in der die Daten einer bestimmten Partition gespeichert und verwaltet werden.Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. Jeder nicht gruppierte Index besitzt also eine IN_ROW_DATA-Zuordnungseinheit pro Partition, die die B-Strukturseiten des Indexes speichert.At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. Außerdem besitzt der nicht gruppierte Index eine LOB_DATA-Zuordnungseinheit, wenn er LOB-Spalten (Large Object) enthält.The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. Darüber hinaus besitzt er eine ROW_OVERFLOW_DATA-Zuordnungseinheit pro Partition, wenn er Spalten variabler Länge enthält, die die Zeilengrößenbeschränkung von 8.060 Byte überschreiten.Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

Die folgende Abbildung veranschaulicht die Struktur eines gruppierten Indexes einer einzigen Partition.The following illustration shows the structure of a nonclustered index in a single partition.

bokind1a

Überlegungen zu DatenbankenDatabase Considerations

Berücksichtigen Sie beim Entwerfen nicht gruppierter Indizes die Merkmale der jeweiligen Datenbank.Consider the characteristics of the database when designing nonclustered indexes.

  • Datenbanken oder Tabellen mit geringen Updateanforderungen, aber großem Datenvolumen, können von zahlreichen nicht gruppierten Indizes zur Verbesserung der Abfrageleistung profitieren.Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. Mit gefilterten Indizes mit klar definierten Teilmengen von Daten können Sie im Vergleich zu nicht gruppierten Tabellenindizes die Abfrageleistung verbessern und den Aufwand für die Indexverwaltung und die Indexspeicherung reduzieren.Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes.

    Entscheidungsunterstützungssystem-Anwendungen und -Datenbanken, die hauptsächlich schreibgeschützte Daten enthalten, können von zahlreichen nicht gruppierten Indizes profitieren.Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. Der Abfrageoptimierer hat hinsichtlich des Ermittelns der schnellsten Zugriffsmethode eine größere Auswahl an Indizes, und da die Datenbankmerkmale auf einen geringen Updateaufwand schließen lassen, wird die Leistung durch die Indexwartung nicht beeinträchtigt.The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database mean index maintenance will not impede performance.

  • Bei Anwendungen und Datenbanken zur Onlinetransaktionsverarbeitung (OLTP) mit Tabellen, die umfassend aktualisiert werden, sollte ein Übermaß an Indizierung vermieden werden.Online Transaction Processing (OLTP) applications and databases that contain heavily updated tables should avoid over-indexing. Zusätzlich sollten die Indizes schmal sein, also so wenig Spalten wie möglich enthalten.Additionally, indexes should be narrow, that is, with as few columns as possible.

    Eine große Anzahl an Indizes für eine Tabelle beeinträchtigt die Leistung von INSERT-, UPDATE-, DELETE- und MERGE-Anweisungen, da alle Indizes entsprechend angepasst werden müssen, sobald Daten in der Tabelle geändert werden.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes.

Überlegungen zu AbfragenQuery Considerations

Bevor Sie nicht gruppierte Indizes erstellen, sollten Sie sich darüber im Klaren sein, wie der Zugriff auf Ihre Daten erfolgt.Before you create nonclustered indexes, you should understand how your data will be accessed. Erwägen Sie das Verwenden eines nicht gruppierten Indexes für Abfragen mit folgenden Attributen:Consider using a nonclustered index for queries that have the following attributes:

  • Sie verwenden JOIN- oder GROUP BY-Klauseln.Use JOIN or GROUP BY clauses.

    Erstellen Sie mehrere nicht gruppierte Indizes für Spalten, die an Join- und Gruppierungsvorgängen beteiligt sind, sowie einen gruppierten Index für eine beliebige Fremdschlüsselspalte.Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

  • Kein Zurückgeben großer Resultsets.Queries that do not return large result sets.

    Erstellen Sie gefilterte Indizes für Abfragen, die eine klar definierte Zeilenteilmenge aus einer großen Tabelle zurückgeben.Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.

    Tipp

    In der Regel stimmt die WHERE-Klausel der CREATE INDEX-Anweisung mit der WHERE-Klausel einer Abfrage überein, die gerade abgedeckt wird.Typically the WHERE clause of the CREATE INDEX statement matches the WHERE clause of a query being covered.

  • Enthalten von Spalten, die häufig an Suchbedingungen einer Abfrage beteiligt sind (z. B. WHERE-Klausel), die genaue Übereinstimmungen zurückgeben.Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.

    Tipp

    Bedenken Sie die Kosten gegenüber dem Nutzen beim Hinzufügen neuer Indizes.Consider the cost versus benefit when adding new indexes. Es kann besser sein, zusätzliche Abfrageanforderungen in einen bestehenden Index zu konsolidieren.It may be preferable to consolidate additional query needs into an existing index. Erwägen Sie beispielsweise, eine oder zwei zusätzliche Spalten auf Blattebene zu einem bestehenden Index hinzuzufügen, wenn er die Abdeckung mehrerer kritischer Abfragen erlaubt, anstatt einen genau abdeckenden Index pro kritischer Abfrage zu haben.For example, consider adding one or two extra leaf level columns to an existing index, if it allows coverage of several critical queries, instead of having one exactly covering index per each critical query.

Überlegungen zu SpaltenColumn Considerations

Ziehen Sie Spalten mit einem oder mehrerer dieser Attribute in Betracht:Consider columns that have one or more of these attributes:

  • Abdecken der Abfrage.Cover the query.

    Leistungsgewinne werden erzielt, wenn der Index alle Spalten in der Abfrage enthält.Performance gains are achieved when the index contains all columns in the query. Der Abfrageoptimierer kann alle Spaltenwerte im Index finden; da auf Daten der Tabelle oder des gruppierten Indexes nicht zugegriffen wird, kommt es zu weniger Datenträger-E/A-Vorgängen.The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. Verwenden Sie einen Index mit eingeschlossenen Spalten, um mehrere Spalten abzudecken, anstatt einen breiten Indexschlüssel zu erstellen.Use index with included columns to add covering columns instead of creating a wide index key.

    Wenn die Tabelle einen gruppierten Index aufweist, werden die im gruppierten Index definierten Spalten automatisch an das Ende sämtlicher nicht gruppierter Indizes für die Tabelle angefügt.If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. Auf diese Weise kann eine abgedeckte Abfrage erstellt werden, ohne dass die Spalten des gruppierten Indexes in der Definition des nicht gruppierten Indexes angegeben werden.This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. Wenn eine Tabelle beispielsweise einen gruppierten Index für Spalte Cbesitzt, weist ein nicht gruppierter Index für die Spalten B und A die Schlüsselwertspalten B, Aund Cauf.For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.

  • Große Anzahl unterschiedlicher Werte, beispielsweise eine Kombination aus Nachname und Vorname, wenn für andere Spalten ein gruppierter Index verwendet wird.Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

    Wenn nur sehr wenige unterschiedliche Werte vorhanden sind, beispielsweise nur 1 und 0, verwenden die meisten Abfragen den Index nicht, da ein Tabellenscan im Allgemeinen effizienter ist.If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient. Für diesen Datentyp sollten Sie einen gefilterten Index anhand eines anderen Werts erstellen, der in weniger Zeilen vorkommt.For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. Beispiel: Wenn die meisten Werte 0 sind, kann der Abfrageoptimierer einen gefilterten Index für die Zeilen verwenden, die den Wert 1 enthalten.For example, if most of the values are 0, the query optimizer might use a filtered index for the data rows that contain 1.

Verwenden eingeschlossener Spalten, um nicht gruppierte Indizes zu erweiternUse Included Columns to Extend Nonclustered Indexes

Sie können die Funktionen nicht gruppierter Indizes erweitern, indem Sie der Blattebene des nicht gruppierten Indexes Nichtschlüsselspalten hinzufügen.You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. Indem Sie Nichtschlüsselspalten einschließen, erstellen Sie nicht gruppierte Indizes, die eine größere Anzahl von Abfragen abdecken.By including nonkey columns, you can create nonclustered indexes that cover more queries. Dies ist der Fall, weil Nichtschlüsselspalten die folgenden Vorteile aufweisen:This is because the nonkey columns have the following benefits:

  • Es kann sich um Datentypen handeln, die als Indexschlüsselspalten nicht zulässig sind.They can be data types not allowed as index key columns.

  • Sie werden von Datenbank-EngineDatabase Engine beim Berechnen der Indexschlüsselspalten oder Indexschlüsselgröße nicht berücksichtigt.They are not considered by the Datenbank-EngineDatabase Engine when calculating the number of index key columns or index key size.

Ein Index mit eingeschlossenen Nichtschlüsselspalten kann die Abfrageleistung erheblich steigern, wenn alle Spalten in der Abfrage in den Index als Schlüssel- oder Nichtschlüsselspalten eingeschlossen werden.An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Leistungsvorteile werden erzielt, weil der Abfrageoptimierer alle Spaltenwerte im Index finden kann; auf Daten der Tabelle oder des gruppierten Indexes wird nicht zugegriffen, sodass als Ergebnis weniger Datenträger-E/A-Vorgänge auftreten.Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

Hinweis

Wenn ein Index alle Spalten enthält, auf die die Abfrage verweist, wird dies normalerweise als Abdecken der Abfrage bezeichnet.When an index contains all the columns referenced by the query it is typically referred to as covering the query.

Die Schlüsselspalten werden auf allen Ebenen des Indexes gespeichert, die Nichtschlüsselspalten nur auf der Blattebene.While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level.

Verwenden eingeschlossener Spalten, um Größenbeschränkungen zu umgehenUsing Included Columns to Avoid Size Limits

Sie können Nichtschlüsselspalten in einen nicht gruppierten Index einschließen, damit die Größenbegrenzungen des aktuellen Indexes von maximal 16 Schlüsselspalten und einer maximalen Größe des Indexschlüssels von 900 Byte nicht überschritten werden.You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. Nichtschlüsselspalten werden von Datenbank-EngineDatabase Engine beim Berechnen der Indexschlüsselspalten oder Indexschlüsselgröße nicht berücksichtigt.The Datenbank-EngineDatabase Engine does not consider nonkey columns when calculating the number of index key columns or index key size.
Angenommen, Sie möchten die folgenden Spalten in der Document -Tabelle indizieren:For example, assume that you want to index the following columns in the Document table:

  • Title nvarchar(50)
  • Revision nchar(5)
  • FileName nvarchar(400)

Da für die Datentypen nchar und nvarchar 2 Bytes für jedes Zeichen erforderlich sind, überschreitet ein Index, der diese drei Spalten enthält, die Größenbeschränkung von 900 Bytes um 10 Bytes (455 * 2).Because the nchar and nvarchar data types require 2 bytes for each character, an index that contains these three columns would exceed the 900 byte size limitation by 10 bytes (455 * 2). Indem die INCLUDE -Klausel der CREATE INDEX -Anweisung verwendet wird, kann der Indexschlüssel als (Title, Revision) und FileName als Nichtschlüsselspalte definiert werden.By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (Title, Revision) and FileName defined as a nonkey column. Auf diese Weise beträgt die Größe des Indexschlüssels 110 Byte (55 * 2), und der Index enthält dennoch alle erforderlichen Spalten.In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. Die folgende Anweisung erstellt einen solchen Index:The following statement creates such an index.

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
Richtlinien für Indizes mit eingeschlossenen SpaltenIndex with Included Columns Guidelines

Wenn Sie nicht gruppierte Indizes mit eingeschlossenen Spalten entwerfen, sollten Sie die folgenden Richtlinien beachten:When you design nonclustered indexes with included columns consider the following guidelines:

  • Nichtschlüsselspalten werden in der INCLUDE-Klausel der CREATE INDEX-Anweisung definiert.Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.

  • Nichtschlüsselspalten können nur für nicht gruppierte Indizes für Tabellen oder indizierte Sichten definiert werden.Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.

  • Mit Ausnahme von text, ntextund imagesind alle Datentypen zulässig.All data types are allowed except text, ntext, and image.

  • Bei berechneten Spalten, die deterministisch und präzise oder unpräzise sind, kann es sich um eingeschlossene Spalten handeln.Computed columns that are deterministic and either precise or imprecise can be included columns. Weitere Informationen finden Sie unter Indexes on Computed Columns.For more information, see Indexes on Computed Columns.

  • Ebenso wie Schlüsselspalten können berechnete Spalten, die aus image, ntextund text -Datentypen abgeleitet werden, Nichtschlüsselspalten (eingeschlossene Spalten) sein, wenn der Datentyp der berechneten Spalte als Nichtschlüssel-Indexspalte zulässig ist.As with key columns, computed columns derived from image, ntext, and text data types can be nonkey (included) columns as long as the computed column data type is allowed as a nonkey index column.

  • Spaltennamen dürfen nicht sowohl in der INCLUDE-Liste als auch in der Schlüsselspaltenliste angegeben werden.Column names cannot be specified in both the INCLUDE list and in the key column list.

  • Spaltennamen können nicht in der INCLUDE-Liste wiederholt werden.Column names cannot be repeated in the INCLUDE list.

Richtlinien für die SpaltengrößeColumn Size Guidelines
  • Es muss mindestens eine Schlüsselspalte definiert werden.At least one key column must be defined. Die maximal zulässige Anzahl der Nichtschlüsselspalten beträgt 1023 Spalten.The maximum number of nonkey columns is 1023 columns. Dies ist die maximale Anzahl der Tabellenspalten minus 1.This is the maximum number of table columns minus 1.

  • Indexschlüsselspalten (ausschließlich der Nichtschlüsselspalten) unterliegen der Begrenzung der Indexgröße auf maximal 16 Schlüsselspalten und der Gesamtgröße des Indexschlüssels von 900 Byte.Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.

  • Die Gesamtgröße aller Nichtschlüsselspalten wird nur durch die in der INCLUDE-Klausel angegebene Größe der Spalten beschränkt; varchar(max) -Spalten sind z. B. auf 2 GB beschränkt.The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB.

Richtlinien für die SpaltenänderungColumn Modification Guidelines

Wenn Sie eine Tabellenspalte ändern, die als eingeschlossene Spalte definiert wurde, gelten die folgenden Einschränkungen:When you modify a table column that has been defined as an included column, the following restrictions apply:

  • Nichtschlüsselspalten können nur aus der Tabelle gelöscht werden, wenn der Index zuvor gelöscht wird.Nonkey columns cannot be dropped from the table unless the index is dropped first.

  • Nichtschlüsselspalten können nur zum Ausführen der folgenden Aufgaben geändert werden:Nonkey columns cannot be changed, except to do the following:

    • Ändern der NULL-Zulässigkeit der Spalte von NOT NULL in NULL.Change the nullability of the column from NOT NULL to NULL.

    • Vergrößern der Länge von varchar-, nvarchar- oder varbinary -Spalten.Increase the length of varchar, nvarchar, or varbinary columns.

      Hinweis

      Diese Einschränkungen hinsichtlich der Spaltenänderung gelten für Indexschlüsselspalten.These column modification restrictions also apply to index key columns.

EntwurfsempfehlungenDesign Recommendations

Überarbeiten Sie nicht gruppierte Indizes mit großen Indexschlüsseln so, dass nur Spalten, die für Suchen und Suchvorgänge verwendet werden, Schlüsselspalten sind.Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Erklären Sie alle anderen Spalten, die die Abfrage abdecken, zu eingeschlossenen Nichtschlüsselspalten.Make all other columns that cover the query included nonkey columns. Auf diese Weise sind alle Spalten vorhanden, die zum Abdecken der Abfrage erforderlich sind, der Indexschlüssel selbst ist jedoch klein und effizient.In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

Angenommen, Sie möchten z. B. einen Index entwerfen, der die folgende Abfrage abdeckt:For example, assume that you want to design an index to cover the following query.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE PostalCode BETWEEN N'98000' and N'99999';  

Damit die Abfrage abgedeckt wird, muss jede Spalte im Index definiert werden.To cover the query, each column must be defined in the index. Sie könnten zwar alle Spalten als Schlüsselspalten definieren, die Schlüsselgröße würde dann aber 334 Byte betragen.Although you could define all columns as key columns, the key size would be 334 bytes. Da die einzige Spalte, die tatsächlich als Suchkriterium verwendet wird, die PostalCode -Spalte mit einer Länge von 30 Byte ist, definiert der bessere Indexentwurf PostalCode als Schlüsselspalte und schließt alle anderen Spalten als Nichtschlüsselspalten ein.Because the only column actually used as search criteria is the PostalCode column, having a length of 30 bytes, a better index design would define PostalCode as the key column and include all other columns as nonkey columns.

Die folgende Anweisung erstellt einen Index mit eingeschlossenen Spalten, um die Abfrage abzudecken:The following statement creates an index with included columns to cover the query.

CREATE INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
Überlegungen zur LeistungPerformance Considerations

Vermeiden Sie es, nicht erforderliche Spalten hinzuzufügen.Avoid adding unnecessary columns. Das Hinzufügen einer zu großen Anzahl von Schlüssel- oder Nichtschlüssel-Indexspalten kann zu folgenden Auswirkungen auf die Leistung führen:Adding too many index columns, key or nonkey, can have the following performance implications:

  • Es passen weniger Indexzeilen auf eine Seite.Fewer index rows will fit on a page. Dies kann zu einer Zunahme der E/A und verringerter Cacheeffizienz führen.This could create I/O increases and reduced cache efficiency.

  • Zum Speichern des Indexes ist eine größere Menge an Speicherplatz erforderlich.More disk space will be required to store the index. Insbesondere das Hinzufügen von varchar(max) , nvarchar(max) , varbinary(max) oder xml -Datentypen als Nichtschlüssel-Indexspalten kann die Anforderungen an den Speicherplatz erheblich erhöhen.In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. Der Grund liegt darin, dass die Spaltenwerte in die Blattebene des Indexes kopiert werden.This is because the column values are copied into the index leaf level. Daher werden sie sowohl im Index als auch in der Basistabelle gespeichert.Therefore, they reside in both the index and the base table.

  • Die Indexwartung kann zu einem größeren Zeitaufwand für das Ausführen von Änderungen, Einfügungen, Updates oder Löschvorgängen an der zugrunde liegenden Tabelle oder indizierten Sicht führen.Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

Sie müssen überprüfen, ob die Steigerungen der Abfrageleistung die negativen Auswirkungen auf die Leistung während der Datenänderung sowie hinsichtlich zusätzlicher Speicherplatzanforderungen aufwiegen.You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements.

Richtlinien zum Entwerfen eindeutiger IndizesUnique Index Design Guidelines

Ein eindeutiger Index garantiert, dass der Indexschlüssel keine doppelten Werte enthält und dass deshalb jede Zeile in der Tabelle in gewisser Weise eindeutig ist.A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Das Angeben eines eindeutigen Indexes ist nur dann sinnvoll, wenn die Eindeutigkeit ein Merkmal der Daten ist.Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. Wenn Sie z. B. sicherstellen möchten, dass die Werte in der NationalIDNumber -Spalte der HumanResources.Employee -Tabelle eindeutig sind, wenn der Primärschlüssel EmployeeIDentspricht, erstellen Sie eine UNIQUE-Einschränkung für die NationalIDNumber -Spalte.For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. Wenn der Benutzer versucht, denselben Wert für mehrere Mitarbeiter in diese Spalte einzugeben, wird eine Fehlermeldung angezeigt, und der doppelte Wert wird nicht eingegeben.If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

Durch eindeutige Indizes für mehrere Spalten stellt der Index sicher, dass jede Kombination der Werte in der indizierten Spalte eindeutig ist.With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. Wenn z. B. ein eindeutiger Index für eine Kombination der Spalten LastName, FirstNameund MiddleName erstellt wird, können zwei Zeilen in der Tabelle nicht über dieselbe Wertekombination für diese Spalten verfügen.For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.

Sowohl gruppierte als auch nicht gruppierte Indizes können eindeutig sein.Both clustered and nonclustered indexes can be unique. Unter der Voraussetzung, dass die Daten in der Spalte eindeutig sind, können Sie einen eindeutigen gruppierten Index und mehrere eindeutige nicht gruppierte Indizes für dieselbe Tabelle erstellen.Provided that the data in the column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table.

Eindeutige Indizes haben folgende Vorteile:The benefits of unique indexes include the following:

  • Die Datenintegrität der definierten Spalten ist sichergestellt.Data integrity of the defined columns is ensured.

  • Es werden zusätzliche, für den Abfrageoptimierer hilfreiche Informationen bereitgestellt.Additional information helpful to the query optimizer is provided.

Durch das Erstellen einer PRIMARY KEY- oder einer UNIQUE-Einschränkung wird automatisch ein eindeutiger Index für die angegebenen Spalten erstellt.Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. Es gibt keine deutlichen Unterschiede zwischen dem Erstellen einer UNIQUE-Einschränkung und dem Erstellen eines eindeutigen Indexes unabhängig von einer Einschränkung.There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. Die Datenüberprüfung erfolgt auf dieselbe Weise, und der Abfrageoptimierer macht keinen Unterschied zwischen einem durch eine Einschränkung erstellten eindeutigen Index und einem manuell erstellten Index.Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. Allerdings sollten sie eine UNIQUE- oder PRIMARY KEY-Einschränkung für die Spalte erstellen, wenn Datenintegrität das Ziel ist.However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. Dadurch wird das Ziel des Indexes klar.By doing this the objective of the index will be clear.

ÜberlegungenConsiderations

  • Ein eindeutiger Index, die UNIQUE-Einschränkung oder die PRIMARY KEY-Einschränkung kann nicht erstellt werden, wenn in den Daten doppelte Schlüsselwerte vorhanden sind.A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

  • Wenn die Daten eindeutig sind und Sie die Eindeutigkeit erzwingen wollen, werden durch das Erstellen eines eindeutigen Indexes anstelle eines nicht eindeutigen Indexes für dieselbe Spaltenkombination zusätzliche Informationen für den Abfrageoptimierer bereitgestellt, mit deren Hilfe effizientere Ausführungspläne erstellt werden können.If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. Das Erstellen eines eindeutigen Indexes (vorzugsweise durch Erstellen einer UNIQUE-Einschränkung) ist in diesem Fall empfohlen.Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

  • Ein eindeutiger, nicht gruppierter Index kann eingeschlossene Nichtschlüsselspalten enthalten.A unique nonclustered index can contain included nonkey columns. Weitere Informationen finden Sie unter Index mit eingeschlossenen Spalten.For more information, see Index with Included Columns.

Richtlinien für den Entwurf gefilterter IndizesFiltered Index Design Guidelines

Ein gefilterter Index ist ein optimierter nicht gruppierter Index, der sich besonders für Abfragen eignet, bei denen aus einer fest definierten Teilmenge von Daten ausgewählt wird.A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. Dieser verwendet ein Filterprädikat, um einen Teil der Zeilen in der Tabelle zu indizieren.It uses a filter predicate to index a portion of rows in the table. Mit einem sorgfältig entworfenen gefilterten Index können im Gegensatz zu Tabellenindizes die Abfrageleistung verbessert und der Aufwand für die Indexverwaltung und -speicherung reduziert werden.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Gilt für: SQL Server 2008SQL Server 2008 bis SQL Server 2019 (15.x)SQL Server 2019 (15.x).Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Gefilterte Indizes können gegenüber Tabellenindizes folgende Vorteile bieten:Filtered indexes can provide the following advantages over full-table indexes:

  • Verbesserte Abfrageleistung und PlanqualitätImproved query performance and plan quality

    Mit einem sorgfältig entworfenen gefilterten Index wird die Abfrageleistung und die Ausführungsplanqualität verbessert, da dieser kleiner ist als ein nicht gruppierter Tabellenindex und mit gefilterten Statistiken arbeitet.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. Die gefilterten Statistiken sind genauer als Tabellenstatistiken, da diese nur die Zeilen im gefilterten Index umfassen.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • Reduzierter Aufwand bei der IndexverwaltungReduced index maintenance costs

    Ein Index wird nur beibehalten, wenn DML-Anweisungen (Data Manipulation Language) die Daten im Index beeinflussen.An index is maintained only when data manipulation language (DML) statements affect the data in the index. Ein gefilterter Index reduziert im Vergleich zu einem nicht gruppierten Tabellenindex den Aufwand für die Indexverwaltung, da dieser kleiner ist und nur beibehalten wird, wenn die Daten im Index beeinflusst werden.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. Eine große Anzahl von gefilterten Indizes ist insbesondere dann von Vorteil, wenn diese Daten enthalten, die nur selten beeinflusst werden.It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Ebenso reduziert die geringere Indexgröße den Aufwand für die Aktualisierung der Statistiken, wenn ein gefilterter Index nur die häufig beeinflussten Daten enthält.Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

  • Reduzierter Aufwand bei der IndexspeicherungReduced index storage costs

    Ein gefilterter Index kann den Speicherplatzbedarf von nicht gruppierten Indizes reduzieren, wenn ein Tabellenindex nicht erforderlich ist.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. Sie können einen nicht gruppierten Tabellenindex durch mehrere gefilterte Indizes ersetzen, ohne damit die Speicherplatzanforderungen wesentlich zu erhöhen.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Gefilterte Indizes sind nützlich, wenn Spalten klar definierte Teilmengen von Daten enthalten, auf die Abfragen in SELECT-Anweisungen verweisen.Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Beispiele:Examples are:

  • Spalten mit geringer Dichte, die nur wenige Werte ungleich NULL enthalten.Sparse columns that contain only a few non-NULL values.

  • Heterogene Spalten, die Datenkategorien enthalten.Heterogeneous columns that contain categories of data.

  • Spalten, die Wertebereiche enthalten, z. B. Dollarmengen, Zeit- und Datumsangaben.Columns that contain ranges of values such as dollar amounts, time, and dates.

  • Tabellenpartitionen, die durch einfache Vergleichslogik für Spaltenwerte definiert werden.Table partitions that are defined by simple comparison logic for column values.

Der reduzierte Verwaltungsaufwand für gefilterte Indizes ist am deutlichsten, wenn die Zeilenanzahl im Index verglichen mit der eines Tabellenindex klein ist.Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. Wenn der gefilterte Index die meisten Zeilen in der Tabelle einschließt, ist der Verwaltungsaufwand möglicherweise größer als bei einem Tabellenindex.If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. In diesem Fall sollten Sie anstelle eines gefilterten Index einen Tabellenindex verwenden.In this case, you should use a full-table index instead of a filtered index.

Gefilterte Indizes werden für eine Tabelle definiert und unterstützen nur einfache Vergleichsoperatoren.Filtered indexes are defined on one table and only support simple comparison operators. Wenn Sie einen Filterausdruck benötigen, der auf mehrere Tabellen verweist oder eine komplexe Logik aufweist, sollten Sie eine Sicht erstellen.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

EntwurfsaspekteDesign Considerations

Wenn Sie effektive gefilterte Indizes entwerfen möchten, müssen Sie wissen, welche Abfragen von Ihrer Anwendung verwendet werden und wie diese mit Teilmengen Ihrer Daten in Beziehung stehen.In order to design effective filtered indexes, it is important to understand what queries your application uses and how they relate to subsets of your data. Einige Beispiele für Daten mit fest definierten Teilmengen sind Spalten, die größtenteils nur NULL-Werte enthalten, Spalten mit heterogenen Wertekategorien und Spalten mit verschiedenen Wertebereichen.Some examples of data that have well-defined subsets are columns with mostly NULL values, columns with heterogeneous categories of values and columns with distinct ranges of values. Die folgenden Entwurfsüberlegungen zeigen, wann ein gefilterter Index Vorteile gegenüber Tabellenindizes hat.The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

Tipp

Die Definition von nicht gruppierten Columnstore-Indizes unterstützt gefilterte Bedingungen.The nonclustered columnstore index definition supports using a filtered condition. Um die Auswirkung auf die Leistung beim Hinzufügen eines Columnstore-Indexes in eine OLTP-Tabelle zu verringern, verwenden Sie eine gefilterte Bedingung, um einen nicht gruppierten Columnstore-Index anhand der kalten Daten Ihrer Betriebsworkload zu erstellen.To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

Gefilterte Indizes für DatenteilmengenFiltered Indexes for subsets of data

Wenn eine Spalte nur wenig relevante Werte für Abfragen aufweist, können Sie für die Teilmenge der Werte einen gefilterten Index erstellen.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. Wenn beispielsweise die Werte in einer Spalte größtenteils NULL sind und die Abfrage nur die Werte ungleich NULL berücksichtigt, können Sie für die Datenzeilen mit den Werten ungleich NULL einen gefilterten Index erstellen.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. Der resultierende Index ist kleiner und verursacht weniger Verwaltungsaufwand als ein nicht gruppierter Tabellenindex, der für dieselben Schlüsselspalten festgelegt wird.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

Die Datenbank AdventureWorks2012 enthält z. B. eine Production.BillOfMaterials -Tabelle mit 2679 Zeilen.For example, the AdventureWorks2012 database has a Production.BillOfMaterials table with 2679 rows. Die EndDate -Spalte hat nur 199 Zeilen mit einem Wert ungleich NULL. Die anderen 2.480 Zeilen enthalten einen NULL-Wert.The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain NULL. Der folgende gefilterte Index würde Abfragen abdecken, die die im Index definierten Spalten zurückgeben und die für EndDatenur Zeilen mit einem Wert ungleich NULL auswählen.The following filtered index would cover queries that return the columns defined in the index and that select only rows with a non-NULL value for EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
    ON Production.BillOfMaterials (ComponentID, StartDate)  
    WHERE EndDate IS NOT NULL ;  
GO  

Der gefilterte Index FIBillOfMaterialsWithEndDate ist für die folgende Abfrage gültig.The filtered index FIBillOfMaterialsWithEndDate is valid for the following query. Sie können den Abfrageausführungsplan anzeigen, um zu bestimmen, ob der Abfrageoptimierer den gefilterten Index verwendet hat.You can display the query execution plan to determine if the query optimizer used the filtered index.

SELECT ProductAssemblyID, ComponentID, StartDate   
FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL   
    AND ComponentID = 5   
    AND StartDate > '20080101' ;  

Weitere Informationen zum Erstellen von gefilterten Indizes und zum Definieren des Prädikatausdrucks für gefilterte Indizes finden Sie unter Create Filtered Indexes.For more information about how to create filtered indexes and how to define the filtered index predicate expression, see Create Filtered Indexes.

Gefilterte Indizes für heterogene DatenFiltered Indexes for heterogeneous data

Wenn eine Tabelle heterogene Datenzeilen enthält, können Sie einen gefilterten Index für eine oder mehrere Datenkategorien erstellen.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data.

Zum Beispiel wird jedes Produkt, das in der Production.Product -Tabelle aufgelistet ist, einer ProductSubcategoryIDzugewiesen, die wiederum den Produktkategorien Fahrräder, Bauteile, Bekleidung oder Zubehör zugeordnet wird.For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or Accessories. Diese Kategorien sind heterogen, da ihre Spaltenwerte in der Production.Product -Tabelle nicht eng zueinander in Beziehung stehen.These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. Beispielsweise besitzen die Spalten Color, ReorderPoint, ListPrice, Weight, Classund Style eindeutige Merkmale für jede Produktkategorie.For example, the columns Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. Angenommen, es werden häufig Abfragen für Zubehör mit Unterkategorien zwischen 27 und 36 einschließlich ausgeführt.Suppose that there are frequent queries for accessories which have subcategories between 27 and 36 inclusive. Sie können die Abfrageleistung für Zubehör verbessern, indem Sie einen gefilterten Index für die Unterkategorien von Zubehör erstellen, wie im folgenden Beispiel veranschaulicht.You can improve the performance of queries for accessories by creating a filtered index on the accessories subcategories as shown in the following example.

CREATE NONCLUSTERED INDEX FIProductAccessories  
    ON Production.Product (ProductSubcategoryID, ListPrice)   
        Include (Name)  
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;  

Der gefilterte Index FIProductAccessories deckt die folgende Abfrage ab, da die AbfrageergebnisseThe filtered index FIProductAccessories covers the following query because the query

im Index enthalten sind und der Abfrageplan keine Basistabellensuche einschließt.results are contained in the index and the query plan does not include a base table lookup. Der Abfrageprädikatausdruck ProductSubcategoryID = 33 ist z. B. eine Teilmenge der gefilterten Indexprädikate ProductSubcategoryID >= 27 und ProductSubcategoryID <= 36, die Spalten ProductSubcategoryID und ListPrice im Abfrageprädikat sind beides Schlüsselspalten im Index, und der Name wird in der Blattebene des Indexes als einbezogene Spalte gespeichert.For example, the query predicate expression ProductSubcategoryID = 33 is a subset of the filtered index predicate ProductSubcategoryID >= 27 and ProductSubcategoryID <= 36, the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.

SELECT Name, ProductSubcategoryID, ListPrice  
FROM Production.Product  
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;  

SchlüsselspaltenKey Columns

Die bewährte Methode besteht darin, eine geringe Anzahl von Schlüsselspalten oder eingeschlossenen Spalten in eine Definition des gefilterten Indexes einzuschließen und nur die Spalten einzubeziehen, die der Abfrageoptimierer benötigt, um den gefilterten Index für den Abfrageausführungsplan auszuwählen.It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. Der Abfrageoptimierer kann einen gefilterten Index für die Abfrage auswählen, unabhängig davon, ob dieser die Abfrage abdeckt oder nicht.The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. Der Abfrageoptimierer wählt jedoch eher einen gefilterten Index aus, der die Abfrage abdeckt.However, the query optimizer is more likely to choose a filtered index if it covers the query.

In einigen Fällen deckt ein gefilterter Index die Abfrage ab, ohne die Spalten im gefilterten Indexausdruck als Schlüsselspalten oder eingeschlossene Spalten in der Definition des gefilterten Indexes einzuschließen.In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. Die folgenden Richtlinien erläutern, wann eine Spalte im gefilterten Indexausdruck eine Schlüsselspalte oder eingeschlossene Spalte in der Definition des gefilterten Indexes sein sollte.The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition. Die Beispiele beziehen sich auf den gefilterten Index FIBillOfMaterialsWithEndDate , der zuvor erstellt wurde.The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

Eine Spalte im gefilterten Indexausdruck muss in der Definition des gefilterten Indexes keine Schlüsselspalte oder eingeschlossene Spalte sein, wenn der gefilterte Indexausdruck dem Abfrageprädikat entspricht und die Abfrage die Spalte im gefilterten Indexausdruck mit den Abfrageergebnissen nicht zurückgibt.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results. Zum Beispiel deckt FIBillOfMaterialsWithEndDate die folgende Abfrage ab, da das Abfrageprädikat dem Filterausdruck entspricht und EndDate nicht mit den Abfrageergebnissen zurückgegeben wird.For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate erfordert nicht, dass EndDate eine Schlüsselspalte oder eingeschlossene Spalte in der Definition des gefilterten Indexes ist.FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;   

Eine Spalte im gefilterten Indexausdruck sollte in der Definition des gefilterten Indexes eine Schlüsselspalte oder eingeschlossene Spalte sein, wenn das Abfrageprädikat die Spalte in einem Vergleich verwendet, der nicht dem gefilterten Indexausdruck entspricht.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression. Zum Beispiel ist FIBillOfMaterialsWithEndDate für die folgende Abfrage gültig, da damit aus dem gefilterten Index eine Teilmenge von Zeilen ausgewählt wird.For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. Damit wird jedoch nicht die folgende Abfrage abgedeckt, da EndDate im Vergleich EndDate > '20040101'verwendet wird, der nicht dem gefilterten Indexausdruck entspricht.However, it does not cover the following query because EndDate is used in the comparison EndDate > '20040101', which is not equivalent to the filtered index expression. Der Abfrageprozessor kann diese Abfrage nicht ausführen, ohne die Werte von EndDateabzurufen.The query processor cannot execute this query without looking up the values of EndDate. Deshalb sollte EndDate eine Schlüsselspalte oder eingeschlossene Spalte in der Definition des gefilterten Indexes darstellen.Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate > '20040101';   

Eine Spalte im gefilterten Indexausdruck sollte in der Definition des gefilterten Indexes eine Schlüsselspalte oder eingeschlossene Spalte sein, wenn die Spalte im Abfrageresultset enthalten ist.A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set. Zum Beispiel deckt FIBillOfMaterialsWithEndDate die folgende Abfrage nicht ab, da damit die EndDate -Spalte in den Abfrageergebnissen zurückgegeben wird.For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. Deshalb sollte EndDate eine Schlüsselspalte oder eingeschlossene Spalte in der Definition des gefilterten Indexes darstellen.Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;  

Der Schlüssel des gruppierten Indexes für die Tabelle muss in der Definition des gefilterten Indexes keine Schlüsselspalte oder eingeschlossene Spalte sein.The clustered index key of the table does not need to be a key or included column in the filtered index definition. Der Schlüssel des gruppierten Indexes ist automatisch in allen nicht gruppierten Indizes enthalten, wozu auch gefilterte Indizes zählen.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

Datenkonvertierungsoperatoren im FilterprädikatData Conversion Operators in the Filter Predicate

Wenn der im gefilterten Indexausdruck der gefilterten Indexergebnisse angegebene Vergleichsoperator eine implizite oder explizite Datenkonvertierung ergibt, kommt es zu einem Fehler, wenn die Konvertierung auf der linken Seite eines Vergleichsoperators auftritt.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. Eine mögliche Lösung besteht darin, den gefilterten Indexausdruck mit dem Datenkonvertierungsoperator (CAST oder CONVERT) auf die rechte Seite des Vergleichsoperators zu schreiben.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

Im folgenden Beispiel wird eine Tabelle mit einer Vielzahl von Datentypen erstellt.The following example creates a table with a variety of data types.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.TestTable (a int, b varbinary(4));  

In der folgenden Definition des gefilterten Indexes wird die Spalte b implizit in einen ganzzahligen Datentyp konvertiert, um sie mit der Konstante 1 vergleichen zu können.In the following filtered index definition, column b is implicitly converted to an integer data type for the purpose of comparing it to the constant 1. Dadurch wird die Fehlermeldung 10611 erzeugt, da die Konvertierung auf der linken Seite des Operators im gefilterten Prädikat auftritt.This generates error message 10611 because the conversion occurs on the left hand side of the operator in the filtered predicate.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = 1;  

Die Lösung besteht darin, die Konstante auf der rechten Seite zu konvertieren, damit diese vom gleichen Typ ist wie Spalte b, wie aus dem folgenden Beispiel hervorgeht:The solution is to convert the constant on the right hand side to be of the same type as column b, as seen in the following example:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = CONVERT(Varbinary(4), 1);  

Durch das Verschieben der Datenkonvertierung von der linken Seite auf die rechte Seite eines Vergleichsoperators wird möglicherweise die Bedeutung der Konvertierung geändert.Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. Im obigen Beispiel wurde aus einem Integer-Vergleich ein varbinary -Vergleich, als der CONVERT-Operator der rechten Seite hinzugefügt wurde.In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.

Richtlinien zum Entwerfen von Columnstore-IndizesColumnstore Index Design Guidelines

Ein columnstore index ist eine Technologie zum Speichern, Abrufen und Verwalten von Daten mithilfe eines spaltenbasierten Datenformats, das als Columnstore bezeichnet wird.A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. Weitere Informationen finden Sie unter Columnstore-Indizes: Übersicht.For more information, refer to Columnstore Indexes overview.

Versionsinformationen finden Sie unter Columnstore-Indizes – Neuigkeiten.For version information, see Columnstore indexes - What's new.

Columnstore-Indizes: ArchitekturColumnstore Index Architecture

Die Kenntnis dieser Grundlagen erleichtert es die anderen Columnstore-Artikel zu verstehen, die erläutern, wie sie effektiv verwendet werden können.Knowing these basics will make it easier to understand other columnstore articles that explain how to use them effectively.

Die Datenspeicherung verwendet die Columnstore- und Rowstore-KomprimierungData storage uses columnstore and rowstore compression

Im Zusammenhang mit Columnstore-Indizes werden die Begriffe Rowstore und Columnstore verwendet, um das Format für die Datenspeicherung zu bezeichnen.When discussing columnstore indexes, we use the terms rowstore and columnstore to emphasize the format for the data storage. Columnstore-Indizes verwenden beide Arten der Datenspeicherung.Columnstore indexes use both types of storage.

Gruppierter Columnstore-IndexClustered Columnstore Index

  • Ein Columnstore enthält Daten, die logisch als Tabelle mit Zeilen und Spalten organisiert und physisch in einem Spaltendatenformat gespeichert sind.A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

    Ein Columnstore-Index speichert die meisten Daten physisch im Columnstore-Format.A columnstore index physically stores most of the data in columnstore format. Im Columnstore-Format werden die Daten als Spalten komprimiert und dekomprimiert.In columnstore format, the data is compressed and uncompressed as columns. Es ist nicht erforderlich, andere Werte in jeder Zeile zu dekomprimieren, die nicht von der Abfrage angefordert werden.There is no need to uncompress other values in each row that are not requested by the query. Das schnelle Scannen einer ganzen Spalte einer großen Tabelle wird dadurch erleichtert.This makes it fast to scan an entire column of a large table.

  • Ein Rowstore enthält Daten, die logisch als Tabelle mit Zeilen und Spalten organisiert und anschließend physisch in einem Zeilendatenformat gespeichert sind.A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. Dies stellte die herkömmliche Methode zum Speichern von Daten (z.B. Heaps oder gruppierte B-Strukturindizes) aus relationalen Tabellen dar.This has been the traditional way to store relational table data such as a heap or clustered B-tree index.

    Ein Columnstore-Index speichert einige Zeilen auch physisch in einem Rowstore-Format, ein sogenannter Deltastore.A columnstore index also physically stores some rows in a rowstore format called a deltastore. Beim Deltastore, auch Delta-Zeilengruppe genannt, handelt es sich um einen Aufbewahrungsort für Zeilen, die eine zu geringe Anzahl darstellen, um in den Columnstore komprimiert zu werden.The deltastore,also called delta rowgroups, is a holding place for rows that are too few in number to qualify for compression into the columnstore. Jede Deltazeilengruppe wird als gruppierter B-Strukturindex implementiert.Each delta rowgroup is implemented as a clustered B-tree index.

  • Der Deltastore ist ein Aufbewahrungsort für Zeilen, die eine zu geringe Anzahl darstellen, um in den Columnstore komprimiert zu werden.The deltastore is a holding place for rows that are too few in number to be compressed into the columnstore. Der Deltastore speichert die Zeilen im Rowstore-Format.The deltastore stores the rows in rowstore format.

Weitere Informationen zu Columnstore-Begriffen und -Konzepten finden Sie unter Columnstore-Indizes: Overview.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Vorgänge werden für Zeilengruppen und Spaltensegmente ausgeführtOperations are performed on rowgroups and column segments

Der Columnstore-Index gruppiert Zeilen in verwaltbare Einheiten.The columnstore index groups rows into manageable units. Diese Einheiten werden jeweils als eine Zeilengruppe bezeichnet.Each of these units is called a rowgroup. Die Anzahl der Zeilen in der Zeilengruppe muss groß genug sein, um die Komprimierungsraten zu verbessern, und klein genug, um von In-Memory-Vorgängen profitieren zu können.For best performance, the number of rows in a rowgroup is large enough to improve compression rates and small enough to benefit from in-memory operations.

Der Columnstore-Index führt diese Vorgänge z.B. auf Zeilengruppen aus:For example, the columnstore index performs these operations on rowgroups:

  • Komprimiert Zeilengruppen in den Columnstore.Compresses rowgroups into the columnstore. Die Komprimierung wird für jedes Spaltensegment innerhalb einer Zeilengruppe ausgeführt.Compression is performed on each column segment within a rowgroup.
  • Führt Zeilengruppen während eines ALTER INDEX ... REORGANIZE-Vorgangs zusammen und entfernt dabei gelöschte DatenMerges rowgroups during an ALTER INDEX ... REORGANIZE operation, including removing deleted data.
  • Erstellt neue Zeilengruppen während eines ALTER INDEX ... REBUILD-Vorgangs.Creates new rowgroups during an ALTER INDEX ... REBUILD operation.
  • Meldet Zeilengruppen-Integrität und Fragmentierung in dynamischen Verwaltungsansichten (DMVs).Reports on rowgroup health and fragmentation in the dynamic management views (DMVs).

Der Deltastore besteht aus mindestens einer Zeilengruppe, die Delta-Zeilengruppen genannt werden.The deltastore is comprised of one or more rowgroups called delta rowgroups. Bei jeder Deltazeilengruppe handelt es sich um einen gruppierten B-Strukturindex, der kleine Massenladevorgänge speichert und Zeilen einfügt, bis die Zeilengruppe 1.048.576 Zeilen enthält. Sobald dies der Fall ist, komprimiert der sogenannte Tupelverschiebungsvorgang automatisch die geschlossene Zeilengruppe in den Columnstore.Each delta rowgroup is a clustered B-tree index that stores small bulk loads and inserts until the rowgroup contains 1,048,576 rows, at which time a process called the tuple-mover automatically compresses the closed rowgroup into the columnstore.

Weitere Informationen zu Zeilengruppenzuständen finden Sie unter sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).For more information about rowgroup statuses, see sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Tipp

Zu viele kleine Zeilengruppen verschlechtern die Qualität des Columnstore-Index.Having too many small rowgroups decreases the columnstore index quality. Ein Neuorganisierungsvorgang führt kleinere Zeilengruppen zusammen, wobei eine interne Schwellenwertrichtlinie eingehalten wird, die bestimmt, wie gelöschte Zeilen entfernt und die komprimierten Zeilengruppen kombiniert werden.A reorganize operation will merge smaller rowgroups, following an internal threshold policy that determines how to remove deleted rows and combine the compressed rowgroups. Nach einem Mergevorgang sollte sich die Indexqualität verbessert haben.After a merge, the index quality should be improved.

Hinweis

Ab SQL Server 2019 (15.x)SQL Server 2019 (15.x) wird der Tupelverschiebungsvorgang von einem Mergetask im Hintergrund unterstützt, der automatisch kleinere OPEN-Deltazeilengruppen komprimiert, die für einen bestimmten Zeitraum vorhanden waren (wie durch einen internen Schwellenwert festgelegt), oder COMPRESSED-Zeilengruppen mergt, aus denen eine große Anzahl von Zeilen gelöscht wurde.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), the tuple-mover is helped by a background merge task that automatically compresses smaller OPEN delta rowgroups that have existed for some time as determined by an internal threshold, or merges COMPRESSED rowgroups from where a large number of rows has been deleted.

Jede Spalte weist einige ihrer Werte in jeder Zeilengruppe auf.Each column has some of its values in each rowgroup. Diese Werte werden als Spaltensegmente bezeichnet.These values are called column segments. Jede Zeilengruppe enthält ein Spaltensegment für jede Spalte in der Tabelle.Each rowgroup contains one column segment for every column in the table. Jede Spalte besitzt ein Spaltensegment in jeder Zeilengruppe.Each column has one column segment in each rowgroup.

SpaltensegmentColumn segment

Wenn der Columnstore-Index eine Zeilengruppe komprimiert, wird jedes Spaltensegment separat komprimiert.When the columnstore index compresses a rowgroup, it compresses each column segment separately. Um eine ganze Spalte zu dekomprimieren, muss der Columnstore-Index nur ein Spaltensegment von jeder Zeilengruppe dekomprimieren.To uncompress an entire column, the columnstore index only needs to uncompress one column segment from each rowgroup.

Weitere Informationen zu Columnstore-Begriffen und -Konzepten finden Sie unter Columnstore-Indizes: Overview.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Kleine Ladungen und Einfügungen werden im Deltastore gespeichertSmall loads and inserts go to the deltastore

Ein Columnstore-Index verbessert die Columnstore-Komprimierung und Leistung durch das Komprimieren von mindestens 102.400 Zeilen gleichzeitig in den Columnstore-Index.A columnstore index improves columnstore compression and performance by compressing at least 102,400 rows at a time into the columnstore index. Um Zeilen in einem Sammelvorgang zu komprimieren, sammelt der Columnstore-Index kleine Lasten, und fügt diese im Deltastore ein.To compress rows in bulk, the columnstore index accumulates small loads and inserts in the deltastore. Die Deltastore-Vorgänge werden im Hintergrund verarbeitet.The deltastore operations are handled behind the scenes. Damit die richtigen Abfrageergebnisse zurückgegeben werden, kombiniert der gruppierte Columnstore-Index Abfrageergebnisse aus dem Columnstore und dem Deltastore.To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.

Zeilen werden im Deltastore aufgenommen wenn sie:Rows go to the deltastore when they are:

  • Mit der INSERT INTO ... VALUES-Anweisung eingefügt werden.Inserted with the INSERT INTO ... VALUES statement.
  • Am Ende eines Massenladevorgangs angelangt sind und ihre Anzahl weniger als 102.400 ist.At the end of a bulk load and they number less than 102,400.
  • Aktualisiert.Updated. Jedes Update wird als Delete und Insert implementiert.Each update is implemented as a delete and an insert.

Der Deltastore speichert auch eine Liste von IDs für gelöschte Zeilen, die als gelöscht markiert, aber noch nicht physisch aus dem Columnstore entfernt wurden.The deltastore also stores a list of IDs for deleted rows that have been marked as deleted but not yet physically deleted from the columnstore.

Weitere Informationen zu Columnstore-Begriffen und -Konzepten finden Sie unter Columnstore-Indizes: Overview.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Wenn Delta-Zeilengruppen voll sind, werden sie in den Columnstore komprimiertWhen delta rowgroups are full they get compressed into the columnstore

Gruppierte Columnstore-Indizes erfassen bis zu 1.048.576 Zeilen in jeder Delta-Zeilengruppe, bevor die Zeilengruppe in den Delta-Store komprimiert wird.Clustered columnstore indexes collect up to 1,048,576 rows in each delta rowgroup before compressing the rowgroup into the columnstore. Dies verbessert die Komprimierung des Columnstore-Index.This improves the compression of the columnstore index. Wenn eine Deltazeilengruppe die maximale Zeilenanzahl erreicht, ändert sich ihr Zustand von OPEN in CLOSED.When a delta rowgroup reaches the maximum number of rows, it transitions from an OPEN to CLOSED state. Ein Hintergrundprozess namens Tupelverschiebungsvorgang überprüft auf geschlossene Zeilengruppen.A background process named the tuple-mover checks for closed row groups. Wenn der Prozess eine geschlossene Zeilengruppe findet, wird diese komprimiert und im Columnstore-Index gespeichert.If the process finds a closed rowgroup, it compresses the rowgroup and stores it into the columnstore.

Wenn eine Deltazeilengruppe komprimiert wurde, ändert sich der Zustand der vorhandenen Deltazeilengruppe in TOMBSTONE, damit sie später im Tupelverschiebungsvorgang entfernt wird, wenn nicht auf sie verwiesen wird. Die neue komprimierte Zeilengruppe wird als COMPRESSED gekennzeichnet.When a delta rowgroup has been compressed, the existing delta rowgroup transitions into TOMBSTONE state to be removed later by the tuple-mover when there is no reference to it, and the new compressed rowgroup is marked as COMPRESSED.

Weitere Informationen zu Zeilengruppenzuständen finden Sie unter sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).For more information about rowgroup statuses, see sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Mit ALTER INDEX können Delta-Zeilengruppen in den Columnstore gezwungen werden, um den Index neu zu erstellen oder zu organisieren.You can force delta rowgroups into the columnstore by using ALTER INDEX to rebuild or reorganize the index. Beachten Sie, dass bei zu wenig verfügbarem Arbeitsspeicher während des Komprimiervorgangs, der Columnstore-Index die Anzahl der Zeilen in der komprimierten Zeilengruppe möglicherweise reduziert.Note that if there is memory pressure during compression, the columnstore index might reduce the number of rows in the compressed rowgroup.

Weitere Informationen zu Columnstore-Begriffen und -Konzepten finden Sie unter Columnstore-Indizes: Overview.For more information about columnstore terms and concepts, see Columnstore indexes: Overview.

Jede Tabellenpartition verfügt über eigene Zeilengruppen und Delta-ZeilengruppenEach table partition has its own rowgroups and delta rowgroups

Das Konzept der Partitionierung ist in einem gruppierten Index, einem Heap, und einem Columnstore-Index identisch.The concept of partitioning is the same in both a clustered index, a heap, and a columnstore index. Das Partitionieren einer Tabelle teilt die Tabelle in kleinere Gruppen von Zeilen, gemäß einer Reihe von Spaltenwerten.Partitioning a table divides the table into smaller groups of rows according to a range of column values. Es wird häufig für die Verwaltung der Daten verwendet.It is often used for managing the data. Sie könnten z.B. für jedes Jahr und dessen Daten eine Partition erstellen, und anschließend einen Partitionswechsel durchführen, um Daten in einem kostengünstigeren Speicher zu archivieren.For example, you could create a partition for each year of data, and then use partition switching to archive data to less expensive storage. Ein Partitionswechsel funktioniert bei Columnstore-Indizes und macht es ganz einfach eine Partition von Daten an einen anderen Speicherort zu verschieben.Partition switching works on columnstore indexes and makes it easy to move a partition of data to another location.

Zeilengruppen werden immer in einer Spalten-Partition definiert.Rowgroups are always defined within a table partition. Wenn ein Columnstore-Index partitioniert ist, besitzt jede Partition seine eigenen komprimierten Zeilengruppen und Delta-Zeilengruppen.When a columnstore index is partitioned, each partition has its own compressed rowgroups and delta rowgroups.

Tipp

Sie sollten die Verwendung der Tabellenpartitionierung in Betracht ziehen, wenn Daten aus dem Columnstore entfernt werden müssen.Consider using table partitioning if there's a need to remove data from the columnstore. Das Auslagern und Abschneiden von Partitionen, die nicht mehr benötigt werden, ist eine effiziente Strategie zum Löschen von Daten, ohne Fragmentierung zu generieren, die durch kleinere Zeilengruppen eingeführt wird.Switching out and truncating partitions that are not needed anymore is an efficient strategy to delete data without generating fragmentation introduced by having smaller rowgroups.

Jede Partition kann über mehrere Delta-Zeilengruppen verfügenEach partition can have multiple delta rowgroups

Jede Partition kann über mehrere Delta-Zeilengruppen verfügen.Each partition can have more than one delta rowgroups. Wenn der Columnstore-Index Daten zu einer Delta-Zeilengruppe hinzufügen muss, und die Delta-Zeilengruppe gesperrt ist, wird der Columnstore-Index versuchen eine Sperre für eine andere Delta-Zeilengruppe zu erhalten.When the columnstore index needs to add data to a delta rowgroup and the delta rowgroup is locked, the columnstore index will try to obtain a lock on a different delta rowgroup. Wenn keine Delta-Zeilengruppen verfügbar sind, wird der Columnstore-Index eine neue Delta-Zeilengruppe erstellen.If there are no delta rowgroups available, the columnstore index will create a new delta rowgroup. Beispielsweise könnte eine Tabelle mit 10 Partitionen problemlos über mindestens 20 Delta-Zeilengruppen verfügen.For example, a table with 10 partitions could easily have 20 or more delta rowgroups.

Sie können Columnstore und Rowstore-Indizes für dieselbe Tabelle kombinierenYou can combine columnstore and rowstore indexes on the same table

Der nicht gruppierte Index enthält eine Kopie eines Teils oder aller Zeilen und Spalten der zugrundeliegenden Tabelle.A nonclustered index contains a copy of part or all of the rows and columns in the underlying table. Der Index ist als eine oder mehrere Spalte(n) der Tabelle definiert und weist eine optionale Bedingung auf, die zum Filtern der Zeilen dient.The index is defined as one or more columns of the table, and has an optional condition that filters the rows.

Ab SQL Server 2016 (13.x)SQL Server 2016 (13.x) können Sie einen aktualisierbaren, nicht gruppierten Columnstore-Index für eine Rowstore-Tabelle erstellen.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can create an updatable nonclustered columnstore index on a rowstore table. Der Columnstore-Index speichert eine Kopie der Daten, sodass Sie keinen zusätzlichen Speicher benötigen.The columnstore index stores a copy of the data so you do need extra storage. Allerdings werden die Daten in den Columnstore-Index komprimiert, auf eine kleinere Größe als die Rowstore-Tabelle es erfordert.However, the data in the columnstore index will compress to a smaller size than the rowstore table requires. Durch dieses Vorgehen können Sie Analysen mit dem Columnstore-Index und Transaktionen mit dem Rowstore-Index zur gleichen Zeit ausführen.By doing this, you can run analytics on the columnstore index and transactions on the rowstore index at the same time. Der Spaltenspeicher wird aktualisiert, wenn sich die Daten in der Rowstore-Tabelle ändern, daher arbeiten beide Indizes auf den gleichen Daten.The column store is updated when data changes in the rowstore table, so both indexes are working against the same data.

Ab SQL Server 2016 (13.x)SQL Server 2016 (13.x) können nicht gruppierte Rowstore-Indizes für einen oder mehrere Columnstore-Indizes erstellt werden.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), you can have one or more nonclustered rowstore indexes on a columnstore index. Auf diese Weise können effiziente Tabellensuchvorgänge im zugrundeliegenden Columnstore ausgeführt werden.By doing this, you can perform efficient table seeks on the underlying columnstore. Auch weitere Optionen werden dadurch verfügbar.Other options become available too. Beispielsweise können Sie eine Primärschlüsseleinschränkung durchsetzen, indem Sie eine UNIQUE-Bedingung auf die Rowstore-Tabelle anwenden.For example, you can enforce a primary key constraint by using a UNIQUE constraint on the rowstore table. Da ein nicht eindeutiger Wert nicht in die Rowstore-Tabelle eingefügt werden kann, kann SQL Server den Wert nicht in den Columnstore einfügen.Since an non-unique value will fail to insert into the rowstore table, SQL Server cannot insert the value into the columnstore.

Überlegungen zur LeistungPerformance considerations

  • Die Definition des nicht gruppierten Columnstore-Index unterstützt gefilterte Bedingungen.The nonclustered columnstore index definition supports using a filtered condition. Um die Auswirkung auf die Leistung beim Hinzufügen eines Columnstore-Indexes in eine OLTP-Tabelle zu verringern, verwenden Sie eine gefilterte Bedingung, um einen nicht gruppierten Columnstore-Index anhand der kalten Daten Ihrer Betriebsworkload zu erstellen.To minimize the performance impact of adding a columnstore index on an OLTP table, use a filtered condition to create a nonclustered columnstore index on only the cold data of your operational workload.

  • Eine In-Memory-Tabelle kann nur über einen Columnstore-Index verfügen.An in-memory table can have one columnstore index. Sie können ihn bei Erstellung der Tabelle generieren oder später mit ALTER TABLE (Transact-SQL) hinzufügen.You can create it when the table is created or add it later with ALTER TABLE (Transact-SQL). Vor SQL Server 2016 (13.x)SQL Server 2016 (13.x) konnte nur eine datenträgerbasierte Tabelle über einen Columnstore-Index verfügen.Before SQL Server 2016 (13.x)SQL Server 2016 (13.x), only a disk-based table could have a columnstore index.

Weitere Informationen finden Sie unter Columnstore-Indizes: Abfrageleistung.For more information, refer to Columnstore indexes - Query performance.

Leitfaden zum EntwurfDesign Guidance

  • Eine Rowstore-Tabelle kann über einen aktualisierbaren nicht gruppierten Columnstore-Index verfügen.A rowstore table can have one updateable nonclustered columnstore index. Vor SQL Server 2014 (12.x)SQL Server 2014 (12.x) waren nicht gruppierte Columnstore-Indizes schreibgeschützt.Before SQL Server 2014 (12.x)SQL Server 2014 (12.x), the nonclustered columnstore index was read-only.

Weitere Informationen finden Sie unter Columnstore indexes - Design Guidance (Leitfaden zum Entwerfen von Columnstore-Indizes).For more information, refer to Columnstore indexes - Design Guidance.

Richtlinien zum Entwerfen von HashindizesHash Index Design Guidelines

Alle speicheroptimierten Tabellen müssen mindestens einen Index enthalten, da die Zeilen durch die Indizes miteinander verbunden werden.All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. Für eine speicheroptimierte Tabelle wird jeder Index auch speicheroptimiert.On a memory-optimized table, every index is also memory-optimized. Hashindizes zählen zu den möglichen Typen von Indizes in einer speicheroptimierten Tabelle.Hash indexes are one of the possible index types in a memory-optimized table. Weitere Informationen zu finden Sie unter Indizes für speicheroptimierte Tabellen.For more information, see Indexes for Memory-Optimized Tables.

Gilt für: SQL Server 2014 (12.x)SQL Server 2014 (12.x) bis SQL Server 2019 (15.x)SQL Server 2019 (15.x).Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Architektur von HashindizesHash Index Architecture

Ein Hashindex besteht aus einem Array von Zeigern. Jedes Element des Arrays wird als Hashbucket bezeichnet.A hash index consists of an array of pointers, and each element of the array is called a hash bucket.

  • Jeder Bucket umfasst 8 Bytes, die zum Speichern der Arbeitsspeicheradresse einer Linkliste von Schlüsseleinträgen verwendet werden.Each bucket is 8 bytes, which are used to store the memory address of a link list of key entries.
  • Jeder Eintrag ist ein Wert für einen Indexschlüssel zuzüglich der Adresse für die entsprechende Zeile in der zugrunde liegenden speicheroptimierten Tabelle.Each entry is a value for an index key, plus the address of its corresponding row in the underlying memory-optimized table.
  • Jeder Eintrag verweist in einer Linkliste von Einträgen – alle mit dem aktuellen Bucket verkettet – auf den nächsten Eintrag.Each entry points to the next entry in a link list of entries, all chained to the current bucket.

Die Anzahl von Buckets muss zum Zeitpunkt der Definition des Indexes angegeben werden:The number of buckets must be specified at index definition time:

  • Je geringer das Verhältnis von Buckets zu Tabellenzeilen oder eindeutigen Werten, desto länger wird die durchschnittliche Bucketlinkliste.The lower the ratio of buckets to table rows or to distinct values, the longer the average bucket link list will be.
  • Kurze Linklisten können schneller verarbeitet werden als lange Linklisten.Short link lists perform faster than long link lists.
  • Die maximale Anzahl von Buckets in einem Hashindex beträgt 1.073.741.824.The maximum number of buckets in hash indexes is 1,073,741,824.

Tipp

Informationen, wie Sie den richtigen BUCKET_COUNT -Wert für Ihre Daten ermitteln, finden Sie unter Konfigurieren der Hashindex-Bucketanzahl.To determine the right BUCKET_COUNT for your data, see Configuring the hash index bucket count.

Die Hashfunktion wird auf die Indexschlüsselspalten angewendet. Durch das Ergebnis dieser Funktion wird bestimmt, welchem Bucket dieser Schlüssel zugeordnet wird.The hash function is applied to the index key columns and the result of the function determines what bucket that key falls into. Jeder Bucket verfügt über einen Zeiger zu den Zeilen, deren Hashschlüsselwerte diesem Bucket zugeordnet sind.Each bucket has a pointer to rows whose hashed key values are mapped to that bucket.

Die Hashfunktion, die für Hashindizes verwendet wird, weist die folgenden Merkmale auf:The hashing function used for hash indexes has the following characteristics:

  • SQL ServerSQL Server hat eine Hashfunktion, die für alle Hashindizes verwendet wird.has one hash function that is used for all hash indexes.
  • Die Hashfunktion ist deterministisch.The hash function is deterministic. Der gleiche Eingabeschlüsselwert wird immer demselben Bucket im Hashindex zugeordnet.The same input key value is always mapped to the same bucket in the hash index.
  • Mehrere Indexschlüssel können dem gleichen Hashbucket zugeordnet werden.Multiple index keys may be mapped to the same hash bucket.
  • Die Hashfunktion ist ausgeglichen. Dies bedeutet, dass die Verteilung der Indexschlüsselwerte auf Hashbuckets üblicherweise nicht einer flachen linearen Verteilung entspricht, sondern einer Poisson- oder Glockenkurvenverteilung.The hash function is balanced, meaning that the distribution of index key values over hash buckets typically follows a Poisson or bell curve distribution, not a flat linear distribution.
  • Eine Poisson-Verteilung ist keine gleichmäßige Verteilung.Poisson distribution is not an even distribution. Indexschlüsselwerte werden in die Hashbuckets nicht gleichmäßig verteilt.Index key values are not evenly distributed in the hash buckets.
  • Wenn zwei Indexschlüssel dem gleichen Hashbucket zugeordnet werden, kommt es zu einem Hashkonflikt.If two index keys are mapped to the same hash bucket, there is a hash collision. Eine große Anzahl von Hashkonflikten kann sich auf die Leistung bei Lesevorgängen auswirken.A large number of hash collisions can have a performance impact on read operations. Ein realistisches Ziel wäre es, wenn 30 % der Buckets zwei verschiedene Schlüsselwerte enthalten.A realistic goal is for 30% of the buckets contain two different key values.

Die Interaktion zwischen dem Hashindex und den Buckets wird im folgenden Bild zusammengefasst.The interplay of the hash index and the buckets is summarized in the following image.

hekaton_tables_23dhekaton_tables_23d

Konfigurieren der Bucketanzahl eines HashindexesConfiguring the hash index bucket count

Die Bucketanzahl des Hashindexes wird während der Indexerstellung angegeben und kann mit der Syntax ALTER TABLE...ALTER INDEX REBUILD geändert werden.The hash index bucket count is specified at index create time, and can be changed using the ALTER TABLE...ALTER INDEX REBUILD syntax.

In den meisten Fällen sollte die Bucketanzahl das Ein- bis Zweifache der Anzahl eindeutiger Werte im Indexschlüssel betragen.In most cases the bucket count would ideally be between 1 and 2 times the number of distinct values in the index key.
Möglicherweise können Sie nicht immer prognostizieren, wie viele Werte ein bestimmter Indexschlüssel enthalten kann oder wird.You may not always be able to predict how many values a particular index key may have, or will have. Die Leistung ist in der Regel dennoch gut, falls der BUCKET_COUNT -Wert im Rahmen des 10-fachen der tatsächlichen Anzahl der Schlüsselwerte liegt, und die Überschätzung ist im Allgemeinen besser als die Unterschätzung.Performance is usually still good if the BUCKET_COUNT value is within 10 times of the actual number of key values, and overestimating is generally better than underestimating.

Zu wenige Buckets hat die folgenden Nachteile:Too few buckets has the following drawbacks:

  • Mehr Hashkonflikte von eindeutigen Schlüsselwerten.More hash collisions of distinct key values.
  • Jeder eindeutige Wert wird gezwungen, denselben Bucket mit einem anderen eindeutigen Wert zu nutzen.Each distinct value is forced to share the same bucket with a different distinct value.
  • Die durchschnittliche Kettenlänge pro Bucket nimmt zu.The average chain length per bucket grows.
  • Je länger die Bucketkette ist, desto langsamer ist die Gleichheitssuche im Index.The longer the bucket chain, the slower the speed of equality lookups in the index.

Zu viele Buckets hat die folgenden Nachteile:Too many buckets has the following drawbacks:

  • Bei einer zu hohen Bucketanzahl können möglicherweise mehr leere Buckets auftreten.Too high a bucket count might result in more empty buckets.
  • Leere Buckets beeinträchtigen die Leistung der vollständigen Indexscans.Empty buckets impact the performance of full index scans. Wenn diese regelmäßig ausgeführt werden, erwägen Sie eine Bucketanzahl, die annähernd der Anzahl eindeutiger Indexschlüsselwerte entspricht.If those are performed regularly, consider picking a bucket count close to the number of distinct index key values.
  • Leere Buckets belegen Speicher, wobei jeder Bucket allerdings nur 8 Bytes belegt.Empty buckets use memory, though each bucket uses only 8 bytes.

Hinweis

Das Hinzufügen von weiteren Buckets trägt nichts zur Reduzierung der Verkettung von Einträgen bei, die sich einen duplizierten Wert teilen.Adding more buckets does nothing to reduce the chaining together of entries that share a duplicate value. Die Rate der Wertduplizierung wird verwendet, um zu entscheiden, ob ein Hash den entsprechenden Indextyp hat, nicht um die Bucketanzahl zu berechnen.The rate of value duplication is used to decide whether a hash is the appropriate index type, not to calculate the bucket count.

Überlegungen zur LeistungPerformance considerations

Die Leistung eines Hash-Indexes ist:The performance of a hash index is:

  • Ausgezeichnet, wenn das Prädikat der WHERE-Klausel einen genauen Wert für jede Spalte im Hashindexschlüssel angibt.Excellent when the predicate in the WHERE clause specifies an exact value for each column in the hash index key. Ein Hashindex wird mit einem Ungleichheitsprädikat auf einen Scan wiederhergestellt.A hash index will revert to a scan given an inequality predicate.
  • Schlecht, wenn das Prädikat der WHERE-Klausel im Indexschlüssel nach einem Wertebereich sucht.Poor when the predicate in the WHERE clause looks for a range of values in the index key.
  • Schlecht, wenn das Prädikat der WHERE-Klausel einen bestimmten Wert für die erste Spalte eines Hashindexschlüssels mit zwei Spalten festlegt, aber keinen Wert für die anderen Spalten des Schlüssels.Poor when the predicate in the WHERE clause stipulates one specific value for the first column of a two column hash index key, but does not specify a value for other columns of the key.

Tipp

Das Prädikat muss alle Spalten im Hashindexschlüssel enthalten.The predicate must include all columns in the hash index key. Der Hashindex erfordert einen Schlüssel (für den Hash), um den Index zu durchsuchen.The hash index requires a key (to hash) to seek into the index. Wenn ein Indexschlüssel aus zwei Spalten besteht und die WHERE-Klausel nur die erste Spalte bereitstellt, besitzt SQL ServerSQL Server keinen vollständigen Schlüssel für den Hash.If an index key consists of two columns and the WHERE clause only provides the first column, SQL ServerSQL Server does not have a complete key to hash. Dies führt zu einem Indexscan-Abfrageplan.This will result in an index scan query plan.

Wenn ein Hashindex verwendet wird und die Anzahl von eindeutigen Schlüsseln um das Hundertfache (oder mehr) über der Zeilenanzahl liegt, sollten Sie entweder die Bucketanzahl erhöhen, um umfangreiche Zeilenketten zu vermeiden, oder stattdessen einen nicht gruppierten Index verwenden.If a hash index is used and the number of unique index keys is 100 times (or more) than the row count, consider either increasing to a larger bucket count to avoid large row chains, or use a nonclustered index instead.

Überlegungen zur DeklarationDeclaration considerations

Ein Hashindex kann nur für eine speicheroptimierte Tabelle vorhanden sein.A hash index can exist only on a memory-optimized table. Er kann nicht in einer datenträgerbasierten Tabelle vorhanden sein.It cannot exist on a disk-based table.

Ein Hashindex kann deklariert werden als:A hash index can be declared as:

  • UNIQUE, oder standardmäßig als nicht eindeutig.UNIQUE, or can default to Non-Unique.
  • NONCLUSTERED. Dies ist die Standardeinstellung.NONCLUSTERED, which is the default.

Im Folgenden finden Sie ein Beispiel der Syntax zum Erstellen eines Hashindexes außerhalb der CREATE TABLE-Anweisung:The following is an example of the syntax to create a hash index, outside of the CREATE TABLE statement:

ALTER TABLE MyTable_memop  
ADD INDEX ix_hash_Column2 UNIQUE  
HASH (Column2) WITH (BUCKET_COUNT = 64);

Zeilenversionen und Garbage CollectionRow versions and garbage collection

Wenn eine Zeile einer speicheroptimierten Tabelle von UPDATE betroffen ist, erstellt die Tabelle eine aktualisierte Version dieser Zeile.In a memory-optimized table, when a row is affected by an UPDATE, the table creates an updated version of the row. Während der Updatetransaktion können andere Sitzungen möglicherweise die ältere Version der Zeile lesen und so den Leistungsabfall vermeiden, der mit einer Zeilensperre einhergeht.During the update transaction, other sessions might be able to read the older version of the row and thereby avoid the performance slowdown associated with a row lock.

Der Hashindex kann möglicherweise auch verschiedene Versionen seiner Einträge haben, um das Update zu berücksichtigen.The hash index might also have different versions of its entries to accommodate the update.

Wenn die älteren Versionen später nicht mehr erforderlich sind, durchsucht ein Thread der automatischen Speicherbereinigung (GC, Garbage Collection) die Buckets und Verknüpfung, um alte Einträge zu beseitigen.Later when the older versions are no longer needed, a garbage collection (GC) thread traverses the buckets and their link lists to clean away old entries. Der GC-Thread bietet eine bessere Leistung, wenn die Kettenlängen kurz sind.The GC thread performs better if the link list chain lengths are short. Weitere Informationen finden Sie unter In-Memory-OLTP-Garbage Collection.For more information, refer to In-Memory OLTP Garbage Collection.

Memory-Optimized Nonclustered Index Design Guideline (Richtlinien zum Entwerfen von speicheroptimierten, nicht gruppierten Indizes)Memory-Optimized Nonclustered Index Design Guidelines

Nicht gruppierte Indizes zählen zu den möglichen Typen von Indizes in einer speicheroptimierten Tabelle.Nonclustered indexes are one of the possible index types in a memory-optimized table. Weitere Informationen zu finden Sie unter Indizes für speicheroptimierte Tabellen.For more information, see Indexes for Memory-Optimized Tables.

Gilt für: SQL Server 2014 (12.x)SQL Server 2014 (12.x) bis SQL Server 2019 (15.x)SQL Server 2019 (15.x).Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2019 (15.x)SQL Server 2019 (15.x).

Architektur nicht gruppierter In-Memory-IndizesIn-memory Nonclustered Index Architecture

Nicht gruppierte In-Memory-Indizes werden mithilfe einer Datenstruktur implementiert, die als Bw-Struktur bezeichnet wird. Diese wurde 2011 ursprünglich von Microsoft Research entworfen und beschrieben.In-memory nonclustered indexes are implemented using a data structure called a Bw-Tree, originally envisioned and described by Microsoft Research in 2011. Bei einer Bw-Struktur handelt es sich um eine Variante der B-Struktur, die keine Sperren oder Latches aufweist.A Bw-Tree is a lock and latch-free variation of a B-Tree. Weitere Informationen finden Sie unter The Bw-Tree: A B-tree for New Hardware Platforms.For more details please see The Bw-Tree: A B-tree for New Hardware Platforms.

Im Allgemeinen kann die Bw-Struktur als Zuordnung von Seiten verstanden werden, die von der Seiten-ID (PidMap) organisiert wird, sowie als Hilfsmittel zum Belegen und Wiederverwenden von Seiten-IDs (PidAlloc) und als Reihe von Seiten, die mit der Seitenzuordnung und miteinander verknüpft sind.At a very high level the Bw-Tree can be understood as a map of pages organized by page ID (PidMap), a facility to allocate and reuse page IDs (PidAlloc) and a set of pages linked in the page map and to each other. Diese drei allgemeinen Unterkomponenten bilden zusammen die grundlegende interne Struktur einer Bw-Struktur.These three high level sub-components make up the basic internal structure of a Bw-Tree.

Die Struktur ähnelt der normalen B-Struktur in folgenden Hinsichten: Jede Seite verfügt über mehrere sortierte Schlüsselwerte, es gibt Ebenen im Index, die jeweils auf eine niedrigere Ebene zeigen, und die Blattebenen zeigen auf eine Datenzeile.The structure is similar to a normal B-Tree in the sense that each page has a set of key values that are ordered and there are levels in the index each pointing to a lower level and the leaf levels point to a data row. Es gibt jedoch einige Unterschiede.However there are several differences.

Genau wie bei Hashindizes können mehrere Datenzeilen (Versionen) miteinander verknüpft werden.Just like hash indexes, multiple data rows can be linked together (versions). Bei den Seitenzeigern zwischen den Ebenen handelt es sich um logische Seiten-IDs, die Offsets zu einer Seitenzuordnungstabelle darstellen, die wiederum die physische Adresse für jede Seite enthält.The page pointers between the levels are logical page IDs, which are offsets into a page mapping table, that in turn has the physical address for each page.

Es gibt keine direkten Updates für Indexseiten.There are no in-place updates of index pages. Deshalb werden neue Änderungsseiten eingeführt.New delta pages are introduced for this purpose.

  • Latches oder Sperren sind für Seitenupdates nicht erforderlich.No latching or locking is required for page updates.
  • Indexseiten verfügen nicht über eine festgelegte Größe.Index pages are not a fixed size.

Der Schlüsselwert in jeder dargestellten Seite auf der inneren Knotenebene entspricht dem höchsten Wert, den das untergeordnete Element enthält, auf das gezeigt wird. Jede Zeile enthält ebenfalls die logische Seiten-ID der Seite.The key value in each non-leaf level page depicted is the highest value that the child that it points to contains and each row also contains that page logical page ID. Auf den Seiten auf Blattebene ist neben dem Schlüsselwert die physische Adresse der Datenzeile enthalten.On the leaf-level pages, along with the key value, it contains the physical address of the data row.

Punktsuchen ähneln den B-Strukturen zwar, der Unterschied ist jedoch, dass die Seiten nur in eine Richtung verknüpft sind und SQL Server-Datenbank-EngineSQL Server Database Engine den richtigen Seitenzeigern folgt. Hierbei enthält jede Seite auf der inneren Knotenebene den höchsten Wert des untergeordneten Elements und nicht den niedrigsten wie bei einer B-Struktur.Point lookups are similar to B-Trees except that because pages are linked in only one direction, the SQL Server-Datenbank-EngineSQL Server Database Engine follows right page pointers, where each non-leaf pages has the highest value of its child, rather than lowest value as in a B-Tree.

Wenn eine Seite auf Blattebene geändert werden muss, ändert SQL Server-Datenbank-EngineSQL Server Database Engine nicht die Seite selbst.If a Leaf-level page has to change, the SQL Server-Datenbank-EngineSQL Server Database Engine does not modify the page itself. Stattdessen erstellt SQL Server-Datenbank-EngineSQL Server Database Engine einen Änderungsdatensatz, der die Änderungen beschreibt, und fügt diesen der vorherigen Seite an.Rather, the SQL Server-Datenbank-EngineSQL Server Database Engine creates a delta record that describes the change, and appends it to the previous page. Anschließend wird ebenfalls die Tabellenadresse der Seitenzuordnung für die vorherige Seite aktualisiert. Die aktualisierte Adresse entspricht der des Änderungsdatensatzes, die nun die physische Adresse für diese Seite darstellt.Then it also updates the page map table address for that previous page, to the address of the delta record which now becomes the physical address for this page.

Es gibt drei verschiedene Vorgänge, die für das Verwalten der Struktur einer Bw-Struktur erforderlich sein können: Konsolidierung, Teilung und Merge.There are three different operations that can be required for managing the structure of a Bw-Tree: consolidation, split and merge.

Konsolidierung von ÄnderungenDelta Consolidation

Eine lange Kette von Änderungsdatensätzen kann die Suchleistung beeinträchtigen, da lange Ketten durchlaufen werden müssen, wenn ein Index durchsucht wird.A long chain of delta records can eventually degrade search performance as it could mean we are traversing long chains when searching through an index. Wenn ein neuer Änderungsdatensatz zu einer Kette hinzugefügt wird, die bereits über 16 Elemente verfügt, werden die Änderungen in den Änderungsdatensätzen in die Indexseite konsolidiert, auf die verwiesen wird. Die Seite wird dann neu erstellt und enthält Änderungen, die vom neuen Änderungsdatensatz angegeben werden, der die Konsolidierung ausgelöst hat.If a new delta record is added to a chain that already has 16 elements, the changes in the delta records will be consolidated into the referenced index page, and the page will then be rebuilt, including the changes indicated by the new delta record that triggered the consolidation. Die neu erstellte Seite besitzt die gleiche Seiten-ID, aber eine neue Speicheradresse.The newly rebuilt page will have the same page ID but a new memory address.

hekaton_tables_23ehekaton_tables_23e

Teilen von SeitenSplit page

Eine Indexseite in einer Bw-Struktur wächst je nach Bedarf, beginnend bei der Speicherung einer einzelnen Zeile bis hin zu einer Speicherung von maximal 8 KB.An index page in Bw-Tree grows on as-needed basis starting from storing a single row to storing a maximum of 8 KB. Sobald die Indexseite 8 KB erreicht, bewirkt das Einfügen einer einzelnen Seite die Teilung der Indexseite.Once the index page grows to 8 KB, a new insert of a single row will cause the index page to split. Bei einer internen Seite bedeutet dies, dass kein Platz zum Hinzufügen eines weiteren Schlüsselwerts oder Zeigers verfügbar ist. Bei einer Blattseite bedeutet dies, dass die Zeile nach dem Integrieren aller Änderungsdatensätze zu groß ist, um auf die Seite zu passen.For an internal page, this means when there is no more room to add another key value and pointer, and for a leaf page, it means that the row would be too big to fit on the page once all the delta records are incorporated. Die statistischen Informationen im Seitenkopf einer Blattseite verfolgen, wie viel Speicherplatz erforderlich ist, um die Änderungsdatensätze zu konsolidieren. Diese Informationen werden jeweils beim Hinzufügen neuer Änderungsdatensätze angepasst.The statistics information in the page header for a leaf page keeps track of how much space would be required to consolidate the delta records, and that information is adjusted as each new delta record is added.

Ein Teilungsvorgang wird in zwei unteilbaren Schritten ausgeführt.A Split operation is done in two atomic steps. Gehen Sie in der folgenden Abbildung davon aus, dass eine Blattseite eine Teilung erzwingt, da ein Schlüssel mit dem Wert 5 eingefügt wird und eine Seite auf der inneren Knotenebene vorhanden ist, die auf das Ende der aktuellen Seite auf Blattebene zeigt (Schlüsselwert 4).In the picture below, assume a Leaf-page forces a split because a key with value 5 is being inserted, and a non-leaf page exists pointing to the end of the current Leaf-level page (key value 4).

hekaton_tables_23fhekaton_tables_23f

Schritt 1: Ordnen Sie zwei neue Seiten namens P1 und P2 zu, und teilen Sie die Zeilen der alten Seite namens P1, einschließlich der neu eingefügten Zeile, auf diese neuen Seiten auf.Step 1: Allocate two new pages P1 and P2, and split the rows from old P1 page onto these new pages, including the newly inserted row. Ein neuer Slot in der Seitenzuordnungstabelle wird verwendet, um die physische Adresse der Seite P2 zu speichern.A new slot in Page Mapping Table is used to store the physical address of page P2. Auf die Seiten P1 und P2 kann derzeit noch nicht durch gleichzeitige Vorgänge zugegriffen werden.These pages, P1 and P2 are not accessible to any concurrent operations yet. Darüber hinaus wird der logische Zeiger von P1 auf P2 festgelegt.In addition, the logical pointer from P1 to P2 is set. Aktualisieren Sie die Seitenzuordnungstabelle anschließend in einem unteilbaren Schritt, um den Zeiger von der alten Seite P1 auf die neue Seite P1 zu ändern.Then, in one atomic step update the Page Mapping Table to change the pointer from old P1 to new P1.

Schritt 2: Die Seite auf der inneren Knotenebene zeigt auf P1, es gibt jedoch keinen direkten Zeiger von einer Seite auf der inneren Knotenebene zu P2.Step 2: The non-leaf page points to P1 but there is no direct pointer from a non-leaf page to P2. P2 ist nur über P1 erreichbar.P2 is only reachable via P1. Zum Erstellen eines Zeigers von einer Seite auf der inneren Knotenebene zu P2, ordnen Sie eine neue Seite auf der inneren Knotenebene zu (interne Indexseite), kopieren Sie alle Zeilen von der alten Seite auf der inneren Knotenebene, und fügen Sie eine neue Zeile hinzu, um auf P2 zu zeigen.To create a pointer from a non-leaf page to P2, allocate a new non-leaf page (internal index page), copy all the rows from old non-leaf page, and add a new row to point to P2. Sobald dieser Vorgang abgeschlossen ist, aktualisieren Sie die Seitenzuordnungstabelle in einem unteilbaren Schritt, um den Zeiger von der alten Seite auf der inneren Knotenebene zur neuen Seite auf der inneren Knotenebene zu ändern.Once this is done, in one atomic step, update the Page Mapping Table to change the pointer from old non-leaf page to new non-leaf page.

Merge von SeitenMerge page

Wenn ein DELETE-Vorgang dazu führt, dass eine Seite über weniger als 10 % der maximalen Seitengröße (derzeit 8 KB) verfügt oder nur eine einzelne Zeile enthält, wird diese Seite mit einer zusammenhängenden Seite zusammengeführt.When a DELETE operation results in a page having less than 10% of the maximum page size (currently 8 KB), or with a single row on it, that page will be merged with a contiguous page.

Wenn eine Zeile aus einer Seite gelöscht wird, wird ein Änderungsdatensatz für den Löschvorgang hinzugefügt.When a row is deleted from a page, a delta record for the delete is added. Darüber hinaus wird eine Überprüfung durchgeführt, um zu bestimmen, ob die Indexseite (d.h. die Seite auf der inneren Knotenebene) für einen Merge qualifiziert ist.Additionally, a check is made to determine if the index page (non-leaf page) qualifies for Merge. Dadurch wird überprüft, ob der verbleibende Speicherplatz nach dem Löschen der Zeile weniger als 10 % der maximalen Seitengröße entspricht.This check verifies if the remaining space after deleting the row will be less than 10% of maximum page size. Wenn dies zutrifft, wird der Merge in drei unteilbaren Schritten durchgeführt.If it does qualify, the Merge is performed in three atomic steps.

Gehen Sie in der folgenden Abbildung davon aus, dass ein DELETE-Vorgang den Schlüsselwert 10 löscht.In the picture below, assume a DELETE operation will delete the key value 10.

hekaton_tables_23ghekaton_tables_23g

Schritt 1: Eine Änderungsseite, die den Schlüsselwert 10 darstellt, (blaues Dreieck) wird erstellt, und deren Zeiger auf die Seite Pp1 auf der inneren Knotenebene wird auf die neue Änderungsseite festgelegt.Step 1: A delta page representing key value 10 (blue triangle) is created and its pointer in the non-leaf page Pp1 is set to the new delta page. Darüber hinaus wird eine besondere Änderungsseite für den Merge (orangefarbenes Dreieck) erstellt und mit dem Zeiger zur Änderungsseite verknüpft.Additionally a special merge-delta page (green triangle) is created, and it is linked to point to the delta page. Zu diesem Zeitpunkt sind beide Seiten (die Änderungsseite und die Änderungsseite für den Merge) nicht für gleichzeitige Transaktionen sichtbar.At this stage, both pages (delta page and merge-delta page) are not visible to any concurrent transaction. In einem unteilbaren Schritt wird der Zeiger zur Seite P1 auf Blattebene in der Seitenzuordnungstabelle aktualisiert, damit dieser auf die Änderungsseite für den Merge zeigt.In one atomic step, the pointer to the Leaf-level page P1 in the Page Mapping Table is updated to point to the merge-delta page. Nach diesem Schritt zeigt der Eintrag für den Schlüsselwert 10 in Pp1 auf die Änderungsseite für den Merge.After this step, the entry for key value 10 in Pp1 now points to the merge-delta page.

Schritt 2: Auf der Seite Pp1 auf der inneren Knotenebene muss die Zeile entfernt werden, die den Schlüsselwert 7 enthält, und der Eintrag für den Schlüsselwert 10 muss aktualisiert werden, damit dieser auf P1 zeigt.Step 2: The row representing key value 7 in the non-leaf page Pp1 needs to be removed, and the entry for key value 10 updated to point to P1. Hierfür wird eine neue Seite namens Pp2 auf der inneren Knotenebene zugeordnet, und alle Zeilen von Pp1 (mit Ausnahme der Zeile, die den Schlüsselwert 7 enthält) werden kopiert. Anschließend wird die Zeile für den Schlüsselwert 10 aktualisiert, damit diese auf die Seite P1 zeigt.To do this, a new non-leaf page Pp2 is allocated and all the rows from Pp1 are copied except for the row representing key value 7; then the row for key value 10 is updated to point to page P1. Sobald dieser Vorgang abgeschlossen ist, wird der Eintrag der Seitenzuordnungstabelle aktualisiert, der auf Pp1 zeigt, damit dieser auf Pp2 zeigt.Once this is done, in one atomic step, the Page Mapping Table entry pointing to Pp1 is updated to point to Pp2. Pp1 ist nicht mehr erreichbar.Pp1 is no longer reachable.

Schritt 3: Die Seiten P2 und P1 auf Blattebene werden zusammengeführt, und die Änderungsseiten werden entfernt.Step 3: The Leaf-level pages P2 and P1 are merged and the delta pages removed. Hierfür wird die neue Seite P3 zugeordnet, die Zeilen von P2 und P1 werden zusammengeführt, und die Änderungen durch die Änderungsseite werden in die neue Seite P3 integriert.To do this, a new page P3 is allocated and the rows from P2 and P1 are merged, and the delta page changes are included in the new P3. Anschließend wird der Eintrag der Seitenzuordnungstabelle, der auf P1 zeigt, in einem unteilbaren Schritt aktualisiert, um auf die Seite P3 zu zeigen.Then, in one atomic step, the Page Mapping Table entry pointing to page P1 is updated to point to page P3.

Überlegungen zur LeistungPerformance considerations

Die Leistung eines nicht gruppierten Indexes ist beim Abfragen von speicheroptimierten Tabellen mit Ungleichheitsprädikaten besser als die eines nicht gruppierten Hashindexes.The performance of a nonclustered index is better than nonclustered hash indexes when querying a memory-optimized table with inequality predicates.

Hinweis

Eine Spalte in einer speicheroptimierten Tabelle kann Teil eines Hashindexes und des nicht gruppierten Indexes sein.A column in a memory-optimized table can be part of both a hash index and a nonclustered index.

Tipp

Wenn eine Spalte in einer nicht gruppierten Indexschlüsselspalte viele doppelte Werte aufweist, kann die Leistung bei Updates, Einfügungen und Löschungen abnehmen.When a column in a nonclustered index key columns have many duplicate values, performance can degrade for updates, inserts, and deletes. Eine Möglichkeit, die Leistung zu verbessern, ist in diesem Fall das Hinzufügen einer anderen Spalte zum nicht gruppierten Index.One way to improve performance in this situation is to add another column to the nonclustered index.

Zusätzliches LesematerialAdditional Reading

CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)CREATE XML INDEX (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
Neuorganisieren und Neuerstellen von Indizes Reorganize and Rebuild Indexes
Verbessern der Leistung mit indizierten Sichten in SQL Server 2008Improving Performance with SQL Server 2008 Indexed Views
Partitionierte Tabellen und IndizesPartitioned Tables and Indexes
Erstellen eines Primärschlüssels Create a Primary Key
Indizes für speicheroptimierte TabellenIndexes for Memory-Optimized Tables
Columnstore-Indizes: ÜbersichtColumnstore Indexes overview
Troubleshooting Hash Indexes for Memory-Optimized Tables (Behandlung von Problemen bei Hashindizes für speicheroptimierte Tabellen) Troubleshooting Hash Indexes for Memory-Optimized Tables
Dynamische Verwaltungssichten für speicheroptimierte Tabellen (Transact-SQL) Memory-Optimized Table Dynamic Management Views (Transact-SQL)
Index Related Dynamic Management Views and Functions (Transact-SQL) (Indexbezogene dynamische Verwaltungssichten und -funktionen (Transact-SQL)) Index Related Dynamic Management Views and Functions (Transact-SQL)
Indizes in berechneten Spalten Indexes on Computed Columns
Indizes und ALTER TABLE Indexes and ALTER TABLE
Adaptive IndexdefragmentierungAdaptive Index Defrag