March 13, 2010 at 4:59 pm
SQL Server 2005 all the latest service packs
Windows server 2003 32Bit 4GB RAM Lots of harddisk space all the latest service packs.
Database size 76GB and growing
MediaBlob table about 4.6GB (260 rows) images saved as jpg typically betwen 20 and 30KB each.
We send photos from a cell phone to an aspx web service that saves the files to disk and then stores them to the above mentioned MediaBlob table. So say we get 500 images a day out of the 500 approximately 140 or so are failing to insert due to a database timeout. No other table in the database ever suffers from this problem. There are other tables that have blob data but don't get near the traffic of the mediablob (photos) table. We have many officers sending upto 6 photos (typically 2-3) to the database all day long.
We have profiled the database and it only verifies that indeed there are timeout occuring. I have Googled until I am cross-eyed and hope someone has some suggestions.
Questions:
1. Could autogrow becausing the database to be busy due to how active the mediablob table is?
2. The files are being stored to disk from HttpPostedFile.Saveas, could this be holding on to the file
while we are attempting to insert it to the database?
3. Some obscure database setting related to using lots of varbinary(MAX) inserts?
Any ideas?
Thanks
March 13, 2010 at 6:34 pm
If you are allowing your database to autogrow - that would definitely be an issue. You should manually grow the file out, monitor and grow it manually as needed.
You will want to make sure you have instant initialization enabled also. That will help.
And finally, what are the indexes on that table? You could be hitting a lot of page splits if it is not correct.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2010 at 6:55 pm
I will look into changing the autogrow configuration.
I am not familiar with instant initialization so I will have to look that up.
The table has two indexes (both Non-Unique, Non-Clustered):
1. Author (int) column (person sending the photos)
2. Document (int) column (document that the photos are associated to)
By page splits do you mean fragmentation?
I just looked at the code and I just also realized that given a list of images we are opening and closing the database connection for each image could that exasterbate the problem?
PS: I am a programmer trying to help our DB guy figure this out so sorry if I am not as technical about database issues as you would probably hope that I am.
March 13, 2010 at 8:03 pm
smesser (3/13/2010)
I will look into changing the autogrow configuration.I am not familiar with instant initialization so I will have to look that up.
The table has two indexes (both Non-Unique, Non-Clustered):
1. Author (int) column (person sending the photos)
2. Document (int) column (document that the photos are associated to)
By page splits do you mean fragmentation?
I just looked at the code and I just also realized that given a list of images we are opening and closing the database connection for each image could that exasterbate the problem?
PS: I am a programmer trying to help our DB guy figure this out so sorry if I am not as technical about database issues as you would probably hope that I am.
By page splits - I mean fragmentation. However, you are using a HEAP which cannot be defragmented. If you do not update on a regular basis, I would recommend adding a clustered index on an IDENTITY column and making that the primary key.
And definitely, opening a connection to the database is an expensive operation and that could be contributing to your timeout issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2010 at 8:18 pm
By page splits - I mean fragmentation. However, you are using a HEAP which cannot be defragmented. If you do not update on a regular basis, I would recommend adding a clustered index on an IDENTITY column and making that the primary key.
And definitely, opening a connection to the database is an expensive operation and that could be contributing to your timeout issues.
Thanks for the input I will update this thread in case other have this type of issue once we find out which changes help.
March 14, 2010 at 2:24 pm
Having thought some more about your suggestion while I am sure they are good suggestions I don't think they can account for our problems other than the reusing the database connections.
If autogrow increases the database size by ten percent. Then unless we are autogrowing every hour during the day and evening I don't think these setting will fix our timeout issue.
Maybe I missunderstand how autogrow works. Istant initialzation would only come into play during a database resize. Since our database is about 76GB and we arent' growing by 10% per day there must be other issues at play.
Just thinking out loud I guess.
March 14, 2010 at 5:16 pm
smesser (3/14/2010)
Having thought some more about your suggestion while I am sure they are good suggestions I don't think they can account for our problems other than the reusing the database connections.If autogrow increases the database size by ten percent. Then unless we are autogrowing every hour during the day and evening I don't think these setting will fix our timeout issue.
Maybe I missunderstand how autogrow works. Istant initialzation would only come into play during a database resize. Since our database is about 76GB and we arent' growing by 10% per day there must be other issues at play.
Just thinking out loud I guess.
Not necessarily - what is the autogrowth set to? If it is set to 10% then the autogrowth is going to try and grow the file by 7.6GB. That will take some time. You really shouldn't leave autogrowth at 10% anyways. It should be set to a fixed value like 1 or 2GB depending upon your rate of growth. You only want autogrowth to happen on a rare occasion and enough to handle a couple days/weeks/months of growth.
The process that is running at the time the autogrowth event is triggered has to wait. If that process times out, the autogrowth event is rolled back and the next process will trigger the event again.
Regardless, you should not be relying on autogrowth to manage the size of your database. You should be monitoring the space used and scheduling a time when you can manually grow the file.
If you have already set up instant initialization, then that probably is not a factor - but it still could be based upon how long it would take to grow the file.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 14, 2010 at 7:33 pm
The process that is running at the time the autogrowth event is triggered has to wait. If that process times out, the autogrowth event is rolled back and the next process will trigger the event again.
I am listening just playing devils advocate.
That would make more since if the whole database was having timeouts not just one table wouldn't it?
March 14, 2010 at 10:12 pm
Well, that depends on the table. Sounds like this table is the most active table in the database - but you would know better than I 😉
Remember, there could also be issues with the inserts causing page splits. Using a HEAP I wouldn't expect that to be an issue, but if there were a lot of indexes (doesn't sound like it) or a lot of data movement, or maybe blocking occurring from other processes - any of these kinds of issues could cause timeout issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2010 at 10:41 am
Don't forget about the disk subsystem. What are the queue lengths? How do the overall disk counters look?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply