Data Quality Services Management

--A. Flow Audit trail will show the duration of each flow (internal action) inside of the Cleansing.

SELECT DATEDIFF(MILLISECOND, F.START_TIME, FA.END_TIME) AS DurationMilliSec,

F.START_TIME, FA.END_TIME, KB.NAME as KB_NAME,

F.*, FA.*

FROM DQS_MAIN.dbo.A_FLOW F WITH (NOLOCK)

LEFT OUTER JOIN DQS_MAIN.dbo.A_FLOW_ANSWER FA WITH (NOLOCK)

ON F.ID=FA.FLOW_ID

LEFT OUTER JOIN DQS_MAIN.dbo.A_KNOWLEDGEBASE KB WITH (NOLOCK)

ON KB.ID=F.KNOWLEDGEBASE_ID

ORDER BY DurationMilliSec DESC;

--Pivot by Process (an activity like running the SSIS Project)

DECLARE @Process_UID varchar(255)

DECLARE processes CURSOR

FOR SELECT UNIQUE_ID FROM DQS_MAIN.dbo.A_PROCESS ORDER BY CREATION_TIME

OPEN processes

FETCH NEXT FROM processes INTO @Process_UID

WHILE @@FETCH_STATUS=0

BEGIN

SELECT * FROM DQS_MAIN.dbo.A_PROCESS WHERE UNIQUE_ID=@Process_UID

SELECT * from DQS_MAIN.dbo.V_A_FLOW where UNIQUE_ID=@Process_UID ORDER BY FLOW_ID

FETCH NEXT FROM processes INTO @Process_UID

END

CLOSE processes

DEALLOCATE processes

--B. Check DMV output for any obvious slow DQS queries (results only show for things run since SQL was last restarted.

SELECT

LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35) AS cacheobjtype,

p.usecounts, p.size_in_bytes / 1024 AS size_in_kb,

PlanStats.total_worker_time/1000 AS tot_cpu_ms, PlanStats.total_elapsed_time/1000 AS tot_duration_ms,

PlanStats.total_physical_reads, PlanStats.total_logical_writes, PlanStats.total_logical_reads,

PlanStats.CpuRank, PlanStats.PhysicalReadsRank, PlanStats.DurationRank,

LEFT (CASE

WHEN pa.value=32767 THEN 'ResourceDb'

ELSE ISNULL (DB_NAME (CONVERT (sysname, pa.value)), CONVERT (sysname,pa.value))

END, 40) AS dbname,

sql.objectid,

CONVERT (nvarchar(50), CASE

WHEN sql.objectid IS NULL THEN NULL

ELSE REPLACE (REPLACE (sql.[text],CHAR(13), ' '), CHAR(10), ' ')

END) AS procname,

REPLACE (REPLACE (SUBSTRING (sql.[text], PlanStats.statement_start_offset/2 + 1,

CASE WHEN PlanStats.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), sql.[text]))

ELSE PlanStats.statement_end_offset/2 - PlanStts.statement_start_offset/2 + 1

END), CHAR(13), ' '), CHAR(10), ' ') AS stmt_text

FROM

(

SELECT

stat.plan_handle, statement_start_offset, statement_end_offset,

stat.total_worker_time, stat.total_elapsed_time, stat.total_physical_reads,

stat.total_logical_writes, stat.total_logical_reads,

ROW_NUMBER() OVER (ORDER BY stat.total_worker_time DESC) AS CpuRank,

ROW_NUMBER() OVER (ORDER BY stat.total_physical_reads DESC) AS PhysicalReadsRank,

ROW_NUMBER() OVER (ORDER BY stat.total_elapsed_time DESC) AS DurationRank

FROM sys.dm_exec_query_stats stat

) AS PlanStats

INNER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = PlanStats.plan_handle

OUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) pa

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sql

WHERE (PlanStats.CpuRank < 50 OR PlanStats.PhysicalReadsRank < 50 OR PlanStats.DurationRank < 50)

AND pa.attribute = 'dbid'

-- filter the result to only DB's for DQS

AND pa.value IN (SELECT database_id FROM Master.SYS.databases where name like 'DQS%')

ORDER BY tot_cpu_ms DESC

dqs queries.sql

Displaying dqs queries.sql.