September 25, 2015 at 12:27 pm
Hello all. I have a strange one that i have never encountered and at this point am totally lost. We have a proc that adds some fields to a few tables of ours and normally there are no issues. For one of our client databases this process is taking anywhere from 5-10 minutes to add the fields. This causes an issue where the app will timeout waiting. After plugging around and looking at the proc and trying different items i found it to only be for this one database and ONLY when there is data in the table. If i truncate the table and run the same procedure everything is fine. Anyone have any idea? Tables all have same index on 4 columns and the columns being added are not indexed because of the stupid hoops we have to jump thru to pre-pivot data for our reporting package. Please help i am totally lost lol.
September 25, 2015 at 12:41 pm
zulmanclock (9/25/2015)
Hello all. I have a strange one that i have never encountered and at this point am totally lost. We have a proc that adds some fields to a few tables of ours and normally there are no issues. For one of our client databases this process is taking anywhere from 5-10 minutes to add the fields. This causes an issue where the app will timeout waiting. After plugging around and looking at the proc and trying different items i found it to only be for this one database and ONLY when there is data in the table. If i truncate the table and run the same procedure everything is fine. Anyone have any idea? Tables all have same index on 4 columns and the columns being added are not indexed because of the stupid hoops we have to jump thru to pre-pivot data for our reporting package. Please help i am totally lost lol.
How about some details? Things like the table definition, including indexes and the script you are running to add the new columns. Without some details this is not a complete question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2015 at 12:50 pm
Sean Lange (9/25/2015)
zulmanclock (9/25/2015)
Hello all. I have a strange one that i have never encountered and at this point am totally lost. We have a proc that adds some fields to a few tables of ours and normally there are no issues. For one of our client databases this process is taking anywhere from 5-10 minutes to add the fields. This causes an issue where the app will timeout waiting. After plugging around and looking at the proc and trying different items i found it to only be for this one database and ONLY when there is data in the table. If i truncate the table and run the same procedure everything is fine. Anyone have any idea? Tables all have same index on 4 columns and the columns being added are not indexed because of the stupid hoops we have to jump thru to pre-pivot data for our reporting package. Please help i am totally lost lol.How about some details? Things like the table definition, including indexes and the script you are running to add the new columns. Without some details this is not a complete question.
Its a little hard to give table definition. There are 5 fields that are standard for each table. Every other field is based on each clients different scenarios. If they add a new earnings code or a tax or deduction this newly created code needs to be added to these tables. Each table has one nonclustered index that is on the 5 standard fields. Each new field is a decimal(18,8) SPARSE NULL.
So what happens is the company enters a new earnings code. The table which this gets inserted to fires off a trigger which calls a procedure that adds these fields using dynamic sql( i did not build any of this but am tasked to fix when it breaks) It has to use dynamic sql because each client can use what ever name/code they want and it has to be added in a specific way for the reporting program we use to be able to use the values from these tables. Below is a small sample of one of the tables. Now each time a new code is entered it is added to the table.
CREATE TABLE [rpt].[FactEmployeeCheckEarningsYTDPivot](
[coCode] [varchar](6) NULL,
[Pay Run Number] [int] NULL,
[empID] [int] NULL,
[checkNumberInPayRun] [int] NULL,
[Record As Of Date] [datetime] NULL,
[41-RETRO YTD Amount] [decimal](18, 8) SPARSE NULL,
[96-1099$$ YTD Amount] [decimal](18, 8) SPARSE NULL,
[4U-AUTO 6 YTD Amount] [decimal](18, 8) SPARSE NULL,
[GSH-HOLIDAY YTD Amount] [decimal](18, 8) SPARSE NULL,
[97-1099$$ YTD Amount] [decimal](18, 8) SPARSE NULL,
[COMMPD-COMM 1 YTD Amount] [decimal](18, 8) SPARSE NULL,
[38-PNL-HOL YTD Amount] [decimal](18, 8) SPARSE NULL
September 25, 2015 at 12:51 pm
This could be due to page splitting and table size. I'm not sure if rebuilding the clustered index with a lower fill factor could prevent this issue.
October 8, 2015 at 1:31 pm
Adding columns to a table requires an exclusive table level lock, so it may be in a blocked state.
Also, if the columns have a default value, then it's an increase in row size, so unless there is enough page padding, pages across the entire table get shuffled around.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply