Image in SQL 2005

[theresatan]

Hi,

Does anyone know SQL 2005 have any improvement the performance (read/write) of image/text stored in table?

As my experience, saving the image/text in folder and using a pointer in sql table has the best performance in SQL 2000.

My situation is:
Cluster server, 4 processors, 6 G memory, instance have one database only.

We have a lot of data including image/text imported to SQL daily.
The import tool is a C# App. It inserts data to SQL row by row (open connection, inserting data, close connection through a loop)

In SQL 2000, we saved image/text in folder without any problem
Since upgrading to SQL 2005, we save the image/text in SQL table.

I got following in SQL ERRORLOG many times per day:

A significant part of sql server process memory has been paged out. This may result in performance degradation. Duration: 302 seconds.
************************************************************************
*
* BEGIN STACK DUMP:
* 12/04/07 06:22:47 spid 0
*
* Non-yielding Resource Monitor
*
* ************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump

I also get SQL server alert many times per day:
SQL Server Alert System: '020 - Fatal Error in current Process' occurred on XXX server

I told developer that they need to address this issue, but no one buys it because there is no problem in SQL 2000, neither is 2005. They ignore the changes they made.

I need your input:
It is storage issue (text/image in SQL table) or
It is loop issue or
Both or
Something else

Thanks in advance!

Theresa

 

[mrdenny]

It's always best to keep the blob data out of the database. If you are going to store it within the database you have to plan very carefully and setup some very fast storage. Also do keep in mind that SQL will take buffer cache space for the blob data which you will probably want to use for your regular data instead.

Doing anything row by row within SQL Server will be slower than a batch import. Try changing the import from a row by row import to a bulk insert. You'll probably get better insert performance this way.

If it was my system, I'd move the blob data back to the file system. This is where it belongs 99% of the time anyway.

If you have Enterprise Edition set the lock pages in memory option. This should prevent SQL from paging anything to the disk. If you have standard edition this isn't available to you.

How much memory is dedicated to SQL Server?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog