SELECT sc.[name] AS [schema]    ,o.[name] AS [table_name]    ,o.type_desc    ,obd.[file_id]    ,obd.page_id    ,obd.page_level    ,obd.row_count    ,obd.free_space_in_bytes    ,obd.is_modified    ,obd.numa_nodeFROM sys.dm_os_buffer_descriptors AS obdJOIN sys.allocation_units AS au ON obd.allocation_unit_id = au.allocation_unit_idJOIN sys.partitions AS p ON au.container_id = p.partition_idJOIN sys.objects AS o ON p.[object_id] = o.[object_id]JOIN sys.schemas AS sc ON o.[schema_id] = sc.[schema_id]WHERE database_id = DB_ID()    AND o.is_ms_shipped = 0ORDER BY obd.page_id    ,o.[name]

SELECT TOP (100) qs.execution_count    ,qs.total_rows    ,qs.last_rows    ,qs.min_rows    ,qs.max_rows    ,qs.last_elapsed_time    ,qs.min_elapsed_time    ,qs.max_elapsed_time    ,total_worker_time    ,total_logical_reads    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (            CASE                 WHEN qs.statement_end_offset = - 1                    THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2                ELSE qs.statement_end_offset                END - qs.statement_start_offset            ) / 2) AS query_textFROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtORDER BY qs.execution_count DESCOPTION (RECOMPILE);

SELECT TOP (100) qs.execution_count    ,qs.last_elapsed_time    ,qs.min_elapsed_time    ,qs.max_elapsed_time    ,total_worker_time    ,total_logical_reads    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (            CASE                 WHEN qs.statement_end_offset = - 1                    THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2                ELSE qs.statement_end_offset                END - qs.statement_start_offset            ) / 2) AS query_textFROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qtORDER BY qs.execution_count DESCOPTION (RECOMPILE);

SELECT TOP (50) [text] AS [QueryText]    ,cp.cacheobjtype    ,cp.objtype    ,cp.size_in_bytes / 1024 AS [Plan Size in KB]FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)CROSS APPLY sys.dm_exec_sql_text(plan_handle)WHERE cp.cacheobjtype = N‘Compiled Plan‘    AND cp.objtype IN (        N‘Adhoc‘        ,N‘Prepared‘        )    AND cp.usecounts = 1ORDER BY cp.size_in_bytes DESCOPTION (RECOMPILE);

SELECT dm_ws.wait_duration_ms    ,dm_ws.wait_type    ,dm_es.STATUS    ,dm_t.TEXT    ,dm_qp.query_plan    ,dm_ws.session_ID    ,dm_es.cpu_time    ,dm_es.memory_usage    ,dm_es.logical_reads    ,dm_es.total_elapsed_time    ,dm_es.program_name    ,DB_NAME(dm_r.database_id) DatabaseName    ,dm_ws.blocking_session_id    ,dm_r.wait_resource    ,dm_es.login_name    ,dm_r.command    ,dm_r.last_wait_typeFROM sys.dm_os_waiting_tasks dm_wsINNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_idINNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_idCROSS APPLY sys.dm_exec_sql_text(dm_r.sql_handle) dm_tCROSS APPLY sys.dm_exec_query_plan(dm_r.plan_handle) dm_qpWHERE dm_es.is_user_process = 1;

CREATE TABLE #sp_who2 (    SPID INT    ,STATUS VARCHAR(255)    ,LOGIN VARCHAR(255)    ,HostName VARCHAR(255)    ,BlkBy VARCHAR(255)    ,DBName VARCHAR(255)    ,Command VARCHAR(255)    ,CPUTime INT    ,DiskIO INT    ,LastBatch VARCHAR(255)    ,ProgramName VARCHAR(255)    ,SPID2 INT    ,REQUESTID INT    )INSERT INTO #sp_who2EXEC sp_who2SELECT *FROM #sp_who2 w--WHERE w.ProgramName = ‘xxx‘DROP TABLE #sp_who2

SELECT spid    ,a.[status]    ,hostname    ,program_name    ,cmd    ,cpu    ,physical_io    ,blocked    ,b.[name]    ,loginameFROM master.dbo.sysprocesses aINNER JOIN master.dbo.sysdatabases b ON a.dbid = b.dbidwhere hostname != ‘‘ORDER BY program_name

DECLARE @current_sql_handle BINARY (20);DECLARE @sql_text_list TABLE (    sql_handle BINARY (20)    ,TEXT NVARCHAR(max)    );DECLARE sql_handle_cursor CURSORFORSELECT sp.sql_handleFROM sys.sysprocesses spWHERE sp.sql_handle != 0x0000000000000000000000000000000000000000    --AND sp.program_name = ‘xxxx‘    ;OPEN sql_handle_cursorFETCH NEXTFROM sql_handle_cursorINTO @current_sql_handleWHILE @@FETCH_STATUS = 0BEGIN    INSERT INTO @sql_text_list (        sql_handle        ,TEXT        )    SELECT @current_sql_handle        ,est.TEXT    FROM sys.dm_exec_query_stats qs    CROSS APPLY sys.dm_exec_sql_text(@current_sql_handle) est;    FETCH NEXT    FROM sql_handle_cursor    INTO @current_sql_handleENDSELECT DISTINCT *FROM @sql_text_list tlWHERE tl.TEXT NOT LIKE ‘%statement_start_offset%‘;CLOSE sql_handle_cursorDEALLOCATE sql_handle_cursor

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (            (                (                    CASE statement_end_offset                        WHEN - 1                            THEN DATALENGTH(st.TEXT)                        ELSE qs.statement_end_offset                        END                    ) - qs.statement_start_offset                ) / 2            ) + 1) AS statement_text    ,last_execution_time    ,total_elapsed_time / execution_count avg_elapsed_time    ,total_physical_reads    ,total_logical_reads    ,total_logical_writes    ,execution_count    ,total_worker_time    ,total_elapsed_time    ,creation_timeFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERE last_execution_time > DATEADD(SECOND, - 60, GETDATE())    AND st.TEXT NOT LIKE ‘%statement_start_offset%‘    AND total_elapsed_time / execution_count >= 300ORDER BY last_execution_time DESC;

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (            (                (                    CASE statement_end_offset                        WHEN - 1                            THEN DATALENGTH(st.TEXT)                        ELSE qs.statement_end_offset                        END                    ) - qs.statement_start_offset                ) / 2            ) + 1) AS statement_text    ,last_execution_time    ,total_elapsed_time / execution_count avg_elapsed_time    ,total_physical_reads    ,total_logical_reads    ,total_logical_writes    ,execution_count    ,total_worker_time    ,total_elapsed_time    ,creation_timeFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERE last_execution_time > DATEADD(SECOND, - 60, GETDATE())    AND st.TEXT NOT LIKE ‘%statement_start_offset%‘    AND execution_count < 100    AND total_elapsed_time / execution_count > 100    AND SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (            (                (                    CASE statement_end_offset                        WHEN - 1                            THEN DATALENGTH(st.TEXT)                        ELSE qs.statement_end_offset                        END                    ) - qs.statement_start_offset                ) / 2            ) + 1) NOT LIKE ‘SELECT%‘ORDER BY last_execution_time DESC;

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (            (                (                    CASE statement_end_offset                        WHEN - 1                            THEN DATALENGTH(st.TEXT)                        ELSE qs.statement_end_offset                        END                    ) - qs.statement_start_offset                ) / 2            ) + 1) AS statement_text    ,total_elapsed_time / execution_count / 1000 AS avg_elapsed_time_by_ms    ,last_execution_time    ,total_elapsed_time    ,execution_count    ,total_worker_time    ,total_physical_reads    ,total_logical_reads    ,total_logical_writes    ,creation_timeFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERE qs.execution_count > 1000    AND last_execution_time > DATEADD(SECOND, - 60, GETDATE())    --AND (    --    st.TEXT LIKE ‘%[[]AAA]%‘    --    OR st.TEXT LIKE ‘%[[]BBB]%‘    --    OR st.TEXT LIKE ‘%[[]CCC]%‘    --    )ORDER BY total_elapsed_time / execution_count DESC;

SELECT TOP 10 TEXT AS ‘SQL Statement‘    ,last_execution_time AS ‘Last Execution Time‘    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]    ,execution_count AS "Execution Count"    ,qp.query_plan AS "Query Plan"FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY total_elapsed_time / execution_count DESC