SQL Server 2012 Tempdb File Size Change tracked with Extended Events

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

Torsten Schuessler

Inappropriate?

Kostenloser PASS-Vortrag "From Locks to Dead-locks. – Concurrency in SQL Server“ - 19.03.2013 - Nürnberg;




www.sqlpass.de

 Hallo PASS‘ler,

hiermit möchten wir euch zum nächsten Treffen - ein wirkliches Highlight -  der Regionalgruppe Franken in Nürnberg am 
Dienstag, 19. März 2013
18:30 bis ca. 20:30
zum 
kostenlosen Vortrag

From Locks to Dead-locks.  – Concurrency in SQL Server 
 

 From Locks to Dead-locks.  – Concurrency in SQL Server

 

In diesem Vortrag geht es um essentielle Mechanismen im SQL Server, die sowohl Administratoren als auch Entwickler beeinflussen und kennen sollten: Warum muss SQL Server Objekte sperren, welche Auswirkungen kann das haben (Performance und Datenintegrität) und wie wir darauf Einfluss nehmen können. 

·          Können Indexe Blockaden verhindern?
·          Warum NOLOCK bei geschäftskritischen Daten keine gute Idee ist.
·          Wann und was passiert bei Lock-Escalation?
·          Was passiert bei zu vielen Sperren auf ein Object? Was ist zu viel?
·          Was hat Table-Design mit Sperren zu tun? (Stichwort "Large Databases")
·          Warum man Fehler Nummer 1205 kennen sollte. - Wie und wobei Deadlocks auftreten können und wie man sie beheben kann.
·          Wie ist „Optimistic Concurrency“ SQL Server implementiert?

Das Ganze natürlich wie immer reich an Demos!!!

 Sprecher
 
 

Andreas Wolter ist einer von weltweit derzeit weniger als 100 Microsoft Certified Master SQL Server 2008 (MCM) und in Deutschland bislang der einzige von Microsoft unabhängige; außerdem MCT, MCITP:DD/DA/BID, MCDBA, MCSA und kann auf über zehn Jahre Erfahrung als Trainer, Consultant und Architekt für SQL Server Datenbanksysteme zurückblicken.

Seit einigen Jahren ist er aktives Mitglied der PASS und Sprecher auf Fachkonferenzen in Deutschland und den USA. Mit seiner Firma SARPEDON Quality Lab (www.SarpedonQualityLab.com) hat er sich besonders auf die Entwicklung und Optimierung von Datenbank- und Datawarehouse-Architekturen spezialisiert und bietet auch intensive Trainings und Coaching an. 

 Sein Blog ist unter http://www.vb-magazin.de/forums/blogs/andreaswolter zu finden. 


 

 

 Veranstaltungsort und Anmeldung

 
 New Elements GmbH / it-schulungen.com

www.it-schulungen.com
Thurn-und-Taxis-Straße 10
(Alcatel-Lucent Gebäude im Nordostpark)
D - 90411 Nürnberg
Fon: 0911 - 65 00 83 - 25 

Kostenfreie Parkplätze sind vorhanden.

Über Euer Kommen freuen wir uns sehr. Bitte teilt uns zwecks Planung der Räumlichkeiten mit, ob Ihr kommt.
Gerne könnt Ihr die Einladung an Kollegen und Mitarbeiter weitergeben.

 Wir bitten um eine vorherige Anmeldung per XING www.xing.com/events oder per Email an: mde@sqlpass.de.

Wir freuen uns auf eine rege Runde – und wer Lust hat bringt Zeit mit, den Abend gemeinsam beim Italiener ausklingen zu lassen.

Torsten Schüßler und Michael Deinhard

 Kontakt
 
 PASS Deutschland e.V.
http://www.sqlpass.de 

Regionalgruppe Franken
http://www.sqlpass.de/Regionalgruppen/Franken/tabid/71/Default.aspx

PASS Deutschland e.V. auf den Frankfurter Datenbanktagen 2013

 Frankfurter Datenbanktage 2013

Die Frankfurter Datenbanktage finden im Frühjahr 2013 vom 14. bis 15. März statt. An zwei Konferenztagen werden Datenbankthemen aus der Welt von IBM, Oracle, Microsoft und Open Source-Datenbanken in fünf parallelen Vortragstracks und rund 60 intensiven Sessions behandelt.

Am Vortag der Konferenz, am 13. März gibt es einen optional buchbaren Schulungstag zu „Virtualisierung, Cloud Computing und Hochverfügbarkeit mit SQL Server“. Maic Beher und Uwe Ricken zeigen die technischen Möglichkeiten, mit Hilfe von HyperV, SCOM und SQL Server komplexe Cloud-Lösungen zu realisieren. AlwaysOn, Mirroring und Replikation sind weitere Themen des Hands on Trainings.

Ruprecht Dröge zeigt in seiner Session einen Praxisvergleich der Hochverfügbarkeitslösungen mit MS SQL Server. Er ist ist Autor der Bücher „SQL Server 2012″, „SQL Server 2008″ und „SQL Server 2005“, die bei Microsoft Press erschienen sind. Lars Platzdasch geht auf Internals zur IO-Infrastrukturplanung von MS SQL Server-Umgebungen ein: IO-Diagnose, IO-Stresstests, Tools zur Problemerkennung und SAN-Optimierung. „SQL Server 2012 Express - Möglichkeiten und Grenzen“ ist das Thema von Robert Panther. Der mehrfach zertifizierte Datenbank-Experte, Trainer und Buchautor veröffentlichte im Mai 2012 bei Microsoft Press in der Reihe "Richtig einsteigen" eines der ersten deutschsprachigen Bücher zu SQL Server 2012. Anfang 2010 erschien bei entwickler.press sein SQL Server Performance-Ratgeber. Daniel Caesar, Autor des Buches "SQL Server 2008 R2 Einführung für Administratoren und Entwickler", das 2010 bei Galileo erschienen ist, spricht über MS SQL Server 2012: Er wird neue Funktionen des MS SQL Server 2012 aus Sicht einer SharePoint 2010 Plattform vorstellen und bewerten.

Mitglieder der PASS Deutschland e.V. erhalten die Möglichkeit, einen Rabatt von 20% auf alle Teilnehmerpakete zu nutzen!

 Natürlich gibt es auch die Möglichkeit die PASS vor Ort kennen zu lernen. Christoph Muthmann(MVP) und Torsten Schüßler beantworten gerne Eure Fragen - wir freuen uns auf Euch!

SQL Server Information

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

Kostenloser PASS-Vortrag "Performance & Manageability der tempdb“ - 19.02.2013 - Nürnberg‏

www.sqlpass.de

 Hallo PASS‘ler,

hiermit möchten wir euch zum nächsten Treffen der Regionalgruppe Franken in Nürnberg am 
Dienstag, 19. Februar 2013
18:30 bis ca. 20:30
zum 
kostenlosen Vortrag

Performance & Manageability der tempdb 
 

 Performance & Manageability der tempdb

Die tempdb, eine Systemdatenbank im SQL Server, die es in sich hat und trotzdem oft genug ein Schattendasein führt.  Missverständnisse und widersprüchliche Aussagen zum Best Practice der tempdb, heizen die anhaltende Diskussion in der SQL Server Community immer wieder an. Anlass genug für Torsten Schüssler die Aufgaben und Einsatzmöglichkeiten der temdb vorzustellen und Antworten zu folgenden Fragen liefern:

  • Gibt es eine einheitliche Lösung zur Performance-Optimierung, die in jeder Situation angewendet werden kann?
  • Wie viele  Dateien sollte die tempdb haben?
  • Welches RAID? Wie wäre es mit Solid State Drives (SSDs)?
  • Wie kann ich Performance-Probleme überwachen? 
 Sprecher

 

Torsten Schüßler aka tosc, ist Kind der Sinclair ZX81 Generation. Seit über 18 Jahren ist er als zertifizierter Datenbank- und Systemadministrator (MCTS|MCITP) tätig. Sein umfassendes Wissen bringt er bei der international tätigen Europoles ein.
Als einer der ersten Regulars von InsideSQL berichtet er zusammen mit Frank Kalis und Christoph Muthmann laufend über Interessantes zum SQL Server. Torsten unterstützt die PASS Deutschland e.V. als RGV in der Region Franken.

 

 Veranstaltungsort und Anmeldung

 
 

New Elements GmbH / it-schulungen.com
www.it-schulungen.com
Thurn-und-Taxis-Straße 10
(Alcatel-Lucent Gebäude im Nordostpark)
D - 90411 Nürnberg
Fon: 0911 - 65 00 83 - 25 

Kostenfreie Parkplätze sind vorhanden.

Über Euer Kommen freuen wir uns sehr. Bitte teilt uns zwecks Planung der Räumlichkeiten mit, ob Ihr kommt.
Gerne könnt Ihr die Einladung an Kollegen und Mitarbeiter weitergeben.

 

Wir bitten um eine vorherige Anmeldung per XING www.xing.com/events oder per Email an: mde@sqlpass.de.
Wir freuen uns auf eine rege Runde – und wer Lust hat bringt Zeit mit, den Abend gemeinsam beim Italiener ausklingen zu lassen.

Torsten Schüßler und Michael Deinhard

 Kontakt
 
 PASS Deutschland e.V.
http://www.sqlpass.de 

Regionalgruppe Franken
http://www.sqlpass.de/Regionalgruppen/Franken/tabid/71/Default.aspx

Kostenloser PASS-Vortrag "Performance Optimierung der Integration Services des SQL Servers“ - 15.01.2013 - Nürnberg‏


www.sqlpass.de

 Hallo PASS‘ler,

hiermit möchten wir euch zum nächsten Treffen der Regionalgruppe Franken in Nürnberg am 
Dienstag, 15. Januar 2013
18:30 bis ca. 20:30
zum 
kostenlosen Vortrag

Performance Optimierung der Integration Services des Microsoft SQL Server 2008 
 

Full story »

SQL Server 2012 Service Pack 1(SP1) Customer Technology Preview 4 (CTP4) Available

MICROSOFT® SQL SERVER® 2012 SERVICE PACK 1 (SP1), COMMUNITY TECHNOLOGY PREVIEW 4 (CTP4)

Download:
SQL Server 2012 SP1 CTP4
SQL Server 2012 SP1 CTP4 Express
SQL Server 2012 SP1 CTP4 Feature Pack

The SQL Server 2012 builds that were released after SQL Server 2012 was released:
Download: http://support.microsoft.com/kb/2692828/

Full story »

Antivirus Software on SQL Server

It’s an old story, but still present - Why or why not installing antivirus software on a database server?

First of all, I’ve been on both sides of the debate with respect, but I can only, only partially agree to install antivirus software on a database server - e.g Microsoft SQL Server. And this only partially agreement means, first not to install antivirus software on SQL Server as many IT departments do in a standard procedure. In addition, the default settings are left untouched, so that every virtually byte is examined in real time.

Have you ever examined the amount of memory, high CPU spikes or high CPU usage and last but not least your high consumer of I/O activities on your (busy) SQL Server?
So let us take a closer look by using Process Explorer to identify antivirus and SQL Server tasks since start-up.

 

Identifying the SQL Server and Antivirus consumers

I prefer using Process Explorer to take a immediate first look what happens on the server, he includes many interesting counters for regarding some performance issues and more. By selecting View-Select Columns…, we can add interesting counters such as Private Bytes, Working Set, Threads, Handles, I/O Reads, I/O Read Bytes, I/O Writes, I/O Write Bytes, CPU Time – as shown in the screenshot below.

Process Explorer Private Bytes, Working Set, Threads, Handles, I/O Reads, I/O Read Bytes, I/O Writes, I/O Write Bytes, CPU Time

The screenshot above shows that while the ntrtscan.exe (Real-time Scan Service) has read 70% of the bytes of SQL Server, it has performed 27 times as much read I/O as the read activity isn’t as efficient as SQL Server. And the tmlisten.exe (antivirus communication service) has only write 7% of the bytes of SQL Server, but it has performed four times as much write I/O as the write activity of SQL Server.

Now I want to see the leaders of high I/O Reads on my server by filtering I/O Reads (see screenshot below).

Process Explorer - Filter high I/O Reads

The first three consumers of I/O Reads are:

  1. Antivirus (ntrtscan) with 111.641.100 Reads
  2. SQL Server (sqlservr) with 4.622.225 Reads
  3. Antivirus (tmlisten) with 2409809 Reads

Same doing on I/O Writes:

Process Explorer - Filter high I/O Writes
  1. Antivirus (tmlisten) with 31.124.046 Writes
  2. SQL Server (sqlservr) with 9.785.283 Writes
  3. ReportingServices with 1.715.489 Writes

So, it’s possible for antivirus to consume many times of I/O’s of SQL Server which can really hamper performance on your (busy) SQL Server. It is possible that the antivirus is consuming more than 50% of total I/O activities! (Please note the recommendations to Microsoft SQL Server Database Engine Input/Output Requirements)

 

And what does Microsoft say for antivirus on your SQL Server (KB309422)?



"We strongly recommend that you individually assess the security risk for each computer that is running SQL Server in your environment and that you select the tools that are appropriate for the security risk level of each computer that is running SQL Server. Additionally, we recommend that before you roll out any virus-protection project, you test the whole system under a full load to measure any changes in stability and performance.
Virus protection software requires some system resources to execute. You must perform testing before and after you install your antivirus software to determine whether there is any performance effect on the computer that is running SQL Server.
"

In my opinion it is very carefully worded, with a “It depends on …” – but they help us!

Read this KB very carefully with you Antivirus Administrator, talk with him about virus risks on the server, performance effects on a (busy) SQL Server and how intrusive the antivirus is at the ratio of the security risk level. In addition read also KB822158, there are further recommendations for the windows server and should be applied as well and first!

I don’t want to repeat these KBs, but here are some recommended must reads:

 


 Turn off scanning of Windows Update or Automatic Update related files
 Turn off scanning of Windows Security files
 Turn off scanning of Group Policy related files
 Directories and file-name extensions to exclude from virus scanning
 Processes to exclude from virus scanning
 Considerations for clustering

AND - consider an external virus scan against your SQL Server, which is possible in most enterprise antivirus products – often you needn’t a real time virus scan, it belongs to the security risk level!

I wish you a nice  day,
tosc

Torsten Schuessler

SQL Server 2012 - New and Modified Dynamic Management Views and Functions - DMVs

 

That's just about the limit. In Books Online for SQL Server 2012 (BOL) by reading What's New (Database Engine), I came around the topic Manageability Enhancements (Database Engine) and its paragraph New and Modified Dynamic Management Views and Functions, and I only see six added or modified system views. And I think by myself, these are the same six DMVs I had read in CTP Denali BOL - no update?

But I knew that there are more new DMVs. I do not mean the AlwaysOn Availability Groups Dynamic Management Views and Functions of the AlwaysOn feature etc. ... - So, I think by myself - figure out!

OK, I have a SQL Server 2008 R2 instance and a SQL Server 2012 instance (both are only for testing, no production environment!). But I don't want to query each instance and copy the result for further investigations in excel - like this way:

USE [master];
GO
SELECT name, type, type_desc
FROM sys.system_objects  WITH (NOLOCK)
WHERE name LIKE 'dm_%'
ORDER BY name OPTION (RECOMPILE);
GO

 

New Dynamic Management Views and Functions in SQL Server 2012

I create a valid linked server point form SQL Server 2012 to SQL Server 2008 R2 (@server = N'SQL2008R2'), then I run below query from SQL Server 2012 - which will find DMVs that did not exists in 2008 R2 ('SQL2008R2') -  comparing the datasets in sys.system_objects.

 

USE [master];  --- execute on SQL Server 2012
GO
SELECT
       N'sys.' d.[name] AS [DMV NAME],
       d.[type] AS [DMV TYPE],
       d.type_desc AS [DMV TYPE DESC]
FROM
      [master].sys.system_objects AS WITH (NOLOCK)
LEFT OUTER JOIN --- LinkedServer SQL Server 2008 R2
      [SQL2008R2].[master].sys.system_objects AS WITH (NOLOCK)
ON
      d.[name] r.[name] ---* collate Latin1_General_CI_AS
      AND d.[schema_id] = r.[schema_id]
WHERE
      r.[name] IS NULL
      AND d.[schema_id] = 4
      AND d.[is_ms_shipped] = 1
      AND d.[name] like 'dm_%'
ORDER BY
      d.[name] OPTION (RECOMPILE);
GO

Linked Query Result

I get this resultset, and YES SQL Server 2012 has really 36 new Dynamic Management Views and Functions.

 
DMV NAMEDMV TYPEDMV TYPE DESC
sys.dm_db_database_page_allocations IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_fts_index_physical_stats VIEW
sys.dm_db_log_space_usage VIEW
sys.dm_db_objects_disabled_on_compatibility_level_change IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_uncontained_entities VIEW
sys.dm_exec_describe_first_result_set IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_exec_describe_first_result_set_for_object IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_filestream_non_transacted_handles VIEW
sys.dm_fts_index_keywords_by_property IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_fts_semantic_similarity_population VIEW
sys.dm_hadr_auto_page_repair VIEW
sys.dm_hadr_availability_group_states VIEW
sys.dm_hadr_availability_replica_cluster_nodes VIEW
sys.dm_hadr_availability_replica_cluster_states VIEW
sys.dm_hadr_availability_replica_states VIEW
sys.dm_hadr_cluster VIEW
sys.dm_hadr_cluster_members VIEW
sys.dm_hadr_cluster_networks VIEW
sys.dm_hadr_database_replica_cluster_states VIEW
sys.dm_hadr_database_replica_states VIEW
sys.dm_hadr_instance_node_map VIEW
sys.dm_hadr_name_id_map VIEW
sys.dm_logconsumer_cachebufferrefs IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logconsumer_privatecachebuffers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_consumers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_hashentries VIEW
sys.dm_logpool_sharedcachebuffers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_stats VIEW
sys.dm_logpoolmgr_freepools IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_respoolsize IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_stats IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_os_cluster_properties VIEW
sys.dm_os_memory_broker_clerks VIEW
sys.dm_os_server_diagnostics_log_configurations VIEW
sys.dm_resource_governor_resource_pool_affinity VIEW
sys.dm_tcp_listener_states VIEW

... Now you can query Books Online for SQL Server 2012 ....

To struggle through these new Dynamic Management Views and Functions in SQL Server 2012, will be an ongoing and hard work :-)

I wish you a nice  day,
tosc

Torsten Schuessler
« 1 2 3 4 5 6 7 8 9 10 11 ... 24 »