{"id":3838,"date":"2014-06-13T09:52:00","date_gmt":"2014-06-13T07:52:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/"},"modified":"2014-06-13T09:52:00","modified_gmt":"2014-06-13T07:52:00","slug":"sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/","title":{"rendered":"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature"},"content":{"rendered":"

\"\"<\/p>\n

Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles<\/strong>. This feature will help the database administrators and experienced developers to troubleshoot long running queries in real-time. I’m pretty sure you had to deal with the following questions: When will this query finish? What percentage of total workload is performed by this request? Which steps are the longest? Before SQL Server 2014 it was impossible to answer the questions above!<\/p>\n

But first, I have to admit that this new DMV has raised of lot of questions during my tests. I will try to share my thoughts and findings with you in this post.<\/p>\n

My test consisted of running this long query:<\/p>\n

select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YEAR(TransactionDate) AS year_tran, <\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MONTH(TransactionDate) AS month_tran,<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FIRST_VALUE(p.ProductNumber) OVER (PARTITION BY YEAR(TransactionDate), MONTH(TransactionDate) ORDER BY TransactionDate) AS first_product_nb,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LAST_VALUE(p.ProductNumber) OVER (PARTITION BY YEAR(TransactionDate), MONTH(TransactionDate) ORDER BY TransactionDate) AS last_product_nb<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
from AdventureWorks2012.dbo.bigTransactionHistory as a<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join AdventureWorks2012.dbo.bigProduct as p<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on a.ProductID = p.ProductID<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n

On my computer this query takes 05\u2019\u201947\u2019 to run. The parallelism is enabled. My SQL Server instance can use up to 8 processors. The first time I was disappointed by viewing the output of the sys.dm_exec_query_profiles DMV during my query execution:<\/p>\n

select *<\/span><\/span><\/span><\/div>\n
from sys.dm_exec_query_profiles<\/span><\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_12_-_1_-_output_sys_dm_exec_query_profiles\"<\/p>\n

\u2026 No output! Ok what are we supposed to do to see something with sys.dm_exec_query_profiles ? \ud83d\ude00\u00a0 Keep good habits and go back to the SQL Server BOL<\/a> that says:<\/p>\n

To serialize the requests of sys.dm_exec_query_profiles and return the final results to SHOWPLAN XML, use SET STATISTICS PROFILE ON; or SET STATISTICS XML ON;<\/strong><\/p>\n

In other words, to have a chance to see something with this DMV we must use some session options like STATISTICS PROFILE, STATISTICS XML<\/strong> or force SQL Server to display the execution plan after execution query with SQL Server Management Studio. My first though was: why do we have to use some additional options to produce some output for this new DMV? These constraints severely limit the scenarios where we can use this DMV \u2026 After some reflexions and discussions with others French MVPs we can think that is normal because tracking the execution plan information is expensive and we could potentially bring a production server to its knees by enabling this feature for all the workload statements. But using additional session options can be impractical in production environments because it requires to execute itself the concerned query and it is not always possible. Fortunately Microsoft provides the query_post_execution_showplan event<\/strong> which can be used into a SQL Server trace or an extended event session. But the implementation design of this event has a significant impact to the performance of a SQL Server instance. Indeed, even with a short-circuit predicate this event will be triggered each time a SQL statement will be executed because the query duration is not known ahead of time (please refer to this Microsoft connect item<\/a>). To summarize, using this event in OLTP production environment should be used in a short period for troubleshooting purposes. In OLAP environment the story is not the same because we don\u2019t have to deal with a lot of short queries but only with long running queries issued by cube processing or ETL processes for example.<\/p>\n

After discussing the pros and cons of this new feature let\u2019s start with my precedent T-SQL statement and the use of the session option: SET STATISTICS PROFILE ON<\/p>\n

SET STATISTICS PROFILE ON;<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 YEAR(TransactionDate) AS year_tran, <\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MONTH(TransactionDate) AS month_tran,<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FIRST_VALUE(p.ProductNumber) OVER (PARTITION BY YEAR(TransactionDate), MONTH(TransactionDate) ORDER BY TransactionDate) AS first_product_nb,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LAST_VALUE(p.ProductNumber) OVER (PARTITION BY YEAR(TransactionDate), MONTH(TransactionDate) ORDER BY TransactionDate) AS last_product_nb<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
from AdventureWorks2012.dbo.bigTransactionHistory as a<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join AdventureWorks2012.dbo.bigProduct as p<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on a.ProductID = p.ProductID<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n

In other session:<\/p>\n

select *<\/span><\/span><\/span><\/div>\n
from sys.dm_exec_query_profiles<\/span><\/span><\/span><\/span><\/div>\n

 <\/p>\n

\"blog_12_-_2_-_output_sys_dm_exec_query_profiles_2\"<\/p>\n

This DMV provides a lot of useful information. These information are more granular than SET STATISTICS IO because\u00a0 the counters returned are per operator per thread (node_id \/ physical_operator_name and thread_id columns).\u00a0 In my case the SQL Server instance can use up to 8 processors and we can notice that for some operators (node_id) we have several threads using in parallel (thread_id). Furthermore two others columns are interesting like estimate_row_count and row_count. The former is the number of estimated rows an operator should address and the latter is the current number of rows addressed by the same operator (remember these counters are per operator per thread \u2026). We can compare these two columns to estimate a completion percentage per operator (or per operator per thread). Finally we can categorize some information provided by this DMV in two parts: information related either to the query execution engine (row_count, estimated_row_count) or the storage execution engine (logical_read_count, write_page_count, lob_logical_read_count etc.)<\/p>\n

As you can see, this DMV provide a lot of information to us and you will certainly customize the final output yourself depending on what you want to focus on.<\/p>\n

Here is an example of a custom script we can create that uses the sys.dm_exec_query_profiles, sys.objects, sys.indexes, sys.dm_exec_requets, sys.dm_exec_sessions, and sys.dm_os_waiting_tasks to cover both the real-time execution information and the related waits occurring in the same time.<\/p>\n

USE AdventureWorks2012;<\/span><\/span><\/span><\/div>\n
GO<\/span><\/div>\n
\u00a0<\/span><\/div>\n
SELECT<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 qp.node_id,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 qp.session_id,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 o.name AS [object_name],<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i.name AS index_name,<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 qp.physical_operator_name + QUOTENAME(CAST(COUNT(qp.thread_id) AS VARCHAR(4))) AS physical_operator_name, <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.estimate_row_count) AS estimate_row_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.row_count) AS row_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE <\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN SUM(qp.row_count) * 1. \/ SUM(qp.estimate_row_count + .00001) THEN CAST(CAST(SUM(qp.row_count) * 100. \/ SUM(qp.estimate_row_count + .00001) AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %’<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE ‘100 % + (‘ + CAST(CAST((SUM(qp.row_count) * 100. \/ SUM(qp.estimate_row_count + .00001)) – 100 AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %)’<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END AS [completed_%],<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Query execution engine<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(CAST(SUM(qp.elapsed_time_ms) * 100. \/(SUM(SUM(qp.elapsed_time_ms)) OVER() + .00001) AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %’ AS [total_elapsed_time_%],<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(CAST(SUM(qp.cpu_time_ms) * 100. \/(SUM(SUM(qp.cpu_time_ms)) OVER() + .00001) AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %’ AS [total_cpu_%],<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Storage engine<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(CAST(SUM(qp.logical_read_count) * 100. \/ SUM(SUM(qp.logical_read_count)) OVER() + .00001 AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %’ AS [total_logical_read_%],<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(CAST(SUM(qp.physical_read_count) * 100. \/(SUM(SUM(qp.physical_read_count)) OVER() + .00001) AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %’ AS [total_physical_read_%],<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(CAST(SUM(qp.lob_logical_read_count) * 100. \/(SUM(SUM(qp.lob_logical_read_count)) OVER() + .00001) AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %’ AS [lob_logical_read_%],<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(CAST(SUM(qp.lob_physical_read_count) * 100. \/(SUM(SUM(qp.lob_physical_read_count)) OVER() + .00001) AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %’ AS [lob_physical_read_%],<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CAST(CAST(SUM(qp.write_page_count) * 100. \/(SUM(SUM(qp.write_page_count)) OVER() + .00001) AS DECIMAL(5,2)) AS VARCHAR(6)) + ‘ %’ AS [total_write_%],<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.logical_read_count) AS total_logical_read_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.lob_logical_read_count) AS total_lob_logical_read_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.lob_physical_read_count) AS total_lob_physical_read_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.lob_read_ahead_count) AS total_lob_read_ahead_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.physical_read_count) AS total_physical_read_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.read_ahead_count) AS total_read_ahead_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.write_page_count) AS total_write_page_count,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — Both<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.cpu_time_ms) AS total_cpu_time_ms,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(qp.elapsed_time_ms) AS total_elapsed_time_ms,<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 — wait info information<\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COALESCE(wait.wait_info, ”) AS wait_info<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
FROM sys.dm_exec_query_profiles AS qp(NOLOCK)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT JOIN sys.objects AS o(NOLOCK)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON qp.object_id = o.object_id<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT JOIN sys.indexes AS i(NOLOCK)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON qp.index_id = i.index_id<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND qp.object_id = i.object_id<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
OUTER APPLY<\/span><\/span><\/span><\/div>\n
(<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT <\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STUFF(<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (SELECT<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ‘,’ + ws.wait_type + ‘ ‘ + QUOTENAME(CAST(SUM(COALESCE(ws.wait_duration_ms, 0)) AS VARCHAR(20)) + ‘ ms’)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM sys.dm_exec_requests AS r(NOLOCK)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.dm_exec_sessions AS s(NOLOCK)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON r.session_id = s.session_id<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 JOIN sys.dm_os_waiting_tasks AS ws(NOLOCK)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON ws.session_id = s.session_id<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE s.session_id = qp.session_id<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 GROUP BY ws.wait_type<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH (”), TYPE).value(‘.’, ‘varchar(max)’) , 1, 1, ”<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/span><\/span><\/div>\n
) AS wait(wait_info)<\/span><\/span><\/span><\/span><\/span><\/div>\n
GROUP BY qp.node_id, session_id, qp.physical_operator_name, o.name, i.name, COALESCE(wait.wait_info, ”)<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
ORDER BY node_id<\/span><\/span><\/span><\/div>\n
OPTION(RECOMPILE);<\/span><\/span><\/span><\/span><\/div>\n

The first section of the above script concerns the general information:<\/p>\n

\"blog_12_-_2_-_output_sys_dm_exec_query_profiles_3\"<\/p>\n

    \n
  1. This above script groups the information per operator. The information in brackets is the number of thread used for the concerned operator.<\/li>\n
  2. Completion is provided as a ratio between rows addressed by the corresponding operator so far and the total estimated rows. As we can expect, the estimated rows and the real rows addressed by an operator can differ. The above script highlights this gap by showing the difference in percentage between brackets if row_count is greater than estimated_row_count. This information can be useful to \u201cestimate\u201d the general completion of the query.<\/li>\n<\/ol>\n

    However Total_elasped_time_% and total_cpu_% columns are the relative percentage of the elapsed time and cpu time consumed by all the query execution operators.<\/p>\n

    3. \u00a0For some physical operators like Clustered Index Scan, Table Scan etc \u2026 we will show the related table and index objects<\/p>\n

    The second section concerns the storage engine statistic for the query execution:<\/p>\n

    \"blog_12_-_3_-_output_sys_dm_exec_query_profiles_4\"<\/p>\n

    The above information can be useful to highlight the top operators depending on the resource or the operation type. For example, in my case I can notice that the cluster index scan of the bigTransactionHistory table is the main consuming physical operator for logical reads. Furthermore, we can observe that the sort operator is the root cause of spilling. Finally as expected we can notice that by design hash match operator consumes a lot of CPU and it\u2019s the top operator in this case.<\/p>\n

    The next section shows the absolute values per operator per type of resources and operation:<\/p>\n

    \"blog_12_-_4_-_output_sys_dm_exec_query_profiles_5\"<\/p>\n

    Finally the last section shows the wait types and aggregated duration by all related threads in brackets during the query execution in a real time:<\/p>\n

    \"blog_12_-_5_-_output_sys_dm_exec_query_profiles_6\"<\/p>\n

    In my case we retrieve the wait type related to a query execution in parallel\u00a0 (CXPACKET) and certainly one of the root cause of the CXPACKET wait type (IO_COMPLETION) but here we don’t have sufficient information to verify it. We can just notice that the query execution generates CXPACKET and IO_COMPLETION wait types in a real time.<\/p>\n

    At this point we can ask why we don\u2019t have a remaining time information. In fact, computing an accurate remaining time is very difficult because a lot of factors must be correlated together. We can have a naive approach by computing the remaining time with the row_count, estimated_row_count and elapsed time columns for each operator, but you will be disappointed when testing this\u2026 According to my own tests, I can claim that the result is never as accurate as you would expect. Please feel free to share your results or thoughts about computing an accurate remaining time with us!<\/p>\n

    The sys.dm_exec_query_profiles DMV can be correlated by external tools like XPERF or Perfmon. I will prepare a next blog post (as soon as I have some time) to share my experience with you.<\/p>\n

    Happy query profiling!<\/p>\n

    By David Barbarin<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

    Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles. This feature will help the database administrators and experienced developers to troubleshoot long running queries in real-time. I’m pretty sure you had to deal with the following questions: When will this query finish? What percentage of […]<\/p>\n","protected":false},"author":26,"featured_media":3590,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[348,49,51,52,44],"type_dbi":[],"class_list":["post-3838","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-application-integration-middleware","tag-execution-plan","tag-microsoft","tag-sql-server","tag-sql-server-2014","tag-troubleshooting"],"acf":[],"yoast_head":"\nSQL Server 2014: sys.dm_exec_query_profiles, a new promising feature - dbi Blog<\/title>\n<meta name=\"description\" content=\"Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature\" \/>\n<meta property=\"og:description\" content=\"Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-06-13T07:52:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"245\" \/>\n\t<meta property=\"og:image:height\" content=\"149\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature\",\"datePublished\":\"2014-06-13T07:52:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/\"},\"wordCount\":1966,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\",\"keywords\":[\"Execution plan\",\"Microsoft\",\"SQL Server\",\"SQL Server 2014\",\"Troubleshooting\"],\"articleSection\":[\"Application integration & Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/\",\"name\":\"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\",\"datePublished\":\"2014-06-13T07:52:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"description\":\"Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\",\"width\":245,\"height\":149},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature - dbi Blog","description":"Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature","og_description":"Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles.","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/","og_site_name":"dbi Blog","article_published_time":"2014-06-13T07:52:00+00:00","og_image":[{"width":245,"height":149,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature","datePublished":"2014-06-13T07:52:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/"},"wordCount":1966,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","keywords":["Execution plan","Microsoft","SQL Server","SQL Server 2014","Troubleshooting"],"articleSection":["Application integration & Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/","name":"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","datePublished":"2014-06-13T07:52:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"description":"Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg","width":245,"height":149},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2014-sysdmexecqueryprofiles-a-new-promising-feature\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3838","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3838"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3838\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/3590"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3838"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3838"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3838"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3838"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}