I came around this problem (depends on SQL Server 2008 R2 jutst to SQL Server 2012) by reading Paul S. Randal's and Ana's blog posts:
DBCC CHECKDB runs CHECKALLOC, CHECKTABLE, CHECKCATALOG etc. against database, tables, views and so on. And I have learnded, however CHECKDB is executed against master database, a second CHECKDB is also running internally on the mssqlsystemressource.
So in my way, I want only make consistency check of disk space allocation by DBCC CHECKALLOC WITH ESTIMATEONLY against a ~ 200GB database, to know the estimated amount of tempdb space.
I run following DBCC command on SQL Server 2012 Enterprise Edition:
DBCC CHECKALLOC () WITH ESTIMATEONLY, TABLERESULTS;
GO
And I get this result
Estimated TEMPDB space (in KB) needed for CHECKALLOC on database XXX = 35383596.
OK, nothing special - but I thought about the posts of Ana and Paul, and a blog entry of Running SQP on SQL Server Blog - Faster DBCC CHECKDB Released in SQL 2008 R2 SP1 CU4 : Traceflag 2562 & 2549
So next shot comparing CHECKDB and CHECKALLOC:
DBCC CHECKDB WITH ESTIMATEONLY, TABLERESULTS;
GO
DBCC CHECKALLOC WITH ESTIMATEONLY, TABLERESULTS;
GO
And the results are diffrent!
For CHECKDB:
Estimated TEMPDB space (in KB) needed for CHECKDB on database XXX = 27165.
And for CHECKALLOC:
Estimated TEMPDB space (in KB) needed for CHECKALLOC on database XXX = 35383596.
This isn't an expected result - what a discrepancy!
By the way I have made a Microsoft Connect entry: http://connect.microsoft.com/SQLServer/feedback/details/787049/discrepancy-by-estimated-tempdb-space-for-checkdb-and-checkalloc-with-estimateonly
So feel free to vote.
I wish you a nice day,
tosc
Heute hörte ich zum ersten Mal von dem BigData-Projekt Polybase von Microsoft. Es geht darum mit dem SQL Server Parallel Data Warehouse Daten aus Hadoop abzufragen. Das finde ich in mehrfacher Hinsicht bemerkenswert: Microsoft versucht nicht Hadoop zu toppen, sondern zu integrieren. Das könnte ein Modell für andere Datenbanksysteme werden, z.B. im Bereich InMemory-Datenbanken. Hier [...]
As I already stated out in my first post I want share some insights about the new SQL Server 2012 version of Parallel Data Warehouse. In this post I will talk about some new features as well as architecture changes.
Architecture Update
The SQL Server 2012 version of PDW introduces some major architecture changes:
- As show in the picture below the Landing Zone and the Backup node have been removed from the appliance and there good reasons for. The standard sizing of these component didn’t met most of the customer requirements and it was very hard to find a configuration that meets 80% of PDW customers. So decision has been made to remove these components and customers have now more flexibilities to size the ETL and the Backup node to their needs.
- Expensive storage components have been replace by an economical high density Direct Attached Storage
- Virtualization of the Software Components based on Windows Server 2012 and Hyper-V
- Modular Design and Smarter Scale Out: SQL Server 2012 PDW has reshaped the very hardware specifications required of an appliance through innovations from the software to deliver optimal value to customers. The new version introduces a new modular design, as show in the following picture:
The smallest PDW appliance consists of a Base Scale Unit (with a Passive Scale Unit for HA) and can be extended by a number of Capacity Units. If you reach the physical space limit of the rack you can add up to 6 more racks.
Depending of the vendor the units are shipped differently: HP offers a Base Scale Unit with 2 compute nodes and every Capacity Unit is also shipped with 2 compute nodes (left side of the picture). Dell offers the Base Scale Unit and the Capacity Unit with 3 compute nodes each (right side of the picture).
If we have a look at the capacity the smallest PDW appliance from HP (Quarter Rack with only the Base Scale Unit) offers a capacity of 53 – 227 TB (depending on the compression rates) and a raw disk space of 45 TB (with 3 TB disks). The Quarter Rack of DELL provides a capacity of 79 – 3470 TB (depending on the compression rates) and a raw disk space of 68 TB (with 3 TB disks).
- Further Scale: Based on the new modular hardware design and a hardware refresh PDW offers now a scale out capacity up to 5 Petabytes.
Software Architecture Overview
The new version of PDW provides also some major software updates. As the product name already states PDW is now running on SQL Server 2012. The operating system on all hosts is Windows Server 2012 Standard edition. All fabric and workload activity happens in Hyper-V virtual machines which also run on Windows Server 2012 Standard edition. A PDW Agent runs on all hosts and all VMs and collects appliance health data on fabric and workload.
A special PDW version of SQL Server 2012 Enterprise Edition is used on the Control node and on all Compute nodes to provide high scale database capabilities.
New Features
Additionally to the new hardware and software architecture I want to highlight some more very interesting features for customers:
- Columnar Storage: Microsoft continues the rollout of xVelocity and provides with SQL Server 2012 PDW a new primary storage type for databases. Customers can now choose between a row store and a new updateable version of the xVelocity memory optimized columnstore as table storage format. This means that we can define a writable Clustered Columnstore Index (Updates and bulk load are fully supported) at a table so that the whole table is stored into memory and we benefit of a much higher compression by the column oriented storage format.
- Visual Studio 2012 Integration: As you probably know the tool used for database administration and database development in PDW Version 1 was Nexus. With the new version of PDW we have now full support for SQL Server Data Tools for Visual Studio 2012.
Also the project types for SSIS, SSRS & SSAS are fully supported with Visual Studio 2012.
- Monitoring Enhancements: The whole monitoring of sessions, queries, loads, appliance health status and performance information has been completely redesign. Microsoft did a very good job here and the design looks like the Azure Portal. This means also for customers that monitoring and operations will look like the same On Premises as well as in the Cloud on Windows Azure. The following screenshot gives an impression of the new web user interface:
- Polybase & Hadoop Integration: Today almost every big database vendor also provide a “Big Data” solutions based on Hadoop. Whether it’s a vendor specific distribution like IBM’s BigInsights solution, which is based on Cloudera or Microsoft’s specific implementation of Hortonworks Hadoop distribution called HDInsight or a full appliance which comes pre-installed with Hadoop. No matter which Hadoop platform you choose you still have the challenge of integration. Some type of data and analytics will happen on Hadoop (like text, log or sensor analysis) but we will still use databases for BI & Reporting.
While this might be something you have decided to do, you realize that there is a big learning curve when your IT department needs to re-orient themselves around HDFS, MapReduce, Hive, Hbase, etc. rather than T-SQL and a standard RDBSMS design. It will require a significant re-training around Hadoop and the ecosystem as well as a major effort to integrate the Hadoop implementation with the data warehouse.
In order to meet the requirements of a modern data platform, it must provide insights to your end users without having to acquire another tool from a third party or another expensive appliance offering to purchase.
The unfortunate reality is that no one vendor can deliver on all the options you need at a cost that you want to pay. They either have a data warehouse solution but no BI or provide BI but no data warehousing. Some vendors provide a Big Data solution but is disconnected with their data warehouse solution. Finally, some vendors might have a solution for each workload and will happily charge you millions of euros or dollars for them all.
Microsoft goes a different way and brings with SQL Server 2012 PDW an integrated query layer called “Polybase” which enables queries across Hadoop and SQL Server. Data structures stored in Hadoop are described by tables in PDW and customers can consume these data by standard T-SQL and can also join those tables with normal relational ones. So to end users that only consume this data it’s totally transparent where the data comes from and IT departments can use their normal database skillset to work with Hadoop. How does those kind of tables look like?
Now we can easily query and join this table.
Polybase is developed by the PDW team together with the Gray System Lab and its famous team lead David DeWitt. More detailed information can be found on the project page: http://gsl.azurewebsites.net/Projects/Polybase.aspx or in the video of the PASS 2012 conference http://www.sqlpass.org/summit/2012/DavidDewittSpotlight.aspx
Summary
As you can see from this post Microsoft did very heavy investments in its modern Big Data platform and gives customers the possibility to invest build high scale solutions on SQL Server 2012 PDW as well as on HDInsight for Hadoop based workloads. With Polybase customers get a fully integrated Hadoop query engine into the Data Warehouse Layer that can easily consumed with T-SQL knowledge.
The new architecture and the modular design of the appliance gives customers the possibility to start with small investments and scale very cost efficient on demand.
In my next post I will talk in more detail about a POC I did with the new version and the results and lessons learned.
Source: Microsoft slide decks of SQL Server 2012 Parallel Data Warehouse
Die Systemvariable @@PROCID enthält die Objekt-ID einer aktuellen Gespeicherten Prozedur, einer Funktion oder eines Triggers.
Ein kleines Beispiel soll dies verdeutlichen.
Dazu ist zunächst im SQL Server Management Studio folgende Gespeicherte Prozedur anzulegen.
CREATE PROC pWerWars
AS
SET NOCOUNT ON;
DECLARE @Meldung nvarchar(1000);
SET @Meldung = N’Dieses Ergebnis wurde Ihnen präsentiert von der Prozedur ‘ + Object_Name(@@PROCID)
SELECT @Meldung as Hinweis;
Die Funktion Object_Name ermittelt anhand [...]
So langsam geht es voran! Nun wurde auch das Fix für diesen Fehler beim Export per Query (SQL Server 2012 SP1) erstellt. Das dauert noch etwas bis zur Verfügbarkeit, aber es gibt mittlerweile zwei Workarounds.
Full story »
Bei unserem ersten SQLSaturday #170 in München hatten wir 3 parallele Tracks und konnten somit 15 verschiedene Sessions anbieten. Eine damals sehr schwierige Entscheidung, da wir sehr viele hochkarätige Sessions eingereicht bekommen haben.
Bei der Hochschule Bonn-Rhein-Sieg haben wir nun die Möglichkeit 5 parallele Tracks zu veranstalten und somit Platz für ca. 25 verschiedene Sessions. Macht es die Aufgabe leichter? Ich glaub eher im Gegenteil, bisher wurden schon über 80 verschiedene Vorträge eingereicht und ich kenne einige Sprecher, die noch nicht dabei sind…Ob es das dieses Jahr einfacher für uns macht? Ich glaube kaum.
Die einzelnen Tracks werden von uns wieder mit den Vorträgen thematisch besetzt. So wird es wieder einen DBA, einen BI und einen Developer Track geben.
Neu in diesem Jahr ist ein Track zum Thema Openness, hier wollen wir Vorträge rund um die Themen Open Source und Interoperabilität unterbringen -alles dabei natürlich auch irgendwo im Rahmen des Microsoft SQL Server. Im Übrigen wurde vor die Microsoft Open Technologies Inc., die sich mit diesen Themen beschäftigt, gerade ziemlich genau vor einem aus dem Microsoft Team für Interoperabilitäts-Strategien heraus gegründet.
Der 5 Track ist aktuell thematisch noch nicht genau benannt.
Der Call for Speakers ist noch bis zum 15.05.2013 geöffnet. Auch wenn die bereits angesprochenen 80 Sessions eingereicht wurden, wir freuen uns über jede weitere Einrichung. Jeder hat noch eine Chance, die Vorträge werden nicht nach dem Datum der Einreichung ausgewählt, sondern auf Basis des eingereichten Abstracts. Die Konferenzsprache ist im Übrigen Deutsch und Englisch gemischt. Auch wenn es auf den ersten Blick den Anschein macht, das alles Sessions in Englisch gehalten werden, es gibt auch einige deutsche Tracks. Die Wahl der Sprache für euren Vortrag bleibt euch überlassen und zählt ebenfalls nicht zu den Auswahlkriterien (solange ihr bei Deutsch und Englisch bleibt 
).
Falls du keinen Vortrag einreichen möchtest, aber noch spezielle Themen vermisst, würde wir uns über eine kurze Info freuen. Entweder per Email an das SQLSaturday-Team oder auch gerne al Kommentar hier im Blog
|
|
|
Hallo PASS‘ler,
hiermit möchten wir euch zu unserem nächsten Treffen der Regionalgruppe Köln/Bonn/Düsseldorf einladen:
Donnerstag, 16. Mai 2013, 19:00 bis ca. 22:00 Uhr
Wir treffen uns wieder bei Microsoft in Köln.
|
|
SQL Server & PowerShell - Nadine Koch
|
|
Wir gehen den Fragen nach:
- Welche Möglichkeiten bietet PowerShell um auf den SQL Server zuzugreifen?
- Was benötige ich um mit PowerShell auf den SQL Server zugreifen zu können?
Anschliessend schauen wir uns die Fallstricke bei den Policy Settings an.
Zahlreiche Beispiele zum ersten Einsatz von Powershell, Informationsbeschaffung aus SQL Server Umgebungen und administrative Tasks mit PowerShell runden den Vortrag ab.
Nadine Koch ist Systemadministratorin bei der Ecofis GmbH und dort zuständig für das SQL Server Umfeld. Nadine ist Fachinformatikerin und hat seit dem Abschluss ihrer Ausbildung SQL Server Umgebungen in unterschiedlichen Unternehmen mit verschiedenen Anforderungen und Technologien betreut.
|
|
Einsatzmöglichkeiten von Window Functions – Christoph Muthmann
|
|
Bereits ab der Version 2005 erweitern diese Funktionen die Möglichkeiten von T-SQL. Im Zusammenhang mit Common Table Expressions lassen sich komplexe Sachverhalte einfach auswerten.
Mit SQL Server 2012 gab es noch einmal eine Erweiterung dieser Funktionen. Wir werden uns die Grundlagen anschauen und anhand einiger Beispiele die Einsatzmöglichkeiten betrachten.
Christoph Muthmann ist seit vielen Jahren mit dem Design und der Administration von Datenbanken beschäftigt. Seit 1994 arbeitet er für den "Verband der Vereine Creditreform" in Neuss im Bereich der Betreuung und Weiterentwicklung der Datenbanken auf Oracle, iSeries und SQL Server. Sein Schwerpunkt ist die Weiterentwicklung und Administration der SQL Server mit über 600 Datenbanken, welche er seit ca. 1996, beginnend mit der Version 6.5 betreut. Seit 2008 ist er SQL Server MVP, berichtet in seinem Blog auf insidesql.org über SQL Server und PASS und seit 2010 unterstützt er aktiv die PASS Regionalgruppe.
|
|
Wir treffen uns bei
|
|
Microsoft Deutschland GmbH
Geschäftsstelle Köln
Holzmarkt 2a
50676 Köln
Tel.: +49 221 8010 0 (Zentrale)
Bitte beachten: Im Haus gibt es eine Tiefgarage, die genutzt werden kann.
|
|
Wir bitten um eine vorherige Anmeldung per Email an: rgv_kbd@sqlpass.de.
Wir freuen uns auf eine rege Runde – Bitte schreibt in der Anmeldung dazu, ob Ihr Interesse und Zeit habt, im Anschluss noch eine Runde zum Plaudern & Networking in ein Lokal in der Nähe zu gehen.
Tillmann, Andreas, Christoph
|
|
Kontakt
|
|
PASS Deutschland e.V.
http://www.sqlpass.de
Regionalgruppe Köln/Bonn/Düsseldorf
http://www.sqlpass.de/Regionalgruppen/KoelnBonnDuesseldorf/tabid/81/Default.aspx
|
Unser nächstes Treffen der Regionalgruppe Köln/Bonn/Düsseldrof findet am 16.05.2013 wieder bei Microsoft in Köln statt.
Bei diesem Treffen haben wir auch mal wieder 2 Vorträge. Zum einen ist Nadine Koch mit dem SQL Server & PowerShell bei uns zum anderen mein RGV Kollege und SQL Server MVP Chrsitoph Muthmann mit dem Thema Einsatzmöglichkeiten von Windows Functions.
Wie immer findet ihr weitere Informationen auf der Internetseite unserer Regionalgruppe.
SQL Server & PowerShell – Nadine Koch
Wir gehen den Fragen nach:
- Welche Möglichkeiten bietet PowerShell um auf den SQL Server zuzugreifen?
- Was benötige ich um mit PowerShell auf den SQL Server zugreifen zu können?
Anschliessend schauen wir uns die Fallstricke bei den Policy Settings an. Zahlreiche Beispiele zum ersten Einsatz von Powershell, Informationsbeschaffung aus SQL Server Umgebungen und administrative Tasks mit PowerShell runden den Vortrag ab.
Nadine Koch ist Systemadministratorin bei der Ecofis GmbH und dort zuständig für das SQL Server Umfeld. Nadine ist Fachinformatikerin und hat seit dem Abschluss ihrer Ausbildung SQL Server Umgebungen in unterschiedlichen Unternehmen mit verschiedenen Anforderungen und Technologien betreut.
Einsatzmöglichkeiten von Window Functions – Christoph Muthmann
Bereits ab der Version 2005 erweitern diese Funktionen die Möglichkeiten von T-SQL. Im Zusammenhang mit Common Table Expressions lassen sich komplexe Sachverhalte einfach auswerten. Mit SQL Server 2012 gab es noch einmal eine Erweiterung dieser Funktionen. Wir werden uns die Grundlagen anschauen und anhand einiger Beispiele die Einsatzmöglichkeiten betrachten.
Christoph Muthmann ist seit vielen Jahren mit dem Design und der Administration von Datenbanken beschäftigt. Seit 1994 arbeitet er für den “Verband der Vereine Creditreform” in Neuss im Bereich der Betreuung und Weiterentwicklung der Datenbanken auf Oracle, iSeries und SQL Server. Sein Schwerpunkt ist die Weiterentwicklung und Administration der SQL Server mit über 600 Datenbanken, welche er seit ca. 1996, beginnend mit der Version 6.5 betreut. Seit 2008 ist er SQL Server MVP, berichtet in seinem Blog auf insidesql.org über SQL Server und PASS und seit 2010 unterstützt er aktiv die PASS Regionalgruppe.
Wir treffen uns bei:
Microsoft Deutschland GmbH
Geschäftsstelle Köln
Holzmarkt 2a
50676 Köln
Tel.: +49 221 8010 0 (Zentrale)
We are using SQL Server 2012 and the tempdb is growing so fast. How do I effectively find what’s causing my tempdb growing. Can I monitor the file size change with Extended Events to find out the ...
The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server. tempdb is re-created every time is started so that the system always starts with a clean copy of the database. The database engine uses tempdb for:
- Temporary user objects (temp tables, table variables ...)
- Internal objects (work tables/work files/ intermediate results ...)
- Version store for data modification transactions (Online indexes, After trigges, Snapshot, MARS ...)
- Service Broker, database mail ...
But this is another deep dive topic of tempdb latch contention on allocation pages... :-)
For now I only want to know
What's causing that my tempdb is growing so fast?
I usually look first to the tempdb file size and autogrow settings, you know something like this:
--> SQL Server tempdb file size and autogrow information <--
SET NOCOUNT ON;
GO
USE [master];
GO
-- Check for trace flag 1117/1118 or any other trace flag
DBCC TRACESTATUS (1117) WITH NO_INFOMSGS; -- Grows all data files at once, else it goes in turns.
DBCC TRACESTATUS (1118) WITH NO_INFOMSGS; -- Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent
DBCC TRACESTATUS () WITH NO_INFOMSGS;
SELECT
old.physical_name AS 'FileName',
old.name AS 'LogicalName',
old.size * 8.0 / 1024.0 AS 'OriginalSizeInMB',
new.size * 8.0 / 1024.0 AS 'CurrentSizeInMB',
'GrowthType' =
CASE
WHEN new.growth = 0 THEN 'File is fixed size and will not grow.'
WHEN new.growth > 0 AND new.is_percent_growth = 0
THEN 'Growth increment is in units of 8-KB pages, rounded to the nearest 64 KB'
ELSE 'Growth increment is expressed as a whole number percentage.'
END,
'GrowthIncrement' =
CASE
WHEN new.is_percent_growth = 0 THEN CAST((new.growth * 8 / 1024) AS NVARCHAR(30)) + 'MB'
ELSE CAST (new.growth AS NVARCHAR(30)) + '%'
END,
'MaximumFileSize' =
CASE new.max_size -- file size, in 8-KB pages
WHEN 0 THEN 'No growth is allowed.'
WHEN -1 THEN 'File will grow until the disk is full.'
-- !Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file!
WHEN 268435456 THEN 'Log file will grow to a maximum size of 2 TB.'
ELSE CAST (new.max_size * 8.0 / 1024.0 AS NVARCHAR(30)) + 'MB'
END
FROM
MASTER.sys.master_files old INNER JOIN tempdb.sys.database_files new ON
old.FILE_ID = new.FILE_ID
WHERE
database_id = DB_ID('tempdb')
AND old.size <> new.size
In fact, this is a good starting point to presize tempdb, for peak periods or set autogrow to be rare but "big enough", but I want to know what's causing my tempdb to grow so fast.
So I came out on Extended Events. Extended Events are a new mechanism introduced in SQL Server 2008 to capture SQL Server performance data with low overhead compared to SQL Server profiler, server side tracing, DBCC commands and other utilities. The scope of Extended Events is far beyond this tip and please refer to the articles below for better understanding of this feature. Extended Events allow more granular level tracking that was impossible in prior SQL Server versions. Extended Events in SQL Server 2012, XE in short, allows you capturing performance monitoring data at individual database level or per session level and for this post I will track information at the database level for tempdb.
The code snippet below creates the session to track tempdb file size changed and some informations of client application, session id, client hostname and so on.
--Drop the event if it already exists
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'tempdb_file_size_changed')
DROP EVENT SESSION [tempdb_file_size_changed] ON SERVER;
GO
-- Create event
CREATE EVENT SESSION [tempdb_file_size_changed] ON SERVER
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
ACTION
(sqlserver.client_app_name,sqlserver.client_hostname, sqlserver.database_id,sqlserver.session_id,
sqlserver.session_nt_username,sqlserver.username) WHERE ([database_id]=(2)))
ADD TARGET package0.event_file
(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\tempdb_file_size_changed.xel')
WITH (MAX_DISPATCH_LATENCY=1 SECONDS)
GO
Once the script is completed and started I can wait for my tempdb rumbling.
To summarize the file size change, I ran these following queries:
SET NOCOUNT ON;
GO
USE [master];
GO
SELECT
database_name AS DatabaseName
, file_type AS FileType
, SUM (size_change_kb) AS Total_Size_Change_KB
, SUM (duration) AS Total_Duration
, client_app_name AS Client_Application
, session_id AS SessionID
FROM (
SELECT
n.value ('(data[@name="size_change_kb"]/value)[1]', 'int') AS size_change_kb
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
FROM
( SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\tempdb_file_size_changed*.xel',
NULL,
NULL,
NULL)
) AS tab
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
GROUP BY client_app_name, database_name, file_type, session_id
ORDER BY client_app_name
And what was causing my tempdb, it was me by Microsoft SQL Server Management Studio :-( - only for this blog entry.
Feel free to download these scripts:
I wish you a nice day,
tosc
Die Google API kann derzeit auf über 100 verschiedene Dimensionen und fast ebenso viele Metriken zugreifen. Eine vollständige Liste aller Dimensionen und Metriken ist in der
Dimensions & Metrics Reference inkl. detaillierter Beschreibung und teilweise sogar mit den genauen Kalkulationen zu finden. Leider besteht bisher jedoch keine Möglichkeit die Dimensionen und Metriken über die API inkl. Datentypen auszulsen, so dass ich diese für die meine SSIS GoogleAnalyticsSource Komponente manuell hinterlegen muss.
In den ersten Versionen waren die entsprechenden Daten als XML Dateien in die Komponente eingebettet, die bei jedem neuen Releases manuell aktualisiert wurden. Seit der letzten Version verfügt die Komponente aber auch über die Möglichkeit Dimensionen und Metriken aus einer externen XML-Datei einzulesen – leider ist das Feature noch nicht so sehr bekannt.
In der Eigenschaften der Komponente befinden sich die beiden Eigenschaften Dimensions und Metrics, jeweils mit den Standardwerten <Embedded Resource>.
Über die Schaltfläche neben der Textbox lassen sich auch XML Dateien mit den jeweiligen Daten aus dem Dateisystem laden. Die Originaldateien werden während der Installation mit in das Installationsverzeichnis %PROGRMFILES%\SSIS-Components.net\SSIS GoogleAnalyticsSource\ gespeichert und können dort sehr einfach editiert werden.
<Dimensions>
<ID>24</ID>
<Name>Ad Destination URL</Name>
<APIName>ga:adDestinationUrl</APIName>
<Description>The URLs to which your AdWords ads referred traffic.</Description>
<Category>AdWords</Category>
<DataType>DT_WSTR</DataType>
<Length>4000</Length>
<Precision />
<Scale />
</Dimensions>
Die ID wird ausschließlich intern verwendet, Name und Description dienen der Anzeige innerhalb der Kompoente, APIName ist der Name innerhalb der Google API. Die vier Elemente DataType, Length, Precision und Scale beschreiben den Datentypen innerhalb der SQL Server Integration Services. Das Elemnt Category stammt auch aus der Google Referenz, wird derzeit aber noch nicht innerhalb der Komponente verwendet.
Die Metriken vefügen über die identische Datentruktur
<Metrics>
<Name>AVG Event Values</Name>
<APIName>ga:avgEventValue</APIName>
<Description>The average value of an event.</Description>
<ID>210</ID>
<Category>Event Tracking</Category>
<DataType>DT_R8</DataType>
<Length />
<Precision />
<Scale />
</Metrics>
Veröffentlich Google neue Dimensionen oder Metriken oder stimmen die hinterlegten Datentypen der Metriken/Dimensionen nicht mehr mit der API überein, so können Elemente in einer oder beiden Dateien verändert werden und in der Komponente ausgewählt werden.
Zu beachten ist der Speicherort der entsprechenden Dateien. Der Installationspfad eignet sich im Normalfall nicht dafür, da der ausführende Benutzer/Prozess des SSIS Paketes über entsprechende Berechtigungen verfügen muss. Auch beim Deployment ist auf die entsprechenden Dateien zu achten, da diese manuell auf das Zielsystem kopiert werden müssen.
Gemeinsam mit Microsoft Partnern vor Ort informiert Microsoft Deutschland in den kommenden Monaten in einer bundesweiten Veranstaltungsreihe über Einsatzmöglichkeiten und Effizienzpotentiale der neuesten Generation business-relevanter Anwendungen und Services. Von Windows 8, über Office 365 oder Windows Server 2012 bis hin zur Integration von Windows Phone 8 und den Tablets der „Surface“-Reihe in das Microsoft Ecosystem.
Full story »
Am 07.05.2013 bin ich zum ersten Mal in der Regionalgruppe Ruhrgebiet zu Gast.
Zusammen mit meiner Kollegin Gabi Münster, präsentiere ich den Vortrag Advanced Data Visualization with SQL Server Reporting Services, den ich auch zusammen mit Oliver auf der PASS Business Analytics Conference gehalten habe.
Weitere Informationen zum Vortrag und zur Veranstaltung sind auf der PASS Seite der RG Ruhrgebiet zu finden: http://www.sqlpass.de/Regionen/Deutschland/Ruhrgebiet.aspx
Advanced Data Visualization with SQL Server Reporting Services
Mit der Standard SQL Server Reporting Services (SSRS) Engine, können schnell und einfach simple Charts erstellt werden. Im Real-Life Reporting sind die Anforderungen jedoch meist wesentlich komplexer. Für eine verbesserte Informationsbereitstellung müssen Methoden von Edward Tufte, Stephen Few, oder Prof. Rolf Hichert eingesetzt werden sowie Möglichkeiten zur Visualisierung eines Corporate Design oder Spatial Data bedacht werden.
Dieser Vortrag konzentriert sich vollständige auf Charts, Gauges und Maps. Wir zeigen verschiedene Tricks und Workarounds, Tips zur Erstellung einer eigenen Chart Engine, erstellen eigene Maps und zeigen neue Wege zur Visualisierung von Daten in SSRS.
So, dies ist er, mein erster Blog Post in Deutsch. Und warum? Weil es um die deutsche SQL PASS “Community” geht. Und um etwas genauer zu werden: der zweite SQLSaturday in Deutschland steht an.
Wer noch keine Idee hat, worum es sich bei dem SQLSaturday handelt, der guckt sich kurz meinen vorangegangenen Blog Post an und natürlich die offizielle Seite www.sqlsaturday.com und dann SQLSaturday #230 Rheinland.
Der so genannte “Call for speakers” findet ja auf der Eventseite statt. Wer also gerne eine Präsentation zum Thema SQL Server (DBA, BI, DEV) halten möchte, der kann einen Session Vorschlag einreichen.
Ich mache hingehen den Ruf nach Fotografen. Warum? Nun, ich bin Anfang des Monats von einem Organisator (um genau zu sein: von Tillmann (b|t)) des ersten und auch des kommenden SQLSaturdays in Deutschland gefragt worden, ob ich nicht dieses Event mit der Kamera begleiten möchte.
Da ich ganz gerne mal zur Kamera greife, werde ich natürlich das Event, so gut es geht, in Fotos festhalten.
Da es an dem Tag mehrere Session Tracks geben wird und ich nicht in jeder Session zugegen sein kann suche ich ein wenig Unterstützung.
Konkret ist Folgendes geplant:
· Fotos aller Sessions
· Fotos während der Pausen des Events
· Fotos der Sponsoren
· Fotos von den Preisverleihungen (Die Sponsoren, welche mit Ständen vertreten sind, bringen gerne mal das ein oder andere Gimmick mit)
Optimal wäre es, wenn sich 2-3 Leute (gerne auch mehr) finden, die ebenfalls Spaß haben, den SQLSaturday mit der Kamera zu begleiten.
Zwei wichtige Punkte muss ich aber noch erwähnen:
· natürlich sollt Ihr auch von der Konferenz an sich etwas mitbekommen. Aber je nachdem, wie viele Freiwillige sich finden, kann es sein, dass Ihr nicht jede Wunsch-Session mitnehmen könnt. So oder so muss man hier aber noch den Session Schedule abwarten. Vielleicht passt es ja auch auf Anhieb.
· die Mitarbeit erfolgt unentgeltlich. Die SQLPass lebt ja primär von der freiwilligen Mitarbeit, daher kann Ich Euch kein Honorar anbieten. Die Leute, die mitmachen, würde ich dann aber schon auf ein Bierchen oder Ähnliches einladen
Zum Thema Equipment: Es ist keine High End Ausrüstung notwendig. Eine Kamera mit einem Standard Zoom und Blitz (oder ein lichtstarkes Objektiv) sollte passen. Wer im Besitz eines leichten Statives ist kann dieses auch mal mit einpacken. Freihandaufnahmen in etwas dunkleren Konferenzräumen sind nicht immer so einfach.
Wer also den SQLSaturday besuchen will und auch noch Spaß daran hat diesen in Fotos festzuhalten, der melde sich doch einfach kurz bei mir. Sei es hier als Kommentar oder per Email, Twitter, Xing, FB.
I had the chance to do a customer POC on a very early version of SQL Server 2012 Parallel Data Warehouse and like to share the new features for PDW as well as the impressive results we got. The good thing with this POC is, that we did the same POC also on the SQL Server 2008R2 version of PDW so we have a direct comparison on the results.
If you are not familiar with Parallel Data Warehouse I can suggest my further post list about PDW to get a step into it: http://dwjunkie.wordpress.com/pdw/
I will split this post into several topics that I will explain in more details in my next posts:
- What’s new in SQL Server 2012 PDW?
- Scope & Customer Expectations of the POC
- SQL Server 2008 R2 PDW vs. SQL Server 2012 PDW
- Performance Results and lessons learned (SSIS vs. TSQL, Querying, Cube Processing, Compression, Limitations, etc.)
Source: http://www.worldwithlove.org/blog/lets-rock-the-green-music-festival/
Während der Besprechung zu einer Projekterweiterung wurde unter anderem ein Problem besprochen, dass sehr häufig anzutreffen ist – Konkatenation eines SQL-String “am Client” und Versand und Ausführung am SQL Server, um die Daten zu ermitteln. Ich habe vorgeschlagen, die komplette Suchroutine in eine Stored Procedure auszulagern. Dieser Stored Procedure werden dann nur noch die Parameter übergeben und die Konkatenation findet dann in der Prozedur statt. Das komplette SQL-Statement wird dann innerhalb der Stored Procedure ausgeführt und die Daten an den Client zurück geliefert. Die Bedenken von SQL-Injection habe ich widerlegt, indem ich argumentiert habe, dass trotz Variabilität in der Parameterübergabe ausschließlich mit sp_executeSQL und expliziter Parameterübergabe gearbeitet wird. Die Herausforderung war nicht ganz einfach aber ich habe eine funktionierende Lösung entwickeln können, die mehrere Vorteile besitzt.
Problemstellung
Am Client wird mittels Konkatenation ein dynamischer SQL-String aufgebaut, der dann gegen den SQL Server ausgeführt wird. Neben der Gefahr von SQL Injection wurde unter anderem ausgeführt, dass Ausführungspläne nicht wiederverwendet werden können, wenn die SQL-Statements nicht parametrisiert gegen die Datenbank ausgeführt werden. Dadurch wird der Prozedurcache des SQL Servers nur unnötig belastet. Die nachfolgende Lösung basiert darauf, dass ein SQL-Statement nicht mehr auf Seiten des Clients “zusammengebaut” wird sondern in einer Prozedur hinterlegt ist. Die Variablen / Parameter für die Daten werden der Stored Procedure übergeben und in der Prozedur zu einem Querystring konkateniert. Anschließend wird dieser String mittels sp_executeSQL unter Ausnutzung der Übergabe von Parametern ausgeführt und die Daten an den Client zurück geliefert. Durch diese Technik werden gleich drei Probleme beseitigt:
- SQL Injection wird unterbunden, da nur noch die Werte selbst übertragen werden und keine SQL-Strings mehr vom Client ausgeführt werden müssen
- Abfragepläne können wiederverwendet werden, da die Abfrage parametrisiert wird und somit wiederverwendet werden kann
- Änderungen in der Ergebnismenge können schnell implementiert werden und die Clients benötigen keine Updates
Datenstruktur
Um die nachfolgende Technik zu demonstrieren, soll das nachfolgende Datenmodell mit ein paar Datensätzen dienen. Hierbei handelt es sich um eine Relation mit einem einfachen Aufbau.
CREATE TABLE dbo.tbl_exec_demo
(
id int NOT NULL IDENTITY (1, 1),
Company varchar(256) NOT NULL,
Street varchar(128) NOT NULL,
ZIP char(10) NOT NULL,
City varchar(128) NOT NULL,
FirstName varchar(64) NULL,
LastName varchar(64) NULL,
CostCenter char(10) NOT NULL,
Phone char(20) NULL,
Fax char(20) NULL,
Email varchar(256) NULL,
CONSTRAINT pk_tbl_exec_demo_Id PRIMARY KEY CLUSTERED (Id)
);
GO
Um die Daten mittels dynamischem SQL in Verbindung mit Parametern abzufragen, wird eine Prozedur verwendet, die für vier abzufragende Attribute in der Relation Parameter bereitstellt, in denen der zu suchende Wert gespeichert wird. Per Definition haben diese Variablen einen Standardwert von NULL. Die Parameter haben identische Datentypen wie die Attribute in der Relation selbst.
CREATE PROC dbo.proc_app_SearchDemo
@Company varchar(256) = NULL,
@Street varchar(128) = NULL,
@ZIP char(10) = NULL,
@City varchar(128) = NULL
AS
SET NOCOUNT ON
DECLARE @base_stmt nvarchar(1000) = N'SELECT * FROM dbo.tbl_exec_demo $(where)';
DECLARE @parms nvarchar(256) = N'@Company varchar(256), @Street varchar(128), @ZIP char(10), @City varchar(128)';
DECLARE @where_stmt nvarchar(1000) = N'';
-- Konkatenation der WHERE-Klausel
IF @Company IS NOT NULL AND LEN(@Company) != 0
IF CHARINDEX('%', @Company) != 0
SET @where_stmt = @where_stmt + 'Company LIKE @Company'
ELSE
SET @where_stmt = @where_stmt + 'Company = @Company'
IF @Street IS NOT NULL AND LEN(@Street) != 0
IF CHARINDEX('%', @Street) != 0
SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0
THEN ' AND '
ELSE ''
END + 'Street LIKE @Street'
ELSE
SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0
THEN ' AND '
ELSE ''
END + 'Street = @Street'
IF @ZIP IS NOT NULL AND LEN(@ZIP) != 0
IF CHARINDEX('%', @ZIP) != 0
SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0
THEN ' AND '
ELSE ''
END + 'ZIP LIKE @ZIP'
ELSE
SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0
THEN ' AND '
ELSE ''
END + 'ZIP = @ZIP'
IF @City IS NOT NULL AND LEN(@City) != 0
IF CHARINDEX('%', @City) != 0
SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0
THEN ' AND '
ELSE ''
END + 'City LIKE @City'
ELSE
SET @where_stmt = @where_stmt + CASE WHEN LEN(@where_stmt) != 0
THEN ' AND '
ELSE ''
END + 'City = @City'
-- Konkatenation von Basis-Statement und WHERE-Klausel
SET @base_stmt = REPLACE(@base_stmt, '$(where)', CASE WHEN LEN(@where_stmt) != 0 THEN 'WHERE ' ELSE '' END + @where_stmt);
-- Ausführung des Statements
EXEC sp_executeSQL @base_stmt, @parms, @Company = @Company, @Street = @Street, @ZIP = @ZIP, @City = @City;
SET NOCOUNT OFF
GO
Funktionsweise
Die Prozedur besteht im Wesentlichen aus drei Bereichen. Zunächst werden die “Konstanten” in der Prozedur definiert. Hierbei genießen die nachfolgenden Variablen besondere Aufmerksamkeit:
| Variable | Beschreibung |
| @base_stmt | Diese Variable beinhaltet den generellen Abfragestring, wie er später gegen die Datenbank ausgeführt wird. In dieser Variablen befindet sich ein Synonym $(Where), das zur Laufzeit durch den im zweiten Teil konkatenierten WHERE-Teil der Abfrage ersetzt wird. |
| @parms | Eine Liste von Variablen, die in @base_stmt verwendet werden. Weitere Details zur Verwendung von Variablen in sp_executeSQL finden sich in den Links am Ende dieses Artikels. |
Im zweiten Teil der Prozedur werden die übergebenen Variablen ausgewertet. Bei der Überprüfung wird nur dann ein Prädikat generiert, wenn auch tatsächlich ein Wert für diese Variable übergeben worden ist. Um die Prozedur etwas flexibler zu gestalten, kann der Anwender auch “” übergeben, dass wie ein NULL behandelt wird. Wenn einer Variablen ein Wert übergeben wurde, wird innerhalb der Routine überprüft, ob der Anwender ein Suchmuster (%) oder einen exakten Suchparameter übergeben hat. Abhängig davon wird die WHERE-Klausel entweder mit einem “=” oder einem “LIKE” definiert.
Der dritte Teil der Prozedur behandelt abschließend die Bereitstellung und Ausführung der Abfrage. Sind so alle Parameter ausgewertet worden, kann diese WHERE-Klausel in die Variable @base_stmt implementiert werden. Hierzu wird einfach das in @base_stmt enthaltene Synonym durch die generierte WHERE-Klausel ersetzt. Übergibt ein Anwender beispielsweise folgende Parameter:
@Company = “db%”
@ZIP = “64390”
wird der reine Abfragetext in der Variablen @base_stmt zu
SELECT * FROM dbo.tbl_exec_demo WHERE Company LIKE @Company AND ZIP = @ZIP;
Die Ausführung de Abfrage erfolgt abschließend mittels sp_executeSQL. Da nun parametrisierte Abfragen verwendet werden, können bestehende Ausführungspläne wiederverwendet werden.
Herzlichen Dank fürs Lesen!
Bei der Durchsicht von Datenbank-Wartungsaufträgen in einem SQL Server 2008 R2 ist aufgefallen, dass ein täglich auszuführender Job ausschließlich den REBUILD / REORG von Clustered Indexe vornimmt. Auf die Frage, warum nur die Clustered Indexe neu organisiert / neu aufgebaut werden, wurde erwidert, dass dieser Job von einem Dienstleister mit der folgenden Aussage implementiert wurde: “Es müssen nur die Clustered Indexe überprüft und gewartet werden. Wenn ein Clustered Index neu aufgebaut wird, werden alle anderen Indexe, die von diesem Index abhängig sind (Non Clustered Indexe) ebenfalls neu aufgebaut!”. Der nachfolgende Artikel beschäftigt sich mit dieser Aussage und zeigt die Abhängigkeit von Clustered Index und Non Clustered Index. Der Artikel belegt, warum die getroffene Aussage falsch ist.
Wird ein Non Clustered Index tatsächlich neu aufgebaut, wenn ein Clustered Index mit REBUILD oder REORG neu aufgebaut wird? Die Antwort ist kurz und knapp: Nein! Wenn ein Clustered Index neu aufgebaut wird, sind Non Clustered Indexe davon nicht berührt. Warum das so ist, soll der nachfolgende Beitrag erklären. Zunächst wird eine einfache Relation mit insgesamt vier Attributen erstellt. Diese Relation wird anschließend mit Testdaten gefüllt. Obwohl von der Verwendung einer GUID als Clustered Key abzuraten ist (siehe “Idealer Datentyp für Clustered Index – GUID vs. INT”), wird er zu Demonstrationszwecken im ersten Beispiel verwendet, um eine hohe Fragmentierung zu erreichen.
IF OBJECT_ID('dbo.tbl_Test', 'U') IS NOT NULL
DROP TABLE dbo.tbl_Test
GO
CREATE TABLE dbo.tbl_Test
(
Id uniqueidentifier NOT NULL DEFAULT (newid()),
Col1 char(20) NOT NULL,
Col2 char(20) NOT NULL,
col3 char(256) NOT NULL,
CONSTRAINT pk_tbl_Test PRIMARY KEY CLUSTERED (Id)
);
-- Erstellung eines unique non clustered Index und eines
-- non unique non clustered Index
CREATE UNIQUE INDEX ix_tbl_Test_col1_col2 ON dbo.tbl_Test (col1, col2);
CREATE INDEX ix_tbl_Test_col3 ON dbo.tbl_Test (col3);
GO
SET NOCOUNT ON
GO
-- 10.000 Datensätze eintragen
DECLARE @Counter int = 1
WHILE @Counter <= 10000
BEGIN
INSERT INTO dbo.tbl_Test (col1, col2, col3)
VALUES (
'Counter: ' + CAST(@Counter AS varchar(5)),
'RowNo: ' + CAST(@Counter AS varchar(5)),
'Just stupid stuff'
)
SET @Counter += 1
END
Nachdem die Daten in die Relation übertragen wurden, gilt das Augenmerk zunächst dem Status aller Indexe. Interessant ist – zunächst – die Fragmentierung und damit auch die Größe der Indexe.
SELECT i.name,
ps.Index_type_desc,
ps.Index_level,
ps.avg_fragmentation_in_percent,
ps.avg_fragment_size_in_pages,
ps.fragment_count,
ps.page_count,
ps.avg_page_space_used_in_percent,
ps.record_count
FROM sys.indexes i INNER JOIN sys.dm_db_Index_physical_stats(db_id(), object_id('dbo.tbl_Test'), DEFAULT, DEFAULT, 'DETAILED') ps
ON (
i.object_id = ps.object_id AND
i.Index_id = ps.Index_id
)
ORDER BY
ps.Index_id ASC,
ps.Index_level ASC;
Sehr deutlich ist zu erkennen, dass die Relation – wie zu erwarten – sehr stark fragmentiert ist. Bevor im nächsten Schritt der Index neu erstellt wird, wird die Struktur aller Indexe“unter der Motorhaube” zusätzlich unter die Lupe genommen. Der Clustered Index hat immer die Index_id 1. Die [Id] der beiden zusätzlich angelegten Indexe können über [sys].[indexes] abgefragt werden. Die Struktur selbst kann für jeden Index mit dem folgende T-SQL Befehl abgerufen werden:
SELECT Index_id,
page_type,
page_level,
page_type_desc,
allocation_unit_type,
allocated_page_page_id,
next_page_page_id,
previous_page_page_id
FROM sys.dm_db_database_page_allocations(db_id(), OBJECT_ID('dbo.tbl_Test', 'U'), 1, NULL, 'DETAILED')
ORDER BY
page_type DESC,
page_level DESC,
previous_page_page_id ASC,
allocated_page_page_id;
Hinweis: Die DMF sys.dm_db_database_page_allocations ist erst ab SQL Server 2012 vorhanden! Ich habe sie für das Beispiel verwendet, da es einfacher ist als mit DBCC IND. Der Workaround für DBCC IND von Kimberly Tripp ist hier beschrieben “Optimierung von Datenbankmodellen – Richtige Wahl von Datentypen und Indexen (2)”. Die nachfolgende Abbildung zeigt die Struktur der Pages bei Ausführung des Befehls für alle drei Indexe ([pk_tbl_Test], [ix_tbl_Test_col1_col2] und [ix_tbl_Test_col3])
Nun wird der Clustered Index neu erstellt
ALTER INDEX pk_tbl_test ON dbo.tbl_Test REBUILD;
Führt man die Abfrage erneut aus, zeigt die erneute Analyse der Struktur der Pages für alle 3 Indexe deutlich, dass ausschließlich der Clustered Index selbst neu erstellt wurde. Die Pagezuordnungen der beiden non clustered Indexes haben sich nicht verändert!
Die – berechtigte – Frage, die sich hier stellt; warum werden bei der Neuerstellung eines Clustered Index nicht auch die Non Clustered Indexe neu aufgebaut? Schließlich erhält ja der Clustered Index einen komplett neuen Datenbereich (Pages) zugewiesen. Die Antwort ist relativ einfach – in einem Non Clustered Index werden nicht Referenzen zu Pages gespeichert sondern ausschließlich der sogenannte “Row Locator” des Clustered Index. Diese “Beziehung” zueinander soll das nachfolgende Beispiel verdeutlichen.
In der obigen Abbildung – nach dem Neuaufbau – ist zu erkennen, dass der Index [ix_tbl_Test_col1_col2] unter anderem die Page 2320 belegt. Den Inhalt dieser Page kann man wie folgt “sichtbar” machen (Bitte darauf achten, dass der Name der Datenbank als auch die Page entsprechend auf Ihre Umgebung angepasst werden muss):
DBCC TRACEON (3604);
DBCC PAGE ('BJ_UR', 1, 2320, 3);
Der Aufbau eines non clustered Index folgt immer dem gleichen Muster. Die Indexattribute (in diesem Fall [col1] und [col2]) beinhalten die zu durchsuchenden Attribute. JEDER non clustered Index führt IMMER das Schlüsselattribut (Clustered Key) des clustered Index mit (in diesem Fall [Id]). Ist der Clustered Key nicht eindeutig, wird zusätzlich zum Clustered Key noch ein Uniquifier [int] für die Sicherstellung der Eindeutigkeit mit gespeichert. Diese Informationen werden als Row Locator bezeichnet. Da im obigen Beispiel der Clustered Index als UNIQUE Index definiert wurde, ist der Schlüssel selbst eindeutiges Kriterium.
Wichtig für das Verständnis der Fragmentierung ist, dass eben nicht der Verweis auf eine Page des Clustered Index gespeichert wird sondern das Schlüsselattribut des Clustered Index. Somit ist für SQL Server die Reorganisation eines Non Clustered Index nicht notwendig. Der Suchalgorithmus ist vom Neuaufbau vollkommen unabhängig. Als Beispiel soll die folgende Abfrage dienen:
SELECT * FROM dbo.tbl_Test WHERE Col1 = 'Counter: 149' AND Col2 = 'RowNo: 149';
Der Ausführungsplan für die Abfrage zeigt, dass der Index [ix_tbl_Test_col1_col2] verwendet wurde. Um jedoch Informationen zu [Col3] abrufen zu können, müssen Informationen aus dem Clustered Index selbst gelesen werden [KeyLookup]. Simpel ausgedrückt besagt der obige Ausführungsplan, dass alle Attribute aus dbo.tbl_Test ausgegeben werden sollen; dabei können die Attribute [Id], [col1] und [col2] direkt aus dem Non Clustered Index entnommen werden. Um Informationen zu [Col3] zu erhalten, muss im Clustered Index [pk_tbl_Test] gesucht werden. Hierzu wird das Schlüsselattribut [Id] als Alleinstellungsmerkmal im Clustered Index verwendet. Um die Werte aus dem Clustered Index zu lesen, muss wieder im Root-Level begonnen werden. Insgesamt benötigt die obige Abfrage im Beispiel 5 IO-Operationen. Betrachtet man die Abfrage noch einmal etwas genauer unter Berücksichtigung des Ausführungsplans, besteht die Abfrage strukturell aus zwei Abfragen!
SELECT Id, Col1, Col2 FROM dbo.tbl_Test WHERE Col1 = 'Counter: 149' AND Col2 = 'RowNo: 149';
SELECT Col3 FROM dbo.tbl_Test WHERE Id = 'C6292A43-82FA-4C03-B1DA-23C3C277A8DA';
Bei der Analyse dieser Abfrage teilen sich die 5 Lesevorgänge wie folgt auf:
tbl_Test-Tabelle. Scananzahl 0, logische Lesevorgänge 2, physische Lesevorgänge 0, ...
tbl_Test-Tabelle. Scananzahl 0, logische Lesevorgänge 3, physische Lesevorgänge 0, ...
Für die Ermittlung der Informationen zu [Id], [Col1] und [Col2] sind zwei Lesevorgänge erforderlich. Anschließend müssen für die fehlende Information zu [Col3] drei weitere Lesevorgänge im Clustered Index durchgeführt werden. Würde ein Non Clustered Index tatsächlich eine Referenz zu einer Page im Leaf-Level speichern, in der sich der Datensatz befindet, so wären keine drei Lesevorgänge notwendig sondern lediglich ein Zugriff auf die Page selbst.
Unter Berücksichtigung dieser Vorgehensweise erklärt sich von selbst, dass der Neuaufbau eines Clustered Index andere – non clustered Indexe – nicht betrifft.
Herzlichen Dank fürs Lesen!
Die SQL Server und .NET Entwickler Konferenz in Nürnberg ist vorüber. Ich selber durfte doch auch einen Vortrag über self-service-BI vorstellen und konnte ebenso viel auf dieser Konferenz über neue Themen wie bspw. Windows 8 App Entwicklung oder auch XEvents dazulernen. Nun stehen auch auf der Konferenz Seite von Karl Donaubauer die Vorträge als Downloads zur Verfügung:
Weiterhin gibt es auch einen schönen Blog von Christoph Jüngling der einige Vorträge sehr gut zusammenfasst und beschreibt: http://www.juengling-edv.de/tag/snek2/
Während ich den vorherigen Artikel “Clustered Index vs. Non Clustered Index” geschrieben habe, habe ich ein paar interessante Beobachtungen gemacht, die es wert sind, etwas genauer unter die Lupe genommen zu werden. Vielmals höre ich aus Bemerkungen in Vorträgen oder Unterhaltungen mit Kollegen, wie wenig Beachtung bei der Indexierung der Vergleich des I/O bei der Umsetzung differenzierter Indexstrategien findet. Wenn ich mit einem Auftrag betraut werde, eine Abfrage zu optimieren, führe ich zunächst die Abfrage im Original aus und lege die daraus resultierenden I/O-Werte und den Ausführungsplan als "Baseline” fest. Anschließend beginne ich mit der Optimierung. Der nachfolgende Artikel soll deutlich machen, dass nicht immer nur der Ausführungsplan im Mittelpunkt stehen sollte sondern – und gerade – das I/O der Gratmesser für eine optimierte Indexstrategie ist.
Ausgangssituation
Gegeben ist zunächst ein HEAP ohne weitere Indexe mit der folgenden Struktur:
CREATE TABLE dbo.tbl_Members_Date
(
SId int NOT NULL,
FirstName char(80) NOT NULL,
LastName char(80) NOT NULL,
MemberSince date NOT NULL
)
Diese Tabelle hat insgesamt 40.417 Datensätze mit einer festen Größe von 167 Bytes / Datensatz. Anschließend wurde die folgende Abfrage mit unterschiedlichen Indexstrategien auf diese Relation ausgeführt:
SELECT YEAR(MemberSince) AS MemberYear,
COUNT_BIG(SId) AS Members
FROM dbo.tbl_Members_Date
GROUP BY
YEAR(MemberSince)
ORDER BY
COUNT_BIG(SId) DESC
Ausführung der Abfrage als HEAP
Die Relation besitzt aktuell noch keinen Index (HEAP) und die physikalische Gegebenheit der Relation (kann mittels sys.dm_db_index_physical_stats ermittelt werden) sieht wie folgt aus:
Die obige Abfrage ergab den folgenden Ausführungsplan und I/O:
Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0, ...
tbl_Members_Date-Tabelle. Scananzahl 1, logische Lesevorgänge 883, ...
Wie aus dem Ausführungsplan deutlich erkennbar ist, handelt es sich um einen “Table Scan”. Betrachtet man die vorherige Auswertung der physikalischen Struktur, kann man deutlich erkennen, dass jede Datenseite des Heaps gelesen werden musste (versteht sich von selbst – es gibt ja auch keine Einschränkungen).
Ausführung der Abfrage mit einem CLUSTERED INDEX auf [MemberSince]
Im nächsten Versuch wird ein Clustered Index erstellt, dessen Key das Attribut [MemberSince] ist. Anschließend wird die Abfrage erneut ausgeführt.
CREATE CLUSTERED INDEX cix_tbl_MemberSince_MemberSince ON dbo.tbl_Members_Date (MemberSince);
Das Ergebnis ist – zunächst – verblüffend. Statt besser zu werden, generiert diese Abfrage ein höheres IO!
Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0, ...
tbl_Members_Date-Tabelle. Scananzahl 1, logische Lesevorgänge 903, ...
Es mussten 20 Pages mehr gelesen werden, um die gleiche Auswertung zu erhalten. Der Grund für dieses seltsame Verhalten ist in den unterschiedlichen Strukturen von HEAPS und CLUSTERED INDEXES zu finden. Ein HEAP besitzt eine sehr flache Struktur – nämlich einen Root-Level und anschließend unmittelbar die Leaf-Levels für die Speicherung der Daten. Ein Clustered Index hingegen hat eine deutlich komplexere Struktur, da bei zunehmendem Datenvolumen die Clustered Keys in B-Trees gespeichert werden. Die nachfolgende Abbildung zeigt den Unterschied sehr deutlich.
Hat der Heap – wie oben abgebildet – nur eine Ebene (Leaf-Level) muss der Clustered Index bereits 6 Seiten mehr für die Root-Page und eine Ebene (B-Tree) bereit halten. Die eigentlichen Daten wiederum werden erst im Level 0 (Leaf-Level) gespeichert. Hierbei werden 896 Seiten für diese Speicherung benötigt. Insgesamt sind es also 896 + 5 + 1 = 902 (+1 Page IAM) Seiten. Warum sind im Leaf-Level mehr Pages vorhanden als bei einem HEAP? Es werden doch identische Daten gespeichert!Die Erklärung hierfür ist relativ schnell gefunden, wenn man sich die Datenstrukturen der Leaf-Level einmal etwas genauer anschaut. Die nachfolgenden Abbildungen zeigen eine Page aus dem Leaf-Level eines HEAP und des CLUSTERED INDEX.
Data Page in HEAP
Data Page in CLUSTERED INDEX
Vollkommen transparent fügt SQL Server JEDEM Index, der nicht UNIQUE ist ein internes Attribut [UNIQUIFIER] hinzu, um einen Datensatz eindeutig zu kennzeichnen. Dieser [UNIQUIFIER] hat eine unangenehme Eigenschaft… – er benötigt Platz. Grundsätzlich wird der UNIQUIFIER immer dann mit einem Wert besetzt, wenn das Schlüsselattribut eines Index redundant ist. In einem HEAP werden solche [UNIQUIFIER] nicht verwendet!
(Danke an Andreas Wolter, der mich auf diesen Fehler hingewiesen hat).
Bei dem anschließend gesetzten Clustered Index auf das Attribut [MemberSince] zeigt die obige Data Page bereits einen 17. ten redundanten Eintrag. Der UNIQUIFIER beginnt intern immer bei 0. hierbei sei ein wichtiger Hinweis darauf gestattet, dass ein UNIQUIFIER mit dem Wert 0 eine physikalische Länge von 0 hat – es wird kein INT für diesen Wert gespeichert. Hingegen ist jedoch zu erkennen, dass der UNIQUIFIER immer 4 Bytes belegt, wenn der Wert größer 0 ist. Das erklärt den unterschiedlichen Platzbedarf des CLUSTERED INDEX im Verhältnis zum HEAP.
Ausführung der Abfrage mit einem dedizierten Index auf das Attribut [SId]
Der dritte Test verwendet eine Abfragestrategie, bei der ein Clustered Index auf das Attribut [SId] angewendet wird. Da dieses Attribut eindeutig ist, wird ein UNIQUE Index verwendet. Des Weiteren wird ein Non Clustered Index auf das Attribut [MemberSince] gelegt. Durch diese Strategie wird für den Clustered Index die geforderte Eindeutigkeit gewährleistet, die die Speicherung eines UNIQUIFIERS vermieden. Da jedoch MemberSince für die Abfrage benötigt wird, wird anschließend ein zweiter Non Clustered Index für dieses Attribut verwendet. Weitere Attribute müssen weder diesem Index als Schlüsselattribut hinzugefügt werden noch muss die SId (wird ja ebenfalls im der Abfrage benötigt) separat hinzugefügt werden, da ja der Clustered Key immer in jedem Non Clustered Index enthalten sein muss.
Nachdem die bestehenden Indexe vollständig gelöscht worden sind, können die zwei nachfolgenden Index angelegt werden:
CREATE UNIQUE CLUSTERED INDEX cix_tbl_MemberSince_SId ON dbo.tbl_Members_Date (SId);
CREATE INDEX ix_tbl_MemberSince_MemberSince ON dbo.tbl_Members_Date (MemberSince);
Ein Blick auf die Indexstatistiken zeigt, dass der Clustered Index nicht wesentlich weniger Volumen besitzt als der zuvor auf dem Attribut [MemberSince] verwendete Clustered Index.
Der Unterschied kommt zu Stande, da – wie bereits oben erwähnt – für die Datensätze kein UNIQUEIFIER mehr mit gespeichert werden muss. Ca. 10 Seiten für die obige Struktur mag erahnen, welches Datenvolumen eingespart werden kann, wenn es um wirklich große Datenmengen geht. Keine Behauptung ohne Beweise – ein Blick auf eine Datenseite zeigt den Unterschied deutlich. Man kann auf der Abbildung deutlich erkennen, dass ein UNIQUIFIER auf der Datenseite fehlt; er wird ja auch nicht mehr benötigt, da per Definition für den Clustered Index definiert wurde, dass er eindeutig ist.
Interessant ist aber ein Blick auf den zweiten – für die Abfrage entscheidenden – Index, der ausschließlich das Attribut [MemberSince] abdeckt. Dieser Index ist – im Verhältnis zu den zuvor untersuchten Indexen – sehr klein.
Wow – dieser Index verwendet insgesamt nur 66 Pages. Das ist dem Umstand geschuldet, dass das Attribut des Index (MemberSince) lediglich 3 Bytes belegt. Hinzurechnen muss man noch den Clustered Key (4 Bytes) + Overhead für die Speicherung der Daten (Slot Array, …). Dadurch, dass nun ca. 620 Informationen ([MemberSince], [SId]) auf eine Datenseite passen, können die 40.000 Datensätze bequem auf 65 Indexseiten gespeichert werden. Und genau das macht sich extrem bemerkbar in der Abfrage.
Das I/O ist erwartungsgemäß gemäß der Größe des Index recht klein:
Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0, …
tbl_Members_Date-Tabelle. Scananzahl 1, logische Lesevorgänge 67, …
Insgesamt konnte so durch geschickte Indexierung die Performance um über 90% gesteigert werden!
Verwendung einer INDEXED View
Die ganze oben aufgeführten Indexvarianten sind ein sehr guter Ansatz, das I/O signifikant zu verbessern. Die bester aller Varianten – unter der Voraussetzung der obigen Anforderungen – ist jedoch ungeschlagen die Verwendung einer INDEXED View. Mehr zu den Anforderungen an eine Indexed View finden Sie im Artikel “Verwendung von “Indexed views” zur Verbesserung der Performance”. Von daher hier nur kurz die Erstellung, Indexstruktur und Auswertung…
CREATE VIEW dbo.view_Member_Statistics
WITH SCHEMABINDING
AS
SELECT YEAR(MemberSince) AS MemberYear,
COUNT_BIG(*) AS Members
FROM dbo.tbl_Members_Date
GROUP BY
YEAR(MemberSince)
GO
CREATE UNIQUE CLUSTERED INDEX cix_view_Member_Statistics ON dbo.view_Member_Statistics (MemberYear);
Die Indexstatistiken sind beeindruckend.
Entsprechend gestaltet sich dann auch Ausführungsplan und I/O-Messung für die Ausführung der View:
view_Member_Statistics-Tabelle. Scananzahl 1, logische Lesevorgänge 2, …
Beeindruckend! Von ursprünglich 903 –> 883 –> 67 –> 2 I/O’s. Man kann sich vorstellen, wie glücklich ein Kunden sein wird, wenn er solche Zahlen präsentiert bekommt.
Fazit
Indexoptimierung muss immer von mehreren Seiten betrachtet werden. Es reicht nicht aus, den Ausführungsplan im Blick zu haben und zu versuchen, z. B. einen Index Scan durch einen Index Seek zu ersetzen oder einen Nested Loop durch einen Hash Join, … Indexoptimierung muss meines Erachtens zunächst das I/O im Auge behalten. Erst, wenn die Schwachstellen beim I/O eliminiert sind, sollte man sich um den Ausführungsplan kümmern. Diese Strategie ist meines Erachtens sogar elementar, da bei Reduktion des I/O Änderungen in der Ausführung einer Abfrage nicht auszuschließen sind.
Herzlichen Dank fürs Lesen
Auch für den SQL Server 2008 R2 sind zwei neue Updates erschienen. Für den SQL Server 2008 R2 mit SP1 ist das kumulative Update 12 erschienen und für den SQL Server 2008 R2 mit SP2 das kumulative Update 06. Diese können unter folgenden Links angefordert werden.
Kumulatives Update 12 für SQL Server 2008 R2 SP1
Kumulatives Update 06 für SQL Server 2008 R2 SP2
Für den SQL Server 2012 RTM, also ohne Servicepack, ist ein neues Update erschienen. Es handelt sich um das kumulative Update 07. Es kann unter folgendem Link angefordert werden:
SQL Server 2012 RTM CU07
Diese Woche werden Oliver und ich einen Workshop zum Thema Data Curation: Effective ways to optimize your data with MDS & DQS halten. Der Workshop findet am 18.04.2013 in der Schweiz statt.
Data Quality Services (DQS) and a new version of Master Data Services (MDS) were introduced with SQL Server 2012. It’s an out of the box product for the Data Steward and the Information professional and can help to solve most of the data quality and master data issues in the enterprise.
This workshop gives you a complete overview on these new technologies in SQL Server based on a real world closed loop scenario. In the scenario we will show how SSIS, DQS, MDS and other SQL Technologies work together, you will learn concepts like DQS Domains, Rules and how to use Remote Data Services with Windows Azure marketplace. You learn how to use MDS Models, Entities and Hierarchies etc.
You will see MDS Staging concepts, SharePoint and Excel Integration and you learn how everything is connected in a closed loop. During the sessions you will see best practices, performance tips and tricks and maybe also stuff you would not do at home. At the end you have a full picture of DQS and MDS and an understanding where you can use it and how to optimize it.
Weitere Details zum Workshops sind auf der Konferenzseite zu finden.
Oliver Engels Talks about Communities, CRM, Data Quality and Database Days
Da der hypothetische Index (Hypothetical Index) vielen SQL Server Administratoren und Entwicklern bisher bewusst in freier Wildbahn noch nicht begegnet ist, habe ich mich mit der Kamera auf die Lauer gelegt und ihn tatsächlich auf Video festhalten können.
Viel Spaß beim Betrachten dieser interessanten und äußerst nützlichen Spezies!
Heute habe ich mit einem sehr geschätzten Freund und Kollegen (Bernd Jungbluth) eine interessante Diskussion im Rahmen meines Vortrags zu Indexstrategien auf der SNEK II in Nürnberg geführt. Die Aufgaben-/Fragestellung war recht simpel. Es ging darum, ob ein Clustered Index auf einem Datumsattribut performanter sei als ein Clustered Index auf einem INT-Attribut und einem zusätzlichen Index auf dem besagten Datumsattribut. Allgemeine Nachteile eines Clustered Index auf einem Datumsattribut (Fragmentierung / Größe) sollen als Pro / Contra Argumente hier nicht beleuchtet werden.
Um einen direkten Vergleich zwischen den beiden Varianten durchführen zu können, wurden zwei Relationen mit identischer Struktur aber unterschiedlichen Indexstrategien erstellt. Die erste Relation besitzt einen Clustered Key auf dem INT-Datentypen und einen zusätzlichen nonclustered Index auf dem Attribut mit dem Datentypen [date].
CREATE TABLE dbo.tbl_Members_SId
(
SId int NOT NULL,
FirstName char(80) NOT NULL,
LastName char(80) NOT NULL,
MemberSince date NOT NULL,
CONSTRAINT pk_tbl_Members_SId_SId PRIMARY KEY CLUSTERED (SId)
)
GO
CREATE INDEX ix_tbl_Members_SId_MemberSince ON dbo.tbl_Members_SId (MemberSince) INCLUDE (FirstName, LastName);
GO
Um einen “fairen” Vergleich zu haben, ist zu beachten, dass der nonclustered Index auf dem Datumsattribut alle weiteren Attribute mittels INCLUDE ebenfalls auf den Indexseiten speichert. Die zweite Relation besitzt einen Clustered Index, der das Datumsattribut als Clustered Key besitzt. Da der Clustered Index immer die Relation selbst ist, wird kein zusätzlicher Index für diese Relation benötigt.
CREATE TABLE dbo.tbl_Members_Date
(
SId int NOT NULL,
FirstName char(80) NOT NULL,
LastName char(80) NOT NULL,
MemberSince date NOT NULL,
CONSTRAINT pk_tbl_Members_Date_MemberSince PRIMARY KEY NONCLUSTERED (SId)
)
GO
CREATE CLUSTERED INDEX ix_tbl_Members_Date_MemberSince ON dbo.tbl_Members_Date (MemberSince)
Beide Relationen haben insgesamt ~41.000 Datensätze. In beiden Relationen befinden sich 1:1 identische Datensätze!
Der Unterschied im Datenvolumen für die Indexe erklärt sich dadurch, dass in der Relation [dbo].[tbl_Members_SId] ein zusätzlicher Index auf dem Datumsattribut implementiert wurde, der alle anderen Attribute auf den Indexpages speichern muss. Auf Basis der obigen Datenstruktur wurden nun Tests durchgeführt, die über verschiedene Datumsbereiche in beiden Relationen getestet wurden. Bevor jedoch das Ergebnis näher beleuchtet wird, müssen noch ein paar wichtige Rahmenbedingungen (Indexanalysen) im Vorfeld bekannt gegeben werden, die die nachfolgenden Ergebnisse erklären können.
Für die Tests werden aus den Datensätzen zwei Jahre betrachtet; es sind Daten aus dem Jahre 2000 und dem Jahr 2012. Um die Anzahl der Datensätze zu ermitteln wurde über eine der beiden Relationen (Beide beinhalten die exakt gleichen Daten!) folgende Abfrage ausgeführt:
SELECT YEAR(MemberSince) AS Jahr,
COUNT (1) AS Mitglieder
FROM dbo.tbl_Members_Date
WHERE YEAR(MemberSince) IN (2000, 2012)
GROUP BY
YEAR(MemberSince)
ORDER BY
YEAR(MemberSince) DESC
Weiterhin wurden vor den Tests auch die für die Abfragen relevanten Indexe genauer unter die Lupe genommen. Bei den zu untersuchenden Indexen handelt es sich für die Relation [dbo].[tbl_Members_Date] um den clustered Index [ix_tbl_Members_Date_MemberSince] und für die Relation [dbo].[tbl_Members_SId] um den non clustered Index [ix_tbl_Members_SId_MemberSince].
SELECT OBJECT_NAME(st.object_id),
i.name,
index_type_desc,
index_level,
page_count,
record_count
FROM sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tbl_Members_Date', 'U'), 1, DEFAULT, 'DETAILED') st
ON (
i.object_id = st.object_id AND
i.index_id = st.index_id
)
SELECT OBJECT_NAME(st.object_id),
i.name,
index_type_desc,
index_level,
page_count,
record_count
FROM sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tbl_Members_SId', 'U'), 2, DEFAULT, 'DETAILED') st
ON (
i.object_id = st.object_id AND
i.index_id = st.index_id
)
Die Auswertung der Indexe zeigt – wie bereits weiter oben die Abbildung der Indexvolumina vermutet – eine fast identische Aufteilung der Daten. Die Anzahl der Pages im Leaf-Level des Clustered Index ist nahezu identisch mit der Anzahl der Pages im Index-Level des non clustered Index der Relation [dbo].[tbl_Menbers_SId]. Dieser Wert war zu erwarten, da ja die Informationen [FirstName] und [LastName] in den non clustered Index mit aufgenommen wurden. Sie sind zwar kein Bestandteil der Indexattribute selbst, wurden aber mittels INCLUDE in die Indexseiten mit aufgenommen, um ein identisches Verhalten zu simulieren, wie es der clustered Index macht.
Es wurden verschiedene Basisabfragen mit jeweils unterschiedlichen Zeiträumen (siehe oben) durchgeführt und das Ergebnis war nicht überraschend – KEINE Strategie war in einer der Abfragevarianten der Sieger. Die identischen Abfragen mit beiden Relationen gingen jeweils 1:1 (sowohl im direkten Vergleich des Ausführungsplans als auch im IO) aus. Stellvertretend für beide Datumseingrenzungen sollen hier die Ergebnisse der Abfragen für das Jahr 2012 gezeigt werden.
SELECT *
FROM dbo.tbl_Members_Date
WHERE MemberSince >= '20120101' AND
MemberSince < '20130101'
SELECT *
FROM dbo.tbl_Members_SId
WHERE MemberSince >= '20120101' AND
MemberSince < '20130101'
SELECT *
FROM dbo.tbl_Members_Date
WHERE MemberSince >= '20120101' AND
MemberSince < '20130101'
ORDER BY
LastName,
FirstName;
SELECT *
FROM dbo.tbl_Members_SId
WHERE MemberSince >= '20120101' AND
MemberSince < '20130101'
ORDER BY
LastName,
FirstName;
Der direkte Vergleich im Rahmen des IO und der Ausführungsstrategie geht unentschieden aus. Dies sollte in der Konsequenz bedeuten, dass beide Konzepte gleich gut sind? Leider nein – es gibt einen eklatanten Nachteil der Variante, in der das Datum den Clustered Index bildet. Was unterscheidet den Clustered Index auf das Datumsattribut in [dbo].[tbl_Members_Date] vom Clustered Index der Relation [dbo].[tbl_Members_SId] – ER IST NICHT UNIQUE!
Genau diese Tatsache gereicht dem clustered Index – marginal – zum Nachteil. Da er nicht eindeutig ist, muss SQL Server für jeden Clustered Key eine “interne” Eindeutigkeit schaffen. Dies geschieht über den sogenannten [UNIQUIFIER]. Die nachfolgenden Abbildungen zeigen eine Page aus dem Leaf-Level des Clustered Index auf das Datumsattribut (kein Unique Key) und auf das Attribut [SId] der zweiten Relation:
Die erste Abbildung zeigt eine Page aus dem Leaf-Level des Clustered Indexes der Relation [dbo].[tbl_Members_Date]. Da das Datum keine Eindeutigkeit besitzt, muss SQL Server für jeden Datensatz eine Eindeutigkeit künstlich erzeugen. Hierzu wird im Index für jeden Eintrag ein [UNIQUIFIER] mitgeführt. Für die obige Abbildung bedeutet dies, das der Eintrag im Slot 0 bereits der 28. Eintrag (Der UNIQUIFIER beginnt bei 0) ist; der nächste Eintrag für den 01.01.2000 ist dann der 29. Eintrag, usw..
Für den Clustered Index der Relation [dbo].[tbl_Members_SId] gilt das nicht. Hier ist der Clustered Key ein eindeutiger Wert und somit muss SQL Server selbst nicht mehr für die Eindeutigkeit sorgen. Insgesamt ist somit die Verwaltung und der Speicherverbrauch des Indexes besser als für einen non unique clustered index.
Fazit
Ich wurde immer wieder gefragt, welche Strategie ich für einen Clustered Index verfolge. Meine Standardantwort darauf war immer: “It depends”. Das obige Beispiel zeigt sehr anschaulich, dass es keinen abfragerelevanten Vorteil gibt, wenn man die Wahl zwischen zwei Lösungen hat. Jedoch gilt für mich bei der Wahl des Clustered Index immer die Bestimmung der Relation selbst. Nehmen wir das obige Beispiel als Grundlage, dann gilt die Überlegung zunächst der Fragen:
- Wie häufig kommt es vor, dass Datumsangaben erfasst werden, die ZWISCHEN bestehenden Datumsangaben in der Relation liegen
- Welche Kardinalität (Selektivität) besitzen die Datumswerte
Je niedriger die Kardinalität eines Clustered Keys um so höher der “Verwaltungsaufwand” des SQL Server - Ist es wichtig, dass Daten schnell und fortlaufend eingetragen werden “müssen”
- Hat die betroffene Relation noch weitere Indexe
Jeder non clustered Index muss IMMER den Clustered Key (Referenz zur Datenseite) in den Indexpages mitführen
Die obigen Fragen beeinflussen meine Entscheidung nach der richtigen Wahl signifikant. Für das obige Beispiel würde ich immer die Wahl des Clustered Keys auf dem Attribut [SId] befürworten. Die Vorteile liegen eindeutig auf dieser Entscheidung:
- Ein Clustered Key mit einer geringen Datengröße und einer i. d. R. fortlaufenden Nummer (IDENTITY)
- Eindeutigkeit des Clustered Keys (Sehr hohe Kardinalität)
- Keine Speicherung von Clustered Key UND Uniquifier in den weiteren non clustered indexes und damit deutlich weniger IO bei der Verwendung
Es kann keine pauschale Antwort nach der Frage des richtigen Clustered Index geben – sie ist immer von vielen Faktoren abhängig, die nicht nur und ausschließlich von den Daten selbst gesteuert sind.
Vielen Dank für’s Lesen.
Das Timing ist perfekt, in wenigen Minuten fährt mein Zug in Frankfurt HBF ein.
Die SNEK II hat mir persönlich sehr gut gefallen – konnte ich doch endlich über “mein” Lieblingsthema INDEXING referieren…
Vielleicht bis zum nächsten Jahr auf der SNEK III, auf der ein “Deep Dive “ fortgesetzt wird.
Mal kurz für zwischen durch auf der Suche nach weiteren Datenquellen für PowerPivot.:
Innerhalb von PowerPivot findet sich kein Konnektor für Sharepoint Listen. Verfügt man aber über solche Listen, wäre es doch gut, wenn ich diese auch gleich in mein PowerPivot Datenmodell mit einbinden könnte. Sharepoint Listen lassen sich per Atom Data Feeds ansprechen und so einbinden. Folgende Artikel bei Microsoft beschreiben das Vorgehen im Detail:
Alle weiteren verüfgbaren Datenquellen für PowerPivot sind unter folgender Webseite aufgelistet: http://technet.microsoft.com/en-us/library/gg399082.aspx
Letzte Woche war ja die Microsoft Synopsis 2013 in Darmstadt.
Ist ein sehr gelungenes Event gewesen!
Ein Gespräch am Stand der deutschen SQL Server Community (PASS Deutschland e.V.) blieb mir besonders in Erinnerung. Dort hatte sich um die Popcorn Maschine ein kleines Grüppchen der SQL Server Szene gebildet. Im Gespräch ging es um “unbekanntere” SQL Server Features. Man kann bei einem so mächtigen Produkt wie dem SQL Server ja auch wirklich nicht mehr alles kennen. Ich warf eines meiner Lieblingsfeatures aus dieser Kategorie in den Ring: Den hypothetischen Index !
Nein, das ist kein brandneues Feature im SQL Server 20-something…
So gut wie alle Anwesenden hatten noch nie davon gehört/gelesen. Aufgrund des Namens wurde spekuliert und schnell geschlossen, dass es sich um einen virtuellen Index, also quasi nur um die Hypothese eines möglichen Index handeln müsste, der genutzt werden könnte, um zu prüfen, ob eine angedachte Optimierung eines Abfrageplans tatsächlich sinnvoll ist. Korrekt!
Was soll das alles? Klassische Optimierung ist doch häufig Trial & Error.
Bei der Optimierung eines Abfrageplans geht es ja meist um die Nutzung eines oder mehrerer Indizes, um beim Abarbeiten möglichst wenige 8 KB Seiten lesen zu müssen. Siehe dazu auch diesen Blogpost.
Es wird der gesamte Workload oder auch mal eine einzelne Query analysiert. Oft wird dann auf Basis des Abfrageplans eine Hypothese entwickelt mit welchen neuen Indizes die Query optimiert werden könnte. Das ist oft eine Wissenschaft für sich. Nur wie kann geprüft werden, ob der Query Optimizer auch wirklich den neuen Index nutzt? Der Optimizer ist ja recht wählerisch, was den Einsatz von Indizes angeht. Nur wie wählt der Optimizer aus dem Angebot an Indizes aus?
Hier kommt bei den meisten Entwicklern Trial & Error ins Spiel. Der Index wird einfach erstellt und es wird dann getestet. Ganz einfach, oder? Nun ja, das funktioniert bei überschaubaren Datenbanken durchaus problemlos. Dummerweise habe ich häufig mit recht großen Tabellen zu tun bei denen selbst die Erstellung eines Test Index mehr als eine Stunde dauern kann. Da will jeder neue Index gut überlegt sein und ist eigentlich etwas für ein explizites Wartungsfenster. Hier kommen wir mit Trial & Error nicht weiter. Ach ja, warum kann man nicht einfach mit einer Entwicklungsumgebung mit nur wenigen Datensätzen arbeiten? Das klappt leider nicht!
Hier kommen wir zu einer meiner Lieblingssätze für Optimierungscoachings: Was ist das Wichtigste am Index?
Die Statistiken ! Hinter jedem Index steht mindestens eine Statistik über die Inhalte also Daten der Indexstruktur. Statistiken führen leider oft ein Dasein im Schatten des Index. Immer wieder werden sie vernachlässigt und meist auch komplett missverstanden. Dabei ist die Erstellung und Pflege von Statistiken ein wesentlicher Teil jeder Performanceoptimierung, denn aufgrund der Statistiken wählt der Optimizer einen Index aus oder eben nicht.
Für eine Überprüfung benötigen wir also die Datenbestände aus dem Produktivsystem. Testdaten können das Ergebnis einer Performanceoptimierung komplett verfälschen. Das führt bei vielen Entwicklern zwar meist zu einer Sinnkrise, wenn sie davon erfahren, dennoch ist dem einfach so!
Wie kommen wir nun aus der Zwickmühle? Doch jedes mal ein halbes TB an Daten indizieren, um dann evtl. den Index gleich wieder zu verwerfen?
Hier kommt endlich der hypothetische Index ins Spiel. Er ist nur dafür da, um eine Hypothese zu überprüfen. Genaugenommen ist er gar nicht physisch vorhanden. Moment, wir brauchen aber den produktiven Datenbestand! Und genau hier kommt das Geniale. Beim hypothetischen Index werden NUR die Statistiken erzeugt. Das geht relativ flott und nimmt nur wenig Platz ein. Dann wird ein geschätzter Abfrageplan vom Queryoptimizer erstellt und man hat sofort Feedback, ob der geplante Index korrekt genutzt werden würde. Super, oder?
Gerade im Enterprise Data Warehouse Umfeld spart man damit wirklich sehr viel Zeit!
Nur wie muss man sich das jetzt in der Praxis vorstellen?
Eines noch vorweg: Es handelt sich hierbei um ein nicht dokumentiertes Feature! Also gibt es keinen Support und keine Garantie, dass das Feature in neuen Versionen oder nach einem Service Pack noch funktioniert.
Und wofür ist das Feature dann da? Für den Data Tuning Advisor (kurz DTA) ! Der nutzt das. Macht ja auch Sinn, oder?
Nun aber Butter bei die Fische. Wie geht das mit dem hypothetischen Index nun in der Praxis?
Als Beispiel nehmen wir mal wieder AdventureWorks2012 und meinen Liebling die FactInternetSales. Hier von eine leicht erweiterte Varianten um ein paar Zeilen mehr. Wie komme ich dazu? Siehe hier! Diese fis hat hier “nur” 12.079.600 Zeilen. Muss reichen für den Moment. ;-)
Dazu nehmen wir diese Query:
SELECT p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear,
AVG(fis.Freight) as 'AVG Freight',
sum(fis.SalesAmount) as 'SUM Salesamount'
FROM demo..FactInternetSales as fis
inner join AdventureWorksDW2012..DimProduct as p on
fis.ProductKey = p.ProductKey
inner join AdventureWorksDW2012..DimDate as d on
fis.OrderDateKey = d.DateKey
group by
p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear
order by p.EnglishProductName,
d.WeekNumberOfYear,
d.CalendarYear;
Für unsere Zwecke sollte sie reichen. Wie Ihr seht, nutze ich die FactInternetSales aus meiner Demo Datenbank und den Rest aus der original Adv2012.
Entscheidend wird der Teil des Queryplans sein, welcher die Faktentabelle lesen wird. Schauen wir uns den Plan einmal an.
Da werden also mal eben 357 MB an Daten gelesen. Okay, noch nicht die Welt, dennoch wollen wir genau das mittels Index optimieren.
Erst mal tun wie so als wüssten wir nix vom hypothetischen Index. Wir legen also nach kurzer Analyse einfach einen neuen Index an. Und zwar diesen hier:
CREATE NONCLUSTERED INDEX [hypIndex] ON [dbo].[FactInternetSales]
( [OrderDateKey] ASC )
INCLUDE ( [ProductKey], [SalesAmount], [Freight] )
Das Anlegen hat auf meinem Testsystem (virtuelle Maschine und so…) mal eben ganze 2-3 Minuten gedauert. Geht ja gar nicht…
Wie ist das Ergebnis?
Okay, wie erwartet nur noch wenige KB.
Optimiert ist! Mit Trial & Error…
Kommen wir zum zweiten Teil. Nun das Ganze mit einem hypothetischen Index. Also den jetzigen löschen… und? Ja, wie legt man nun so einen Index an?
Wie gesagt, wir brauchen ja NUR die Statistiken. Daher ist die magische Erweiterung auch: “WITH STATISTICS_ONLY = –1”. Nicht wundern, dass das SSMS ihn nicht erkennt. Ist ja auch undokumentiert.
Dennoch, oh Wunder, SQL Server hat es gefressen.
Und es hat gefühlt nur 1 Sekunde gedauert. Genau hier ist ja unsere Zeitersparnis!
Nur ein Blick ins SSMS lässt uns zweifeln, ob das überhaupt geklappt hat.
Unser Index ist gar nicht da!!! Was soll das denn nun?
Kurzer Blick in die Statistiken zeigt dennoch, dass da was passiert sein muss:
Schauen wir uns das Ganze mal mit einem Catalogview an:
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('[dbo].[FactInternetSales]')
Hier taucht unser Index auf. Super. Und wenn man ein wenig zur Seite scrollt, dann findet man eine Spalte “is_hypothetical”. Ehrlich, die war immer schon da! Versprochen! :-)
SELECT is_hypothetical,*
FROM sys.indexes
WHERE object_id = OBJECT_ID('[dbo].[FactInternetSales]')
Hier kurz nach vorne geholt:
Unheimlich, oder? Und es geht noch weiter. Der Queryoptimizer nutzt den Index nämlich noch nicht.
Wie bringen wir ihn nun dazu? Dazu gibt es den nächsten undokumentierten Befehl: DBCC AUTOPILOT().
DBCC AUTOPILOT(0, 9, 261575970, 3)
GO
Was sind denn das für Parameter?
9 ist die Datenbank ID. Die gibt es u.a. so: SELECT DB_ID() AS DBID.
261575970 ist die Objekt ID. Siehe einfach oben.
3 ist die Index ID. Siehe nochmals oben. :-)
Und nun? Es wird noch besser…
SET AUTOPILOT ON
GO
Wenn wir nun den geschätzten Queryplan abfragen, bekommen wir:
Also der Queryoptimizer schlägt den hypIndex vor, obwohl dieser gar nicht existiert. Wir sehen auch, dass die Schätzung zwar sehr genau ist, aber er hat sich um ein paar KB vertan. Ich finde das ist okay, oder?
Und was passiert im AUTOPILOT Modus, wenn wir die Query einfach laufen lassen?
Fast nix anderes, denn der SQL Server führt nun keine Queries mehr aus in dieser Session. Wir bekommen nur noch geschätzte Pläne zurück.
Mit SET AUTOPILOT OFF schaltet Ihr den SQL Server wieder zurück. Und löschen geht wie gewohnt mit:
DROP INDEX hypindex ON dbo.FactInternetSales;
Natürlich ist das immer noch Trial & Error. Nur wesentlich optimierter.
So, damit wären wir auch schon am Ende dieses Posts. Hmm, irgendwie länger als geplant geworden, dafür mit Demo Teil. :-)
Ich wünsche Euch viel Spaß beim experimentieren mit Euren hypothetischen Indizes!
Der zweite Konferenz Tag der PASS Business Analytics startet mit einem der Top Speaker
Weltweit: Steven Levitt http://en.wikipedia.org/wiki/Steven_Levitt
Stevens Keynote benötigte keine PowerPoint der Mann macht seine Session als Free Speach.
Er beginnt davon zu sprechen, dass er keine Ahnung von Mathematik hat und das “a good
economist” auch keine Ahnung davon haben muss.
Nachfolgend einige der wesentlichen Aussagen, die amüsant in seine Story gepackt wurden:
“sometimes clarity only comes from having the courage to ask the right question”
“many of the greets data discoveries are found in the data you collected but
didn’t find essential”
“if you have the choice between walking drunk and driving drunk it is far safer
to drive according to the data”
“common sense has never been the calling card of sociologist”
“Most people in business have not the time to think”
“The biggest incentive you can do in your firm is to care about what you’re doing”
“There is nothing more important than doing your homework”
“A Chicago drug gang has the same hierarchy the McDonalds”
Ein interessantes Beispiel aus Levittes Analysen:
Diese Keynote hat mir wieder gezeigt wie wichtig gutes Storytelling ist. Das Publikum
war gespannt, es wurde aufgelockert durch witzige Anekdoten, es hat sich gelohnt früh
aufzustehen.
Am zweiten Konferenztag hatte ich endlich mal Zeit Sessions zu besuchen dabei haben mir
die Session von Chris Webb zum Thema ODATA und unsere Ruben und Paco von SolidQ Spain
mit ihrem Vortrag zu Social Text, Sentiment und Tone Analysis. Ich hoffe die beiden
kommen zum SQL Saturday nach Bonn, wer sich für Sentiment Analyse interessiert, der sollte
sollte sich diesen Vortrag unbedingt anschauen.
Die Konferenz nähert sich dem Ende, Tillmann und ich haben viel positives Feedback zu
unserer Session über Advanced Visualization mit SSRS gehabt, scheint wir haben den Nerv
der Reporting-Gemeinde getroffen und Rolf Hicherts Konzepte & Methoden sind auch in den
USA von Interesse.
Zum Abschluss noch ein Gedanke, ob diese Konferenz empfehlenswert ist und Sinn macht?
Ich denke schon. Das Thema Datenanalyse wird uns in den nächsten Jahren immer tiefer,
schneller und umfangreicher beschäftigen. Schon heute haben wir nicht genug Menschen,
welche in diesem Bereich arbeiten und ausgebildet sind. Eine Business Analytics Konferenz
kann hier weiter helfen, das Thema zu fokussieren und Inhalte sowie Techniken zu liefern.
Wir werden sehen, es bleibt: spannend!
Dieser erstmalig von Microsoft ausgelobte Preis wurde am ersten Konferenztag der Synopsis 2013 an die Theobald Software verliehen.
Full story »
Chicago, 11. April 2013, 7.00 Frühstück, kein Problem für den Jetlag geplagten Deutschen,
8.00 Keynote der ersten PASS Analytics Conference. Let’s go:
PASS hat sein Conference Program mit dem Summit, der SQL Rally, den SQL Saturdays um ein weiteres Format ergaenzt: Der Business Analytics Conference. Warum? BA = Business Analytics wird in den Zeiten, wo Social Media und Big Data in aller Munde ist immer wichtiger. Hier verbindet PASS neue Community Gruppen: Data Analysts, Data Stewards, Data Architects, Data Scientists und die IT Pros. Wer kann das besser als die SQL Server Community, denn SQL Server in Verbindung mit SharePoint und Office ist die Basis des Microsoft BI Stacks. Das scheint Sinn zu machen: Über 1000 registrierte Teilnehmer kommen hier in Chicago zusammen. Laut PASS HQ sind über 60% neue Teilnehmer, welche bis dato noch nicht auf den vielen Community Angeboten waren.
Die Keynote wird eröffnet durch den PASS Presidenten Bill Graziano, gefolgt vom Sponsor DELL. Wirklich richtig interessant wurde es als Kamal Hathi und Amir Netz für Microsoft die Bühne betraten. Kamal startet mit einer DataExplorer Suche in Wikipedia. Ein wesentlicher Punkt den ich von der Keynote mitnehme ist: Simplicity! In Zeiten wo mit BigData die Komplexität steigt, sollten sich die Softwarehersteller mehr um dieses Keyword kümmern. Wie kann ich Sachverhalte am schnellsten und einfachsten analysieren? Das beinhaltet die Datenbereitstellung sowie die Datenpräsentation.
Am Beispiel von American Idol zeigen Amir und Kamal die Power von Data Explorer und PowerView. In der Präsentation wird eine Slide mit 3 Buchstaben gezeigt: FUN. Spass, Amir erklärt ohne das Menschen Spass an der Analyse und an der Nutzung haben, werden BI Tools nicht erfolgreich sein.
Im zweiten Teil von Amir und Kamals Keynote wird das neue Excel Add In GeoFlow vorgestellt. GeoFlow ist ein geographisches Daten Visualisierungs Add In welches BING als Kartenmaterial nutzt. Eine BING Maps Integration gibt es bereits schon, das Neue an GeoFlow ist die Interaktivität. Man kann sogenannte Scenes erstellen, dies sind animierte Kamerafahrten, welches die Dynamik der Daten in Zeit und Raum darstellen kann.
Amir Netz stellt diese Funktionalität in einer genialen “Hans-Rosling-Like” Demonstration vor. Für mich war es eine der besten PASS Keynotes, denn mit Dynamik und Witz wurden elementare Strategien von Microsoft für BI sichtbar: Einfach zu benutzende BI Tools die komplexe und große Datenmengen beherrschen können und dabei noch Spass machen Sachverhalte zu Analysieren.
Wie Amir sagte: “wir sind in BI gerade da, wo der Diaprojektor für Präsentationen vor Jahren war” Interessante Zeiten… interessante Konferenz.
Microsoft wird den Extended Support für XP am 8. April 2014 einstellen.
Full story »
In meinem Artikel "Umzug von Windows Azure HDInsight ins Management Portal" hatte ich bereits berichtet, dass für die Nutzung der neuen HDInsight Preview ein Windows Azure Storage Konto in der East US Region benötigt wird. Wie ich allerdings weitere Speicherkonten im Windows Azure HDInsight Dienst nutzen kann, möchte ich heute vorstellen…
So richtig glücklich war ich der neuen Voraussetzung eines Blob Speichers in der East US Region zu Anfang nicht.
Zum Einen bin ich mir noch nicht sicher, ob der Windows Azure Blob Storage genau so schnelle Analysen zulässt, wie es das Hadoop Distributed File System (HDFS) bislang tat.
Zum Anderen liegen meine Daten derzeit noch in den europäischen Microsoft Rechenzentren, und ich möchte diese nur Stück für Stück in die USA transferieren, damit die monatlichen Transferkosten nicht in die Höhe schnellen.
Die guten Neuigkeiten sind aber, dass sich weitere Speicherkonten in einem Windows Azure HDInisght Cluster hinzufügen lassen.
Hinzufügen eines Azure Storage Account
Nachdem man ein Windows Azure HDInsight Cluster erstellt hat, muss man sich zuerst mit dem Head-Node via Remote Desktop Connection verbinden:
Anschließend editiert man die Konfigurationsdatei core-site.xml im Verzeichnis C:\apps\dist\hadoop-1.1.0-SNAPSHOT\conf\
Dort sucht man nach dem Eintrag mit dem Namen fs.azure.account.key.[...].blob.core.windows.net und dupliziert diesen.
Im Duplikat muss dann nur noch der Name und Zugriffsschlüssel des zweiten Speicherkontos angepasst werden:
<property>
<name>fs.azure.account.key.[Account Name].blob.core.windows.net</name>
<value>[Account Key]</value>
</property>
Zugriff auf das zweite Storage Account
Mit der Pfadangabe "asv://[Blob Storage Container]@[Account Name].blob.core.windows.net/" kann auf die Daten des zweiten Speicherkontos zugreifen werden.
Dieses kann beispielsweise bei MapReduce-Jobs oder auch in der interaktiven JavaScript Konsole verwendet werden:
 |
Weitere Informationen:
|
Für das kürzlich veröffentlichte CU3 für SQL Server 2012 mit ServicePack 1 gibt es bereits ein Hotfix, da es Anwendern nicht mehr möglich war SSIS-Projekte oder Wartungspläne zu erstellen oder zu öffnen.
Full story »
Nachdem wir im letzten Jahr unseren ersten SQLSaturday in München mit großem Erfolg organisiert haben, werden wir am 13.07.2013 unter dem Namen SQLSaturday #230 – Rheinland die nächste kostenlose Community Konferenz organisieren.
Mit der Hochschule Bonn-Rhein-Sieg konnten wir einen fantastischen Raumsponsor gewinnen. In den neuen Räumlichkeiten haben wir die Möglichkeit in insgesamt 5 parallelen Tracks voraussichtlich 25 Sessions von nationalen und internationalen Speakern präsentieren zu können.
Seit dem 25.03.2013 ist der Event auf der offiziellen SQLSaturday Seite verfügbar und der Call for Speakers eröffnet; bereits heute haben wir schon 30 Session Einreichungen.
Bei 5 parallelen Tracks wollen wir in diesem Jahr etwas neues probieren, und versuchen in einem Track Vergleiche anderer Produkte und/oder OpenSource Projeke mit Microsoft SQL Server zu platzieren. Über zahlreiche Einreichungen zu entsprechenden Themen würden wir uns freuen.
In den nächsten Wochen werde ich hier regelmäßig updates zum SQLSaturday #230 – Rheinland veröffentlichen.
Die Frankfurter Datenbanktage sind vorbei. Ein Tag Workshop gefolgt von zwei Tagen Konferenz mit parallelen Vortragstracks zu verschiedenen Datenbank-Systemen sind zu Ende. Viele interessante Kontakte sind geknüpft und jeder Teilnehmer hatte auch die Möglichkeit, mal über den Tellerrand zu schauen wie dieses und jenes Problem bei anderen Datenbank-Anbietern gelöst wird. Ich selbst war mit zwei Vorträgen vertreten, deren Präsentationen hier heruntergeladen werden können:
Weitere Infos sowie die Slides der anderen Vorträge sind auf der offiziellen Veranstaltungswebsite zu finden: http://www.frankfurter-datenbanktage.de
Im nächsten Jahr finden die Frankfurter Datenbanktage vom 26.-28. März statt.
Eine viel zu wenig bekannte Möglichkeit für PASS-Mitglieder kostengünstig Schulungen zu erhalten, sind die PASS Essentials, die ich heute einmal kurz vorstellen möchte.
Full story »
Im April treffen wir uns bei Startplatz, einem Inkubator und Treffpunkt für Startups in Köln. Bei Startplatz findet am 04. Mai 2013 auch der Windows 8 Hackaton in zusammenarbeit mit dotnet cologen statt.
Wir bitten wie immer um eine vorherige Anmeldung, aber auch kurzfristig entschlossene Teilnehmer sind natürlich herzlich willkommen.
Weitere Details findet ihr auf der Seite unserer Regionalgruppe auf der Seite des PASS Deutschland e.V.
Performance Optimierung in SSIS
Torsten wird die Unterschiede zwischen synchronen und asynchronen Tasks aufzeigen und auch auf blocking, semi-blocking und non-blocking Tasks eingehen und erläutern wie sich die unterschiedlichen Task-Arten auf die Performance auswirken. Außerdem wird er erklären, was ein Execution-Tree ist und wie dieser die Data-Pipeline beeinflusst. Des Weiteren geht es um DataFlow-Optimierungen und darum Performance-Counter richtig zu lesen und zu interpretieren.
Torsten Strauss ist als Reporting & Process Manager bei BP Europe SE beschäftigt ist. Seit etwa 7 Jahren beschäftigt sich Torsten mit dem Thema Business Intelligence (MCTS Business Intelligence Development and Maintenance und MCTS SQL Server Development). Dabei liegt sein Schwerpunkt auf den Applikationen MS SQL Server und SAP Business Objects. Leidenschaftlich trainiert er die Kampfkunst Wing Tsun und stemmt ein paar Gewichte.
STARTPLATZ
Im Mediapark 5
50670 Köln
Nach dem großen Erfolg des ersten SQL Saturday im Herbst, findet nun im Sommer der nächste in Deutschland statt: SQL Saturday #230.
Full story »
Ich habe in dieser Woche einen Fall zu untersuchen gehabt, der ein System für mehrere Stunden (teilweise sogar Tage) komplett lahm gelegt hat. Die Analyse hat gezeigt, dass (unter anderem) regelmäßige Index- und Statistikaktualisierungen durchgeführt wurden. Was ich dann herausgefunden habe, mag man kaum glauben. In diesem konkreten Beispiel wurden wirklich ALLE Fehler gemacht, die man in Verbindung mit sys.dm_db_index_physical_stats und dessen Anwendungsspektrum überhaupt machen kann.
Ausgangsstellung der Untersuchung
Bei dem zu untersuchenden System handelte es sich um eine ca. 1.500 GB große Datenbank, in der sich mehrere hundert (~650) Relationen befanden. Insgesamt gab es – inklusive Clustered Index – ca. 1.200 Indexe in der Datenbank. Die größten Relationen hatten ein Volumen von ca. 70 GB / Clustered Index. Ca. die Hälfte aller Relationen hatte KEINE Daten.
Mittels SQL Agent Job wurden Aufträge in eine “Jobrelation” geschrieben, die vielfältige Aufgaben bereitstellten. Unter anderem wurde für JEDE Relation ein Auftrag eingestellt, der eine Prozedur [dbo].[proc_app_Reindex] aufruft, dessen Übergabeparameter der Name der zu prüfenden Relation ist. Der Inhalt der Prozedur sah (stark vereinfacht) wie folgt aus:
ALTER PROC dbo.proc_app_ReIndex
@TableName sysname
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Table_Id int
SET @Table_Id = OBJECT_ID(@TableName);
SELECT 'ALTER INDEX ' + r.name + ' ON ' + @TableName +
CASE WHEN r.avg_fragmentation_in_percent < 30.0 AND r.index_id != 1
THEN ' REORGANIZE'
ELSE ' REBUILD'
END
FROM (
SELECT i.object_id,
i.name,
i.index_id,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (db_id(), 0, DEFAULT, DEFAULT, 'DETAILED') AS ps INNER JOIN sys.indexes i
ON (
ps.Object_id = i.Object_id AND
ps.Index_id = i.Index_id
)
WHERE ps.avg_fragmentation_in_percent > 10.0
) AS r
WHERE r.object_id = @Table_Id
-- Cursordurchlauf mit sp_executeSQL
SET NOCOUNT OFF
END
Analyse der Prozedur
Die Prozedur hatte einige eklatante Schwächen. Insbesondere ist die Routine für die Ermittlung der richtigen Strategie der Reorganisation / Neuaufbau eines Index mehr als mangelhaft. Grundsätzlich wird gemäß der obigen Abfrage für den Clustered Index IMMER ein Neuaufbau durchgeführt. Eklatant ist auch die Tatsache, dass pauschal ohne Bewertung von Anzahl der Pages / Datensätze eine solche Strategie gefahren wird. Dies soll aber nicht das Thema dieses Artikels sein.
Ein weiteres – nicht zu unterschätzendes – Problem ist, dass ein Index auch häufiger pro Durchlauf neu strukturiert werden kann. Es wird zwar geprüft, dass nur Ebenen des Index ausgegeben werden, die eine Fragmentierung von mehr als 10% aufweisen aber dabei wurde nicht berücksichtigt, dass bei einem Clustered Index eine hohe Fragmentierung in den B-Tree als auch in den Leaf Levels vorkommen kann. In diesem Fall wird der Index zwei mal in der Ergebnismenge vorhanden sein und somit auch zwei Mal neu aufgebaut.
Neben diesen Schwachstellen war jedoch mein Fokus zuerst auf die Verwendung von sys.dm_db_index_physical_stats gelenkt worden. Hier wurden von den Entwicklern gleich zwei Fehler gemacht, die darauf schließen ließen, dass sie sich über die Anwendung dieser FUNKTION keine Gedanken gemacht haben.
Filterung von Ergebnismengen
Bei dem DMO sys.dm_db_index_physical_stats handelt es sich nicht um eine View/Abfrage sondern um eine Funktion. Dies wird bereits dadurch erkennbar, dass Argumente als Kriterium übergeben werden können. Genau dies haben aber die Entwickler sehr vernachlässigt. Folgende Argumente können der Funktion übergeben werden:
| Parameter | Beschreibung |
| database_id | Eindeutige Id der Datenbank für die eine Statistik der Indexe ausgegeben werden soll. |
| object_id | Eindeutige Id des Objekts (Relation/View), dessen Indexe analysiert werden sollen |
| Index_Id | Eindeutige Id des Indexes, der analysiert werden soll |
| Partition_Id | Bei partitionierten Objekten die Id der Partition, auf dem sich der Index befindet |
| Mode | Name des Modus der Analyse. Hierzu weiter unten deutlich mehr Details |
Wie aus der obigen Abfrage hervor geht, wird eine Filterung aller Indexe einer Relation außerhalb der Funktion durchgeführt, Die Variable @Table_Id kommt explizit in einer WHERE-Klausel zum Tragen. Das bedeutet jedoch für die Funktion, dass zunächst eine Analyse über ALLE Objekte durchgeführt wird. Anschließend erst wird die Teilmenge herausgefiltert, die für den weiteren Vorgang von Relevanz ist. Dieses Verfahren – insbesondere bei dem oben genannten Datenvolumen – erzeugt aber ein ERHEBLICHES IO, das so hätte nicht sein müssen.
Das nachfolgende Beispiel soll den Unterschied demonstrieren. In der obigen Abbildung ist erkennbar, dass eine Relation dbo.tblStammClasses insgesamt 105 MB im Clustered Index belegt. Für die nachfolgenden zwei Abfragen – die beide das gleiche Ergebnis haben – wird der IO gemessen:
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tblStammClasses', 'U'), DEFAULT, DEFAULT, 'DETAILED')
GO SELECT * FROM sys.dm_db_index_physical_stats(db_id(), 0, DEFAULT, DEFAULT, 'DETAILED')
WHERE object_id = OBJECT_ID('dbo.tblStammClasses', 'U');
GO
Das Ergebnis (selbst bei der “kleinen” Relation) zeigt deutliche Unterschiede…
Die erste Abfrage verwendet das Selektionskriterium IN der Funktion während die zweite (originale) Abfrage die Filterung außerhalb der Funktion durchführt. Insgesamt ergibt sich ein um das 5-fache reduziertes IO-Verhalten bei Verwendung des Filterkriteriums IN der Funktion. Dieses Verhalten ist logisch – wird doch IN der Funktion bereits nur die Relation berücksichtigt, dessen object_Id als Parameter übergeben wurde. In der Originalversion muss zunächst einen Analyse über ALLE Indexe aller Objekte durchgeführt werden. Anschließend wird das gewünschte Objekt aus der Ergebnismenge herausgefiltert. Dadurch wird natürlich ein deutlich höheres IO generiert. Multipliziert man dieses Verhalten mit der Gesamtanzahl aller Relationen (in diesem konkreten Fall 650 Relationen), kann man sich ausmalen, wie lang der Prozess PRO Relation dauerte. Kommt dann noch dazu, dass einige Indexes von großen Relationen sogar zwei / dreimal neu erstellt wurden, durfte man sich nicht wundern, dass der Prozess sich über mehr als 18 Stunden hinzog und so das ganze System mehrmals pro Tag lahm legte.
Modus der Analyse
Ein weiterer Schwachpunkt war die Art und Weise der Analyse der Indexe. Das DMO stellt für die Analyse von Indexen drei unterschiedliche Verfahren bereit. Diese Verfahren werden durch den Parameter [Modus] entsprechend für die DMF aktiviert.
| Modus | Beschreibung |
| LIMITED | ermittelt die logische Fragmentierung des LEAF-Levels und die Anzahl der Pages
Dieser Modus wird als Standard verwendet, wenn nicht explizit ein anderer Modus angegeben wird. |
| SAMPLED | Ermittlung der logischen Fragmentierung wie LIMITED
ist die Anzahl der Seiten im Leaf-Level < 10.000, werden ALLE anderen Indexseiten in allen Indexebenen untersucht. Ist die Anzahl der Seiten im Leaf-Level >= 10.000 wird nur jede 1.000 Seite untersucht. |
| DETAILED | Ermittlung der logischen Fragmentierung wie LIMITED
Alle zusätzlichen Informationen (Pages / Records / …) werden ermittelt, indem ALLE anderen Indexseiten in allen Indexebenen untersucht werden. |
Auch hierzu wurden Tests durchgeführt, um den IO für die einzelnen Stati zu überprüfen.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), 0, DEFAULT, DEFAULT, 'DETAILED')
WHERE object_id = OBJECT_ID('dbo.tblStammClasses', 'U');
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), 0, DEFAULT, DEFAULT, 'LIMITED')
WHERE object_id = OBJECT_ID('dbo.tblStammClasses', 'U');
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), 0, DEFAULT, DEFAULT, 'SAMPLED')
WHERE object_id = OBJECT_ID('dbo.tblStammClasses', 'U');
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tblStammClasses', 'U'), DEFAULT, DEFAULT, 'DETAILED')
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tblStammClasses', 'U'), DEFAULT, DEFAULT, 'LIMITED')
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tblStammClasses', 'U'), DEFAULT, DEFAULT, 'SAMPLED')
Das Ergebnis dieser Analyse spricht für sich und zeigt einen erheblichen Unterschied in den einzelnen Modi.
Ganz klarer Gewinner in diesem Verfahren ist die Abfrage mit Modus [LIMITED], gefolgt von [SAMPLED und Schlusslicht ist – wie erwartet – der Modus [DETAILED]. Einen weiteren – auch für die obige Ausführung wesentlichen – Vorteil hat die Verwendung des Modus [LIMITED]; die Ergebnismenge wird reduziert auf den Leaf-Level. Die nachfolgende Abbildung zeigt das Ergebnis der DMF bei Ausführung mit den Modi [DETAILED], [LIMITED], [SAMPLED]
Das Ergebnis erklärt sich in Bezug auf die Arbeitsweise der DMF von allein. Während im Modus [DETAILED] tatsächlich ALLE Datenseiten analysiert werden, betrifft das bei den beiden anderen Modi ausschließlich die LEAF-Level. Bezogen auf den Inhalt der Prozedur würde das bedeuten, dass im Moduls [DETAILED] der Clustered Index ZWEI mal neu erstellt wird (LEAF und B-TREE), da beide eine Fragmentierung von > 10% haben.
Wir haben die Prozedur nur geringfügig geändert (größere Änderungen konnten wir nicht machen, da erst der Vendor eine Freigabe erteilen muss). Das Ergebnis “unserer” Anpassungen sieht wie folgt aus:
SELECT 'ALTER INDEX ' + r.name + ' ON ' + @TableName +
CASE WHEN r.avg_fragmentation_in_percent < 30.0 AND r.index_id != 1
THEN ' REORGANIZE'
ELSE ' REBUILD'
END
FROM (
SELECT i.object_id,
i.name,
i.index_id,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (db_id(), @Table_Id, DEFAULT, DEFAULT, 'LIMITED') AS ps INNER JOIN sys.indexes i
ON (
ps.Object_id = i.Object_id AND
ps.Index_id = i.Index_id
)
WHERE ps.avg_fragmentation_in_percent > 10.0
) AS r
- Die Abfrage nach dem zu untersuchenden Objekt wurde IN die DMF verlagert, indem die Variable @Table_Id als Parameter für die DMF übergeben wird
- Die Auswertung erfolgt nicht mehr vollständig sondern nur auf Ebene des LEAF-Levels, der ja den Datenbestand des Index repräsentiert
technisches Fazit
Insgesamt wurde das komplette System durch einen ineffektive Indexoptimierungs-Strategie vollständig lahm gelegt. Durch das Verlagern der Filterung in die DMF konnte das IO – gesehen auf 650 Relationen – bei jedem Durchlauf um mehrere GB reduziert werden, da eine Abfrage aller Relationen für jeden Durchlauf unterbunden werden konnte. Weiterhin wurde als Modus für die DMF [LIMITED] gewählt, da es vollkommen ausreichend ist, nur den LEAF-Level des Index auf Fragmentierung zu untersuchen. Sofern es tatsächlich intensiverer Analysen bedarf, sollten solche Aktionen eher manuell als automatisch gemacht werden.
Von ursprünglich 18 – 22 Stunden Ausführungszeit wurde der gesamte Prozess auf 22 Minuten reduziert; jedoch versteht sich von selbst, dass diese Zeit selbst nur relativ ist, da eine mögliche Neuorganisation von Indexen im konkreten Fall nicht durchgeführt wurde.
persönliches Fazit
Warum diesmal ein persönliches Fazit, mag sich der Eine oder Andere fragen. Nun, ich bin seit über 20 Jahren im Projektgeschäft und speziell im Bereich SQL Server unterwegs. Seit den letzten 6 Jahren fast ausschließlich für globale Konzerne, die entweder eine eigene IT-Abteilung oder aber die IT-Abteilung komplett ausgelagert haben. Das gleiche gilt auch für die eingesetzten Systeme. Sie werden entweder von Vendoren eingekauft oder aber von Entwicklungsteams offshore entwickelt. In diesem Fall war es eine “Out of the Box” Lösung.
Spricht man mit einem Techniker des Vendors und fragt, warum der Prozess so lange dauert und was man dagegen tun könnte, erhält man als Antwort, dass es sicherlich ein IO-Problem / Storage / Netzwerk / sonstige Hardware ist. Man möge doch die Datenbank auf einem dedizierten Server mit … bla bla bla bla… Mir ist bei diesen Aussagen schlicht und einfach die Kinnlade heruntergefallen. Wie kann man einem Kunden so einen Unsinn erzählen? Da sprach der Blinde von der Farbe.
Sehr häufig kommt es vor, dass ich hinzu gezogen werde, wenn “das Kind in den Brunnen gefallen ist”. Das hängt damit zusammen, dass meine Aufgaben primär im Engineering und im 3rd-Level Support liegen. In diesem speziellen Fall war die Produktionsumgebung für mehrere Stunden nicht nutzbar, weil der Prozess das System stellenweise komplett lahm gelegt hat – der Grund war ein schlechtes Design UND vollkommene Unkenntnis über Verfahren, die angewendet wurden.
Es steht mir nicht zu, zu bewerten, ob ein Entwickler schlecht oder gut ist – aber es war in diesem Fall so , dass eine Lösung implementiert worden ist, die so eventuell aus dem Internet mittels COPY und PASTE in eine “Prozedur” kopiert worden ist, ohne Gedanken über die Auswirkungen gemacht zu haben. Hier fehlte jegliche Sachkenntnis. Sei es der Unterschied von REBUILD und REORG oder die Bedingungen im CASE-Konstrukt und last but not least die Kenntnis über eingesetzte Werkzeuge (DMO).
Es wäre wünschenswert, wenn sich jemand, der sich Entwickler nennt, im Vorfeld mit der Technologie auseinander setzt und nicht nur oberflächlich Themen aus dem Handbuch überfliegt und/oder aus dem Internet kopiert. Der Leidtragende ist der Kunde, der für dieses – in der Regel dann auch noch sehr teuren – mangelhafte Produkt zahlen muss. Das schadet nicht nur dem Kunden sondern stellt auch die Fähigkeiten des Vendors in Frage.
Herzlichen Dank für’s Lesen
Letztes Wochenende war ich zusammen mit Oliver (Twitter: @oengels) und Kostja auf dem SQLSaturday #188 in Lissabon. Oliver und ich haben dort unsere Session Data Qualit Rulez!, die wir auch schon auf dem SQLSaturday in München gehalten haben, präsentiert. Zusätzlich wurden wir auch noch kurzfristig gebeten mit Closed Loop in Enterprise Information Management eine zweite Session zu übernehmen. Die Folien beider Sessions stehen auf der Webseite des SQLSaturday zur Verfügung.
Der SQLSaturday wurde in der Microsoft Niederlassung in Lissabon durchgeführt. In 5 parallelen Tracks konnten über 200 Teilnehmer mehr als 35 Sessions besuchen. Neben vielen lokalen Sprechern haben die Organisatoren auch ein tolles Aufgebot an internationalen Sprecher nach Lissabon holen können.
Um nicht in einen Sprecher in einer Auflistung versehentlich zu vergessen, verweise ich einfach auf die vollständige Agenda (http://sqlsaturday.com/188/schedule.aspx).
Von meiner Seite aus noch einmal ein ganz großes Lob an die Organisatoren. Nicht nur dass der Event für die Teilnehmer super organisiert war, das Organisations-Team hat sich auch das ganze Wochenende um uns Speaker hervorragend gekümmert. Danke! Ihr habt ein echt toles Event auf die Beine gestellt.
Für den SQL Server 2008 ist ebenfalls ein neues Update erschienen. Dies ist für den SQL Server 2008 mit SP3 gedacht und kann unter folgenden Link gedownloadet werden.
Kumulatives Update 10 für SQL Server 2008 SP3
Für den SQL Server 2012 mit Servicepack 1 ist ein neues Update erschienen. Es handelt sich um das kumulative Update 03. Es kann unter folgendem Link angefordert werden:
SQL Server 2012 SP1 CU03
Gestern war es endlich soweit:
Die Windows Azure HDInsight Preview wurde ins Windows Azure Management Portal integriert.
Somit können die Apache Hadoop Dienste jetzt direkt über das Portal erstellt und konfiguriert werden.
Durch die Integration der Windows Azure HDInsight Preview, lassen sich mit wenigen Mausklicks im Handumdrehen ein Hadoop-Cluster erstellen.
Somit kann im Bedarfsfall schnell ein passendes Hadoop-Cluster erstellt, die Daten analysiert und das Cluster dann wieder herunterfahren werden. So ergeben sich Kosteneinsparungen, die nur in einer Cloudumgebung möglich sind.
Dank der Integration mit der Microsoft-Datenplattform können die in Hadoop befindlichen Daten mit PowerPivot, Power View und anderen BI-Tools von analysiert werden.
Anmelden an der Preview
Über das Menü im Management Portal steht ein neuer Menüpunkt für die HDInsight Preview zur Verfügung:
Auch wer sich bereits an der vorherigen Hadoop-On-Azure Preview angemeldet hatte, muss dies erneut tun.
Dazu kann der Link im Menü oder der Preview Features Bereich in der Kontoverwaltung genutzt werden:
Hadoop-Cluster erstellen
Sobald man für die Preview die Bestätigungsemail erhalten hat, kann auch schon losgelegt werden.
Ein Windows Azure Storage Account erstellen
Zu Beginn benötigt mein ein Storage Account in der Region East US:
Wozu dieses benötigt man dieses Storage Account?
Normalerweise wird bei Hadoop das eigene verteilte Dateisystem HDFS (Hadoop Distributed File System) eingesetzt, um die Daten auf allen Serverknoten zur Verfügung zu stellen.
Microsoft geht mit Windows Azure HDInsight allerdings einen eigenen Weg.
Bereits in der Hadoop-On-Azure Preview konnte man auf den Windows Azure Blob Storage zugreifen.
Dazu musste man im Dashboard die Kachel "Manage Cluster" auswählen ...
... und im Menüpunkt "Set up ASV" die Storage Account Zugangsdaten hinterlegen.
Somit konnte man über den asv:// Prefix auf den Windows Azure Blob Storage zugreifen, um beispielsweise die dort hinterlegten Daten für einen MapReduce-Job zu verwenden.
Die Abkürzung ASV steht für Azure Storage Vault
In der neuen Preview ist die Verwendung eines Storage Accounts die Voraussetzung für die Nutzung von Azure HDInsight.
Der große Vorteil dieser Architektur ist erneut die Kostenersparnis:
Da die Daten vom eigentlichen Hadoop-Cluster getrennt sind, wird das Cluster nur für die Analyse benötigt.
Nach der Analyse kann dieses wieder freigegeben werden, um so Kosten für Rechenzeit zu sparen.
HDInsight Servers anlegen
Über New -> Data Services -> HDInsight -> Quick Create kann anschließend ein neues Hadoop-Cluster erstellt werden:
Alternativ kann über New -> Data Services -> HDInsight -> Custom Create der Wizard gestartet werden. Hierbei können detailliertere Angaben für Installation vorgenommen werden, wie beispielsweise eine genauere Angabe der Data Nodes ...
... oder der Benutzername des Administratorkontos.
Zu guter Letzt kann noch ein Speicherkonto und - wenn bereits vorhanden - ein Blob Storage Container ausgewählt werden.
Nachdem die Bereitstellung des Dienstes abgeschlossen ist, kann das HDInsight Dashboard über das Management Portal erreicht werden:
|
Weitere Informationen:
|
Auf die nachfolgende Problematik wurde ich auf Grund eines Threads in den Microsoft Foren zu SQL Server aufmerksam. In dem Thread ging es darum, ob es sinnvoller ist, einen Surrogatschlüssel als “clustered primary key” zu verwenden oder besser einen – aus mehreren Attributen bestehenden – Naturalschlüssel. Die Vor- und Nachteile der verschiedenen Ausprägungen sollen im nachfolgenden Artikel etwas genauer betrachtet werden.
Was ist ein Primärschlüssel?
Untter einem Primärschlüssel versteht man eine Einschränkung einer Relation, die zum Erzwingen von Datenintegritäten in den Relationen von SQL Server verwendet werden kann. Eine Relation verfügt normalerweise über ein Attribut oder eine Kombination aus Attributen, die Werte enthalten, die jede Zeile in der Relation eindeutig identifizieren. Dieses Attribut oder die Kombination aus Attributen wird als Primärschlüssel (PK, Primary Key) der Relation bezeichnet und erzwingt die Entitätsintegrität der Relation.
Für einen Primärschlüssel gelten die folgenden Einschränkungen:
- Eine Relation kann nur eine PRIMARY KEY-Einschränkung enthalten.
- Ein Primärschlüssel darf 16 Attribute und eine Schlüssellänge von 900 Bytes nicht überschreiten.
- Wenn CLUSTERED oder NONCLUSTERED für eine PRIMARY KEY-Einschränkung nicht angegeben ist, wird CLUSTERED verwendet, sofern keine gruppierten Indizes für die Relation vorliegen.
- Alle Attribute, für die eine PRIMARY KEY-Einschränkung definiert wurde, müssen ungleich NULL definiert sein. Falls keine NULL-Zulässigkeit angegeben ist, wird für alle Spalten, auf die eine PRIMARY KEY-Einschränkung angewendet wird, die NULL-Zulässigkeit auf NOT NULL festgelegt.
Ausgangsstellung der Untersuchung
Für die nachfolgende Untersuchung stand die Frage im Raum, ob ein sich über ein Attribut erstreckender Surrogatschlüssel für einen geclusterten Primärschlüssel besser sei als ein Naturalschlüssel, der sich über mehrere Attribute (cola – cold) erstreckt. Für die Untersuchung wurden zwei Relationen mit identischer Struktur erstellt, die folgenden Aufbau haben:
-- Relation mit Naturalschlüssel auf cola - cold
CREATE TABLE [dbo].[tbl_PK_AD]
(
[Id] int IDENTITY(1,1) NOT NULL,
[cola] char(20) NOT NULL,
[colb] char(20) NOT NULL,
[colc] char(20) NOT NULL,
[cold] char(20) NOT NULL,
[cole] char(80) NULL,
CONSTRAINT pk_tbl_pk_ad PRIMARY KEY CLUSTERED
([cola], [colb], [colc], [cold])
)
GO
-- Relation mit Surrogatschlüssel auf Id
CREATE TABLE [dbo].[tbl_PK_ID]
(
[Id] int IDENTITY(1,1) NOT NULL,
[cola] char(20) NOT NULL,
[colb] char(20) NOT NULL,
[colc] char(20) NOT NULL,
[cold] char(20) NOT NULL,
[cole] char(80) NULL,
CONSTRAINT pk_tbl_PK_Id PRIMARY KEY CLUSTERED
([Id])
)
GO
Während in der Relation dbo.tbl_PK_Id das Attribut des Primärschlüssels auf ID liegt, wird in der Relation dbo.tbl_PK_AD ein zusammengesetzter Primärschlüssel implementiert, der sich über die Attribute [cola] – [cold] erstreckt. In beiden Relation befinden sich ~350.000 Datensätze aus simplen Textkombinationen für die nachfolgenden Tests. Die Datenbank kann für Testzwecke mit allen Testscripts herunter geladen werden. Der Link zum Download befindet sich am Ende des Artikels.
ORDER BY
Eine besondere Eigenschaft des clustered index ist die Tatsache, dass die Daten physikalisch nach den Schlüsselattributen des Clustered Keys gespeichert werden. Zu diesem komplexen Thema können im Artikel “Idealer Datentyp für Clustered Index – GUID vs. INT” weitere Informationen bezogen werden. Die nachfolgende Abfrage auf beide Relationen verdeutlicht einen entscheidenden Vorteil, wenn das Sortierkriterium das/die Primärattribut(e) sind.
SELECT * FROM dbo.tbl_pk_id ORDER BY cola, colb, colc, cold;
SELECT * FROM dbo.tbl_pk_ad ORDER BY cola, colb, colc, cold;
Der Ausführungplan zeigt, dass die Sortierung über Attribute, die nicht indiziert sind, sehr teuer ist. Während für die zweite Abfrage der Clustered Index vollständig verwendet werden kann, muss für die Relation dbo.tbl_PK_Id ein expliziter – nicht durch einen Index abgedeckter – Sortierprozess gestartet werden. Dieser Sortierprozess verursacht fast 50% der Kosten für die Ausführung der Abfrage. Ist nicht ausreichend Arbeitsspeicher vorhanden, muss der Sortiervorgang auf die TEMPDB ausgelagert werden. Interessant ist aber im obigen Zusammenhang die Auswertung des IO für beide Abfragen.
Table 'tbl_PK_ID'. Scan count 9, logical reads 8222, physical reads 0,…
Table 'tbl_PK_AD'. Scan count 1, logical reads 7860, physical reads 0,…
Das IO für die Relation dbo.tbl_PK_ID ist ca. 5% höher als für die Relation dbo.tbl_PK_AD. Dieser höhere Wert ist der Sortierung geschuldet. Der Scan-Count definiert die Anzahl der Durchläufe für die Relation. Während also die Relation dbo.tbl_pk_ad sequentiell gelesen werden konnte, musste die Relation dbo.tbl_pk_id insgesamt 9 Mal durchlaufen werden. Die nächste Auswertung soll zeigen, wie sich das Verhalten ändert, wenn man die zu sortierenden Attribute indiziert.
Verwendung eines Index auf zu sortierende Attribute
Wie bereits oben gesehen, kann ein effizienter Sortiervorgang durchgeführt werden, wenn die Daten sequentiell gelesen werden können und wenn keine wiederholten Scans durchgeführt werden müssen. Wendet man dieses Verhalten auf die Relation dbo.tbl_PK_ID an, so sollte ein Index auf die Attribute [cola], [colb], [colc] und [cold] das Ausführungsverhalten signifikant verändern. Der zu erstellende Index ist ein UNIQUE index, da ja die Attribute in Relation dbo.tbl_PK_AD technisch bedingt (Primary Key) ebenfalls UNIQUE sind.
CREATE UNIQUE INDEX ix_tbl_pk_Id ON dbo.tbl_PK_ID ([cola], [colb], [colc], [cold]);
Nachdem der obige Index erstellt wurde, werden die zuvor ausgeführten Abfragen erneut unter Beachtung der Ausführungspläne ausgeführt.
SELECT * FROM dbo.tbl_pk_id ORDER BY [cola], [colb], [colc], [cold];
SELECT * FROM dbo.tbl_pk_ad ORDER BY [cola], [colb], [colc], [cold];
Das Ergebnis ist ernüchternd – es ändert sich nichts! Der Abfrageplan und die IO-Messungen sind absolut identisch. Das ist der Tatsache geschuldet, dass der zuvor erstellte Index auf der Relation dbo.tbl_PK_ID nicht alle Attribute einschließt, die ausgegeben werden sollen. Die Abfrage soll – auf Grund des “*” alle Attribute der Relation ausgeben. Tatsächlich kann der Index aber nur die Attribute [cola], [colb], [colc] und [cold] abdecken. Um die anderen Informationen ([cole]) abzurufen, müßte ein teurer “Nested Loop” in Verbindung mit einem Key Lookup ausgeführt werden.
SELECT * FROM dbo.tbl_pk_id WITH (INDEX(ix_tbl_pk_id)) ORDER BY cola, colb, colc, cold
Die obige Abbildung zeigt den Ausführungsplan bei der forcierten Verwendung des Indexes. Insgesamt hat SQL Server also tatsächlich alles richtig gemacht und auf teuere Key Lookups verzichtet und statt dessen einen Clustered Index Scan durchgeführt.
Um den Index optimal zu nutzen, müsste also das Attribut [cole] ebenfalls in den Index aufgenommen werden. Da jedoch dieses Attribut nicht der Einschränkung der Eindeutigkeit unterliegt, darf es nicht Bestandteil des Index selbst sein sondern muss mittels INCLUDE lediglich in den Indexseiten gespeichert werden.
CREATE UNIQUE INDEX ix_tbl_pk_Id ON dbo.tbl_PK_ID ([cola], [colb], [colc], [cold]) INCLUDE ([cole]);
Die Besonderheit bei dieser Indexkonfiguration liegt in der Tatsache, dass nun die Relation vollständig abgedeckt ist. Der nachfolgende Screenshot zeigt eine typische Datenseite des Index. Informationen über die Ausgabe von Indexinformationen finden Sie z. B. in dem Blogbeitrag “Neue DMV für Struktur der Datenseiten (Pages)”
Die Attribute [cola] bis [cold] sind Indexattribute (gekennzeichnet durch “(key)”) während das Attribut [code] zusätzlich auf der Datenseite gespeichert wird. Das Schlüsselattribut eines clustered index wird IMMER zu jedem Indexeintrag mitgespeichert. Wie man sehr gut erkennen kann, wird nun die vollständige Relation durch den Index abgedeckt und das Ergebnis des Auführungsplans und des IO ändert sich deutlich.
SELECT * FROM dbo.tbl_pk_id ORDER BY [cola], [colb], [colc], [cold];
SELECT * FROM dbo.tbl_pk_ad ORDER BY [cola], [colb], [colc], [cold];
Nicht nur, dass nun der Index verwendet werden kann – auch die Performance ist deutlich besser als bei der Verwendung des “natural keys”. Schaut man sich die IO-Statistiken an, wird erkennbar, warum dieser Unterschied existiert:
Table 'tbl_PK_ID'. Scan count 1, logical reads 7232, physical reads 0,…
Table 'tbl_PK_AD'. Scan count 1, logical reads 7860, physical reads 0,…
Der IO Unterschied kann zwei Ursachen haben. Zum einen besitzt ein Clustered Index immer mehr Pages, da der Clustered Index die Relation selbst in nach dem Schlüsselattribut physikalisch sortierten repräsentiert. Somit muss also der Clustered Index deutlich mehr Daten speichern als ein non clustered Index.
Eine andere Erklärung für den Unterschied kann der Grad der Fragmentierung des Index sein. Der “clustered key” des Index für die Relation dbo.tbl_PK_AD muss einen Bereich von 4 Attributen abdecken. Sofern die Datensätze nicht mehr auf eine Indexseite abgelegt werden können, wird bei einer vollen Datenseite ein Page Split ausgeführt und die Daten einer betroffenen Seite werden zu 50% in eine neue Datenseite verschoben. Der so gewonnene Platz wird dann so lange aufgefüllt, bis erneut kein Platz mehr vorhanden ist. Durch diesen Vorgang wird der Index stark fragmentiert. Genauere Informationen dazu finden Sie im Artikel “Idealer Datentyp für Clustered Index – GUID vs INT”.
Das nachfolgende Ergebnis zeigt einen direkten Vergleich zwischen dem clustered Index der Relation dbo.tbl_PK_AD und dem zuvor angelegten Index ix_tbl_PK_ID für die Relation dbo.tbl_PK_ID. Die Abfrage für das Ergebnis sieht wie folgt aus:
SELECT OBJECT_NAME(object_id), index_type_desc, index_level, avg_fragmentation_in_percent,page_count, record_count
FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.tbl_PK_AD'), 1, 1, 'DETAILED');
SELECT OBJECT_NAME(object_id), index_type_desc, index_level, avg_fragmentation_in_percent,page_count, record_count
FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.tbl_PK_ID'), 2, 1, 'DETAILED');
In der Abbildung ist sehr deutlich erkennbar, dass der clustered index eine sehr hohe Fragmentierung auf Datenebene (index_level = 0) besitzt. Daraus resultieren natürlich eine erhöhte Anzahl von Pages. Desweiteren besteht der clustered index aus insgesamt 3 B-Tree-Levels während der non clustered index trotz gleichem Datenvolumen mit 2 B-Tree-Levels auskommt. Nach dem Rebuild des clustered index sieht das Ergebnis wie folgt aus:
SELECT * FROM dbo.tbl_pk_id ORDER BY [cola], [colb], [colc], [cold];
SELECT * FROM dbo.tbl_pk_ad ORDER BY [cola], [colb], [colc], [cold];
Abfragen auf indizierte Attribute
Wie im Vorfeld gesehen, hat ein “natural key” im Verhältnis zu einem Surrogatschlüssel zunächst keine signifikanten Vorteile wenn es darum geht, den gesamten Inhalt der Relation auszugeben. Ob eventuell ein Vorteil bei Selektionskriterien vorliegt, soll das nachfolgende Beispiel demonstrieren.
Die nachfolgende Abfrage wird sowohl auf die Relation dbo.tbl_PK_ID als auch dbo.tbl_PK_AD ausgeführt. Hierbei werden zwei Attribute des Index mittels WHERE-Klausel eingeschränkt.
SELECT * FROM dbo.tbl_pk_id
WHERE cola = 'AAAAAAAAAAAAAAAAAAAA' AND
colb = 'AAAAAAAAAAAAAAAAAAAA'
ORDER BY
cola, colb, colc, cold;
SELECT * FROM dbo.tbl_pk_ad
WHERE cola = 'AAAAAAAAAAAAAAAAAAAA' AND
colb = 'AAAAAAAAAAAAAAAAAAAA'
ORDER BY
cola, colb, colc, cold;
Das Ergebnis des Auführungsplans zeigt deutlich, dass es KEINEN Vorteil für eine der beiden Varianten gibt. Beide Abfragen vewenden einen optimalen Index für die Ausführung. Während für die Relation dbo.tbl_PK_ID der Index über die Attribute [cola] bis [cold] verwendet werden kann, wird für die Relation dbo.tbl_PK_AD der clustered index verwendet. Der Index für dbo.tbl_PK_ID kann nur deswegen optimal verwendet werden, da er alle Attribute der Relation (siehe Abbildung weiter oben) abdeckt und keine teure Key Lookups nach sich zieht.
Wichtig ist bei den Abfragen jedoch, dass Attribute der Links-Rechts-Regel folgen. Diese Regel besagt, dass bei Abfragen immer das äusserst linke Attribut mit verwendet werden muss, um einen Index Seek durchführen zu können. Die nachfolgenden Beispiele sollen das verdeutlichen.
SELECT *
FROM dbo.tbl_PK_AD
WHERE cola = REPLICATE('A', 20) AND
colc = REPLICATE('A', 20)
SELECT *
FROM dbo.tbl_PK_AD
WHERE colb = REPLICATE('A', 20) AND
colc = REPLICATE('A', 20)
Vergleicht man die Ausführungspläne, erkennt man sofort, dass die erste Variante deutlich performanter arbeitet, da sie einen Index Seek statt eines Index Scan verwenden kann.
Die Erklärung für dieses Verhalten kann man sehr einfach durch Vewendung eines Beispiels aus dem täglichen Leben ableiten – dem Telefonbuch! Ein Telefonbuch ist immer nach Nachnamen, Mittelnamen und Vornamen sortiert. Sofern der Nachnamen (das erste linke Attribut im Index des Telefonbuchs) nicht bekannt ist sondern nur den Vornamen, dann müsste das komplette Telefonbuch durchsucht werden, um alle Einträge mit dem gesuchten Vornamen ausfindig zu machen. Diese Funktionsweise habe ich im Artikel “Multi-Column-Indexe – Vor- und Nachteile” ausführlich beschrieben.
Datenvolumen
Das Datenvolumen beider Relationen unterscheidet sich deutlich. Das Ergebnis kann auch nicht verwundern, da ja – wie bereits weiter oben erwähnt wurde – der Clustered Index immer die Relation selbst ist, bedeutet dies für die Relation dbo.tbl_PK_AD die Speicherung eines Index. Für die Relation dbo.tbl_PK_ID jedoch sieht dieses Verhältnis wieder anders aus. Hier wird zum einen der clustered index als auch der nachträglich für die Optimierung der Abfragen angelegte Index gespeichert. Diese Manko schlägt sich auch deutlich im benötigten Speichervolumen nieder:
Fazit
Die Frage, die sich auf Basis der obigen Ergebnisse stellt, ist natürlich, warum man einen Surrogatschlüssel vewenden soll, wenn doch ein Natural Key ebenfalls zum gewünschten Ziel führt. Die Antwort ist recht einfach: “It depends”.
Es gibt keine feste Richtlinie, mit der klar definiert werden kann, wann welche Variante verwendet werden kann/soll. Beide Varianten haben ihre Vor- und ihre Nachteile. Auf Ebene der Performance gibt es – bei ausreichender Kenntnis über das Verhalten von Indexen – keine großen Unterschiede. Jedoch sollte immer das gesamte Datenmodell im Fokus bleiben.
In einem OLTP-System ist ein kleiner Index in Form eines Surrogatschlüssel sicherlich performanter. Dieser Vorteil kann jedoch im Rahmen von optimierten Indexen zu deutlich mehr Volumen für zusätzliche Indexe wieder negiert werden. In OLTP-Systemen ist es wichtig, dass Daten möglichst schnell gespeichert werden. Je mehr Indexe jedoch in der Relation vorhanden sind, um so mehr Aktualisierungen müssen durchgeführt werden.
Ein – für mich wesentlicher – weiterer Vorteil von Surrogatschlüsseln ist die Möglichkeit der Anonymisierung von Daten. Wenn z. B. der Primärschlüssel auf einer [Id] statt auf [Vorname], [Nachname], [Ort] liegt, wird in einer abhängigen Relation nur die Id gespeichert. während bei der zweiten Konstruktion immer alle drei Attribute in der Relation gespeichert werden müssen.
In einem DWH (Data Warehouse) findet man sehr häufig statische Daten mit wenig Änderungspotential. Hier bietet sich unter Umständen ein aus mehreren Attributen zusammengesetzter Naturalschlüssel an, um zum Beispiel teure Sortieruoperationen zu vermeiden. Jedoch sind solche Schlüssel kein Garant für schnelle Systeme, da bei Beziehungen zwischen mehreren Relationen das Primärattribut in jeder abhängigen Relation mitgeführt werden muss.
Herzlichen Dank für’s Lesen
Wie schon mal geschrieben, ist ja am 10. und 11.4.2013 die Microsoft Konferenz Synopsis 2013.
Auf dieser könnt Ihr Euch über aktuelle Microsoft-Produkte wie Windows Server 2012, SQL Server 2012, System Center 2012, Exchange und SharePoint 2013 sowie Windows Azure zu informieren! Wo gibst das schon sooo geballt.
Zu den Highlights der Konferenz gehören sicherlich auch die nachmittäglichen IT Camp Breakouts, die Themen wie Private Cloud und Dynamic Infrastructure in den Blick nehmen. Für diese könnt Ihr Euch direkt nach der Anmeldung zur Synopsis registrieren, da die Plätze wiederum begrenzt sind.
by the way, gerade gesehen. Leser des TechNet Newsletters bekommen einen Promo-Code mit 100 Euro Anmeldegebühr. Da Ihr ja alle den Newsletter abonniert habt, hier der Promo-Code "TECHNET2013". :-)
Wir sehen uns auf der Synopsis in Darmstadt!
Diese Woche ist es soweit. Andreas Wolter kommt nach Hamburg in die SQL Server Usergroup (PASS Deutschland e.V.) und hält einen Vortrag zum Thema Extended Events im SQL Server 2012.
Details zum Treffen hatte ich schon mal hier gepostet. Nicht verpassen, wir hatten beim letzten Treffen über 30 Teilnehmer gehabt.
Sofern das Thema für Euch komplett neu ist und Ihr noch nicht genau einschätzen könnt, ob das für Euch etwas ist, dann empfehle ich für den schnellen Hunger zwischendurch meine Einsteiger Webcasts auf Youtube.
SQL Server 2012 Extended Events Grundlagen - Playlist
In den vergangenen Monaten gab es immer wieder Verwirrung rund um die SQL Server Data Tools (SSDT). Denn bisher gab es einen kleinen, aber feinen Unterschied im zur Verfügung stehenden Funktionsumfang zwischen der Version für Visual Studio 2010 und der Version für die Visual Studio 2012 Shell. Die Projektvorlagen für Business Intelligence Projekte (also Analysis […]
Als Fortsetzung zu meinem letzten Cast habe ich einen weiteren aufgenommen, da es diverse Fragen gab.
Ich hatte ja den SSIS Balanced Data Distributor von Microsoft gezeigt und erläutert wie ein Ansatz für die Nutzung für die parallele Sortierung von Daten aussehen könnte.
Bezüglich meiner verwendeten Skript Komponenten kamen Fragen auf, welche ich hier nun beantworten möchte:
Ich hoffe, dass ich damit alle (Un)Klarheiten beseitigen konnte. :-)
Wenn nicht, dann wieder fragen. Okay?
Ich bin ein großer Freund von Inline-Table-Valued-Functions. Das sind Funktionen, die nur aus einem SELECT-Statement bestehen, dass eine Tabelle als Ergebnis liefert. Hier ein Beispiel: CREATE FUNCTION test3.f_orders (@name NVARCHAR(100)) RETURNS TABLE RETURN (SELECT ordid, o.custid, o.uid, orderdate, total FROM test3.orders AS o JOIN test3.user2customers AS uc [...]
Only a short script to get informations about your SQL Server enviroment, like machine name, instance name, edition, version, level, cluster security, user mode, collation and login. I use it as a piece on my whole admin scripts.
--> SQL Server Information <--
SET nocount ON;goUSE [master];goSELECT
CONVERT(CHAR(100), Serverproperty('MachineName')) AS 'MACHINE NAME',
CONVERT(CHAR(50), Serverproperty('ServerName')) AS 'SQL SERVER NAME',
(CASE WHEN CONVERT(CHAR(50), Serverproperty('InstanceName')) IS NULL
THEN 'Default Instance'
ELSE CONVERT(CHAR(50), Serverproperty('InstanceName'))
END) AS 'INSTANCE NAME',
CONVERT(CHAR(30), Serverproperty('EDITION')) AS EDITION,
CONVERT(CHAR(30), Serverproperty('ProductVersion')) AS 'PRODUCT VERSION',
CONVERT(CHAR(30), Serverproperty('ProductLevel')) AS 'PRODUCT LEVEL',
(CASE WHEN CONVERT(CHAR(30), Serverproperty('ISClustered')) = 1
THEN 'Clustered'
WHEN CONVERT(CHAR(30), Serverproperty('ISClustered')) = 0
THEN 'NOT Clustered'
ELSE 'INVALID INPUT/ERROR'
END) AS 'FAILOVER CLUSTERED',
(CASE WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 1
THEN 'Integrated Security '
WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 0
THEN 'SQL Server Security '
ELSE 'INVALID INPUT/ERROR'
END) AS 'SECURITY',
(CASE WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 1
THEN 'Single User'
WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 0
THEN 'Multi User'
ELSE 'INVALID INPUT/ERROR'
END) AS 'USER MODE',
CONVERT(CHAR(30), Serverproperty('COLLATION')) AS COLLATION,
Getdate() AS RunTime,
@@SPID AS 'ID',
SYSTEM_USER AS 'Login Name?',
USER AS 'User Name';go
-- END SQL Server Information --
sql_server_informations.sql
Auch für den SQL Server 2008 R2 sind neue Updates erschienen. Für den SQL Server 2008 R2 mit SP1 ist das kumulative Update 11 erschienen und für den SQL Server 2008 R2 mit SP 2 das kumulative Update 05. Diese können unter folgenden Links angefordert werden.
Kumulatives Update 11 für SQL Server 2008 R2 SP1
Kumulatives Update 05 für SQL Server 2008 R2 SP2
Für den SQL Server 2012 ohne Servicepack ist ein neues Update erschienen. Es handelt sich um das kumulative Update 06 und kann unter folgendem Link angefordert werden:
SQL Server 2012 CU06