November 11, 2020 at 10:21 am
Hi, there I have a SQL Server 2008 R2 database and recently I have had major performance issues (I am not a DB admin).
The database is connected to a CRM and the CRM users call many queries through the CRM interface on the various tables in their day to day work. I was made aware of the issue when user of the CRM reported slow running reports.
I’ve spent several weeks troubleshooting this looking at server issues, CRM code issues, Microsoft application issues… literally everything I could think of. Then I checked the size of the primary table that was being reported on, this table I found to have 540 columns (I didn’t create this took it over). I thought that was quite a lot, so I setup a test system so that I could run a few tests, I randomly thought cut the table columns down by approx. 25%, I randomly selected the columns and deleted them, then tested the reports and they started running better! I thought great, I have a potential cause of the problem at least. I naively thought too many columns. More research has lead me to understand this is not the cause but part of it to a degree. I went back to the DB owner and said we need to scale back the columns, many are redundant anyway so a good time to do a clean-up. So we found 150 random unused columns and deleted them. I ran some test reports, the reports were still very slow, my potential solution didn’t work! I then deleted more columns, ran the reports, they worked again. I then started digging about max sizes of data in SQL 2008 tables and came across this on ibm.com.
“You are limited to 8060 bytes of data per row. ”
Reading this I realised, or assumed that when I randomly deleted the fields initially I must of stripped the rows back so that they were under the 8060 bytes limit. I as I say, I’m certainly not a DB admin and just stumbling across things here.
So I’m here for some assistance with this so that I can confirm my suspicions, see if there is anything I can do to be more accurate in diagnosing the issue with some scripts maybe?! And workout a better solution than randomly deleting fields, maybe I can identify what fields are good and bad?!
Any advice would be greatly appreciated.
November 12, 2020 at 2:57 am
Please see the following link...
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15
... and then search on that page for the words "doesn't reclaim" to answer your questions on why some of your actions made no difference.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply