Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dugalh
Starting Member
3 Posts |
 Posted - 2005-03-29 : 09:59:35
|
| HiI 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
15732 Posts |
Posted - 2005-03-29 : 10:20:08
|
| 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. |
 |
|
|
dugalh
Starting Member
3 Posts |
Posted - 2005-03-29 : 11:44:20
|
| 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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-29 : 14:00:54
|
| 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. |
|
|
|
dugalh
Starting Member
3 Posts |
Posted - 2005-03-30 : 11:49:30
|
| 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. |
|
|
|
|
|
|
|
|
Insert timeout on image table