HoneyHoney

引用 http://sqlblog.com/blogs/hugo_kornelis/archive/2014/10/19/43466.aspx 

CREATE TABLE dbo.LargeTable 
(KeyVal int NOT NULL PRIMARY KEY, 
DataVal int NOT NULL CHECK (DataVal BETWEEN 1 AND 10) 
);

WITH Digits 
AS (SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(d)) 
INSERT INTO dbo.LargeTable (KeyVal, DataVal) 
SELECT 10000 * tt.d + 1000 * st.d 
+ 100 * h.d + 10 * t.d + s.d + 1, 
10 * RAND(CHECKSUM(NEWID())) + 1 
FROM   Digits AS s,  Digits AS t,  Digits AS h, 
Digits AS st, Digits AS tt;

--Scalar function
CREATE FUNCTION dbo.Triple(@Input int) 
RETURNS int 
AS 
BEGIN; 
DECLARE @Result int; 
SET @Result = @Input * 3; 
RETURN @Result; 
END; 
go 


--inline TVF
CREATE FUNCTION dbo.TripleV2(@Input int)
RETURNS TABLE
AS
RETURN
SELECT 3*@Input AS amt
GO


--Multlple-Line TVF
CREATE FUNCTION dbo.TripleV3(@Input int) 
RETURNS @Result TABLE(amt INT)
AS 
BEGIN; 
INSERT INTO @Result(amt) 
SELECT @Input * 3; 
RETURN; 
END; 
go 

SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple 
FROM   dbo.LargeTable;

SELECT MAX(t.amt)
FROM dbo.LargeTable lt
CROSS APPLY dbo.TripleV3(lt.DataVal) t

SELECT MAX(t.amt)
FROM dbo.LargeTable lt
CROSS APPLY dbo.TripleV2(lt.DataVal) t

直接上结果

1. 最糟糕的是Multiple-Line Table Value Function，一次都要10秒。最主要还是要执行100000次。
2. 次之的是Scalar Value Function。一次大概1.6秒的样子。其实每个function也要执行100000次。
3. 最好的是Inline Table Value Function。无论CPU还是执行时间，都非常得好。

问题

解决

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

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;

延伸

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

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

1. SP_RECOMPILE 在不知道怎么改的时候，一般的临时方案就是sp_recompile. 

   EXEC sys.sp_recompile 'Get_OrderID_OrderQty' 
   GO

2. 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;

3. 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);

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

SET STATISTICS IO  ON

EXEC Get_OrderID_OrderQty @ProductID=870

EXEC Get_OrderID_OrderQty @ProductID=897

SET STATISTICS IO OFF

什么是parameter sniffing

拿AdventureWorks2012为例，执行下面的query

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



创建一个SP。

CREATE PROCEDURE Get_OrderID_OrderQty
 @ProductID INT
AS 

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

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

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

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

1. 当没有cached query plan 的时候，执行query就会创建一个query plan并被cache起来。
2. 下面这些操作可以导致cached plan被清掉
• SERVER RESTART/DATABASE LEVEL CHANGES/CONFIGURATION CHANGES
• DBCC FREEPROCCACHE/DBCC FREESYSTEMCACHE/SP_RECOMPILE
• DROP & RECREATE SP
• Object changes，包括view，table，statistics
• 长时间没用