在SQL服务器管理演播室中循环SQL脚本会导致内存泄漏。脚本的性能也会更好。

Question

我正在运行一个具有以下结构的数据生成SQL脚本：

CREATE table T where time it took for each transaction will be stored

DECLARE statements
SET statements 

WHILE (condition that will loop for very long time)
  DECLARE CURSOR and OPEN it
    WHILE @@FETCH_STATUS = 0
      some if's, some for's, some SET statements for some variables (setting some values depending on the cursor value).
    FETCH NEXT
  CURSOR closing
  SET other variables to random values

  SET @start = SYSDATETIME();
  EXEC [dbo].[UP_XXX]
  SET @end = SYSDATETIME();

  INSERT results into T
END WHILE

但是，这会缓慢地填充数据库--100万。存储过程每10小时调用一次(i) +它会导致内存泄漏(我的SSMS和SQL server都声称拥有更多内存)(Ii)

我想：

• 优化脚本的速度(有关于SQL脚本优化的建议吗？)
• 修复内存泄漏--也许一些命令行选项会垃圾收集每个循环上创建的变量？我能手动做吗？

剧本：

     /*CONSTRUCT TABLE FOR THE PERFORMANCE RESULTS TO BE STORED AT*/
     CREATE TABLE dbo.Performance_Results
     (
            TransactionNumber int NOT NULL PRIMARY KEY IDENTITY(1,1),
            TimeTaken_mcs int,
            TimeTaken_ms int,
            TimeTaken_s int,
            InputStringLength int,
            InputString varchar(5000),
            InfoOut varchar(4096)
     );

     /*
     PERFORMANCE testing AND DATA GENERATION SCRIPT
     ATTENTION: script uses @counter and @maxcounter variables for how many times it will run, please adjust them
     */

     USE [my_DB] 
     GO

     Declare    @counter int, /*<< for the loop*/
            @maxcounter int,

            @TypeId int, /*<< for CURSOR solution*/
            @Type varchar(32),
            @inputStr varchar(2048),

            @TypeToVarBinary VARBINARY(MAX),    
            @bankdetails VARCHAR(MAX),
            @cardnumber VARCHAR(MAX),
            @dateofbirth VARCHAR(MAX),
            @domid VARCHAR(MAX),
            @emailaddress VARCHAR(MAX),
            @IPaddress VARCHAR(MAX),
            @resid VARCHAR(MAX),
            @telephone VARCHAR(MAX),

            @acc_number varchar (10), /*<< for the non hashed string inputs (one fore each  type)*/
            @sortCode varchar (10),
            @bankdetails varchar(MAX),

            @cardnumber varchar(20),
            @dateofbirth varchar(10),
            @domid varchar(10),
            @emailaddress varchar(70),
            @resid varchar(10),
            @telephone varchar(11),

            @Types varchar(20),/*for @inputString*/
            @ValidCharacters varchar(20),
            @HashCharacters varchar(70),
            @email varchar(40),
            @IPaddress varchar (40),
            @DataLength smallint,

            @concatenatedStringPart1 int, /*for LTJ*/
             @concatenatedStringPart2 int, 
             @concatenatedStringPart3 int, 
             @concatenatedStringPart4 int,

            @inputString varchar (2048), /*<< INPUTS for stored procedure*/
            @ltjInput varchar(60),
            @sectorIdInput bigint,
            @companyIdInput bigint,
            @groupIdInput bigint,
            @profileIdInput bigint,

            @idOut bigint, /*<< OUTPUTS OF stored procedure*/
            @inputsSuppliedOut int,
            @inputsOkOut int,
            @infoOut varchar(4096),

            @start Datetime2,/*datetime2 has more precision than datetime :: datetime2 is accurate to 100ns, datetime only goes in incremens: 200, 500, 700*/
            @end Datetime2

     /*setting variables which will not change as the loop iterates*/       
     SET        @ValidCharacters = 'abcdefghijklmnopqrstuvwxy'
     SET        @DataLength = DATALENGTH (@ValidCharacters) - 1;

     /*setting variables for the out-most loop*/        
     SET        @maxcounter = 10000000;
     SET        @counter = 0;


     WHILE  @counter <= @maxcounter

        BEGIN
            /***************************************** SOLUTION WITH CURSORS ********************************************/
            SET     @inputString = '';

            DECLARE cursor_types CURSOR FOR
            SELECT TOP ((ABS(CHECKSUM(NewId())) % 8) + 1) * FROM type ORDER BY NEWID() --ordering randomly, then selecting random number of elements
            OPEN cursor_types

            FETCH NEXT FROM cursor_types
            INTO @TypeId, @yType

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF (@inputString != '')
                        SET @inputString = @inputString + '|';

                        /**THIS WHERE I CONSTRUCT THE INPUT STRING**/

                            /**CASES FOR EACH TYPE**/

                            IF (@Type = 'bankdetails')
                                BEGIN
                                    SET @sortCode = CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 900000 + 100000 AS VARCHAR)
                                    SET @acc_number = CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 900000000 + 10000000 AS VARCHAR)
                                    SET @bankdetails = @sortCode + ' ' + @acc_number

                                    SET @TypeToVarBinary = CONVERT(varbinary(max), @bankdetails)
                                    SET @bankdetails = CAST(CAST('' as XML).value('xs:base64Binary(sql:variable("@TypeToVarBinary"))', 'varchar(max)') as varchar(MAX))
                                    SET @inputString = @inputString + @Type + '^' + @bankdetails;
                                END

                            ELSE IF (@Type = 'cardnumber')
                                BEGIN
                                    /*cardnumber - 341614349755260 [12-19] numbers*/
                                    SET @cardnumber = CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT)) AS VARCHAR) --19 diggits
                                    SET @TypeToVarBinary = CONVERT(varbinary(max), @cardnumber)
                                    SET @cardnumber = CAST(CAST('' as XML).value('xs:base64Binary(sql:variable("@TypeToVarBinary"))', 'varchar(max)') as varchar(MAX))
                                    SET @inputString = @inputString + @Type + '^' + @cardnumber;
                                END

                            ELSE IF (@Type = 'dateofbirth')
                                BEGIN
                                    /*dateofbirth - 15/01/1981, pattern :: [dd] + '/' + [dd] + '/' [dddd]*/
                                    SET @dateofbirth = (CAST(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS tinyint)) % 30 + 1 AS varchar) + --days
                                                 '/' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS tinyint)) % 12 + 1 AS varchar) + --months
                                                 '/' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS tinyint)) % 109 + 1900 AS varchar) AS VARCHAR)) --years
                                    SET @TypeToVarBinary = CONVERT(varbinary(max), @dateofbirth)
                                    SET @dateofbirth = CAST(CAST('' as XML).value('xs:base64Binary(sql:variable("@TypeToVarBinary"))', 'varchar(max)') as varchar(MAX))
                                    SET @inputString = @inputString + @Type + '^' + @dateofbirth;
                                END

                            ELSE IF (@Type = 'domid')
                                BEGIN
                                    /*domid - from CR, pattern :: 36614147*/
                                    SET @domid = (CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 90000000 + 10000000 AS VARCHAR))
                                    SET @TypeToVarBinary = CONVERT(varbinary(max), @domid)
                                    SET @domid = CAST(CAST('' as XML).value('xs:base64Binary(sql:variable("@TypeToVarBinary"))', 'varchar(max)') as varchar(MAX))
                                    SET @inputString = @inputString + @Type + '^' + @domid;
                                END

                            ELSE IF (@Type = 'emailaddress')
                                BEGIN
                                    /*emailaddress :: uses the variable @ValidCharacters to construct random lenght email addy*/
                                    SET @emailaddress = SUBSTRING(@ValidCharacters,ABS(CHECKSUM(NewId())) % @DataLength + 1,ABS(CHECKSUM(NewId())) % @DataLength + 1) + 
                                            '@' + SUBSTRING(@ValidCharacters,ABS(CHECKSUM(NewId())) % @DataLength + 1, ABS(CHECKSUM(NewId())) % @DataLength + 1) + '.com'       
                                    SET @TypeToVarBinary = CONVERT(varbinary(max), @emailaddress)
                                    SET @emailaddress = CAST(CAST('' as XML).value('xs:base64Binary(sql:variable("@TypeToVarBinary"))', 'varchar(max)') as varchar(MAX))
                                    SET @inputString = @inputString + @Type + '^' + @emailaddress;
                                END

                            ELSE IF (@Type = 'ipaddress')
                                BEGIN
                                    /*ipaddress - >> [0-255].[0-255].[0-255].[0-255] -- tiny int is 1 byte so 0-255*/
                                    SET @IPaddress = CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS tinyint)) AS varchar)  + '.' 
                                                    + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS tinyint)) AS varchar) + '.' 
                                                    + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS tinyint)) AS varchar) + '.'
                                                    + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS tinyint)) AS varchar)
                                    SET @TypeToVarBinary = CONVERT(varbinary(max), @IPaddress)
                                    SET @IPaddress = CAST(CAST('' as XML).value('xs:base64Binary(sql:variable("@TypeToVarBinary"))', 'varchar(max)') as varchar(MAX))
                                    SET @inputString = @inputString + @Type + '^' + @IPaddress;
                                END

                            ELSE IF (@Type = 'resid')
                                BEGIN
                                    /*resid - from CR, the pattern :: 237637668 */
                                    SET @resid = CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 900000000 + 100000000 AS VARCHAR)
                                    SET @TypeToVarBinary = CONVERT(varbinary(max), @resid)
                                    SET @resid = CAST(CAST('' as XML).value('xs:base64Binary(sql:variable("@TypeToVarBinary"))', 'varchar(max)') as varchar(MAX))
                                    SET @inputString = @inputString + @Type + '^' + @resid;
                                END

                            ELSE IF (@Type = 'telephone')
                                BEGIN
                                    /*telephone - 11 diggits all striped :: can implement the 9-11 diggits*/
                                    SET @telephone = CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS BIGINT) / 100000000 ) AS varchar);
                                    SET @TypeToVarBinary = CONVERT(varbinary(max), @telephone)
                                    SET @telephone = CAST(CAST('' as XML).value('xs:base64Binary(sql:variable("@TypeToVarBinary"))', 'varchar(max)') as varchar(MAX))
                                    SET @inputString = @inputString + @Type + '^' + @telephone;
                                END

                    FETCH NEXT FROM cursor_types
                    INTO @TypeId, @Type

                END

            CLOSE cursor_types
            DEALLOCATE cursor_types 

            /**************************************** END SOLUTION WITH CURSORS ********************************************************/

            SET     @concatenatedStringPart1 = 1000 + ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 9000 /*for LTJ*/
            SET     @concatenatedStringPart2 = 10000 + ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 90000
            SET     @concatenatedStringPart3 = 10000 + ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 90000
            SET     @concatenatedStringPart4 = 1 + ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 9

            SET     @ltjInput = 'LTJ-' + 'OLI' + '-' + CAST(@concatenatedStringPart1 AS varchar(20)) + '-' + CAST(@concatenatedStringPart2 AS varchar(20)) + '-' + CAST(@concatenatedStringPart3 AS varchar(20)) + '-' + CAST(@concatenatedStringPart4 AS VARCHAR) /*the 2nd part - does not change*/
            SET     @sectorIdInput = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 21; /*1-21*/
            SET     @companyIdInput = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 1000/*1-1000 :: decided arbitrarilly*/
            SET     @groupIdInput = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 1000; /*1-1000 :: decided arbitrarilly*/
            SET     @profileIdInput = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 1000 /*1-1000 :: decided arbitrarilly*/

            /*DBCC statements :: act as Database Console Commands for SQL Server.*/
            DBCC    FREEPROCCACHE; -- Removes all elements from the plan cache, a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.
            DBCC    DROPCLEANBUFFERS; -- Removes all clean buffers from the buffer pool.

            SET     @start = SYSDATETIME();

                    EXEC    [dbo].[UP_XXX]
                        @inputStr = @inputString, 
                        @ltjIn = @ltjInput, 
                        @sectorIdIn = @sectorIdInput, 
                        @companyIdIn = @companyIdInput, 
                        @groupIdIn = @groupIdInput, 
                        @profileIdIn = @profileIdInput, 
                        @idOut = @idOut OUTPUT,
                        @inputsSuppliedOut = @inputsSuppliedOut OUTPUT,
                        @inputsOkOut = @inputsOkOut OUTPUT,
                        @infoOut = @infoOut OUTPUT 

                        -- Display Stored Procedures errors :: SELECT @infoOut;

            SET     @end = SYSDATETIME()
            SET     @counter = @counter + 1

            /*INSERTING THE PERFORMANCE RESULTS INTO THE TABLE*/
            INSERT INTO dbo.Performance_Results VALUES
            (
                DATEDIFF(mcs, @start, @end),
                DATEDIFF(ms, @start, @end),
                DATEDIFF(s, @start, @end),
                LEN(@inputString),
                @inputString,
                @infoOut
            );

        END

Answer 1

为什么不去掉光标呢？好好看看这个，我不明白为什么这必须是一个光标。这是一个让你朝着正确方向前进的开始：

DECLARE @inputString varchar (2048)

SET @inputString = ''

SELECT TOP ((ABS(CHECKSUM(NewId())) % 8) + 1) 
    @inputString = @inputString + '|' + 
    CASE WHEN Type = 'bankdetails' THEN
            bd_bankdetails2.bankdetails2
        WHEN Type = 'cardnumber' THEN
            ...
FROM type 
    CROSS APPLY (SELECT CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 900000 + 100000 AS VARCHAR) sortCode) as bd_SortCode 
    CROSS APPLY (SELECT CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 900000000 + 10000000 AS VARCHAR) acc_number ) as bd_acc_number  
    CROSS APPLY (SELECT bd_SortCode.sortCode + ' ' + bd_acc_number.acc_number bankdetails1 ) as bd_bankdetails1 
    --CROSS APPLY (SELECT CONVERT(varbinary(max), bd_bankdetails1.bankdetails1) TypeToVarBinary) as bd_TypeToVarBinary 
    CROSS APPLY (SELECT CAST(CAST('' as XML).value(bd_bankdetails1.bankdetails1, 'varchar(max)') as varchar(MAX)) bankdetails2) as bd_bankdetails2
ORDER BY NEWID()

在这个例子中，我使用了交叉应用，主要是为了显示变量的当前细分，所以这对您来说更有意义，但是大多数情况下可能是在一个大案例中完成的。