HoneyHoney: January 2016

SQL SERVER 捕获最里层的error message

Posted on Wednesday, January 27, 2016 by 醉·醉·鱼 and labeled under sql

问题

有时候，SQL SERVER 会抛两个error message出来，比如

这个时候，如果你用ERROR_MESSAGE()，你只会拿到最后一条error message。那如何拿到所有的error message呢？

解决

Paulo Santos 创建了一个SP来实现这件事。http://pjondevelopment.50webs.com/articles/retrieveErrorMessage.htm 。我拿来以后稍稍改了一下里面的bug，现在就可以捕获所有的error message了。


IF NOT EXISTS(SELECT
                *
              FROM sys.procedures
              WHERE name = 'usp_GET_LastErrorMessage')
    EXEC ('
    CREATE PROCEDURE dbo.usp_GET_LastErrorMessage
    AS 
        DECLARE @error_message NVARCHAR(2000)
        SET @error_message = ''Stored procedure ''+OBJECT_NAME(@@PROCID)+'' not yet implemented'' 
        RAISERROR(@error_message, 16, 1)
    ')
GO

ALTER PROCEDURE dbo.usp_GET_LastErrorMessage
    @output_result BIT = 1
AS
BEGIN
  DECLARE 
    @dbccrow CHAR(77),
    @byte INT,
    @pos INT, -- position in buffer line
    @numMsg INT, -- number of error messages
    @gather INT, -- error message piece
    @count INT, -- byte position within a collection of bytes
    @messageLength INT,
    @stringLength INT,
    @nchar INT,
    @errNumber BIGINT,
    @errState INT,
    @errLevel INT,
    @errMessage NVARCHAR(1000),
    @errInstance NVARCHAR(256),
    @errProcedure NVARCHAR(256),
    @errLine INT,
    @bytesRead INT;

/*
Buffer sample
--------------------------------------------------
00000000   04 01 00 c8 00 37 01 00 aa 30 00 50 c3 00 00 01   ...È.7..ª0.PÃ...
00000010   10 0c 00 73 00 69 00 6d 00 70 00 6c 00 65 00 20   ...s.i.m.p.l.e. 
00000020   00 65 00 72 00 72 00 6f 00 72 00 0b 46 00 4e 00   .e.r.r.o.r..F.N.
00000030   44 00 44 00 45 00 56 00 4f 00 52 00 41 00 30 00   D.D.E.V.O.R.A.0.
00000040   31 00 00 02 00 00 00 fd 03 00 f6 00 00 00 00 00   1......ý..ö.....

Buffer description
--------------------------------------------------
0 - We need to scan the buffer for the byte marker 0xAA that starts an error message.
1 - Size of the entire error message (2 bytes)
2 - Error Number (4 bytes)
3 - Error State (1 byte)
4 - Error Level (1 byte)
5 - Size of the text, in character, of the error message (2 bytes)
6 - Variable length of bytes with the Unicode error message
7 - Size of the name of SQL Server instance (1 byte)
8 - Variable length of bytes with the Unicode name of the SQL Server instance
9 - Size of the name of the stored procedure where the error occurred (1 byte)
10 - Variable length of bytes with the Unicode name stored procedure
11 - Line where the error occurred (2 bytes)


Buffer sample as it will be parsed
--------------------------------------------------
___________________________________0#_1####_2##########_3#
00000000   04 01 00 c8 00 37 01 00 aa 3c 00 50 c3 00 00 01   ...È.7..ª0.PÃ...
___________4#_5####_6#####################################
00000010   10 0c 00 73 00 69 00 6d 00 70 00 6c 00 65 00 20   ...s.i.m.p.l.e. 
___________################################_7#_8##########
00000020   00 65 00 72 00 72 00 6f 00 72 00 0b 46 00 4e 00   .e.r.r.o.r..F.N.
___________###############################################
00000030   44 00 44 00 45 00 56 00 4f 00 52 00 41 00 30 00   D.D.E.V.O.R.A.0.
___________##_9#_10_11###_________________________________
00000040   31 00 00 02 00 00 00 fd 03 00 f6 00 00 00 00 00   1......ý..ö.....


1 - 0X003c = 60
2 - 0X0000C350 = 50000
3 - 0X01 = 1
4 - 0X10 = 16
5 - 0X000C = 12
6 - "simple error"
7 - 0X0B = 11
8 - "LOCALHOST"
9 - 0X0
10 - 0X0
11 - 0X0002 = 2


The problem with this approach is that if the buffer contains any user data it might have the marker byte and thus provoking a false response.
This has been mitigated by comparing the message bytes and the bytes read for the message.

*/

  -- Catch the output buffer.
  CREATE TABLE #OutputBuffer(contents CHAR(77));
  INSERT INTO #OutputBuffer EXEC('DBCC OUTPUTBUFFER(@@spid) WITH NO_INFOMSGS');

  IF OBJECT_ID('TEMPDB..#errors') IS NULL
      CREATE TABLE #errors(
        id INT,
        errNumber BIGINT,
        errState INT,
        errLevel INT,
        errMessage NVARCHAR(2000),
        errInstance NVARCHAR(256),
        errProcedure NVARCHAR(256),
        errLine INT
      );

  -- Step through the buffer lines.
  DECLARE error_cursor CURSOR STATIC FORWARD_ONLY FOR SELECT contents FROM #OutputBuffer ORDER BY contents;

  -- Init variable, and open cursor.
  OPEN error_cursor;
  FETCH NEXT FROM error_cursor INTO @dbccrow;

    -- Count the number of error messages
    SET @numMsg = 0
    SET @pos    = 12
    SET @gather = 0

  -- Now assemble rest of string.
  WHILE @@FETCH_STATUS = 0
  BEGIN
Start:
    IF @pos > 57
    BEGIN
      SET @pos = 12;
      GOTO NextRow;
    END;

    -- Get a byte from the stream
    SET @byte = CAST(CONVERT(VARBINARY(2), SUBSTRING(@dbccrow, @pos, 2), 2) AS INT)

    -- move to the next byte
    SET @pos = @pos + 3

    -- Searching for the 0xAA marker
    IF @gather = 0
    BEGIN
      IF @byte != 170 GOTO Start;
      SELECT @gather = 1, @bytesRead = 0, @count = 0, @messageLength = 0;
      GOTO Start;
    END;

    SET @bytesRead += 1; -- record number of bytes read for message
--    IF @bytesRead > @messageLength AND @gather > 1 GOTO NextRow; -- bail out if we have read beyond the message length

    -- Get the message length
    IF @gather = 1
    BEGIN
      IF @count = 0 SET @messageLength = 0;
      SET @messageLength += (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 2 SELECT @count = 0, @gather = 2;
      GOTO Start;
    END;

    -- Get the error message
    IF @gather = 2
    BEGIN
      IF @count = 0 SET @errNumber = 0;
      SET @errNumber += (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 4 SELECT @count = 0, @gather = 3;
      GOTO Start
    END;

    -- Get the Error State
    IF @gather = 3
    BEGIN
      SELECT @gather = 4, @errState = @byte;
      GOTO Start
    END;

    -- Get the Error Level
    IF @gather = 4
    BEGIN
      SELECT @gather = 5, @errLevel = @byte;
      GOTO Start;
    END;

    -- Get the error message length
    IF @gather = 5
    BEGIN
      SET @stringLength = ISNULL(@stringLength, 0) + (@byte * POWER(256, @count));
      SET @count = @count + 1;
      IF @count = 2 SELECT @nchar = 0, @count = 0, @gather = 6;
      GOTO Start;
    END;

    -- Get the error message
    IF @gather = 6
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1;
        IF @count = 2 SELECT @count = 0, @errMessage = ISNULL(@errMessage, '') + nchar(@nchar);
        IF LEN(@errMessage) = @stringLength SET @gather = 7;
        GOTO Start;
      END;
      ELSE SET @gather = 7;
    END;

    -- Get the instance name size
    IF @gather = 7
    BEGIN
      SELECT @gather = 8, @stringLength = @byte;
      GOTO Start;
    END;

    -- Get the instance name
    IF @gather = 8
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1;
        IF @count = 2 SELECT @count = 0, @errInstance = ISNULL(@errInstance, '') + nchar(@nchar);
        IF LEN(@errInstance) = @stringLength SET @gather = 9;
        GOTO Start;
      END;
      ELSE SET @gather = 9;
    END;

    -- Get the procedure name size
    IF @gather = 9
    BEGIN
      SELECT @gather  = 10, @stringLength = @byte, @nchar = 0;
      GOTO Start;
    END;

    -- Get the procedure name
    IF @gather = 10
    BEGIN
      IF @stringLength > 0
      BEGIN
        IF @count = 0 SET @nchar = 0;
        SET @nchar += (@byte * POWER(256, @count));
        SET @count = @count + 1
        IF @count = 2 SELECT @count = 0, @errProcedure = ISNULL(@errProcedure, '') + nchar(@nchar);
        IF LEN(@errProcedure) = @stringLength SET @gather = 11;
        GOTO Start
      END;
      ELSE SET @gather = 11;
    END;

    -- Get the error line
    IF @gather = 11
    BEGIN
      SET @errLine = ISNULL(@errLine, 0) + (@byte * POWER(256, @count));
      SET @count = @count + 1;

      IF @count = 2
      BEGIN
        SET @numMsg += 1;
        -- validate the message and insert
        IF @bytesRead = @messageLength INSERT #errors VALUES(@numMsg, @errNumber, @errState, @errLevel, @errMessage, @errInstance, @errProcedure, @errLine);
        -- reset variables
        SELECT @errMessage = NULL, @errInstance = NULL, @errProcedure = NULL, @errLine = NULL;
        SET @gather = 0
        SET @count = 0
        SET @nchar = 0;
      END;

      GOTO Start;
    END;

NextRow:
    FETCH NEXT FROM error_cursor INTO @dbccrow;
  END;

  CLOSE error_cursor;
  DEALLOCATE error_cursor;
  IF @output_result = 1
    SELECT * FROM #errors ORDER BY id;
END;
GO

IF OBJECT_ID('dbo.usp_GET_LastErrorMessage') IS NOT NULL
    PRINT '*** CREATED PROCEDURE dbo.usp_GET_LastErrorMessage ***'
ELSE
    PRINT '*** FAILED CREATING PROCEDURE dbo.usp_GET_LastErrorMessage ***'
GO

部署上面的SP以后，


IF OBJECT_ID('TEMPDB..#foo') IS NOT NULL
    DROP TABLE #foo

CREATE TABLE #foo
(
c INT DEFAULT(0)
)

ALTER TABLE #foo ALTER COLUMN c VARCHAR(10)

GO
EXEC usp_GET_LastErrorMessage;

你就可以看到所有的error message了。

看上去挺不错了吧。

延伸

但是，如果你想通过TRY/CATCH来根据里面的error number进行不同的异常处理，那你还是洗洗睡吧！最主要的问题在于：直到你THROW了整个error，上面的SP才会捕获到异常信息。但是一旦已经抛异常了，TSQL就不会再继续往下执行了，你也就更加没法进行异常捕获操作了。


IF OBJECT_ID('TEMPDB..#foo') IS NOT NULL
    DROP TABLE #foo

CREATE TABLE #foo
(
c INT DEFAULT(0)
)

BEGIN TRY
ALTER TABLE #foo ALTER COLUMN c VARCHAR(10)
END TRY
BEGIN CATCH
    EXEC usp_GET_LastErrorMessage; -- Empty
    THROW;
END CATCH
GO

EXEC usp_GET_LastErrorMessage; -- Show Data

上面就是一个例子，第一次执行SP的时候，你会得到一个空结果集，第二次执行SP的时候，你就可以拿到正常的结果集了。基于这点，我的任务就没法完成了，真是然并卵！

关于Parameter sniffing的那些事儿 - Dynamic query

Posted on Monday, January 04, 2016 by 醉·醉·鱼 and labeled under sql

引用: http://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/
http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/
http://www.sqlskills.com/blogs/kimberly/high-performance-procedures/

Kimberly Tripp 看上去建议使用Dynamic query来避免parameter sniffing的问题。这里来一个简化版了。

第一次执行SP的时候，query plan会被cache起来。第二次执行的时候，会被用到。很显然，第二次会遇到parameter sniffing问题。

ALTER PROCEDURE Get_OrderID_OrderQty
 @ProductID INT,
 @OrderId INT = NULL
AS 

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID 
AND (SalesOrderID = @OrderId OR @OrderId IS NULL)

GO

SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=870

EXEC Get_OrderID_OrderQty @ProductID=870, @OrderId= 51108

SET STATISTICS IO OFF

改成Dynamic query的方式，就好了。

ALTER PROCEDURE Get_OrderID_OrderQty
 @ProductID INT,
 @OrderId INT = NULL
AS 

DECLARE @sql NVARCHAR(MAX) = N'';

SET @sql = 'SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID '

IF @OrderId IS NOT NULL
    SET @sql = @sql + 'AND SalesOrderID = @OrderId'

EXEC sys.sp_executesql @sql
    , N'@ProductID INT, @OrderId INT'
    , @ProductID, @OrderId

GO

SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=897

EXEC Get_OrderID_OrderQty @ProductID=897, @OrderId= 51108

SET STATISTICS IO OFF

这样就好了？那我们就too young too simple了。有一个明显的问题，就是tipping point。如果这个时候执行下面的query，你就会得到的一个糟糕的结果。实际上，IO由1246变成了14379，这是非常非常的糟糕的。最好参数里面能够有high selective的，否则还是得老老实实加上OPTION(RECOMPILE)。


SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=870

SELECT SalesOrderDetailID, OrderQty, SalesOrderID 
FROM Sales.SalesOrderDetail
WHERE ProductID = 870
SET STATISTICS IO OFF

关于Parameter sniffing的那些事儿 - 怎么解决Parameter sniffing

Posted on Monday, January 04, 2016 by 醉·醉·鱼 and labeled under sql

引用: http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
http://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/
http://www.sqlpass.org/summit/2014/PASStv.aspx?watch=p-6ii2NiUI0

SP_RECOMPILE 在不知道怎么改的时候，一般的临时方案就是sp_recompile.
```
EXEC sys.sp_recompile 'Get_OrderID_OrderQty' 
GO
```

WITH RECOMPILE 能够工作，但是这样做会没有cached query plan，也没有DMV data。

ALTER PROCEDURE Get_OrderID_OrderQty
 @ProductID INT
 WITH RECOMPILE
AS 

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;

OPTION RECOMPILE 比上面稍微好点，能够cached 最后一次执行的query plan，DMV里面也能够看到数据了。

ALTER PROCEDURE Get_OrderID_OrderQty
 @ProductID INT
AS 

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID OPTION(RECOMPILE);

核武器 - DBCC FREEPROCCACHE 世界一下清净了，所有的都需要重新compile和cache。但这只是短暂的，等下一次糟糕的query plan被cache起来以后，问题还是会出现。

Dynamic query？这个看上去是个不错的选择。但实际上我还是发现，我需要加上OPTION(RECOMPILE)，否则，第一次执行的query plan会被cache起来被第二次执行用掉，问题依旧。而加上以后，其实就是第三个方案一样了。

ALTER PROCEDURE Get_OrderID_OrderQty
 @ProductID INT
AS 

DECLARE @sql NVARCHAR(MAX) = N''

SET @sql = 'SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID OPTION(RECOMPILE)'

EXEC sys.sp_executesql @sql, N'@ProductId INT', @ProductID

GO

附上方案5的IO和执行计划

SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=870

EXEC Get_OrderID_OrderQty @ProductID=897

SET STATISTICS IO OFF

事实上，这里涉及到SQL SERVER的tipping point了。所以，在这个例子里面，我们只能够加上OPTION(RECOMPILE）了。至少我现在还没有想出什么好方法。

关于Parameter sniffing的那些事儿 - 什么是parameter sniffing

Posted on Monday, January 04, 2016 by 醉·醉·鱼 and labeled under sql

引用 http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

什么是parameter sniffing

来看个例子。

拿AdventureWorks2012为例，执行下面的query

可以看到每个query的IO和执行计划。

创建一个SP。


CREATE PROCEDURE Get_OrderID_OrderQty
 @ProductID INT
AS 

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID;

执行下面的query。第一次执行的query plan会被cache起来，并被第二个query引用。问题也就出在第二次query上面。可以从IO 和执行计划上面看出，SQL SERVER使用了一个不是很好，有时候可能会使很糟糕的执行计划。IO从之前的10跑到了1246，看执行计划也会发现，预估的是返回4688条记录，实际上只有2条记录，相差太大了。

实际上，Sales.SalesOrderDetail也只有1237个page。


DBCC IND ('AdventureWorks2012', 'Sales.SalesOrderDetail', 0);

这就是parameter sniffing，由于传入的参数的不同，会倒是返回的结果集差异太大，而SQL SERVER却不能够正确的选择最优的执行计划。

HoneyHoney

醉醉鱼·涂鸦志

SQL SERVER 捕获最里层的error message

问题

解决

延伸

关于Parameter sniffing的那些事儿 - Dynamic query

关于Parameter sniffing的那些事儿 - 怎么解决Parameter sniffing

关于Parameter sniffing的那些事儿 - 什么是parameter sniffing

什么是parameter sniffing

About Me

Search

Blog Archive

Label Cloud