Inside TSQL Querying - Chapter 3. Query Tuning-白红宇

Inside TSQL Querying - Chapter 3. Query Tuning

阅读量：6417 次

发布时间：2019-06-23

本文共 27035 字，大约阅读时间需要 90 分钟。

Tuning Methodology

When dealing with performance problems, database professionals tend to focus on the technical aspects of the system, such as resource queues, resource utilization, and so on. However, users perceive performance problems simply as waitsthey make a request and have to wait to get the results back. A response that takes longer than three seconds to arrive after an interactive request is typically perceived by users as a performance problem. They don't really care how many commands wait on average on each disk spindle or what the cache hit ratio is, and they don't care about blocking, CPU utilization, average page life expectancy in cache, and so on. They care about waits, and that's where performance tuning should start.

The tuning methodology I recommend applies a top-down approach. It starts by investigating waits at the instance level, and then drills down through a series of steps until the processes/components that generate the bulk of the waits in the system are identified. Once you identify the offending processes, you can focus on tuning them. Following are the main steps of the methodology:

1.	Analyze waits at the instance level.
2.	Correlate waits with queues.
3.	Determine a course of action.
4.	Drill down to the database/file level.
5.	Drill down to the process level.
6.	Tune indexes/queries.

Analyze Waits at the Instance Level

The first step in the tuning methodology is to identify, at the instance level, which types of waits contribute most to the waits in the system. In SQL Server 2005, you do this by querying a dynamic management view (DMV) called sys.dm_os_wait_stats; in SQL Server 2000, you do this by running the command DBCC SQLPERF(WAITSTATS). The aforementioned DMV in SQL Server 2005 is fully documented, and I urge you to read the section describing it in Books Online. I'm not sure why, but the command in SQL Server 2000 is undocumented and surfaced only several years after the product was released. However, from the documentation in SQL Server 2005 you can learn about the different types of waits that are relevant to SQL Server 2000 as well.

Run the following query to return the waits in your system sorted by type:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

The following query isolates the top waits that accumulate in total to 90 percent of the wait time in the system, and it generates (on my system) the output shown in :

WITH Waits AS(  SELECT    wait_type,    wait_time_ms / 1000. AS wait_time_s,    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn  FROM sys.dm_os_wait_stats  WHERE wait_type NOT LIKE '%SLEEP%'  -- filter out additional irrelevant waits)SELECT  W1.wait_type,  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pctFROM Waits AS W1  JOIN Waits AS W2    ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pctHAVING SUM(W2.pct) - W1.pct < 90 -- percentage thresholdORDER BY W1.rn;

Query Template

DECLARE @my_templatetext AS NVARCHAR(MAX);

DECLARE @my_parameters AS NVARCHAR(MAX);

EXEC sp_get_query_template

N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78',

@my_templatetext OUTPUT,

@my_parameters OUTPUT;

SELECT @my_templatetext AS querysig, @my_parameters AS params;

The problem with this stored procedure is that you need to use a cursor to invoke it against every query string from the trace data, and this can take quite a while with large traces.

Lis Creation script for the fn_SQLSigTSQL UDF

IF OBJECT_ID('dbo.fn_SQLSigTSQL') IS NOT NULL  DROP FUNCTION dbo.fn_SQLSigTSQL;GOCREATE FUNCTION dbo.fn_SQLSigTSQL  (@p1 NTEXT, @parselength INT = 4000)RETURNS NVARCHAR(4000)---- This function is provided "AS IS" with no warranties,-- and confers no rights.-- Use of included script samples are subject to the terms specified at-- http://www.microsoft.com/info/cpyright.htm---- Strips query stringsASBEGIN  DECLARE @pos AS INT;  DECLARE @mode AS CHAR(10);  DECLARE @maxlength AS INT;  DECLARE @p2 AS NCHAR(4000);  DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);  DECLARE @p2len AS INT;  SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));  SET @maxlength = CASE WHEN @maxlength > @parselength                     THEN @parselength ELSE @maxlength END;  SET @pos = 1;  SET @p2 = '';  SET @p2len = 0;  SET @currchar = '';  set @nextchar = '';  SET @mode = 'command';  WHILE (@pos <= @maxlength)  BEGIN    SET @currchar = SUBSTRING(@p1,@pos,1);    SET @nextchar = SUBSTRING(@p1,@pos+1,1);    IF @mode = 'command'    BEGIN      SET @p2 = LEFT(@p2,@p2len) + @currchar;      SET @p2len = @p2len + 1 ;      IF @currchar IN (',','(',' ','=','<','>','!')        AND @nextchar BETWEEN '0' AND '9'      BEGIN        SET @mode = 'number';        SET @p2 = LEFT(@p2,@p2len) + '#';        SET @p2len = @p2len + 1;      END      IF @currchar = ''''      BEGIN        SET @mode = 'literal';        SET @p2 = LEFT(@p2,@p2len) + '#''';        SET @p2len = @p2len + 2;      END    END    ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!')      SET @mode= 'command';    ELSE IF @mode = 'literal' AND @currchar = ''''      SET @mode= 'command';    SET @pos = @pos + 1;  END  RETURN @p2;ENDGO

The function accepts as inputs a query string and the length of the code you want to parse. The function returns the query signature of the input query, with all parameters replaced by a number sign (#). Note that this is a fairly simple function and might need to be tailored to particular situations. Run the following code to test the function:

SELECT dbo.fn_SQLSigTSQL  (N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000);

You will get the following output:

SELECT * FROM dbo.T1 WHERE col1 = # AND col2 > #

Of course, you could now use the function and aggregate the trace data by query signature. However, keep in mind that although T-SQL is very efficient with data manipulation, it is slow in processing iterative/procedural logic. This is a classic example where a CLR implementation of the function makes more sense. The CLR is much faster than T-SQL for iterative/procedural logic and string manipulation. SQL Server 2005 introduces .NET integration within the product, allowing you to develop .NET routines based on the common language runtime (CLR). CLR routines are discussed in Inside T-SQL Programming; there you will find more thorough coverage and a comparison of the T-SQL and CLR-based implementations of the function that generates query signatures. You can also find some background information about CLR development in SQL Server .

fn_SQLSigCLR and fn_RegexReplace functions, C# version

using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlTypes;using System.Text.RegularExpressions;public partial class SQLSignature{    // fn_SQLSigCLR    [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]    public static SqlString fn_SQLSigCLR(SqlString querystring)    {        return (SqlString)Regex.Replace(            querystring.Value,            @"([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?#    expression coming             )(?:([N])?(')(?:[^']|'')*('))(?#           character             )|(?:0x[\da-fA-F]*)(?#                     binary             )|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?#     precise number             )(?:[eE]?[\d]*)))(?#                       imprecise number             )|(?:[~]?[-+]?(?:[\d]+))(?#                integer             ))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?#   operators             ))",            @"$1$2$3#$4");    }    // fn_RegexReplace - for generic use of RegEx-based replace    [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]    public static SqlString fn_RegexReplace(        SqlString input, SqlString pattern, SqlString replacement)    {        return (SqlString)Regex.Replace(            input.Value, pattern.Value, replacement.Value);    }}

The code has the definitions of two functions: fn_SQLSigCLR and fn_RegexReplace. The function fn_SQLSigCLR accepts a query string and returns the query signature. This function covers cases that the T-SQL function overlooks, and it can be easily enhanced to support more cases if you need it to. The function fn_RegexReplace exposes more generic pattern-based string replacement capabilities based on regular expressions, and it is provided for your convenience.

Note

I didn't bother checking for NULL inputs in the CLR code because T-SQL allows you to specify the option RETURNS NULL ON NULL INPUT when you register the functions, as I will demonstrate later. This option means that when a NULL input is provided, SQL Server doesn't invoke the function at all; rather, it simply returns a NULL output.

If you're familiar with developing CLR routines in SQL Server, deploy these functions in the Performance database. If you're not, just follow these steps:

1.	Create a new Microsoft Visual C#, Class Library project in Microsoft Visual Studio 2005 (File>New>Project...>Visual C#>Class Library).
2.	In the New Project dialog box, name the project and solution SQLSignature, specify C:\ as the location, and confirm.
3.	Rename the file Class1.cs to SQLSignature.cs, and within it paste the code from , overriding its current content.
4.	Build the assembly by choosing the Build>Build SQLSignature menu item. A file named C:\SQLSignature\SQLSignature\bin\Debug\SQLSignature.dll containing the assembly will be created.
5.	At this point, you go back to SQL Server Management Studio (SSMS) and apply a couple of additional steps to deploy the assembly in the Performance database, and then register the fn_SQLSigCLR and fn_RegexReplace functions. But first, you need to enable CLR in SQL Server (which is disabled by default) by running the following code: EXEC sp_configure 'clr enable', 1;RECONFIGURE;

6.	Next, you need to load the intermediate language (IL) code from the .dll file into the Performance database by running the following code: USE Performance;CREATE ASSEMBLY SQLSignatureFROM 'C:\SQLSignature\SQLSignature\bin\Debug\SQLSignature.dll';
7.	Finally, register the fn_SQLSigCLR and fn_RegexReplace functions by running the following code: CREATE FUNCTION dbo.fn_SQLSigCLR(@querystring AS NVARCHAR(MAX))RETURNS NVARCHAR(MAX)WITH RETURNS NULL ON NULL INPUTEXTERNAL NAME SQLSignature.SQLSignature.fn_SQLSigCLR;GOCREATE FUNCTION dbo.fn_RegexReplace( @input AS NVARCHAR(MAX), @pattern AS NVARCHAR(MAX), @replacement AS NVARCHAR(MAX))RETURNS NVARCHAR(MAX)WITH RETURNS NULL ON NULL INPUTEXTERNAL NAME SQLSignature.SQLSignature.fn_RegexReplace;GO

You're done. At this point, you can start using the functions like you do any other user-defined function. In case you're curious, the CLR implementation of the function runs faster than the T-SQL one by a factor of 10.

To test the fn_SQLSigCLR function, invoke it against the Workload table by running the following query, which will generate the output shown in abbreviated form in :

SELECT  dbo.fn_SQLSigCLR(tsql_code) AS sig,  durationFROM dbo.Workload;

You can also use the more generic fn_RegexReplace function to achive the same output, like so:

SELECT  dbo.fn_RegexReplace(tsql_code,    N'([\s,(=<>!](?![^\]]+[\]]))(?:(?:(?:(?#    expression coming     )(?:([N])?('')(?:[^'']|'''')*(''))(?#      character     )|(?:0x[\da-fA-F]*)(?#                     binary     )|(?:[-+]?(?:(?:[\d]*\.[\d]*|[\d]+)(?#     precise number     )(?:[eE]?[\d]*)))(?#                       imprecise number     )|(?:[~]?[-+]?(?:[\d]+))(?#                integer     ))(?:[\s]?[\+\-\*\/\%\&\|\^][\s]?)?)+(?#   operators     ))',    N'$1$2$3#$4') AS sig,  durationFROM dbo.Workload;

As you can see, you get back query signatures, which you can use to aggregate the trace data. Keep in mind, though, that query strings can get lengthy, and grouping the data by lengthy strings is slow and expensive. Instead, you might prefer to generate an integer checksum for each query string by using the T-SQL CHECKSUM function. For example, the following query generates a checksum value for each query string from the Workload table, and it generates the output shown in abbreviated form in :

SELECT  CHECKSUM(dbo.fn_SQLSigCLR(tsql_code)) AS cs,  durationFROM dbo.Workload;

Clearing the Cache

When analyzing query performance, you sometimes need to clear the cache. SQL Server provides you with tools to clear both data and execution plans from cache. To clear data from cache globally, use the following command:

DBCC DROPCLEANBUFFERS;

To clear execution plans from cache globally, use the following command:

DBCC FREEPROCCACHE;

To clear execution plans of a particular database, use the following command:

DBCC FLUSHPROCINDB(
       
        ); Note that the DBCC FLUSHPROCINDB command is undocumented.

TableSample

Here's an example for using the TABLESAMPLE clause in a query against the Orders table, requesting 1,000 rows:

SELECT *FROM dbo.Orders TABLESAMPLE SYSTEM (1000 ROWS);

SELECT * FROM dbo.Orders TABLESAMPLE (0.1 PERCENT);

When you use the ROWS option, SQL Server internally first converts the specified number of rows to a percentage. Remember that you are not guaranteed to get the exact number of rows that you requested; rather, you'll get a close value that's determined by the number of pages that were picked and the number of rows on those pages (which may vary).

To make it more likely that you'll get the exact number of rows you are after, specify a higher number of rows in the TABLESAMPLE clause and use the TOP option to limit the upper bound that you will get, like so:

SELECT TOP(1000) *FROM dbo.Orders TABLESAMPLE (2000 ROWS);

There's still a chance that you will get fewer rows than the number you requested, but you're guaranteed not to get more. By specifying a higher value in the TABLESAMPLE clause, you increase the likelihood of getting the number of rows you are after.

If you need to get repeatable results, use a clause called REPEATABLE which was designed for this purpose, providing it with the same seed in all invocations. For example, running the following query multiple times will yield the same result, provided that the data in the table has not changed:

SELECT *FROM dbo.Orders TABLESAMPLE (1000 ROWS) REPEATABLE(42);

Cursor solution

DECLARE  @sid     AS VARCHAR(5),  @od      AS DATETIME,  @prevsid AS VARCHAR(5),  @prevod  AS DATETIME;DECLARE ShipOrdersCursor CURSOR FAST_FORWARD FOR  SELECT shipperid, orderdate  FROM dbo.Orders  ORDER BY shipperid, orderdate;OPEN ShipOrdersCursor;FETCH NEXT FROM ShipOrdersCursor INTO @sid, @od;SELECT @prevsid = @sid, @prevod = @od;WHILE @@fetch_status = 0BEGIN  IF @prevsid <> @sid AND @prevod < '20010101' PRINT @prevsid;  SELECT @prevsid = @sid, @prevod = @od;  FETCH NEXT FROM ShipOrdersCursor INTO @sid, @od;ENDIF @prevod < '20010101' PRINT @prevsid;CLOSE ShipOrdersCursor;DEALLOCATE ShipOrdersCursor;

Others

USE [NutsAndBolts] go /****** Object:  StoredProcedure [dbo].[utility_Trace_GetPerformanceWorkLoadTrace]    Script Date: 2013/11/28 12:38:13 ******/ SET ansi_nulls ON go SET quoted_identifier ON go -- ============================================= -- Author:    Alex Tian -- Create date: 2012-09-01 -- Description:  Define your trace -- DECLARE @dbid  INT -- DECLARE @tracefile NVARCHAR(245) -- SET @dbid = DB_ID('TestDB') -- SET @tracefile=N'd:\temp\Test' -- EXEC [dbo].[utility_Trace_GetPerformanceWorkLoadTrace] @dbid,@tracefile  -- EXEC sp_trace_setstatus 2, 0; --stop -- EXEC sp_trace_setstatus 2, 1; --start -- EXEC sp_trace_setstatus 2, 2; -- close -- SELECT * FROM sys.traces -- SELECT CHECKSUM(dbo.fn_SQLSigTSQL(TextData,4000)) AS CodeSerial, --        Duration --   FROM fn_trace_gettable('D:\TEMP\ Test.trc',null) -- ============================================= ALTER PROCEDURE [dbo].[Utility_trace_getperformanceworkloadtrace] @dbid      AS INT,                                                                   @tracefile AS NVARCHAR(245) AS   BEGIN       -- Create a Queue         DECLARE @rc AS INT;       DECLARE @traceid AS INT       DECLARE @maxfilesize AS BIGINT;       SET @maxfilesize = 5;       EXEC @rc = Sp_trace_create         @traceid output,         0,         @tracefile,         @maxfilesize,         NULL       IF ( @rc != 0 )         GOTO error; -- Set the events        DECLARE @on AS BIT;       SET @on = 1;       -- RPC:Completed         EXEC Sp_trace_setevent         @traceid,         10,         15,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         8,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         16,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         48,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         1,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         17,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         10,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         18,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         11,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         12,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         13,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         6,         @on;       EXEC Sp_trace_setevent         @traceid,         10,         14,         @on;       -- SP:Completed         EXEC Sp_trace_setevent         @traceid,         43,         15,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         8,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         48,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         1,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         10,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         11,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         12,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         13,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         6,         @on;       EXEC Sp_trace_setevent         @traceid,         43,         14,         @on;       -- SP:StmtCompleted         EXEC Sp_trace_setevent         @traceid,         45,         8,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         16,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         48,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         1,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         17,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         10,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         18,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         11,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         12,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         13,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         6,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         14,         @on;       EXEC Sp_trace_setevent         @traceid,         45,         15,         @on;       -- SQL:BatchCompleted         EXEC Sp_trace_setevent         @traceid,         12,         15,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         8,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         16,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         48,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         1,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         17,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         6,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         10,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         14,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         18,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         11,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         12,         @on;       EXEC Sp_trace_setevent         @traceid,         12,         13,         @on;       -- SQL:StmtCompleted         EXEC Sp_trace_setevent         @traceid,         41,         15,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         8,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         16,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         48,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         1,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         17,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         10,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         18,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         11,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         12,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         13,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         6,         @on;       EXEC Sp_trace_setevent         @traceid,         41,         14,         @on;       -- Set the Filters        -- Application name filter         EXEC Sp_trace_setfilter         @traceid,         10,         0,         7,         N'SQL Server Profiler%';       -- Database ID filter         EXEC Sp_trace_setfilter         @traceid,         3,         0,         0,         @dbid;       -- Set the trace status to start        EXEC Sp_trace_setstatus         @traceid,         1;       -- Print trace id and file name for future references        PRINT 'Trace ID: '             + Cast(@traceid AS VARCHAR(10))             + ', Trace File: ''' + @tracefile + '.trc''';       GOTO finish;       ERROR:       PRINT 'Error Code: ' + Cast(@rc AS VARCHAR(10));       FINISH:   END

USE [NutsAndBolts] go /****** Object:  StoredProcedure [dbo].[utility_Wait_GetWaitsForAllDatabase]    Script Date: 2013/11/28 12:42:56 ******/ SET ansi_nulls ON go SET quoted_identifier ON go -- ============================================= -- Author:    Alex Tian -- Create date: 2012-09-01 -- Description:  DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) -- ============================================= ALTER PROCEDURE [dbo].[Utility_wait_getwaitsforalldatabase] AS   BEGIN       SET TRANSACTION isolation level READ uncommitted;       WITH waits            AS (SELECT Row_number()                         OVER(                           ORDER BY wait_time_ms DESC) AS RowNumber,                       wait_type                       AS WaitType,                       wait_time_ms / 1000.            AS WaitSecond,                       100. * wait_time_ms / Sum(wait_time_ms)                                               OVER()  AS WaitPercent                FROM   sys.dm_os_wait_stats                WHERE  wait_type NOT LIKE '%SLEEP%'               -- filter out additional irrelevant waits               )       SELECT W1.waittype                                 AS WaitType,              Cast(W1.waitsecond AS DECIMAL(12, 2))       AS WaitSecond,              Cast(W1.waitpercent AS DECIMAL(12, 2))      AS WaitPercent,              Cast(Sum(W2.waitpercent) AS DECIMAL(12, 2)) AS RunningPercent       FROM   waits AS W1              JOIN waits AS W2                ON W2.rownumber <= W1.rownumber       GROUP  BY W1.rownumber,                 W1.waittype,                 W1.waitsecond,                 W1.waitpercent       HAVING Sum(W2.waitpercent) - W1.waitpercent < 90 -- percentage threshold       ORDER  BY W1.rownumber;   END  USE [NutsAndBolts] go /****** Object:  UserDefinedFunction [dbo].[fn_SQLSigTSQL]    Script Date: 2013/11/28 12:44:09 ******/ SET ansi_nulls ON go SET quoted_identifier ON go -- ============================================= -- Author:    Alex Tian -- Create date: 2012-09-01 -- Description: Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm -- SELECT dbo.fn_SQLSigTSQL(N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78', 4000); -- DECLARE @my_templatetext AS NVARCHAR(MAX); -- DECLARE @my_parameters   AS NVARCHAR(MAX); -- EXEC sp_get_query_template N'SELECT * FROM dbo.T1 WHERE col1 = 3 AND col2 > 78',@my_templatetext OUTPUT,@my_parameters OUTPUT; -- SELECT @my_templatetext AS querysig, @my_parameters AS params; -- ============================================= ALTER FUNCTION [dbo].[Fn_sqlsigtsql](@p1          NTEXT,                                      @parselength INT = 4000) returns NVARCHAR(4000) AS   BEGIN       DECLARE @pos AS INT;       DECLARE @mode AS CHAR(10);       DECLARE @maxlength AS INT;       DECLARE @p2 AS NCHAR(4000);       DECLARE @currchar AS CHAR(1),               @nextchar AS CHAR(1);       DECLARE @p2len AS INT;       SET @maxlength = Len(Rtrim(Substring(@p1, 1, 4000)));       SET @maxlength = CASE                          WHEN @maxlength > @parselength THEN @parselength                          ELSE @maxlength                        END;       SET @pos = 1;       SET @p2 = '';       SET @p2len = 0;       SET @currchar = '';       SET @nextchar = '';       SET @mode = 'command';       WHILE ( @pos <= @maxlength )         BEGIN             SET @currchar = Substring(@p1, @pos, 1);             SET @nextchar = Substring(@p1, @pos + 1, 1);             IF @mode = 'command'               BEGIN                   SET @p2 = LEFT(@p2, @p2len) + @currchar;                   SET @p2len = @p2len + 1;                   IF @currchar IN ( ',', '(', ' ', '=',                                     '<', '>', '!' )                      AND @nextchar BETWEEN '0' AND '9'                     BEGIN                         SET @mode = 'number';                         SET @p2 = LEFT(@p2, @p2len) + '#';                         SET @p2len = @p2len + 1;                     END                   IF @currchar = ''''                     BEGIN                         SET @mode = 'literal';                         SET @p2 = LEFT(@p2, @p2len) + '#''';                         SET @p2len = @p2len + 2;                     END               END             ELSE IF @mode = 'number'                AND @nextchar IN ( ',', ')', ' ', '=',                                   '<', '>', '!' )               SET @mode= 'command';             ELSE IF @mode = 'literal'                AND @currchar = ''''               SET @mode= 'command';             SET @pos = @pos + 1;         END       RETURN @p2;   END

转载于:https://www.cnblogs.com/flysun0311/p/3446230.html

你可能感兴趣的文章

黄聪：利用ImageMagicK给图片加水印

查看>>

由lwip的mbox中netbuf传递看指针的指针