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_IDLEFT OUTER JOIN DQS_MAIN.dbo.A_KNOWLEDGEBASE KB WITH (NOLOCK)ON KB.ID=F.KNOWLEDGEBASE_IDORDER BY DurationMilliSec DESC;--Pivot by Process (an activity like running the SSIS Project) DECLARE @Process_UID varchar(255)DECLARE processes CURSORFOR SELECT UNIQUE_ID FROM DQS_MAIN.dbo.A_PROCESS ORDER BY CREATION_TIMEOPEN processesFETCH NEXT FROM processes INTO @Process_UIDWHILE @@FETCH_STATUS=0BEGINSELECT * FROM DQS_MAIN.dbo.A_PROCESS WHERE UNIQUE_ID=@Process_UIDSELECT * from DQS_MAIN.dbo.V_A_FLOW where UNIQUE_ID=@Process_UID ORDER BY FLOW_IDFETCH NEXT FROM processes INTO @Process_UIDENDCLOSE processesDEALLOCATE processes--B. Check DMV output for any obvious slow DQS queries (results only show for things run since SQL was last restarted. SELECTLEFT (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 (CASEWHEN 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), CASEWHEN sql.objectid IS NULL THEN NULLELSE 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 + 1END), CHAR(13), ' '), CHAR(10), ' ') AS stmt_textFROM(SELECTstat.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 DurationRankFROM sys.dm_exec_query_stats stat) AS PlanStatsINNER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = PlanStats.plan_handleOUTER APPLY sys.dm_exec_plan_attributes (p.plan_handle) paOUTER APPLY sys.dm_exec_sql_text (p.plan_handle) AS sqlWHERE (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