Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
NguyenL71
Posting Yak Master
228 Posts |
 Posted - 2014-02-12 : 14:34:32
|
| Hi,When I ran the DMV below, I got details info but how can I can the top 20 SP name from the queries below.Please see the desired results below.Thank you so much in advance.-- Listing 4.6 The queries that use the most I/OSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT TOP 20--[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)--, [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /-- qs.execution_count--, qs.execution_count 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) + 1) AS [Individual Query], qt.text AS [Parent Query]--, DB_NAME(qt.dbid) AS DatabaseName--, qp.query_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpORDER BY [Total IO] DESC-- Desired Results:-------------CREATE PROCEDURE [dbo].[usp_HOS_InsertDailySummary]CREATE PROCEDURE [dbo].[asp_Application_GetEventTrackingDataActivity] |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Return only the ObjectName from DMVs...