Anmelden

Navigationslinks überspringen
Unternehmen
Leistungen
Blog
Deutsche Seiten English Sites
Startet die SucheErweiterte Suche
Kategorien
SQL Server
Analyses Services
Coding
Reporting Services
Integration Services
Microsoft Access
Sonstiges
Sharepoint
Andere Blogs
Diese Liste enthält keine Elemente.
Hyperlinks
Fotos
Archiv
Archiv (Kalender)
Deutsch > Blog > Kategorien

26.07.2009

msmdsrv.exe - hohe CPU Auslastung und starker Disk I/O
Man kann es zwar im Technet oder BOL nachlesen, aber wer tut das schon. :-)
 
 
Bin selbst in einem Kundenprojekt darüber gestolpert wegen Performance Probleme mit MDX Abfragen bzw. hoher CPU Nutzung und hohem Disk I/O von Analysis Services (msmdsrv.exe)
 
Hintergrund: In diesem BI Projekt werden zyklisch Partitionen und Dimension in kurzen Abständen (durch ETL Prozesse gesteuert) verarbeitet.
Die Partition werden mit ProcessFull und die Dimensionen mit
ProcessUpdate verarbeitet.
 
Durch einen Abbruch wegen Out-of-Memory stand danach die gesamt Maschine Kopf, alleine der msmdsrv.exe Prozess hat den Server (32GB RAM, 4x Xeon 3GHz DualCore) komplett ausgelastet. Wie sich herausstellte sind merkwürdigerweise sämtliche Aggregationen in den Partitionen verloren gegangen. Mit dem Seiteneffekt das der msmdsrv.exe Prozess wie wild von der Festplatte liest statt seinen Cache zu benutzen, was dazu führte das schon 10-20 parallele MDX Abfragen den Server CPU-seitig zum erliegen brachten. Das Aggregation-Design war noch hinterlegt doch auf Platte fehlten sämtliche agg.flex.data und agg.rigid.data Dateien.
 
Soweit so gut, bereitet man halt alle Partitionen mit ProcessIndex auf und die Welt ist wieder in Ordnung.
 
... Tage später ...
 
Die Performance der Abfragen war wieder i.O. auch die CPU Auslastung durch msmdsrv.exe war wie gewohnt. Doch das Disk I/O (Im TaskManager die Spalte I/O Read Bytes) war mir noch zu hoch. Nach ca. 4 Tagen will der 1,8 TB von Platte gelesen haben?! Die meisten Partitionen hatten wieder ihre agg.flex.data bzw. agg.rigid.data Dateien verloren, was auch der Grund für das Verhalten war.
 
Wer lesen kann ist klar im Vorteil. Nicht grundlos steht im Analysis Services 2005 Processing Architecture beschrieben (Side Effects and Impact Analysis), das beim ProcessUpdate einer Dimension die abhängigen Partition ihre Indexes und Aggregationen verlieren.
 
Also, die ETL Processe angepasst, die auch die Aufbereitung übernehmen, und beim ProcessUpdate einer Dimension werden nun auch die abhängigen Partitionen mit ProcessIndex aufbereitet.
Bereitgestellt um 10:32 von Andy Löwen | Kategorie: Analyses Services | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
SSAS 2008 Attach & Detach Analysis Services Database (Scaleout)
Die Attach und Detach Methoden sollen grundsätzlich die Möglichkeit bieten, um SSAS Datenbanken auf eine andere Storagelocation zu verschieben. Sie bieten dadurch aber auch die Möglichkeit eines Scaleout.
 
Trennen / Detach einer SSAS Datenbank (Lokal):
Das Trennen der Datenbank kann mittels XMLA Command oder über das SSMS (GUI) durchgeführt werden. Wenn für das Trennen der Datenbank ein Kennwort benutzt wird, ist für das Anfügen der Datenbank das gleiche Kennwort erforderlich.
Beim Trennen einer Datenbank werden folgende Schritte durchgeführt.
 
Read/Write DB
  1. Der Server gibt eine Anforderung für eine CommitExclusive-Sperre für die Datenbank aus.

  2. Der Server wartet, bis für alle laufenden Transaktionen ein Commit oder ein Rollback ausgeführt wird.

  3. Der Server erstellt alle Metadaten, die zum Trennen der Datenbank erforderlich sind.

  4. Die Datenbank wird als gelöscht markiert.

  5. Der Server führt einen Commit für die Transaktion aus.

Readonly DB:

  1. Die Datenbank wird als gelöscht markiert.

  2. Der Server führt einen Commit für die Transaktion aus.


<Detach xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>Adventure Works DW 2008</DatabaseID>

</Object>

<Password>SQL2008</Password>

</Detach>

Nach dem Detach wird im Data Verzeichnis der Datenbank eine .detach_log Datei angelegt, die wesentliche Informationen über die Metadaten enthält. z.B. Adventure Works Dw 2008.detach_log

Anfügen / Attach einer SSAS Datenbank (Lokal):
Beim Anfügen einer Datenbank kann der ReadWriteMode gewählt werden (ReadOnly oder ReadWrite). Beim ReadWrite wird versucht die zuvor angelegte .detach_log wieder zu löschen. Ist die Datenbank z.B. im ReadOnly Mode noch angebunden, schlägt dies fehl.

<Attach xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Folder>D:\Programme\Microsoft SQL Server\MSAS10.MSSQL2008\OLAP\Data\Adventure Works DW 2008.0.db</Folder>

<ReadWriteMode xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100">ReadWrite</ReadWriteMode>

<Password>SQL2008</Password>

</Attach>

Abfrage wird ausgeführt...
Dateisystemfehler: Fehler beim Öffnen der Datei '\\?\D:\Programme\Microsoft SQL Server\MSAS10.MSSQL2008\OLAP\Data\Adventure Works DW 2008.0.db\Adventure Works DW 2008.detach_log'.
Ausführung abgeschlossen

Ist der Attach (ReadWrite/ReadOnly) erfolgreich, wird die 0.CryptKey.bin Datei im Datenverzeichnis der DB wieder erstellt und die VersionMap (master.vmp) im DataDir der Instanz aktuallisiert.

SAN ScaleOut Szenario:
Diese Funktionen sind nicht nur nützlich um Datenbanken zu verschieben, es lassen sich darüber auch ScaleOut Szenarien realisieren. In Kombination mit SAN kann so elegant die Aufbereitung von den Abfragen getrennt werden, um bessere Performance zu erzielen.

Beispiel Ablauf:

  1. Aufbereitung der OLAP Objekte (Rechner1)
  2. Detach der Datenbank (Rechner1)
  3. SAN Snapshot der Disk mit der OLAP Datenbank erstellen und auf einem anderen Rechner wieder bereitstellen. (Rechner1 & RechnerN)
  4. Attach ReadWrite der Datenbank (Rechner1)
  5. Weitere Aufbereitung der OLAP Objekte (Rechner1)
  6. Attach ReadOnly der Datenbank (RechnerN)

Ein wirklich Beispielhaften Szenario wie es auch bei Microsoft empfohlen wird. Die Erfahrung hat aber gezeigt das die SAN Snapshots nicht in jeder Kundenkonfiguration bzw. abhängig vom SAN Hersteller so einfach erstellt werden können. Wenn es funktioniert eine elegante und schnelle Lösung für ein ScaleOut. Statt SAN können die Datenbanken auch mit Robocopy oder vergleichbaren kopiert werden, dauert nur wesentlich länger.

Remote Attach / Detach:
Was lokal geht, funktioniert unter SQL 2008 natürlich auch im Netzwerk. Eine wesentlich Vorraussetzung dafür, der SSAS Dienst muss mit einem User gestartet sein, der Zugriff auf das Netzwerk hat. Dem Standard User "LocalSystem" sind diese Rechte entzogen bzw. wird er auf dem Remotehost keine gültige Anmeldung erzielen. Als Folder wird das Remoteverzeichnis der DB angegeben.

<Attach xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Folder>\\RemoteHost\MSAS10.MSSQL2008\OLAP\Data\Adventure Works DW 2008.0.db</Folder>

<ReadWriteMode xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100">ReadOnly</ReadWriteMode>

<Password>SQL2008</Password>

</Attach>

Somit können N Rechner auf den selben Datenbestand über das Netzwerk zugreifen und für Abfragen zur Verfügung stellen. Nur mit dem Nachteil das dass Netzwerk dadurch stark belastet wird. Ein wesentlicher Teil oder gar die komplette DB wird von Analysis Services Nach und Nach im Memory gehalten, was den erneuten Netzwerkzugriff reduziert aber nicht ganz ausschließt. Der andere Nachteil die Datenbank muss auf allen Rechnern erst wieder mit Detach getrennt werder, bevor ein anderer Rechner die Aufbereitung dieser Daten wieder übernimmt.

Quick Hack:
Laut Dokumentation und auch Tests kann eine Datenbank entweder einmal im ReadWrite Mode oder mehrfach im ReadOnly Mode angebunden werden. Eine Kombination ist nicht möglich. Mit einem Trick geht es doch, wobei ich ausdrücklich davor warne dieses undokumentierte Feature ernsthaft zu nutzen. Alle Randerscheinungen und Effekte dazu sind mir noch nicht bekannt, aber es gibt Sie mit Sicherheit.

Beispiel Ablauf:

  1. Detach der Datenbank (Rechner1)
  2. Kopieren der detach_log (Rechner1)
  3. Attach ReadWrite der Datenbank (Rechner1)
  4. Die nun fehlende Datei detach_log aus der Kopie wieder herstellen
  5. Remote Attach ReadOnly der Datenbank (RechnerN)

Die Datenbank kann jetzt wieder abgefragt (Rechner1 & RechnerN) und aufbereitet (Rechner1) werden. Allerdings sind die Neu aufbereiteten Daten, für die ReadOnly angebundenen Rechner erst nach erneutem Detach und Attach (siehe Beispiel Ablauf) sichtbar.

Bereitgestellt um 10:22 von Andy Löwen | Kategorie: Analyses Services | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
SSAS 2005 - neue WHERE Syntax in MDX für bessere Performance
Um in MDX Abfragen unter Analysis Services 2005, die eine Where Klausel benutzen, performante Ergebnisse zu erziehlen ist in vielen Fällen eine neue Schreibweise zu nutzen.
Bei Durchschnittsabfragen meist der Fall

SSAS 2000:
WITH
set Zeitraum as '{Zeit.&[20060101]:Zeit.&[20061031]}'
set Wochentage as '{Montag,Dienstag,Mittwoch,Donnerstag,Freitag}'
member Zeit.Zeitraum as 'AGGREGATE(Zeitraum)'
member Tage.Wochentage as 'AGGREGATE(Wochentage)'
...
SELECT {Measures...} ON AXIS(0)
FROM CUBE
WHERE (Zeit.Zeitraum, Tage.Wochentage)

SSAS2005:
SELECT {Measures...} ON AXIS(0)
FROM
(
  SELECT ({Zeit.&[20060101]:Zeit.&[20061031]}) ON AXIS(0)
  FROM CUBE
)
Bereitgestellt um 10:20 von Andy Löwen | Kategorie: Analyses Services | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
Sind Slowly Changing Dimension (SCD) im SQL Server 2005 überhaupt Neu?
Ein neues Feature, was keines ist?

Was hat Microsoft nicht mit dem „Neuen“ Feature „Slowly Changing Dimensions“ (SCD) geworben. Und auch in den ganzen neuen Büchern zum SQL Server 2005 wird dieses Feature immer wieder umworben und umschrieben. Aber mehr als eine Umschreibung zu den SCD findet man nicht, auch nicht im Internet. Alle beziehen sich auf dieselbe Beschreibung, wie sie bei Microsoft kursiert. Daraus geht aber nicht mehr hervor, als eine grobe Umschreibung des eigentlichen Sinn und Zweck einer solchen Dimension. Für die meisten sind sicherlich die SCD vom Typ 2 oder Typ 3 interessant. Denn nur diese bieten die Möglichkeit historische Veränderungen in der Dimension Nachzuhalten. Aber ging das nicht auch schon im SQL Server 2000????

SCD von Typ 2 (Parent-Child) anhand der AdventureWorks
Nach etwas suchen in der Dokumentation und stöbern in der „AdventureWorks“ findet man heraus wie eine SCD Dimension zu definieren ist.

Die Dimension „Employee“ ist z.B. eine solche. Doch was macht eine SCD vom Typ2 aus.

Das ein Feld mit der Usage Eigenschaft „Key“ benötigt wird, ist unumstritten. Egal ob Parent-Child oder normale Hierarchie definiert sich laut Microsoft eine SCD Typ 2 noch über diverse „Typen“ Attribute


SCDStartDate GültigVon
SCDEndDate GültigBis
SCDStatus Status (aktive oder inaktive)
SCDOriginalID ein nicht eindeutiger Alternatekey, mehr dazu gleich

Die zugrunde liegende Tabelle „DimEmployee“ erfüllt dies. Neben vielen anderen Feldern existieren dort nämlich auch diese.

[EmployeeKey] unser Key Feld
[ParentEmployeeKey] der Parent zum Key
[EmployeeNationalIDAlternateKey] SCDOriginalID
[ParentEmployeeNationalIDAlternateKey] der nicht wirklich benötigte Parent zur SCDOriginalID
[StartDate] SCDStartDate
[EndDate] SCDEndDate
[Status] SCDStatus


Wie hier zu sehen gibt es Mrs. Sheela Word in 3 Ausprägungen. Irgendetwas wird sich wohl im laufe der Zeit bei der Mitarbeiterin geändert haben. Vielleicht hat sie einen anderen Parent erhalten, oder hat geheiratet. Zu erkenne ist in jedem Fall, das sich das Key Feld verändert jedoch nicht die SCDOriginalID. Die Personalnummer bleibt also gleich, egal wie oft der Datensatz geändert wird.

select EmployeeKey
,ParentEmployeeKey
,EmployeeNationalIDAlternateKey
,ParentEmployeeNationalIDAlternateKey
,SalesTerritoryKey
,FirstName
,Lastname
from [AdventureWorksDW].[dbo].[DimEmployee]
where [EmployeeNationalIDAlternateKey] = 895209680

EmployeeKey ParentEmployeeKey EmployeeNationalIDAlternateKey ParentEmployeeNationalIDAlternateKey SalesTerritoryKey FirstName Lastname
----------- ----------------- ------------------------------ ------------------------------------ ----------------- -------------------------------------------------- --------------------------------------------------
278 73 895209680 121491555 11 Sheela Word
279 73 895209680 121491555 11 Sheela Word
280 73 895209680 121491555 11 Sheela Word

(3 row(s) affected)



Hier sieht man wie die Dimension Employee in der AdventureWorks definiert ist. Das Attribute SCDOriginalID habe ich selbst gepflegt.


Da eine Dimension immer mit dem Key Feld zur Faktentabelle referenziert, kann man jede Ausprägung der Sheela Word mit MDX analysieren. Doch will ich das immer?
Mal angenommen mich interessieren 20 – 30 oder gar 100 Mitarbeiter, jeder mit 5 bis 10 Ausprägungen. Sind solche Abfragen überhaupt noch pflegbar?

Wäre es unter Umständen nicht besser einfach nur die SCDOriginalID als Auswahl in der Abfrage zu hinterlegen, also Sheela Word.
Mich interessiert der Wert eines Measures pro Ausprägung vielleicht gar nicht. Ich möchte einfach den aggregierten Wert des Measure für meine Zeitbetrachtung haben. Welche Ausprägungen dafür notwendig sind, soll der Server entscheiden. Hauptsache er beachtet die Parent-Child Hierarchie dabei.

Schöner Traum, und das wird es auch erstmal bleiben.
Denn diese Möglichkeit gibt es nicht. Ich könnte zwar eine Hierachie auf die SCOriginalID legen und diese mittels der Filter Funktion in MDX abfragen, doch stößt man damit schnell an Performance Grenzen. Unter bestimmten Umständen erhält nur Werte der Datamember selbst, die Parent-Child Hierarchie wird missachtet. Somit falsche Werte.

Kurzum, wenn das nun die neuen SCD sein sollen, ist das echt schade. Denn das ging schon im alten SQL Server 2000 und war dort sogar Alltag.

In einem meiner letzten Projekte war genau dies auch ein Thema.
Eine Frage an das BI Kompetenz Center von Microsoft, der Kunde gehört zu den etwas größeren Microsoft Kunden, hat dann ergeben dass die obigen Attribute überhaupt keine Auswirkung auf das Verhalten der Dimension haben. Es sind nur Attribute die der abfragenden Applikation als Informationen dienen soll. Wie diese dann damit umgeht, bleibt Ihr überlassen. Zum jetzigen Zeitpunkt konnte uns Microsoft keine Applikation nennen, die den obigen Traum bereits realisiert hat.

Wie erfüllt man sich also den Traum, man schreibt sich die passende Assembly dafür selbst. Eine erste Alpha Version habe ich bereits fertig.
Sobald ich das erste CTP :-) oder gar Release fertig habe, veröffentliche ich dies natürlich hier.
Bereitgestellt um 10:18 von Andy Löwen | Kategorie: Analyses Services | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
Werte bis Gestern (MDX)
Aufgabe:
Dynamisch mit Werte nur bis gestern anzeigen (MDX)

Definition:
Beide Zeitdimensionen sind mit dem Datum an die Faktentabelle gebunden

Dimension: DIM_JahrMonatDatum
Hierachie: Jahr (2005)
Monat (200504)
Datum (20050403)

Dimension: DIM_WocheDatum
Hierachie: Woche (200513)
Datum (20050329)

Angenommen heute ist der 11.04.2006, dann sollte das folgende Statement für das Measure "Wert" alle Werte bis ende des Monats liefern.
Das Calculated Measure "WertBisGestern" allerdings nur bis zum 10.04.2006
Alle Monate vor dem aktuellen werden natürlich komplett berechnet.

In MDX
with
set setZeitraum as '{[DIM_JahrMonatDatum].&[200601]:[JahrMonat].&[200604]}'
...
--Mit "[DIM_JahrMonatDatum].CurrentMember.Level.Ordinal > 0" frage ich ab,
--ob ich gerade in der Dimension bin oder ob ich auf "Alle DIM_JahrMonatDatum" stehe.
--Das ist hilfreich um zwischen verschiedenen Dimensionen zu unterscheiden die sich ähneln
member measures.DatumCurrent as '
iif([DIM_JahrMonatDatum].CurrentMember.Level.Ordinal > 0
,left(right(membertostr(DIM_JahrMonatDatum.currentmember),7),6)
,left(right(membertostr(DIM_WocheDatum.currentmember),7),6)
)'

--Ergibt z.B 200604 (JahrMonat) und dient zur Überprüfung mit DatumCurrent, ob wir
--uns gerade mit currentmember im aktuellen oder vergangenen Monaten befinden
member measures.DatumGestern as '
iif([DIM_JahrMonatDatum].CurrentMember.Level.Ordinal > 0
,left(right(membertostr(strtomember("DIM_JahrMonatDatum.[" + VBA!format(VBA!dateadd("d",-1,VBA!now()),"yyyymmdd") + "]")),9),6)
,left(right(membertostr(strtomember("DIM_WocheDatum.[" + VBA!format(VBA!dateadd("d",-1,VBA!now()),"yyyymmdd") + "].Parent")),7),6)
) '

set setMonatBisGestern as '
Periodstodate(DIM_JahrMonatDatum.Monat,strtomember("DIM_JahrMonatDatum.[" + VBA!format(VBA!dateadd("d",-1,VBA!now()),"yyyymmdd") + "]")) '

set setWocheBisGestern as '
Periodstodate(DIM_WocheDatum.Woche,strtomember("DIM_WocheDatum.[" + VBA!format(VBA!dateadd("d",-1,VBA!now()),"yyyymmdd") + "]"))'

member measures.WertBisGestern as '
iif(DatumGestern=DatumCurrent
,iif([DIM_JahrMonatDatum].Currentmember.Level.Ordinal > 0
,AVG(setMonatBisGestern, (Wert))
,AVG(setWocheBisGestern, (Wert))
)
,iif(DatumGestern<DATUMCURRENT< span>
,null
,(Wert)
)
)'
...
SELECT {measures.Wert, measures.WertBisGestern} on Axis(0),
{setZeitraum} on Axis(1)
FROM Cube
Bereitgestellt um 10:17 von Andy Löwen | Kategorie: Analyses Services | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
SSAS2000 OLAP Datenbank umbenennen (Inoffiziell)
OLAP Datenbank umbenennen (Inoffiziell)

Hier ist kurz beschrieben wie eine OLAP Datenbank unter SQL Server 2000 umbenannt werden kann.

  • Die SQL OLAP Repository Datenbank vorher sichern
  • Den Service MSSQLServerOLAPService stoppen
  • Das Verzeichnis der OLAP Datenbank umbenennen (z.B. C:\Programme\Microsoft Analysis Services\Data\DB4711 -> C:\Programme\Microsoft Analysis Services\Data\DB0815)
  • In der SQL OLAP Repository DB in der Tabelle OlapObjects den Namen der Datenbank von DB4711 auf DB0815 ändern (z.B. Feld ObjectName von DB4711 -> DB0815). Dann noch das Feld ObjectDescription ändern (z.B. ....' -> '...'
  • Im Data Verzeichnis der Analysis Services die Datei DB4711.odb -> DB0815.odb umbenennen.
  • Den Service MSSQLServerOLAPService wieder starten

Sollte in der Analysis Management Console die Änderung nicht angezeigt werden, ggfls. die Console neu starten.

Bereitgestellt um 10:16 von Andy Löwen | Kategorie: Analyses Services | Permalink | Diesen Beitrag per E-Mail senden | Kommentare (0)
certlogo
Small Business Specialist
Impressum  | Kontaktieren Sie uns  |