如何使用图像字段提高SQL Server表的性能? [英] How to improve performance in SQL Server table with image fields?

问题描述

我在工作中遇到一个非常特殊的性能问题！

I'm having a very particular performance problem at work!

在系统中，我们使用一个表来保存有关当前工作流程的信息.其中一个字段包含一个电子表格，其中包含有关该过程的元数据(不要问我为什么！而且我不能更改它！)

In the system we're using there's a table that holds information about the current workflow process. One of the fields holds a spreadsheet that contains metadata about the process (don't ask me why!! and NO I CAN'T CHANGE IT!!)

问题在于此电子表格存储在SQL Server 2005的IMAGE字段中(在具有SQL 2000兼容性的数据库中).

The problem is that this spreadsheet is stored in an IMAGE field in an SQL Server 2005 (within a database set with SQL 2000 compatibility).

此表当前有22K +行，甚至是一个简单的查询，如下所示:

This table currently has 22K+ lines and even a simple query like this:

SELECT TOP 100 *
  FROM OFFENDING_TABLE

需要30秒才能在Query Analyser中检索数据.

Takes 30 seconds to retrieve the data in Query Analyser.

我正在考虑更新与SQL 2005的兼容性(一旦得知该应用程序可以处理它).

I'm thinking about updating the compatibility to SQL 2005 (once that I was informed that the app can handle it).

我想考虑的第二件事是将列的数据类型更改为varbinary(max)，但是我不知道这样做是否会影响应用程序.

The second thing I'm thinking is to change the data-type of the column to varbinary(max) but I don't know if doing this will affect the application.

我正在考虑的另一件事是使用sp_tableoption将large value types out of row设置为1，因为它当前是0，但是我不知道这样做是否可以提高性能.

Another thing that I'm considering is to use sp_tableoption to set the large value types out of row to 1 as it's currently 0, but I have no information if doing this will improve performance.

有人知道在这种情况下如何提高性能吗?

Does anyone know how to improve performance in such scenario?

经过编辑以澄清

我的问题是我无法控制应用程序向SQL Server发出的请求，并且对此进行了一些思考(该应用程序是.NET 1.1网站)，并且它使用了令人反感的字段来处理某些内部问题不知道那是什么.

My problem is that I have no control on what the application asks to the SQL Server, and I did some Reflection on it (the app is a .NET 1.1 website) and it uses the offending field for some internal stuff that I have no idea what it is.

我需要改善此表的总体性能.

I need to improve the overall performance of this table.

推荐答案

我建议您调查有问题的表格布局运行状况:

I'd recommend you look into the offending table layout health:

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

寻找的内容还有avg_fragmentation_in_percent，page_count，avg_page_space_used_in_percent，record_count和ghost_record_count.诸如高碎片化，大量幽灵记录或低页面使用率之类的提示表明存在问题，并且只需从头开始重建索引(即表)就可以大大改善问题:

Things too look for are avg_fragmentation_in_percent, page_count, avg_page_space_used_in_percent, record_count and ghost_record_count. Cues like high fragmentation, or a high number of ghost records, or a low page used percent indicate problems and things can be improved quite a bit just by rebuilding the index (ie. the table) from scratch:

ALTER INDEX ALL ON offending_table REBUILD;

我说这是考虑到您不能更改表或应用程序.如果您能够更改表和应用程序，那么您已经得到的建议就是很好的建议(不要使用"*"，不要选择没有条件，使用更新的varbinary(max)类型，等等)

I'm saying this considering that you cannot change the table nor the app. If you'd be able to change the table and the app, the advice you already got is good advice (don't use '*', dont' select w/o a condition, use the newer varbinary(max) type etc etc).

我还将研究性能计数器中的平均页面寿命，以了解系统是否内存不足.从对症状的描述来看，系统看起来是IO受限的，这使我认为正在进行的页面缓存很少，更多的RAM可以提供帮助，以及更快的IO子系统.在SQL 2008系统上，我也建议打开页面压缩功能，但在2005年则不能.
并且，请确保没有被应用本身争用阻止查询，即.查询不会花费那30秒中的90％等待行锁定.在查询运行时，查看 sys.dm_exec_requests ，请参见wait_time ，wait_type和wait_resource.是PAGEIOLATCH_XX吗?还是锁?另外，服务器中的 sys.dm_os_wait_stats 是什么?最需要等待的原因?

I'd also look into the average page lifetime in performance counters to understand if the system is memory starved. From your description of the symptomps the system looks IO bound which leads me to think there is little page caching going on, and more RAM could help, as well as a faster IO subsytem. On a SQL 2008 system I would also suggest turning page compression on, but on 2005 you can't.
And, just to be sure, make sure the queries are not blocked by contention from the app itself, ie. the query doesn't spend 90% of that 30 seconds waiting for a row lock. Look at sys.dm_exec_requests while the query is running, see the wait_time, wait_type and wait_resource. Is it PAGEIOLATCH_XX? Or is it a lock? Also, how is the sys.dm_os_wait_stats in your server, what are the top wait reasons?

这篇关于如何使用图像字段提高SQL Server表的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！