February 9, 2023 at 4:36 pm
Hi all,
I inherited an app schema that is shared across 140+ different DBs [one for each client; they all have their own data but the schemas are otherwise exactly the same]. I am their tsql dev, and have been doing a lot of performance enhancements. One thing I'm running into is that one of the largest tables in the schema (both by data size/record count as well as width) is frequently queried against, and at least three of cols used for filters that are used in nearly every query are varchar(max) fields; ridiculous, as the largest value in any of those fields is 50 char.
I keep running into problems where my execution plans would benefit from a query with those as key cols, but varchar(max) columns cannot be used in key columns. So, I'm on a mission this release to reduce these columns to something more reasonable, like varchar(50). This wouldn't be so difficult if I knew the indexes in place that has these fields in the includes portion of the index, however, I don't. We have had auto-create indexes on since before I was hired. I cannot be sure that there are no indexes out there in an auto created index, so altering the column could result in an error without accounting for that, right?
Have you encountered this issue before? How have you dealt with it? I have basically created a tsql script that finds the indexes that has the cols, builds/exec tsql to drop those, do the column size change, then build/exec tsql that will recreate the indexes, moving the field from the includes to the key. This, however, seems also pretty ridiculous and I frankly feel like a madman scrawling insane gibberish onto their bedroom walls when I'm writing this stuff because there has to be a better way... right?
Basically I feel insane trying to account for objects that may or may not exist, and then also altering those objects. Does anyone have any advice or guidance around this?
FWIW, my experience with TSQL mostly comes from SQL Server, not Azure SQL, so I am not super familiar with auto-create indexes, etc.
Thanks for your time
February 10, 2023 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 13, 2023 at 7:20 am
I have had to do similar things, such as changing the clustered index column or 'correcting' varchar(max) to varchar(pick-a-number). Sad to say my bedroom wall is covered in scribblings, I never found a better way than what you have done. Some jobs are just plain hard but necessary.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 23, 2023 at 4:33 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply