SQL Server 中警告消息的来源

Question

运行存储过程时，我收到如下警告：

Warning: Null value is eliminated by an aggregate or other SET operation.

我知道警告的含义，但是是什么存储过程和行号导致了该警告？

问题是该存储过程非常庞大，并且调用了十几个其他过程。因此，当 SQL Server 没有提供警告产生的行号和过程名称时，定位问题就变得非常困难。这是使用 SQL Server 2008。

Answer 1

您在包含空值的列上有 ansi 警告和聚合（总和、最大值、最小值...）。

您可以将 ansi_warnings 设置为关闭，但最好删除空值，

例如
总和（合并（col，0））

Answer 2

您可以为此使用扩展事件。

1) 创建并启动会话

/*Create Extended Events Session*/

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='WarningLogger')
    DROP EVENT SESSION [WarningLogger] ON SERVER;
CREATE EVENT SESSION [WarningLogger]
ON SERVER
ADD EVENT sqlserver.error_reported(
     ACTION (sqlserver.plan_handle, sqlserver.sql_text, sqlserver.tsql_stack)
     WHERE (([severity]=(10))))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)

/*Start the Session*/
ALTER EVENT SESSION [WarningLogger] ON SERVER STATE = START 

2) 测试

CREATE PROC #baz 
AS 
declare @g int
select sum(@g)
waitfor delay '00:00:02'

Go
CREATE PROC #bar AS EXEC #baz

GO
CREATE PROC #foo AS EXEC #bar

GO
EXEC #foo

获取结果

DECLARE 
    @session_name VARCHAR(200) = 'WarningLogger';


with pivoted_data AS(
    SELECT 
        MIN(event_name) as event_name, 
        MIN(event_timestamp) as event_timestamp, 
        unique_event_id, 
        CONVERT 
        ( 
            INT, 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'error' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.error],
        CONVERT 
        ( 
            VARCHAR(MAX), 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'message' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.message],
        CONVERT 
        ( 
            VARCHAR(MAX), 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'plan_handle' and 
                        d_package IS NOT NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.plan_handle],
        CONVERT 
        ( 
            INT, 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'severity' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.severity],
        CONVERT 
        ( 
            VARCHAR(MAX), 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'sql_text' and 
                        d_package IS NOT NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.sql_text],
        CONVERT 
        ( 
            INT, 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'state' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.state],
        CONVERT 
        ( 
            XML, 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'tsql_stack' and 
                        d_package IS NOT NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.tsql_stack],
        CONVERT 
        ( 
            VARCHAR(MAX), 
            MIN 
            ( 
                CASE 
                    WHEN 
                        event_name = 'error_reported' and 
                        d_name = 'user_defined' and 
                        d_package IS NULL 
                            THEN d_value
                END 
            ) 
        ) AS [error_reported.user_defined]
    FROM 
    ( 
        SELECT 
            *, 
            CONVERT(VARCHAR(400), NULL) AS attach_activity_id 
        FROM 
        ( 
            SELECT 
                event.value('(@name)[1]', 'VARCHAR(400)') as event_name, 
                event.value('(@timestamp)[1]', 'DATETIME') as event_timestamp, 
                DENSE_RANK() OVER (ORDER BY event) AS unique_event_id, 
                n.value('(@name)[1]', 'VARCHAR(400)') AS d_name, 
                n.value('(@package)[1]', 'VARCHAR(400)') AS d_package, 
                n.value('((value)[1]/text())[1]', 'VARCHAR(MAX)') AS d_value, 
                n.value('((text)[1]/text())[1]', 'VARCHAR(MAX)') AS d_text 
            FROM 
            ( 
                SELECT 
                    ( 
                        SELECT 
                            CONVERT(xml, target_data) 
                        FROM sys.dm_xe_session_targets st 
                        JOIN sys.dm_xe_sessions s ON 
                            s.address = st.event_session_address 
                        WHERE 
                            s.name = @session_name 
                            AND st.target_name = 'ring_buffer' 
                    ) AS [x] 
                FOR XML PATH(''), TYPE 
            ) AS the_xml(x) 
            CROSS APPLY x.nodes('//event') e (event) 
            CROSS APPLY event.nodes('*') AS q (n) 
        ) AS data_data 
    ) AS activity_data 
    GROUP BY 
        unique_event_id 
),
        StackData AS
        ( SELECT 
            pivoted_data.*,
            frame_xml.value('(./@level)', 'int')      AS [frame_level],
            frame_xml.value('(./@handle)', 'varchar(MAX)') AS [sql_handle],
            frame_xml.value('(./@offsetStart)', 'int')     AS [offset_start],
            frame_xml.value('(./@offsetEnd)', 'int')       AS [offset_end]
        FROM pivoted_data CROSS APPLY [error_reported.tsql_stack].nodes('//frame') N (frame_xml)
        )

   SELECT unique_event_id, [frame_level], sd.[error_reported.message],event_timestamp,sd.[error_reported.sql_text],
        object_name(st.objectid, st.dbid) AS ObjectName,
        SUBSTRING(st.text, (sd.offset_start/2)+1, ((
                CASE sd.offset_end
                    WHEN -1
                    THEN DATALENGTH(st.text)
                    ELSE sd.offset_end
                END - sd.offset_start)/2) + 1) AS statement_text,
        qp.query_plan,
        qs2.creation_time,
        qs2.last_execution_time,
        qs2.execution_count
    FROM StackData AS sd CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(MAX),sd.sql_handle,1)) AS st
        LEFT OUTER JOIN sys.dm_exec_query_stats qs2
        ON  qs2.sql_handle = CONVERT(VARBINARY(MAX),sd.sql_handle,1) OUTER APPLY sys.dm_exec_query_plan(CONVERT(VARBINARY(MAX),qs2.plan_handle,1)) AS qp
        WHERE st.text NOT LIKE '%this_query%'
        ORDER BY unique_event_id ASC, [frame_level] DESC

Answer 3

在跟踪文件中捕获正在运行的主过程。