排队问题 - sql server加载100％cpu使用率 [英] Queuing issue - sql server load 100% cpu usage
问题描述
嗨！
我在处理Project Server 2010用户同步时遇到了非常艰难的时期。
I'm having a real tough time dealing with Project Server 2010 user synchronization.
我们大约有450个项目（包括项目站点）并且只处理不到100个用户。
We have roughly 450 projects (with project sites) and are dealing with less than 100 users.
我在AD Synch中遇到了很多问题（由于AD中的非活动资源导致部分失败），所以我决定手动管理用户创建。
I've had many issues with AD Synch (partial failure due to inactive resources in the AD), so I decided to manage user creation manually.
我开始编辑安全组，根据需要添加和删除用户，然后保存组。
大约3个小时后，我收到了来自我的SQL管理员的电话，惊慌失措，因为SQL服务器的所有8个CPU内核都处于100％的使用率，影响了该服务器上托管的sql数据库的所有应用程序。
I started editing security groups, adding and removing users as required, then saved the groups.
About 3 hours later, I received a call from my SQL admin, panicked because all of the 8 CPU cores of the SQL server were at 100% usage, impacting all applications with sql databases hosted on that server.
每次我将一个用户添加到一个安全组时，该操作就会被排队，CPU会提升100％并且该任务只会在发生任何事情之前长达10分钟。我已经对队列设置进行了更改，减少了线程数，取消选中快速轮询选项
，但没有任何事情...... SQL服务器过载且几乎无法使用。
Everytime I add a user to a security group, the action gets enqueued, CPU boosts at 100% and the task just stands there for as long as 10 minutes before anything happens. I've made changes to the queue settings, reducing the number of threads, unchecking the Fast Polling option, but nothing does...the SQL server is overloaded and almost unusable.
有人已经遇到过这种问题吗？我没有看到dbs上的维护计划，因为我不是sql管理员，但是被告知存在一个......
Anybody already experienced this kind of issue ? I haven't seen the maintenance plan on the dbs, as I'm not the sql admin, but have been told that there exists one...
我可以检查的其他任何东西？
anything else I could check for ?
谢谢，
Jeremy
Jeremy
推荐答案
嗨Jeremy，
Hi Jeremy,
你能看看SQL中长时间运行的查询吗？另外，您能否告诉我们最新应用于项目的补丁。
您可以参考以下链接来帮助提高性能：•管理Project Server中大量资源的最佳实践2010：technet.microsoft.com/en-us/library/hh670402(v=office.14).aspx
•Project Server 2010：如何最好地管理大量资源：blogs.msdn。 com / b / brismith / archive / 2011/08 / 10 / project-server-2010-how-to-best-manage-large-numbers-of-resources.aspx
Can you have a look at the long running query in SQL. Also, can you let us know the most recent patch applied for Project.
You can refer the below links to help improve the performance:•Best practices for managing a large number of resources in Project Server 2010: technet.microsoft.com/en-us/library/hh670402(v=office.14).aspx
•Project Server 2010: How to best manage large numbers of resources: blogs.msdn.com/b/brismith/archive/2011/08/10/project-server-2010-how-to-best-manage-large-numbers-of-resources.aspx
考虑到问题，您是否可以告诉我们您是否已将"查看项目网站"设置为全部的用户或大多数用户提供了类别权限？
Considering the issue, can you let us know whether you have provided category permissions to users or most of the users with ‘view project sites’ set to all?
如果是，这是一项要求吗？
If yes, is this a requirement?
如果这不是必需条件，则可以将类别权限限制为用户以确保谁看到了什么。
In case this is not a requirement then, you can restrict the category permissions to users to ensure who sees what.
您可以参考下面提到的文章来检查Project Server的数据库维护：
•Project Server 2010的数据库维护计划：technet.microsoft.com/en-us/library/cc973097(v=office.14).aspx
•如何对Windows SharePoint Services 3.0数据库和SharePoint Server 2007数据库进行碎片整理：support.microsoft。 com / kb / 943345
You can refer the articles mentioned below to check for DB maintenance for Project Server:
•Database maintenance plans for Project Server 2010: technet.microsoft.com/en-us/library/cc973097(v=office.14).aspx
•How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases: support.microsoft.com/kb/943345
问候，
Dinesh S. Rai
Dinesh S. Rai
这篇关于排队问题 - sql server加载100％cpu使用率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
