重启后的第二天SQL Server上的CPU异常高。 [英] Unusually High CPU on SQL Server the 2nd day after a reboot.
问题描述
To All，
我有一个运行在4x3GB物理CPU上的SQL2KSP3a数据库（< 1GB），带有
4GB内存。打开超线程的是Windows Server 2003.
有~4 .Net用户/ cxns（胖客户端，没有网络/应用服务器）和
连接池和~1 trx / sec。数据库的增长可以忽略不计
实际上甚至不是相关的，我会在一分钟内解释。
99％的trxs来自一个执行选择的SP。结果集
相对较小以及1~100行。是的我已经使用索引调整了它
调优向导，更改了SQL内存配置等....
我的问题是这个......
重启后的第一天，服务器在高峰时段运行6％的CPU。
在非高峰时段直到第二天显然是
发生了。第二天（重启后的第二天），在高峰时段它会跳到40％CPU
。在
高峰时段，服务器将继续以40％CPU运行，直到下次重启。这种现象已经发生了6个月或更长时间的b $ b，并且服务器上的流量在当天是相同的
1，因为它是第2,3,4天。 ..这个数据库位于另一台服务器上，其价格为
100+ dbs并且表现出相同的行为，因此将该服务器带到了它的膝盖上，因此我们不得不将它移到有问题的服务器没有
其他dbs。
我用google搜索了我的眼睛，微软网站，白皮书，perfmon，
SQLDiag，PSSDiag，执行计划，索引调整向导和列表
继续！我目前有一个与微软开放的案例，现在几个月已经开放了。我被传递到第3个MS Tech
专家。我已经为他们运行了PSSDiag共计6个小时
。我改变了MAXDOP。我可以提供更多信息，但是我会在这里花上好几天。我已经没有耐心/想法了。
微软显然正在抽烟。
任何想法都非常感谢！
提前致谢！
JL
>>服务器将在高峰时段继续以40％的CPU运行，直到下次重新启动时为
在黑暗中拍摄。确保TempDB在运营时间内没有耗尽
空间。我相信每次重新启动MSSQL服务时都会重新创建TempDB.
JL（jl ***** @ yahoo） .com）写道：我的问题是这个......
重启后的第一天，服务器在高峰时段运行6％的CPU。
在非高峰期几个小时，直到第二天显然发生了一些事情。第二天（重启后的第二天），它会在高峰时段跳到40％的CPU。在高峰时段，服务器将继续以40％CPU运行，直到下次重启。这种现象已经发生了6个月或更长时间，服务器上的流量与白天相同，因为它是2,3,4天，...这个数据库在另一台服务器上使用100+ dbs并且表现出相同的行为，从而使该服务器屈服于膝盖，因此我们不得不将其移动到有问题的服务器而没有其他dbs。
我已经google了我的眼睛，微软网站，白皮书，perfmon，
SQLDiag，PSSDiag，执行计划，索引调整向导，以及列表
继续！
该列表是否包含Profiler？
也就是说，如果我被要求查看
这个，是为了启动Profiler，看看是否有任何查询运行
比需要更长的时间。
啊，有尝试代替重启的另一件事：运行DBCC
DROPCLEANBUFFERS。这会完全刷新缓存。如果这样就像重新启动一样降低了b / b
的CPU使用率，那么缓存就会出现问题。
现在，为了进一步缩小它，下次尝试DBCC FREEPROCCAHCE 。这个
只刷新查询计划的缓存。如果这也有帮助，我有一个强烈怀疑的
。也就是说，如果有太多
提交类似查询的查询，则会出现问题，因为
缓存中的哈希值会崩溃。
然后，再次，因为您似乎使用存储过程，这不是一个问题 - 除非这些过程生成动态SQL？
顺便问一下，你在某处使用OPENXML吗？
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf ... 2000 / books.asp
另一个提示是监视数据库连接的数量。也许吧
不断增长，几天后开始淹没系统。
HTH，
Gert-Jan
JL写道：
To All，
我有一个运行在4x3GB物理CPU上的SQL2KSP3a数据库（< 1GB） /> 4GB的内存。它是带有超线程打开的Windows Server 2003.
连接池和~1 trx /秒有~420 .Net用户/ cxns（胖客户端，没有web / app服务器）。数据库的增长是可以忽略的，实际上甚至不是相关的，我将在一分钟内解释。
99％的trx来自一个选择的SP。结果集相对较小，也可以是1~100行。是的我已经使用索引调整向导调整它，更改了SQL内存配置等....
我的问题是这个......
第一天之后重启时，服务器在高峰时段运行6％的CPU。
在非高峰时段直到第二天，显然会发生一些事情。第二天（重启后的第二天），它会在高峰时段跳到40％的CPU。在高峰时段，服务器将继续以40％CPU运行，直到下次重启。这种现象已经发生了6个月或更长时间，服务器上的流量与白天相同，因为它是2,3,4天，...这个数据库在另一台服务器上使用100+ dbs并且表现出相同的行为，从而使该服务器屈服于膝盖，因此我们不得不将其移动到有问题的服务器而没有其他dbs。
我已经google了我的眼睛，微软网站，白皮书，perfmon，SQLDiag，PSSDiag，执行计划，索引调整向导，以及列表
继续！我目前有一个与微软开放的案例已经开放数月了。我被传递到第3位MS Tech
专家。我已经为他们运行了PSSDiag共计6个小时
。我改变了MAXDOP。我可以提供更多信息，但我会在这里呆上好几天。我已经没有耐心/想法了。
微软显然正在冒烟。
任何想法都非常感谢！
提前致谢！
JL
To All,
I have a SQL2KSP3a database(<1GB) running on a 4x3GB physical CPU with
4GB of ram. It is Windows Server 2003 with hyper-threading turn on.
There are ~420 .Net users/cxns (fat client, no web/app servers) with
connection pooling and ~1 trx/sec. The database growth is neglegeable
and actually is not even relevent which I will explain in a minute.
99% of the trxs are from one SP that does a select. The resultsets are
relatively small as well 1~100 rows. Yes I have tuned it with index
tuning wizard as well, changed the SQL memory configurations, etc....
My problem is this...
The first day after a reboot, the server runs 6%CPU during peak hours.
During the non-peak hours until the next day something apparently
happens. The next day (2nd day after a reboot), it jumps to 40%CPU
during peak hours. The server will continue to run at 40%CPU during
peak hours until the next reboot. This phenomenon has been occurring
for 6 months or more and the traffic on the server is the same for day
1 as it is for day 2,3,4,... This database was on another server with
100+ dbs and exibited the same behavior, thus bringing that server to
its knees, and thus we had to move it to the server in question with no
other dbs.
I have googled my eyes out, Microsoft site, white papers, perfmon,
SQLDiag, PSSDiag, execution plans, index tuning wizard, and the list
goes on! I currently have a case open with Microsoft that has been
open for months now. I have been passed around to the 3rd "MS Tech
Specialist". I have ran PSSDiag a total of 6 times for them for hours
on end. I have changed MAXDOP. I could give more information, but I
would be here for days. I am running out of patience/ideas and
Microsoft is apparently blowing smoke.
Any ideas are greatly appreciated!
Thanks in advance!
JL
>> The server will continue to run at 40%CPU during peak hours until
the next reboot
Taking a shot in the dark. Ensure the TempDB isn''t running out of
space during operational hours. I believe TempDB is recreated each
time the MSSQL Service is restarted.
JL (jl*****@yahoo.com) writes:My problem is this...
The first day after a reboot, the server runs 6%CPU during peak hours.
During the non-peak hours until the next day something apparently
happens. The next day (2nd day after a reboot), it jumps to 40%CPU
during peak hours. The server will continue to run at 40%CPU during
peak hours until the next reboot. This phenomenon has been occurring
for 6 months or more and the traffic on the server is the same for day
1 as it is for day 2,3,4,... This database was on another server with
100+ dbs and exibited the same behavior, thus bringing that server to
its knees, and thus we had to move it to the server in question with no
other dbs.
I have googled my eyes out, Microsoft site, white papers, perfmon,
SQLDiag, PSSDiag, execution plans, index tuning wizard, and the list
goes on!
Does that list include Profiler?
That is, the first thing I would to do if I was called in to look at
this, was to start Profiler, to see if there are any queries that runs
longer time than needed.
Ah, there is another thing to try in place of a reboot: run a DBCC
DROPCLEANBUFFERS. This flushes cache completely. If this lowers the
CPU usage just like a reboot does, then there is an issue with a cache.
Now, to further narrow it down, next time try DBCC FREEPROCCAHCE. This
only flushes the cache for query plans. If this helps too, I have a
strong suspicion. To wit, there is a problem if there are too many
similarly-looking queries being submitted, because the hashing in the
cache breaks down.
Then again, since you seem to use stored procedures, this is not likely
to be an issue - unless these procedures generates dynamic SQL?
By the way, are you using OPENXML somewhere?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Another hint is to monitor the number of database connections. Maybe it
keeps growing and after a few days starts swamping the system.
HTH,
Gert-Jan
JL wrote:
To All,
I have a SQL2KSP3a database(<1GB) running on a 4x3GB physical CPU with
4GB of ram. It is Windows Server 2003 with hyper-threading turn on.
There are ~420 .Net users/cxns (fat client, no web/app servers) with
connection pooling and ~1 trx/sec. The database growth is neglegeable
and actually is not even relevent which I will explain in a minute.
99% of the trxs are from one SP that does a select. The resultsets are
relatively small as well 1~100 rows. Yes I have tuned it with index
tuning wizard as well, changed the SQL memory configurations, etc....
My problem is this...
The first day after a reboot, the server runs 6%CPU during peak hours.
During the non-peak hours until the next day something apparently
happens. The next day (2nd day after a reboot), it jumps to 40%CPU
during peak hours. The server will continue to run at 40%CPU during
peak hours until the next reboot. This phenomenon has been occurring
for 6 months or more and the traffic on the server is the same for day
1 as it is for day 2,3,4,... This database was on another server with
100+ dbs and exibited the same behavior, thus bringing that server to
its knees, and thus we had to move it to the server in question with no
other dbs.
I have googled my eyes out, Microsoft site, white papers, perfmon,
SQLDiag, PSSDiag, execution plans, index tuning wizard, and the list
goes on! I currently have a case open with Microsoft that has been
open for months now. I have been passed around to the 3rd "MS Tech
Specialist". I have ran PSSDiag a total of 6 times for them for hours
on end. I have changed MAXDOP. I could give more information, but I
would be here for days. I am running out of patience/ideas and
Microsoft is apparently blowing smoke.
Any ideas are greatly appreciated!
Thanks in advance!
JL
这篇关于重启后的第二天SQL Server上的CPU异常高。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
