none
Queuing issue - sql server load 100% cpu usage RRS feed

  • Question

  • Hi !

    I'm having a real tough time dealing with Project Server 2010 user synchronization.

    We have roughly 450 projects (with project sites) and are dealing with less than 100 users.

    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.

    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.

    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.

    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 ?

    Thanks,

     Jeremy

    Wednesday, July 3, 2013 9:00 PM

All replies

  • Hi Jeremy,

    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.

    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

    Regards,

    Dinesh S. Rai

    Friday, July 5, 2013 5:33 PM
    Moderator
  • Hi Jeremy,

    Try to use Ignite tool to analyse, what all are the queries running on your DB server, There might be some queries got stucked or continuosly running on that server. I already faced this issue in our Production server and there was some queries stucked on that server.

    Below is link where you can download the tool for SQL Server:

    http://www.confio.com/performance/sql-server/ignite/ 

    Please share me your findings.

    Cheers,

    Amar Deep Singh


    Friday, July 5, 2013 5:49 PM
  • Hi Dinesh,

    thanks a lot for your suggestions!!
    Currently we are at SP1, CU June 2012 ; Infra is 3 WFE, 2 APPS, 1 SQL 2008 cluster.

    We are on a "shared" standard/enterprise farm, but will be moving out all of our project server instances to a dedicated Enterprise farm in the coming months, so that we can be up to date with the SP, CU releases...

    The strange thing is, we don't have many resources in this particular Project Server instance, less than 100...but yes, they do have the "view project sites" category permission enabled, as they all contribute to the site either as readers or contributors.
    About the long running query, I'll see if the DBA is able to pull something out, I unfortunately have no accesses at all to the server.

    once again, thanks for the suggestions, I'll validate that the databases maintenance plan fits the articles you provided.

    Jeremy

    Friday, July 5, 2013 7:31 PM
  • Hi Amar,

    I'll send the link to my DBA and see if he can spot queries running or stuck on the server.

    I'll try to post my findings next week !

    Jeremy

    Friday, July 5, 2013 7:58 PM
  • Hi Jeremy,

    I would also suggest that you go through the ink blogs.msdn.com/b/brismith/archive/2012/09/19/when-your-project-server-queue-slows-down.aspx to help improve the performance of the queue jobs.

    Regards,

    Dinesh S. Rai

    Friday, July 5, 2013 10:44 PM
    Moderator