How to improve performance in a SQL Server table with image fields?

Question

I have a very difficult problem with work!

In the system, we use a table that contains information about the current workflow process. One of the fields contains a table containing metadata about this process (don’t ask me why !! AND NO, I CAN’T CHANGE THIS !!)

The problem is that this table is stored in the IMAGE field in SQL Server 2005 (in a database installed with SQL 2000 compatibility).

This table currently has 22K + rows and even a simple query:

SELECT TOP 100 * FROM OFFENDING_TABLE 

Gets 30 seconds to retrieve data in Query Analyzer.

I am thinking about updating compatibility with SQL 2005 (after I was informed that the application can handle it).

Secondly, I want to change the column data type to varbinary(max) , but I do not know if this will affect the application.

Another thing I am considering is to use sp_tableoption to set large value types out of row to 1 , as it is currently 0 , but I have no information if this improves performance.

Does anyone know how to improve performance in such a scenario?

---

Edited to clarify

My problem is that I do not control what the application requests from SQL Server, and I did some reflection on it (the application is a .NET 1.1 website) and it uses a field for some internal things, which I have no idea I have what it is.

I need to improve the overall performance of this table.

Answer 1

I would recommend you look into the health of the offending table:

 select * from sys.dm_db_index_physical_stats( db_id(), object_id('offending_table'), null, null, detailed); 

Things that are also looking for are avg_fragmentation_in_percent, page_count, avg_page_space_used_in_percent, record_count and ghost_record_count. Signals such as high fragmentation or a large number of ghost entries, or a low percentage of used pages indicate problems, and something can be improved by simply restoring the index (i.e. the Table) from scratch:

 ALTER INDEX ALL ON offending_table REBUILD; 

I am talking about this, given that you cannot change the table and application. If you can change the table and application, the advice that you already have is good advice (do not use '*', do not select the w / oa condition, use the new type varbinary (max), etc. Etc .) ..

I would also look at the average page life in performance counters to see if the system is starving. From your description of the symptoms, the system looks at IO bound, which makes me think that a little page caching is happening, and more RAM can help, as well as a faster I / O subsystem. In SQL 2008, I also propose to enable page compression, but in 2005 you cannot. And to be sure, make sure that requests are not blocked by rivalry from the application itself, i.e. the request does not spend 90% of these 30 seconds of waiting for a row lock. Look at sys.dm_exec_requests at query time, look at wait_time, wait_type and wait_resource. Is this PAGEIOLATCH_XX? Or is it a castle? Also, like sys.dm_os_wait_stats on your server, what are the reasons for waiting for a wait?

Answer 2

First of all - never make SELECT * in the production code - messages or not.

You have three main options:

• move this blob field to a separate table if this is not always necessary; probably not practical since you mention that you cannot change the circuit

• be careful with SELECT to select only those fields that you really need and omit the blob field

• see if you can limit your query to include a WHERE and find a way to optimize the query plan, for example. adding a suitable index to the table (if possible)

There is no magic “make it faster” switch, but you can optimize your query or optimize your table layout. Both help. If you can’t change anything - neither the layout of the table, nor add an index, or change queries, it will be difficult for you to optimize something, I'm afraid ....

A simple field change in VARBINARY (MAX) will not change anything - performance improvements are not expected simply because of a data type change.

Answer 3

The short answer is only to do SELECT against only a few lines when the returned fields do not include the image field with the error, i.e. SELECT *. If you want to get the value of the image field, extract it in each case.

Answer 4

Setting parameters of large values ​​from a string can definitely help to improve performance. The row size will be significantly smaller; SQL Server can do much less physical readings to go through the table.