SQL Server Performance

Slow\CPU Intensive query. Need help willing to hire.

Discussion in 'ALL SQL SERVER QUESTIONS' started by KINGED, Jun 13, 2013.

  1. KINGED New Member

    Hello so I’m at my wits end and just can’t come up with a solution. For a little overview this query is being ran simultaneously by many applications so you can imagine the locking\blocking\slow responses so I’m just checking to see if there is a better way. I’m willing to hire someone to resolve our issue. I can answer any questions. If we get more in-depth I will answer them via MSGnr or email.

    System:

    TempDB are (one for each CPU) all on their own SSD (Enterprise Grade)

    DB is on a EqualLogic 15K SAN (Used to be on SSD but the drives died)

    64bit SQL 2008 R2 64GB 16CPU DELL R900

    Basically we have many agents that run this query to see if there are any emails ready to send. So its basically a Queue. The server barely uses any IO and is always at 100% CPU. The problem query is below. I’ve tried optimizing it for months and this is what I came up with:

    Declare @EDNodeID int

    Declare @AgentID int

    Select @EDNodeID = 5

    Select @AgentID = 35

    Declare @ResultTable table ( singleID int);

    WITH UpdatedRows AS (

    Select

    SingleSendID,

    SingleGuid,

    CompletedBool,

    AgentID

    From

    tblSingleSend WITH(ROWLOCK,READPAST)

    Where

    AgentID = 0

    And EDNodeID = @EDNodeID

    And(ScheduledDate IsNullOr ScheduledDate <GETDATE())

    And(TypeEnum = 2 Or TypeEnum = 6 Or TypeEnum = 9 Or TypeEnum = 7 Or TypeEnum = 12 Or TypeEnum = 4 Or TypeEnum = 16 Or TypeEnum = 17 )

    )

    Updatetop(2000)

    UpdatedRows

    Set

    AgentID = @AgentID,

    SingleGuid =NewID(),

    CompletedBool = 0

    Output

    INSERTED.SingleSendID

    Into

    @ResultTable

    Select*From tblSingleSend Where SingleSendID IN(Select singleID From @ResultTable)

    Index’s seem to be good but who knows now. The execution plan seems to do all seeks but like I said I can provide you guys with whatever is needed I just wanted to get something out there quick to start things off. Again I can hire no problem!
  2. SQL Server Helper New Member

    Here's a few things you can try:

    1. Remove the common table expression and do a direct update on your tblSingleSend table:

    Update TOP (2000) tblSingleSend
    Set AgentID = @AgentID, SingleGuid =NewID(), CompletedBool = 0
    Output INSERTED.SingleSendID
    Into @ResultTable

    2. Replace the last SELECT statement with an INNER JOIN:

    Select *
    From tblSingleSend INNER JOIN @ResultTable
    ON SingleSendID = SingleID

    3. Make sure you have an index on the following columns, in this order:

    AgentID, EDNodeID, TypeEnum, ScheduledDate

    Although a lot of agents will be accessing your database at the same time, one agent will only be able to query the database one query at a time so there should be no contention between agents in terms of locked records. If the update part takes a long time since it needs to update 2000 records at a time, try lowering that down to maybe 200 and just call it 10 times from your application. This will minimize the locking.

    Hope this helps.

    SQL Server Helper
    http://www.sql-server-helper.com/ti...&tkw=sql-server-tutorial-update-statement-101

Share This Page