/*============================================================================
  File:     DMObjectQueries.sql

  Summary:  This script just looks around for the new DM Objects and runs
			some queries to verify their structure, etc.
			Use the DMObjects script to create some of the functions 
			used in this script!

  Date:     March 15, 2005

  SQL Server Version: 9.00.1090 (IDW13)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Querying for DMV and DMF names
SELECT so.* 
FROM sys.system_objects AS so
WHERE so.[name] LIKE 'dm_%index%' 
ORDER BY so.[name]

-- Find all dm objects by name and then order by type...
SELECT so.* 
FROM sys.system_objects AS so
WHERE so.[name] LIKE 'dm_%' 
ORDER BY so.[name]

-- Use the sp_GetDMObjectInfo procedure created in
-- DMObjects to get more details about the different
-- DM Objects that exist.

EXEC sp_GetDMObjectInfo N'dm_db_index_physical_stats'
EXEC sp_GetDMObjectInfo N'dm_os_latch_stats'
EXEC sp_GetDMObjectInfo N'dm_os_performance_counters'

SELECT * FROM sys.dm_os_performance_counters

EXEC sp_GetDMObjectInfo N'dm_exec_cached_plans'
SELECT * FROM sys.dm_exec_cached_plans
-- but now you see a plan_handle - what's the SQL?
-- you get the text from another dm called dm_exec_sql_text

EXEC sp_GetDMObjectInfo N'dm_exec_sql_text'
go

SELECT cp.*, st.text 
FROM sys.dm_exec_cached_plans AS cp 
	CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
ORDER BY cp.pagesused DESC
COMPUTE SUM(cp.refcounts), SUM(cp.usecounts), SUM(cp.pagesused)
go

-- What if the output is XML and may include multiple rows of output?
CREATE FUNCTION sql_text (@handle [varbinary](64), @start [int], @end [int])
RETURNS varchar(max)
AS
BEGIN
 DECLARE @result varchar(max)
 DECLARE @batch nvarchar(max)
 SET @batch = (SELECT [text] FROM sys.dm_exec_sql_text(@handle))
 IF (@end = -1)
  SET @end = len(@batch) * 2
  SET @result =  substring(@batch, @start/2, (@end - @start)/2)
  RETURN @result
END
go