August 4, 2011 at 1:39 am
Ninja's_RGR'us (8/3/2011)
How many GBs for the heap the db?
I'm trying to run the script but it seems to take a long time (ran for 10 mins before i decided to cancel it). I think this could well be having an effect here since the current data load routine is as follows:
3 times a day data is loaded from files that contain up to 2 months data.
All data that is contained in the files is deleted from the table
All Indexes dropped on table
All data that is contained in the files is inserted into the table
All Indexes rebuilt
This has been building up over some time... 18 months i think.
August 4, 2011 at 2:09 am
Ninja's_RGR'us (8/3/2011)
Before adding the index I'd like if you could run that script for that table and post the results back... I want to see if that script can pick up that issue.
TableName = F_Sales
NumRows = 14762625
InRowDataSizeMB = 13327.1328125
LOBDataSizeMB = 0
OFlowDataSizeMB = 0
AllUsedPages = 1705873
AllPages = 1715365
FreeDataSpaceMB = 74.15625
AllDataSizeMB = 13327.1328125
IndexSizeMB = 0
TableSizeMB = 13401.3
UserRequests_Cnt = 1312
UserUpdates_Cnt = 96076
LastUpdate = 40758.5087475694
PercentofDB = 11.5979
DatabaseSize = 114909.19
August 4, 2011 at 5:44 am
Thanx for the update.
So do you still need help with this.
August 4, 2011 at 6:39 am
Ninja's_RGR'us (8/4/2011)
Thanx for the update.So do you still need help with this.
We are trying to rebuild the table into a new blank table. It seems to have relieved the issue with index creation time as that is back within 'normal' time ranges now.
We will continue testing with a regular data load etc but I'd really like to understand why this has happened. Given the figures i have posted above, do you think the issue you were trying to identify is present or not? I assume that the figure in question was the FreeDataspaceMB? 74MB is not a massive amount i guess.
Regards,
Chris
August 4, 2011 at 6:44 am
Well this is a table with 13GB of data in it. I don't know what you call slow, normal and fast, but any index built on this will require some <extensive> time.
For the 1 off difference I'd suggest you look at competition for ressources (other backup, AV, etc). Anything that takes a crapload of disks or cpu.
Could simply be that tempdb was growing which takes time. You can check the default trace for that one.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply