Donnerstag, November 26, 2015

Korrigierte Histogramm-Statistiken im SQL Server anlegen

Nachdem ich viele Jahre lang Tom Kytes Mantra wiederholt habe, dass alle RDBMS unterschiedlich sind und man die Unterschiede kennen muss, um sinnvoll mit den Systemen umzugehen, behaupte ich in jüngerer Vergangenheit immer häufiger, dass die relationalen Datenbanken im Prinzip alle ziemlich ähnlich sind und sich in mancher Hinsicht immer ähnlicher werden. In jedem Fall bin ich immer wieder froh, wenn ich neue Gemeinsamkeiten feststelle, so etwa auch diese hier: im immer wieder lesenswerten SQL Performance.com Blog erläutert Dan Holmes anhand eines umfangreichen Beispiels, wie man mit Hilfe der (nicht supporteten) Option STATS_STREAM des UPDATE STATISTICS Kommandos Optimizer-Statistiken exportieren und importieren kann, um auf diese Weise ein passenderes Histogram einer ungleichen Datenverteilung zu erstellen, als das durch die WITH SAMPLE Option von UPDATE STATISTICS erzeugte. Im Oracle-Universum ist diese Strategie nicht unbekannt (und wird dort sogar offiziell unterstützt) - ein entsprechendes Beispiel liefert (wie üblich) Jonathan Lewis.

Kommentare:

  1. Eine Alternative könnte die Verwendung von UPDATE STATISTICS + ON PARTITIONS Clause sein. Mit leeren Statistiken anfangen und sie dann gezielt für die relevanten Partitionen aktualisieren.

    https://msdn.microsoft.com/en-us/library/ms187348.aspx
    ON PARTITIONS ( { | } [, …n] ) ]
    Forces the leaf-level statistics covering the partitions specified in the ON PARTITIONS clause to be recomputed, and then merged to build the global statistics. WITH RESAMPLE is required because partition statistics built with different sample rates cannot be merged together.
    Applies to: SQL Server 2014 through SQL Server 2016.

    AntwortenLöschen
  2. Hallo Andrej, danke für den Hinweis. Die Möglichkeit, globale Statistiken aus Partitionsstatistiken abzuleiten, gibt es bei Oracle auch (und geschieht automatisch, wenn keine globalen Statistiken vorliegen), aber davon sind Histogramme - meiner Erinnerung nach - nicht betroffen.

    AntwortenLöschen
  3. Hallo Martin,

    auch die globalen Histogramme (auf Tabellenebene) werden beim Aktualisieren der inkrementellen Statistiken (auf Partitionsebene) aktualisiert. Ein großes Manko ist, dass der Query Optimizer weder in SQL Server 2014 noch in 2016 in der Lage ist, die Partitionsstatistiken zu verwenden. Er greift immer auf die globalen Statistiken zu, die gemergte und deswegen ungenauere Daten enthalten. Das Feature ist momentan also eher auf die Reduzierung des Aufwands beim Aktualisieren von Statistiken beschränkt (+exotische Anwendungsfälle wie in Deinem Blogpost :) ).
    Da die inkrementellen Statistiken aber fast wie die normalen (globalen) Statistiken aussehen und man sie auch über eine undokumentierte DMF sys.dm_db_stats_properties_internal abfragen kann, bleibt die Hoffnung, dass auch der Query Optimizer irgendwann soweit ist, dass er diese Informationen nutzen kann.

    Ein guter Artikel zum Thema:
    http://sqlperformance.com/2015/05/sql-statistics/improving-maintenance-incremental-statistics
    "I mentioned previously that, prior to 2014, you could create filtered statistics to give the optimizer better information about the partitions. Those incremental statistics? They aren’t currently used by the optimizer. The query optimizer still just uses the main histogram that represents the entire table....
    So what’s the point of incremental statistics? If you assume that only data in the most recent partition is changing, then ideally you only update statistics for that partition. You can do this now with incremental statistics – and what happens is that information is then merged back into the main histogram. The histogram for the entire table will update without having to read through the entire table to update statistics, and this can help with performance of your maintenance tasks"

    AntwortenLöschen
    Antworten
    1. gerade hatte ich in meinem hoffnungslos überladenen Feedly Aaron Bertrands Liste von Connect-Vorschlägen gesehen - http://sqlperformance.com/2015/11/sql-statistics/please-help-improve-sql-server-statistics -, in der er auf den von Dir zitierten Artikel verweist und außerdem um Unterstützung für https://connect.microsoft.com/SQLServer/feedback/details/2010834 bittet ("Optimizer should actually *use* per-partition stats"). Dafür hätte ich gern gestimmt, aber leider ist das Ticket als Duplikat gekennzeichnet und enthält anscheinend keinen Verweis auf das duplizierte Element - schade eigentlich...

      Löschen