September 16, 2015 at 12:56 pm
Our vendor changed column widths on almost every column in the entire database. The database is around 7TB, 9000+ tables. We are trying to create an index on a table that has 5.5billion rows. Before the vendor's upgrade we could create the index in 2 hours. Now it takes days. What they have done is increase any varchar(xx) size to varchar(256). So most columns used to be varchar(18) or varchar(75), etc.
Anyway the primary key consists of 6 columns that combined width was 126 characters. Now after the upgrade, the primary key is 1283 characters which violates SQL Servers limit of 900 characters. The entire table column width went from a total combined varchar count of 1049 to a total combined varchar count of 4009.
There is not an increase in data, the table doesn't take up any more "space" than it did before all the column width increase, but performance to create something as simple as an index is now taking an unreasonable amount of time.
Can anyone explain why SQL Server is reacting differently when the size of the columns increased?
September 16, 2015 at 4:45 pm
When the column is bigger, the index is bigger. Bigger indexes take more space on the disk and take longer to create.
September 16, 2015 at 8:05 pm
agerard (9/16/2015)
When the column is bigger, the index is bigger.
Yeah, but the data (in the key columns for the index) isn't any bigger so surely "no change" in that regard?
Longshot:
Memory allocated for max possible column width, and something thrashing as a consequence?
September 17, 2015 at 5:49 am
Kristen-173977 (9/16/2015)
agerard (9/16/2015)
When the column is bigger, the index is bigger.Yeah, but the data (in the key columns for the index) isn't any bigger so surely "no change" in that regard?
Longshot:
Memory allocated for max possible column width, and something thrashing as a consequence?
This is also what I'm wondering and am hopeful that there is someone out there who understands the internals of SQL Server enough to be able to explain what is happening inside SQL Server when a column is created with a huge width, but has very little data in the column.
Especially when the vendor is violating the 900 max char for primary key.
September 17, 2015 at 9:53 am
Clearly if the only thing that has changed is the size of the column--even if it's a variable length column--that is causing the problem.
Use this query to check the size of your indexes. Make 2 copies of the table, one with shorter length columns and one with longer. See which has a larger index. If they're identical then I'm wrong...
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id
September 17, 2015 at 10:40 am
agerard (9/17/2015)
if the only thing that has changed
Good point. Worth double checking nothing else has changed? Fill Factor for example.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply