search

BrentOzarULTD / SQL-Server-First-Responder-Kit

sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning.
http://FirstResponderKit.org
Other
3.37k stars 997 forks source link

sp_blitzindex - no index on #IndexColumns means performance very slow #1149

Closed ghost closed 6 years ago

ghost commented 7 years ago

Version is 2017-10-01, 5.8

Current behavior: Database has 241,000+ columns. To run @mode=3 takes over 40 minutes on the build of the compression table, which is irrelevant to a Missing Index.

Steps to Reproduce: Just run sp_blitzindex @mode=3 on a DB with 200K+ columns.

Expected behavior: It should return in a few minutes at most, especially as the focus is just "Missing Indexes"

Run on both SQL 2014 Enterprise and SQL 2016 Developer (SP1 CU2) - Windows Server 2008R2 and Windows Server 2016 respectively.

Suggestions

  1. Around line 1453,

IF NOT EXISTS ( SELECT 1 FROM tempdb.sys.indexes WHERE name = 'CLIX_#IndexColumns' ) CREATE CLUSTERED INDEX CLIX_#IndexColumns ON #IndexColumns ( database_id, object_id, index_id );

  1. At around 1750: RAISERROR(N'Populate #PartitionCompressionInfo.', 0, 1) WITH NOWAIT; Make the Compression information optional: IF @Mode <> 3 BEGIN ... END

  2. There may be many opportunities to skip a number of information retrieval/UPDATEs when @Mode = 3, just to make the proc faster.

BrentOzar commented 7 years ago

Oh, goodness. Alright, well, that's definitely an interesting challenge. This wouldn't be something we'd code, but it sounds like you've got a good test case there, and a good idea of what you'd want to change. Do you want to go ahead and start tackling the code? If so, check out the Contributing.md in the root folder.

ghost commented 7 years ago

Well, that'll be a first! Never done a thing on GitHub... Guess I should read a User Guide.

I can say that the added index and branch renders a result in 38 seconds on that DB, in 2 environments on separate SQL instances and versions...

BlitzErik commented 6 years ago

Howdy,

Are you still working on this? Are you stuck with something? Just checking in on older issues that we can possibly close out.

Thanks!

ghost commented 6 years ago

It's still very clearly an issue. I'm a bit surprised nobody else has run into the problem - 240,000 columns isn't that rare, I'd venture.

I've absolutely ZERO idea how to do anything at all on github even after grubbing around for something that may give some instruction... For someone who's being developing SQL for over 30 years, it hasn't proven to be a very intuitive process, but then I could simply have not been looking in the right places!

Neither, realistically, do I have the confidence to muck with a very public tool. All the changes I suggested are in the version I've modified for internal use, and they don't appear to fail, but that may not mean a lot as I rarely use the non-customer-deployed version - far more likely to use a version on one of the score and more customer servers in my charge, and they have older (March 2017) versions.

So, "STUCK" would be a very apt description... Stuck clueless, would be appropriate, too, in regards to github.

Sorry to disappoint. I've attached the version I changed, if that may help with a code merge... I added a little to the version identification to distinguish it from the community version, in case it blew up in my face...

Best regards,

Stephen

From: BlitzErik notifications@github.com Sent: Tuesday, November 21, 2017 12:23:12 PM To: BrentOzarULTD/SQL-Server-First-Responder-Kit Cc: SAinCA; Assign Subject: Re: [BrentOzarULTD/SQL-Server-First-Responder-Kit] sp_blitzindex - no index on #IndexColumns means performance very slow (#1149)

Howdy,

Are you still working on this? Are you stuck with something? Just checking in on older issues that we can possibly close out.

Thanks!

— You are receiving this because you were assigned. Reply to this email directly, view it on GitHubhttps://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FBrentOzarULTD%2FSQL-Server-First-Responder-Kit%2Fissues%2F1149%23issuecomment-346149609&data=02%7C01%7Cstephen_anslow%40hotmail.com%7C3c14893d7a0d4e7124d308d5311db1d3%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636468925956758393&sdata=ZatUmK9rRzRmDK8sR3X1yfIBw9vlGu8YlNmGra9e8Jg%3D&reserved=0, or mute the threadhttps://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FANaU7UvMnmas58CBS9ng1cIPCSRvLvcjks5s4zEwgaJpZM4PzNWj&data=02%7C01%7Cstephen_anslow%40hotmail.com%7C3c14893d7a0d4e7124d308d5311db1d3%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636468925956758393&sdata=xpwhMwUIuQ%2BnRM4yk0Jv6agpKsBucc1gMUl2NkNa2%2B4%3D&reserved=0.

BlitzErik commented 6 years ago

Hi there,

I don't think an attachment came through.

I'll close this out for now.

Thanks!

ghost commented 6 years ago

In case it proves to be of any use, and zip files are accepted on your end........ Attached as a zip this time

From: BlitzErik notifications@github.com Sent: Sunday, November 26, 2017 11:29:17 AM To: BrentOzarULTD/SQL-Server-First-Responder-Kit Cc: SAinCA; Assign Subject: Re: [BrentOzarULTD/SQL-Server-First-Responder-Kit] sp_blitzindex - no index on #IndexColumns means performance very slow (#1149)

Hi there,

I don't think an attachment came through.

I'll close this out for now.

Thanks!

— You are receiving this because you were assigned. Reply to this email directly, view it on GitHubhttps://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FBrentOzarULTD%2FSQL-Server-First-Responder-Kit%2Fissues%2F1149%23issuecomment-347032093&data=02%7C01%7Cstephen_anslow%40hotmail.com%7Cbcbe1d565f0a4517b16308d53503fcbc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636473213589583383&sdata=M%2F0pNTos%2FrtdUtEbOZK9DVJ5hzWXRTO6NyabpPkdXo0%3D&reserved=0, or mute the threadhttps://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FANaU7e5sETyNdz-aT73Fx8aClQkqDh9Cks5s6bwMgaJpZM4PzNWj&data=02%7C01%7Cstephen_anslow%40hotmail.com%7Cbcbe1d565f0a4517b16308d53503fcbc%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636473213589583383&sdata=34uZNcETnYnVpF959jidQ2xr2Qx9EEmVQsM7E3cMPvA%3D&reserved=0.

nschonni commented 6 years ago

@SAinCA you can only attach files through the web interface. Attachments through email isn't supported by GitHub

ghost commented 6 years ago

sp_BlitzIndex.zip

ghost commented 6 years ago

GitHub know-nowt has added the file the right way - thanks @nschonni

BlitzErik commented 6 years ago

Thanks! So the only difference is the index create on #IndexColumns? Nothing else? If so, this should be easy to work in.

ghost commented 6 years ago

That’s all I did for our version, yes.

From: BlitzErik notifications@github.com Sent: Monday, November 27, 2017 7:01:35 PM To: BrentOzarULTD/SQL-Server-First-Responder-Kit Cc: SAinCA; Mention Subject: Re: [BrentOzarULTD/SQL-Server-First-Responder-Kit] sp_blitzindex - no index on #IndexColumns means performance very slow (#1149)

Thanks! So the only difference is the index create on #IndexColumns? Nothing else? If so, this should be easy to work in.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FBrentOzarULTD%2FSQL-Server-First-Responder-Kit%2Fissues%2F1149%23issuecomment-347398933&data=02%7C01%7Cstephen_anslow%40hotmail.com%7Ceab754463c6c40a56bbf08d5360c566a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636474348966636085&sdata=uHP6vkSBXL0jxylgnUyETxr8j0SSkcMHXSC9iML0dso%3D&reserved=0, or mute the threadhttps://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FANaU7aB-ux4dgTa7dtbdWfSCp8adWvijks5s63ePgaJpZM4PzNWj&data=02%7C01%7Cstephen_anslow%40hotmail.com%7Ceab754463c6c40a56bbf08d5360c566a%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636474348966636085&sdata=2ofUSj5yAecfPZSwdTOAtlTSVQ9KSDyl5HRvNrCz%2FU8%3D&reserved=0.