Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Feature Request - sp_BlitzIndex @GetAllDatabases = 1, @ExcludeDatabases #1944

Closed
richardlhughes opened this issue Feb 5, 2019 · 4 comments

Comments

Projects
None yet
3 participants
@richardlhughes
Copy link

commented Feb 5, 2019

Do you want to request a feature or report a bug?
Current Version

What is the current behavior?
EXEC sp_BlitzIndex @GetAllDatabases = 1

I want to run on all databases however I want to Skip partitioned databases like my MDS database.
Last @Dsql: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @RowcountOUT = SUM(1) FROM [MDS].sys.partitions WHERE partition_number > 1 OPTION ( RECOMPILE );
Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1644 [Batch Start Line 4]
MDS database failed to process. Setting @SkipPartitions = 1 because > 100 partitions were found. To check them, you must set @Bringthepain = 1.

If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com

What is the expected behavior?

Expected, nice to have behavior:
EXEC sp_BlitzIndex @GetAllDatabases = 1, @ExcludeDatabases = 'MDS';

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

sql 2016

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Feb 18, 2019

Thinking about the design on this:

  • The @ExcludeDatabases input parameter should mimic sp_BlitzCache's @IgnoreQueryHashes parameter, using the same naming style (@IgnoreDatabases) and use the same comma-delimited input and parsing, then dump the contents into an #IgnoredDatabases temp table
  • When we do @GetAllDatabases = 1, the insert into #DatabaseList, do a where-not-exists subquery into the temp table looking for the same database name
  • Move the "Checking partition count" section earlier in the code so that we first check if any databases have over 100 partitions, and if @Bringthepain = 0, add the partitioned databases to the #IgnoreDatabases temp table
  • In the Mode 0/2/4 output result sets, at the top, put an informational message for each database that was skipped, and why (because the user passed it in, or because we detected over 100 partitions and they didn't have @Bringthepain = 1)
@BrentOzar

This comment has been minimized.

Copy link
Member

commented Feb 18, 2019

(Also - Richard, just so you know - I'm still wildly in love with this request and I'm absolutely determined to do it because I want it for the Consultant Toolkit too. It'll just likely be in the March release instead of the Feb one.)

@BrentOzar BrentOzar added this to the 2019-03 milestone Feb 18, 2019

@richardlhughes

This comment has been minimized.

Copy link
Author

commented Feb 18, 2019

@RichBenner

This comment has been minimized.

Copy link
Contributor

commented Feb 26, 2019

I've been working on it but haven't managed to get it completed this week.

This is the code I've currently got for the exclusion list passed by users;

DECLARE @IgnoreDatabases VARCHAR(MAX) = 'test,test2,test3';
DECLARE @GetAllDatabases VARCHAR(MAX) = 1 ;
DECLARE @individual VARCHAR(MAX) = NULL ;

SET @IgnoreDatabases = LTRIM(RTRIM(@IgnoreDatabases)) 

IF OBJECT_ID('tempdb..#Ignore_Databases') IS NOT NULL DROP TABLE #Ignore_Databases
    CREATE TABLE #Ignore_Databases (DatabaseName NVARCHAR(128), Reason NVARCHAR(100))
    

IF @GetAllDatabases = 1 
    AND @IgnoreDatabases IS NOT NULL
    AND LEN(@IgnoreDatabases) > 0
BEGIN
    RAISERROR(N'Setting up filter to ignore databases', 0, 1) WITH NOWAIT;
    SET @individual = '';

    WHILE LEN(@IgnoreDatabases) > 0
    BEGIN
        IF PATINDEX('%,%', @IgnoreDatabases) > 0
        BEGIN  
               SET @individual = SUBSTRING(@IgnoreDatabases, 0, PATINDEX('%,%',@IgnoreDatabases)) ;
               
               INSERT INTO #Ignore_Databases (DatabaseName, Reason)
               SELECT @individual, 'Ignored by User'
			   OPTION (RECOMPILE) ;
               
               SET @IgnoreDatabases = SUBSTRING(@IgnoreDatabases, LEN(@individual + ',') + 1, LEN(@IgnoreDatabases)) ;
        END;
        ELSE
        BEGIN
               SET @individual = @IgnoreDatabases ;
               SET @IgnoreDatabases = NULL ;

               INSERT INTO #Ignore_Databases (DatabaseName, Reason)
               SELECT @individual, 'Ignored by User'
			   OPTION (RECOMPILE) ;
        END;
   END;
        
END

SELECT * FROM #Ignore_Databases

Here's the cursor check code that I've made for databases with >100 partitions;

/* this is already in BlitzIndex */
DECLARE @BringThePain int = 0;
DECLARE @SkipPartitions int = 0;
DECLARE @TableName nvarchar(128) = NULL;
DECLARE @dsql NVARCHAR(MAX);
DECLARE @Rowcount BIGINT;
DECLARE @DatabaseName NVARCHAR(128) = NULL

IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE #DatabaseList
CREATE TABLE #DatabaseList 
(
	DatabaseName NVARCHAR(256),
	secondary_role_allow_connections_desc NVARCHAR(50)
);

INSERT INTO #DatabaseList (DatabaseName)
SELECT DISTINCT  DB_NAME(d.database_id)
FROM    sys.databases d
WHERE d.user_access_desc = 'MULTI_USER'
AND d.state_desc = 'ONLINE'
AND d.database_id > 4
AND DB_NAME(d.database_id) NOT LIKE 'ReportServer%'
AND DB_NAME(d.database_id) NOT LIKE 'rdsadmin%'
AND d.is_distributor = 0

/* a new temp table */

CREATE TABLE #Ignore_Databases
(
	DatabaseName NVARCHAR(128) NOT NULL,
	Reason NVARCHAR(100)
);

/* This is the partition check cursor */
RAISERROR (N'Checking partition count',0,1) WITH NOWAIT;
IF @BringThePain = 0 AND @SkipPartitions = 0 AND @TableName IS NULL
    BEGIN   
        DECLARE partition_cursor CURSOR FOR
        SELECT DatabaseName
        FROM #DatabaseList

        OPEN partition_cursor
        FETCH NEXT FROM partition_cursor INTO @DatabaseName
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
            /* Count the total number of partitions */
            SET @dsql = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
                    SELECT @RowcountOUT = SUM(1) FROM ' + QUOTENAME(@DatabaseName) + '.sys.partitions WHERE partition_number > 1 OPTION    ( RECOMPILE );';
            EXEC sp_executesql @dsql, N'@RowcountOUT BIGINT OUTPUT', @RowcountOUT = @Rowcount OUTPUT;
            IF @Rowcount > 100
                BEGIN
                   RAISERROR (N'Skipping database %s because > 100 partitions were found. To check this database, you must set @BringThePain = 1.',0,1,@DatabaseName) WITH NOWAIT;
				INSERT INTO #Ignore_Databases (DatabaseName, Reason)
				SELECT @DatabaseName, '>100 Partitions'
                END;
            FETCH NEXT FROM partition_cursor INTO @DatabaseName
        END;
    END;					
CLOSE partition_cursor
DEALLOCATE partition_cursor

Putting it here in case anybody else wants a stab at it, I'm hoping to get it looked at next week if not.

@BrentOzar BrentOzar removed this from the 2019-03 milestone Mar 20, 2019

@BrentOzar BrentOzar added this to the 2019-04 Release milestone Apr 22, 2019

BrentOzar added a commit that referenced this issue Apr 23, 2019

#1944 sp_BlitzIndex ignore databases
Adds new IgnoreDatabases parameter, and skips databases with >100 partitions. Closes #1944.

BrentOzar added a commit that referenced this issue Apr 23, 2019

#1944 sp_BlitzIndex ignore databases (#2029)
Adds new IgnoreDatabases parameter, and skips databases with >100 partitions. Closes #1944.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.