非常小的桌子非常慢 [英] Very small table incredibly slow

问题描述

您好。


我正在管理SQL Server（Windows 2003上的企业版）

从某个月开始，无法理解将要发生什么在最新的

周（当数据库增长很多时）。


数据库大约250G，有一个10亿行的表。这是一个体面的表现，但不能理解为什么一个特殊的表格

有很强的性能问题。


我有一个存储过程，从表A读取表并插入它们，

在表B中处理后，然后将它们移到其他表中（类似

到一个星号）用于报告的模式。


表B是关于SP的编写方式，不超过3000行。表

B非常简单，有3行，没有索引。


有些奇怪的是表B的性能真的很慢。如果

我从table_b做一个选择计数（*），它需要30s& 2分钟到

返回它有0行。当存储过程插入1000行时，它需要20/30秒才需要20/30秒才能删除它们。


对我来说它没有'看起来像锁定问题，因为当访问该表的唯一过程停止时它也很慢。我在这张桌子上用fullscan进行了更新

统计数据没有任何改进。


数据库位于存储区域网络上，应该可以正常运行。我使用的

LUN配置为使用32块磁盘，也可以由

其他应用程序使用。我没有SAN的性能数据。

机器是HP DL580，带有4个CPU（禁用高速线程），8G内存，


我不知道如何解决这种情况。这可能是一个b $ b腐败问题吗？那桌子这么慢？是不是有可能这个数据库在上周创造了很多问题呢？这个

这个小而简单的桌子呢？


您对如何管理这种情况有任何想法或暗示吗？或者

指向可以帮助分析这种情况的文档？


到达这里的人，谢谢你的时间和

耐心阅读我的坏英语...


最好的问候，

Mamo


PS

我无法重写存储过程，因为它是已关闭的

源产品的一部分。

Hello.

I am administering a SQL Server (Enterprise Edition on Windows 2003)
from some month and can''t understand what is going on in the latest
week (when the db grow a lot).

The DB is around 250G, and has one table with 1 billion rows. It is
performing in a decent way, but can''t understand why a particolar table
has strong performance problem.

I have a stored procedure that read table from table A and insert them,
after processing in table B, and then move them in other table (similar
to a Star Schema) for reporting.

Table B is, for how the SP is written, not more than 3000 lines. Table
B is very simple, has 3 rows, and no index.

What is very strange is that performance of table B is really slow. If
I do a select count (*) from table_b it takes between 30s & 2minutes to
return it has 0 lines. When the stored procedure insert 1000 lines, it
takes 20/30 seconds and it takes 20/30 seconds to delete them.

To me it doesn''t look like a lock problem, because it is slow also when
the only procedure that access that table are stopped. I did an update
statistics with fullscan on this table with no improvement.

The DB is on a Storage Area Network that should perform decently. The
LUN I use is configured to use a piece of 32 disk that are used also by
other application. I don''t have performance data of the SAN. The
machine is an HP DL580 with 4 CPU (hiperthreading disabled), 8G of RAM,
AWE and PAE and 5G reserved for SQL Server.

I don''t know what to do to solve this situation. Could it be a
"corruption problem" that slow this table so much? is it possible the
fact the db grow a lot in the last week created problem also to this
small and simple table?

Do you have any idea or hint on how to manage this situation, or
pointer to documentation that can help in analizing this situation?

For the ones that arrived till here, thank you for your time and
patience reading my bad english...

Best Regards,
Mamo

PS
I can''t rewrite the stored procedure, because it is part of a closed
source product.

推荐答案

首先。请不要使用行。我们中的纯粹主义者将有适合的价格。

First of all. Please don''t use "lines" the purists among us will have
a fit.

表B是关于如何编写SP的，不超过3000线。
TableB非常简单，有3行，没有索引。


表B一次不超过3000行，有3列没有

索引？

如果我这样做来自table_b的选择计数（*）需要30秒和30秒之间。
2分钟返回它有0行。当存储过程插入1000行时，
需要20/30秒，并且需要20/30秒才能删除它们。


a）当你运行这些查询/存储

程序时，我会检查执行计划。

b）检查数据库配置。

只有在查询涉及表B时才会出现减速吗？或者

整个数据库是否缓慢？

表A，B和& C是在同一个数据库中还是在不同的数据库中？

是否有单个或多个数据和日志文件？

有哪些数据库选项？你有什么时髦的东西，比如auto

close或自动收缩或已启用？

数据库是否设置为自动增长？任何最大大小限制？

c）在查询进行时尝试运行sp_who2

这可能是一个腐败问题。那桌子这么慢？是不是有可能这个数据库在上周创造了很多问题，这个小而简单的表也会产生问题？

Table B is, for how the SP is written, not more than 3000 lines. TableB is very simple, has 3 rows, and no index.
Table B has no more than 3000 rows at a time and has 3 columns with no
index?
If I do a select count (*) from table_b it takes between 30s & 2minutes toreturn it has 0 lines. When the stored procedure insert 1000 lines, ittakes 20/30 seconds and it takes 20/30 seconds to delete them.
a) I would check the execution plans when you run these queries/ stored
procedures.
b) Check database configuration.
Does the slowdown only occur when a query involves table B? Or is the
entire database slow?
Is table A, B, & C all in the same database or in separate databases?
Does it have single or multiple data and log files?
What are the database options? Do you have something funky like "auto
close" or "auto shrink" enabled?
Is the database set to autogrow? Any max size restriction?
c) Try running sp_who2 while the query is progress
Could it be a
"corruption problem" that slow this table so much? is it possible the
fact the db grow a lot in the last week created problem also to this
small and simple table?

腐败不太可能。耗尽空间 - 绝对是可能的。

当磁盘阵列接近容量时，

你会遇到神秘的问题。乔布斯会失败。查询需要很长的时间。错误消息，没有任何感觉。检查数组的大小 - 对于数据库和日志和

tempDb。

Corruption unlikely. Running out of space -- definitely a possibility.
When the disk arrays are nearing capacity,
you''ll get mysterious problems. Jobs will fail. Queries take a long
time. Error messages that don''t make any
sense. Check the size of the arrays -- for the database and log and
tempDb.

louis写道：

首先。请不要使用行。我们中间的纯粹主义者将获得
。


对不起....你是对的。

表B一次不超过3000行，有3列
否指数？
是的！

a）当你运行这些查询/
存储过程时，我会检查执行计划。
我检查过它们。减速是在表B中的插入/删除。

b）检查数据库配置。


你会在什么方面检查？

只有在查询涉及表B时才会出现减速？或者是整个数据库的
慢？
数据库速度不快。应用程序有时会超时。但是对于大量数据来说，它也很重要。我不能说它可以表现得更好

微调。是表A，B和& C是在同一个数据库中还是在不同的数据库中？


表A，B，C都在同一个DB中。它有单个还是多个数据和日志文件？


它们位于同一个文件系统中。文件系统位于SAN上。我可以

机器上有一些本地磁盘（SCSI 15K），但我不确定

是否有帮助（SAN专家说我是SAN应该是很多比本地磁盘快b / b
。您如何看待它？

有哪些数据库选项？你有什么时髦的东西，比如
" auto close"或自动收缩或启用？
DB没有自动关闭或收缩等选项。我有自动

创建/更新统计数据，Torn Page检测，允许跨数据库

所有权链接（需要一些存储过程在
$ b $中b供应商代码）。恢复模型很简单。

数据库是否设置为自动增长？任何最大尺寸限制？
数据库已创建为150GB。它具有1G步进的自动增长功能。现在

它是250G。它可以长到350G。现在DB有50G的可用空间。

文件系统是0.5Tbyte。现在它已经使用了60％。 40％的人应该有一个不错的表现吗？有多少可以用来获得不错的性能？

我们的交易日志一个月前增长到80G，但现在我们备份了

系统每天和db代理截断事务日志所以

现在它大约是0.5G（我们有75G交易日志免费，因为我们

didn'不要缩小它。


c）在查询进行时尝试运行sp_who2


我做了但是找不到有什么问题。我能找到什么？

从执行计划来看，在一个简单的表中插入1000行需要花费20秒

First of all. Please don''t use "lines" the purists among us will have a fit.
Sorry....You are right.
Table B has no more than 3000 rows at a time and has 3 columns with no index? Yes!
a) I would check the execution plans when you run these queries/ stored procedures. I checked them. The slowdown is on the insert/delete in the "table B".
b) Check database configuration.
What would you check in particolar?
Does the slowdown only occur when a query involves table B? Or is the entire database slow? The DB is not fast. The application sometimes timeout. But it is also
big with a lot of data. I can''t say how much it could perform better
with fine tuning. Is table A, B, & C all in the same database or in separate databases?
Table A,B,C are all in the same DB. Does it have single or multiple data and log files?
They are in the same filesystem. The filesystem is on the SAN. I could
have some local disk on the machine (SCSI 15K), but I am not sure that
could help (the "SAN expert" says me that the SAN should be a lot
faster than local disk). What do you think about it?
What are the database options? Do you have something funky like "auto close" or "auto shrink" enabled? The DB doesn''t have options like auto close or shrink. I have auto
create/update statistics, Torn Page detection, allow cross database
ownership chaining (it is required by some stored procedure in the
vendor code). The recovery model is Simple.
Is the database set to autogrow? Any max size restriction? The DB have been created as 150GB. It has autogrow with step of 1G. Now
it is 250G. It can grow till 350G. Now the DB has 50G free space. The
filesystem is 0.5Tbyte. Now it is 60% Used. Is 40% enought to have a
decent performance? What is the maximum that can be used to have decent
performance?
We had the transaction log grow till 80G a month ago, but now we backup
the system every day and the db agent truncate the transaction log so
now it is around 0.5G (We have 75G transaction log free, because we
didn''t shrink it).

c) Try running sp_who2 while the query is progress
I did it but could not find what is wrong. What could I look for?
From the execution plan it looks like it take 20s to insert 1000 rows

& 20秒从那个简单的表中删除它们。我会想要解决这个问题，因为它对我来说真的很奇怪。我是DBA

新手，但对我而言，在DMBS管理的快速存储上写入5 / 10kby的

数据看起来真的很奇怪需要花费很多时间。是不是

它奇怪？


最好的问候，

Massimo

in a simple table & 20s to delete them from that simple table. I would
like to solve this because it looks really strange to me. I am a DBA
newbie, but to me it looks really strange that to write 5/10kbyte of
data on a fast storage managed by a DMBS it takes so much time. Isn''t
it "strange"?

Best Regards,
Massimo

ma****@gmail.com （ma **** @ gmail .com）写道：

ma****@gmail.com (ma****@gmail.com) writes:

我有一个存储过程，从表A读取表并插入它们，
在表B中处理后，然后在其他表中移动它们（类似的到星型模式）用于报告。

表B是关于SP的编写方式，不超过3000行。表格B非常简单，有3行，没有索引。

很奇怪的是，表B的性能真的很慢。如果我从table_b中选择计数（*）需要30秒和30秒之间。 2分钟返回它有0行。当存储过程插入1000行时，它需要20/30秒，并且需要20/30秒才能删除它们。

I have a stored procedure that read table from table A and insert them,
after processing in table B, and then move them in other table (similar
to a Star Schema) for reporting.

Table B is, for how the SP is written, not more than 3000 lines. Table
B is very simple, has 3 rows, and no index.

What is very strange is that performance of table B is really slow. If
I do a select count (*) from table_b it takes between 30s & 2minutes to
return it has 0 lines. When the stored procedure insert 1000 lines, it
takes 20/30 seconds and it takes 20/30 seconds to delete them.

好​​的，所以这里去了我的ESP：


表B经常被插入和删除。它没有
有一个聚集索引。这导致了巨大的碎片，特别是如果有剩余的行会导致整个扩展区被遗忘。


在表上运行DBCC SHOWCONTIG以查看一些令人讨厌的数字。


然后在表上创建一个聚簇索引，并保留它。一个偶然的

DBCC DBREINDEX也是一件好事。

-

Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se


SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf。 ..2000 / books.asp

这篇关于非常小的桌子非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！