SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Insert timeout on image table
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

dugalh
Starting Member

3 Posts

Posted - 03/29/2005 :  09:59:35  Show Profile
Hi

I have a table with 4 image columns, some other varchar and int columns and an int primary key. I run a test that repeatedly inserts 2 images of about 16K each, plus data for half of the other fields, into the table. Everything works fine for somewhere between 20000 and 300000 inserts and then (without any gradual performance degradation) it suddenly comes to a halt. Any attempted insert then times out. The perfomance monitor shows ridiculous ammounts of disk traffic but nothing to suggest its cause (at least to my inexperienced eye). The only way I have found of recovering from this situation is to rebuild the primary key index (an INDEXDEFRAG does not help). This seems a bit strange as the SHOWCONTIG reports a scan density of 95% on this index. I am new to Sql Server and databases in general - please give me some advice.

Thanks

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/29/2005 :  10:20:08  Show Profile  Visit robvolk's Homepage
Image and text data are stored on separate (8K) pages for each ROW of data. It's no wonder you're seeing a lot of disk activity, and none of this will be reflected in the SHOWCONTIG values nor be improved by REINDEX or INDEXDEFRAG. Books Online has more details on this.

This is the nature of storing this kind of data in SQL Server. You should consider NOT storing it in image columns, but rather as files on disk.
Go to Top of Page

dugalh
Starting Member

3 Posts

Posted - 03/29/2005 :  11:44:20  Show Profile
Thanks - unfortunately storing images in the filesystem is not really an option - we need the features of the database for the images too.

I am aware that image data is stored in separate pages but this does not explain why an insert of about 32K of data should take longer than 10 secs, or why the performance is perfectly acceptable for 20000+ consecutive inserts and then suddenly dies and stays dead, or why "CREATE INDEX WITH DROP_EXISTING" is the only thing I can find that will fix it.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/29/2005 :  14:00:54  Show Profile  Visit robvolk's Homepage
Image columns are not indexed at all, and are not affected by any indexes on the table. You can add or drop all the indexes you want and it won't make a difference. The image/text data is stored on any (random) page in the data files. The only reason you may see an improvement with DROP EXISTING is that it will rebuild the clustered index and defragment it. It still doesn't affect the existing image/text data pages, which can be (and probably are) scatterd all over the place, and the read/write pattern will essentially be random and not sequential. Having four image columns only makes it four times worse.

You may see some improvement by putting the image data on a separate filegroup, but that's about the only option you have. Short of saving them as files, that is.
Go to Top of Page

dugalh
Starting Member

3 Posts

Posted - 03/30/2005 :  11:49:30  Show Profile
I put the image data in a separate file group on a separate disk and there was a general performance improvement. The problem I was observing was however not related to fragmented images. The timeout was occurring when the data file was large and was being auto-grown (by percent). (I had,in my inexperience, used the default data file settings.) Somehow it seems the auto-grow occurs as part of the insert query that reaches the end of the file size limit i.e. if that query times out, the auto-grow doesn't complete and then all subsequent insert's time out while attempting the auto-grow. With the data file space allocated to the disk size, I no longer have the timeout problem.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000