Cool Tips‎ > ‎Data Validation‎ > ‎

Data Quality Services Management

Applicability:

                   SQL Server 2012:        Not Tested  
                   SQL Server 2014:        Not Tested  
                   SQL Server 2016:        Not Tested  
                   SQL Server 2017:        Not Tested  
      

Credits:

Author:    Unknown
Date:       10 Jun 2020

Description

 Some general queries for DQS management.  Not able to test as yet as i don't have a DQS setup on my VMs

Code

--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



ċ
Andy Hughes,
Jun 10, 2020, 6:37 AM
Comments