Azure SQL Data Warehouse "Impacted RowCount"

Question

In SQL Azure DB when update statement is executed,we can get the impacted row count by @@ROWCOUNT, similarly in Azure SQL Data Warehouse I am unable to get the impacted Row Count. Is there a way to fetch the impacted row count in Azure SQL Data Warehouse.

Answer 1

You can find common workarounds for SQL DW https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-migrate-code/

The workaround for @@rowcount is:

SELECT  SUM(row_count) AS row_count
FROM    sys.dm_pdw_sql_requests
WHERE   row_count <> -1
AND     request_id IN
                    (   SELECT TOP 1    request_id
                        FROM            sys.dm_pdw_exec_requests
                        WHERE           session_id = SESSION_ID()
                        ORDER BY end_time DESC
                    )
;

Answer 2

In addition to the correct answer above. In case you use a dynamic SQL then you should use "TOP 4" to get to the row count. Otherwise the @@rowcount workaround would return nothing. Correction: changing the subsql to this corrects the dynamic sql rowcount issue: SELECT TOP 1 request_id FROM sys.dm_pdw_exec_requests WHERE session_id = SESSION_ID() ORDER BY end_time DESC, start_time DESC

Answer 3

I have noticed the above work-arounds can provide erroneous results as they can be impacted by other request steps i.e. when performing an UPDATE one of my tables had related materialised views so INSERT actions were also performed as additional request/dm steps which inflate the row counts.

For SELECTS I also use the modified code below which is normally embedded into a rowcount stored proc with a @QueryLabel parameter. Generally I always use query labels and in the case of queries where I require a rowcount I add in the @@SPID to the label text. This is also because I can't rely on the session id used in the above example due to heavy use of dynamic queries/exec which spawns different session id's.

    -- Use a simple methods for SELECT statements first
    SET @RowCount =
    (
        SELECT
        TOP 1 row_count
        FROM sys.dm_pdw_request_steps
        WHERE row_count >= 0
        AND request_id IN 
            (
                SELECT TOP 1 request_id
                FROM sys.dm_pdw_exec_requests
                WHERE CHARINDEX(@QueryLabel, [label]) > 0
                AND operation_type = 'ReturnOperation'
                ORDER BY end_time DESC -- ok if request has ended
            )
    )

For UPDATE/INSERT/DELETE I had to look at separate code to see if there are additional predicates to correlate to the specific request/dm actions for the DML I am interested in. But on profiling the various system views when performing UPDATE/INSERT/DELETE the solution seemed to be the same as for SELECT but with just the use of a different operation_type.

So the code which seems to work OK (more testing required perhaps) for UPDATE/INSERT/DELETE is ...

        -- Query which works for UPDATE/INSERT/DELETE
        SET @RowCount =
        (
            SELECT
            TOP 1 row_count
            FROM sys.dm_pdw_request_steps
            WHERE row_count >= 0
            AND request_id IN 
                (
                    SELECT TOP 1 request_id
                    FROM sys.dm_pdw_exec_requests
                    WHERE CHARINDEX(@QueryLabel, [label]) > 0
                    AND operation_type = 'OnOperation'
                    ORDER BY end_time DESC
                )
        )