Uwe Ricken - Neueste Kommentare auf CTE, Tabellenvariablen und temporäre Tabellen https://www.insidesql.org/blogs/uricken/?disp=comments de-DE http://backend.userland.com/rss 60 uricken in Antwort auf: CTE, Tabellenvariablen und temporäre Tabellen Fri, 22 Feb 2013 15:04:44 +0000 c2200@https://www.insidesql.org/blogs/ Hallo Torsten, auf jeden Fall ist ein optimiertes Szenario der TEMPDB sinnvoll. Insbesondere ein schnelles IO-Subsystem ist hier entscheidend. Da aber in großen Umgebungen fast ausschließlich mit SAN gearbeitet wird, ist es fast schon wieder vernachlässigbar, da ja zunächst kein physischer IO generiert wird. Grundsätzlich würde ich aber bei größeren Datenmengen - unabhängig von der Leistung von TempDB - auf temporäre Tabellen zurückgreifen, da ich hier deutlich mehr Optimierungsmöglichkeiten (Indexes / Statistiken !!!) habe als mit Tablevariablen und TableFunctions. Bezüglich der "Dirty Pages" habe ich keine Verschlechterungen bemerkt. Hierbei bin ich wie folgt vorgegangen: 1. CHECKPOINT in tempdb um alle Änderungen zu speichern 2. Ausführung der CTE und unmittelbar danach die Ausführung der nachfolgenden Abfrage (in TEMPDB) ;WITH memusage_CTE AS ( SELECT bd.database_id, bd.file_id, bd.page_id, bd.page_type, COALESCE(p1.object_id, p2.object_id) AS object_id, COALESCE(p1.index_id, p2.index_id) AS index_id, bd.row_count, bd.free_space_in_bytes, CONVERT(tinyint, bd.is_modified) AS 'DirtyPage' FROM sys.dm_os_buffer_descriptors bd INNER JOIN sys.allocation_units au ON (au.allocation_unit_id = bd.allocation_unit_id) OUTER APPLY ( SELECT TOP(1) p.object_id, p.index_id FROM sys.partitions AS p WHERE p.hobt_id = au.container_id AND au.type IN (1, 3) ) p1 OUTER APPLY ( SELECT TOP(1) p.object_id, p.index_id FROM sys.partitions AS p WHERE p.partition_id = au.container_id AND au.type = 2 ) p2 WHERE bd.database_id = DB_ID() AND bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE','TEXT_MIX_PAGE') ) SELECT DB_NAME(database_id) AS 'Database', OBJECT_NAME(object_id, database_id) AS 'Table Name', index_id,COUNT(*) AS 'Pages in Cache', SUM(dirtyPage) AS 'Dirty Pages' FROM memusage_CTE GROUP BY database_id, object_id, index_id ORDER BY SUM(dirtyPage) DESC Du wirst sehen, dass sich die Anzahl der Dirty Pages nicht erhöhen wird. PS: Diese Abfrage ist im Original NICHT von mir sondern ich habe sie dankenswerter Weise aus dem Blog von Steve Hindmarsh: <a href="http://sqlblogcasts.com/blogs/steveh/archive/2010/04/02/dbcc-memusage-in-2008.aspx">http://sqlblogcasts.com/blogs/steveh/archive/2010/04/02/dbcc-memusage-in-2008.aspx</a> http://sqlblogcasts.com/blogs/steveh/archive/2010/04/02/dbcc-memusage-in-2008.aspx]]> https://www.insidesql.org/blogs/uricken/2013/02/06/cte-tabellenvariablen-und-temporaere-tabellen#c2200 tosc in Antwort auf: CTE, Tabellenvariablen und temporäre Tabellen Thu, 21 Feb 2013 20:36:53 +0000 c2199@https://www.insidesql.org/blogs/ Hallo Uwe,<br /> <p>"<em>Bei sehr großen Datenmengen empfiehlt sich auf jeden Fall der Einsatz von temporären Tabellen an Stelle von ...</em>" setzt natürlich ein wirklich gutes tuning der tempdb voraus, so dass Latch-Konflikten entgegen gewirkt werden kann. Das user object in der tempdb wird ziemlich voll werden.</p> <p>Was mich ein wenig stutzig macht, ist das UNION ALL Statement in der CTE - je nach Datenmenge wird es dann in den internal objects der tempdb evtl. zu dirty pages führen - oder wie siehst Du das?</p> <p>Gruß,<br />Torsten</p>

"Bei sehr großen Datenmengen empfiehlt sich auf jeden Fall der Einsatz von temporären Tabellen an Stelle von ..." setzt natürlich ein wirklich gutes tuning der tempdb voraus, so dass Latch-Konflikten entgegen gewirkt werden kann. Das user object in der tempdb wird ziemlich voll werden.

Was mich ein wenig stutzig macht, ist das UNION ALL Statement in der CTE - je nach Datenmenge wird es dann in den internal objects der tempdb evtl. zu dirty pages führen - oder wie siehst Du das?

Gruß,
Torsten

]]> https://www.insidesql.org/blogs/uricken/2013/02/06/cte-tabellenvariablen-und-temporaere-tabellen#c2199 Uwe Ricken in Antwort auf: CTE, Tabellenvariablen und temporäre Tabellen Fri, 15 Feb 2013 10:03:47 +0000 Uwe Ricken c2193@https://www.insidesql.org/blogs/ Hallo Christoph, sorry - mein Fehler. Ich habe die ganzen Downloads nun etwas verwaltbarer gemacht :(. Nun sollte der Download gehen :) Danke für den Hinweis. Any comment is really appreciated! https://www.insidesql.org/blogs/uricken/2013/02/06/cte-tabellenvariablen-und-temporaere-tabellen#c2193 cmu in Antwort auf: CTE, Tabellenvariablen und temporäre Tabellen Thu, 14 Feb 2013 14:47:13 +0000 c2192@https://www.insidesql.org/blogs/ Hallo Uwe, das sind sehr interessante Ausführungen! Leider funktioniert der Link für das Beispielscript nicht. Gruß Christoph https://www.insidesql.org/blogs/uricken/2013/02/06/cte-tabellenvariablen-und-temporaere-tabellen#c2192