Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2005
    Posts
    319

    Thumbs down Unanswered: locking issues? query runs fine with no load

    Ok this is kind of strange, I was tasked with optimizing a query.

    My main question is what would cause this proc to run so slow on our development environment (measured in minutes) on Friday and so fast today with no change in objects? Could locking be partly to blame on Friday? We run merge replication if that matters.

    According to the production DBA here is some data regarding production performance:

    Before Release:
    Avg Dur: 2786
    Avg Reads: 117889

    After Release:
    Avg Dur: 11839
    Avg Reads: 197029

    When I was trying to optimize it on Friday I routinely got a runtime of about 2 minutes and 45 seconds no matter what I did (runtimes were about the same for the previous version of the proc). If I precomputed it and deleted/inserted differences per proc call it would take only 1-3 seconds so that is what I recommended, have a job run nightly that takes ~3 minutes to precompute a full refresh and then have the proc delete/insert differences per execution. This data is being cached by the application as well so it will only hit the DB after the data expires after a set time interval so I thought this was a good solution. I was told by my boss not to use a job and to just optimize the indexes (!).

    Here's a little more info:
    5 tables inner joined
    9 more left outer joined
    ...there is also a nasty UDF that builds a job history of the 3500 row resultset, even commenting out the UDF makes the query take over a minute which is unacceptable.

    row counts of first 5 and next 9 tables (k = 1,000's of rows)
    1) 870k
    2) 260k
    3) 3500k
    4) 160k
    5) 40k
    ------- below are Left outer joined
    6) 4k
    7) 140k
    8) 4000k
    9) 56k
    10) 7k
    11) 140k
    12) 9k
    13) 1k
    14) 680k

    I started to look at this again today when there is no activity on our dev sql servers. When I run it this weekend I was initially getting 125k reads on our payment table (first table row count listed above) and run time is about 1m 15s, now all of a sudden I am getting 10k reads and run time is only a few seconds (even when clearing the proc cache). Is this variance normal? no new indexes have been introduced on this table (when I compare with indexes on production).

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Could it be that an index or statistics maintenance job just ran right before you tested the query for the last time? BTW, reads are pretty bad, but I don't think you can dramatically decrease their number with just indexes (that's considering that those 14 tables are actually returning all those rows). If the number of rows finally returned is lower than that, - you may be able to restructure the procedure so that number of logical reads is much less. Otherwise, - lowering transaction isolation level is the next (but least desireable) option. It will not reduce the reads, but will reduce the query's dependence on availability of resources.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    I forgot to mention that the transaction isolation level was set at READ UNCOMMITTED.

    The numbers above are how many rows are in each table.

    I ran DBCC DBREINDEX for any of the main tables on our dev SQL Server first on Friday to see if any of the indexes were not up to date. So while it could have been a weekend job, I think this is less likely. I will check to see what jobs were scheduled to run.

    My first pass at optimizing the query was greatly reducing the number of joins, inserting the base data into a temp table and then updating once per table joined against originally.

    My second pass was making that temp table a regular table with indexes and all.

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    I just ran it now and the reads have gone up to 28k for the PAYMENT table, execution time is at 50 seconds

    Here is what the execution plan reveals:

    Far most right:
    Index Seek
    Cost 0%

    below that:
    Index Seek
    Cost 11%

    to the immediate left of both of those:
    Hash Match (Inner Join)
    Cost: 20%
    Output list: -- same as the where clause in comments below
    PAYMENT_ID
    PAYMENT_STATUS_ID
    MEETING_ID
    /*
    WHERE P.ACTIVE_IND = 1
    AND P.MEETING_ID > 0
    AND P.PAYMENT_STATUS_ID = 1
    */

    below that:
    Key Lookup
    Cost: 57%
    Object:
    PAYMENT.PK_PAYMENT
    Output list: -- 11 columns that are in the SELECT clause from the payment table
    Seek predicates:
    PAYMENT.PAYMENT_ID = Scalar Operator(PAYMENT.PAYMENT_ID AS P.PAYMENT_ID)
    Last edited by Gagnon; 06-02-08 at 04:22.

  5. #5
    Join Date
    Jun 2005
    Posts
    319
    Ok this is why I think the variance in run times has to do with locking issues. I am still puzzled why I would have this problem at 4AM Monday morning (or Sunday night if you want to look at that way).

    I created two indexes, and the proc runs in 17 seconds, I remove the 2 indexes and the proc runs in 1 minute, 30 seconds. I create the two indexes again and this time I do an SP_WHO2 and I run a custom get_locks proc and the proc now runs in 1 minute and 10 seconds (which just ran in 17 seconds previously!). SP_WHO2 is showing my spid as SUSPENDED.

    get_locks is showing:
    18 tables locked by my spid, request mode: Sch-S

    for a different spid I am seeing 5 lock records:
    3 where obj is NULL, request mode is: (IX, Sch-S, X)
    and 2 where obj is:
    REPLICATION_WATCH (Sch-S)
    COUNCIL_MEMBER (Sch-S)

    I am guessing this is related to Merge Replication locking down these tables somehow and suspending my spid causing it to run much longer? Is this typical behavior with merge replication? Can I create a (better) covering index to avoid this performance slow-down?

    Well REPLICATION_WATCH is a custom job we run to update data between DB's. I thought that was the culprit but when I disabled it, my spid is still getting SUSPENDED. What causes a SPID to get SUSPENDED?
    Last edited by Gagnon; 06-02-08 at 05:50.

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    Not sure if it matters but these dev instances are 32-bit instances, production is 64-bit. It looks like we are going to try to upgrade these instances to 64-bit in the next week or two. Probably does not make that much of a difference.

    This spid from which I am running this proc is consistently getting suspended, not sure what to make of it. This is causing the proc to run on this dev instance for upwards of a minute (on production the average execution time is 12 seconds, and prior to the latest change it would run in ~3 seconds).

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Can you run the following, and post the result:

    Code:
    set showplan_text on
    go
    your query here
    This will save you trying to describe the pictures, and such in the graphical showplan widget.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Difference between 32 and 64 is huge, to the point that when you upgrade, 64-bit may mask the real problem, but you may never encounter this situation again. This is just a guess because we have no idea about your procedure or your environment for that matter.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2005
    Posts
    319
    I had to shorten it to under 10k characters...

    first half
    Code:
    StmtText
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           |--Compute Scalar(DEFINE:([Expr1042]=CASE WHEN [Expr1067]>(0) THEN [GLGLIVE].[dbo].[MEETING_COUNCIL_MEMBER_RELATION].[MARKED_DOWN_RATE] as [MCMR].[MARKED_DOWN_RATE] ELSE CONVERT(int,[GLGLIVE].[dbo].[COUNCIL_MEMBER].[PAY] as [CM].[PAY],0) END, [Expr1
                |--Nested Loops(Left Outer Join, PASSTHRU:(IsFalseOrNull [GLGLIVE].[dbo].[PAYMENT].[MEETING_GROUP_IND] as [P].[MEETING_GROUP_IND]=(1)), OUTER REFERENCES:([P].[MEETING_GROUP_ID]))
                     |--Nested Loops(Left Outer Join, OUTER REFERENCES:([R].[MEETING_ID], [Expr1107]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |--Merge Join(Right Outer Join, MERGE:([U2].[USER_ID])=([Expr1084]), RESIDUAL:(CASE WHEN [GLGLIVE].[dbo].[MEETING_RSVP].[USER_ID] as [RSVP2].[USER_ID] IS NOT NULL THEN [GLGLIVE].[dbo].[MEETING_RSVP].[USER_ID] as [RSVP2].[USER_ID] ELSE
                     |    |    |--Clustered Index Scan(OBJECT:([GLGLIVE].[dbo].[USER_TABLE].[IX_USER_ID] AS [U2]), ORDERED FORWARD)
                     |    |    |--Sort(ORDER BY:([Expr1084] ASC))
                     |    |         |--Compute Scalar(DEFINE:([Expr1084]=CASE WHEN [GLGLIVE].[dbo].[MEETING_RSVP].[USER_ID] as [RSVP2].[USER_ID] IS NOT NULL THEN [GLGLIVE].[dbo].[MEETING_RSVP].[USER_ID] as [RSVP2].[USER_ID] ELSE [GLGLIVE].[dbo].[MEETING_USER_R
                     |    |              |--Hash Match(Right Outer Join, HASH:([RSVP2].[MEETING_ID])=([R].[MEETING_ID]), RESIDUAL:([GLGLIVE].[dbo].[MEETING_GROUP_MEETING_RELATION].[MEETING_ID] as [R].[MEETING_ID]=[GLGLIVE].[dbo].[MEETING_RSVP].[MEETING_ID] as 
                     |    |                   |--Clustered Index Scan(OBJECT:([GLGLIVE].[dbo].[MEETING_RSVP].[PK_MEETING_RSVP] AS [RSVP2]))
                     |    |                   |--Nested Loops(Left Outer Join, OUTER REFERENCES:([R].[MEETING_ID], [Expr1106]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CM].[COUNCIL_MEMBER_ID], [Expr1105]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |--Nested Loops(Inner Join, PASSTHRU:([IsBaseRow1030] IS NULL), OUTER REFERENCES:([MGD].[MEETING_GLG_DELEGATE_ID]))
                     |    |                        |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([M].[MEETING_ID], [Expr1104]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |--Nested Loops(Inner Join, PASSTHRU:([IsBaseRow1028] IS NULL), OUTER REFERENCES:([U].[USER_ID]))
                     |    |                        |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([MUR].[USER_ID], [RSVP].[USER_ID], [Expr1103]) WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |--Nested Loops(Inner Join, PASSTHRU:([IsBaseRow1026] IS NULL), OUTER REFERENCES:([RSVP].[MEETING_RSVP_ID]))
                     |    |                        |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([M].[MEETING_ID], [Expr1102]) WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([M].[MEETING_ID], [Expr1101]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CT].[CLIENT_ID], [Expr1100]) WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([MCTR].[CONTACT_ID], [Expr1099]) WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([M].[MEETING_ID], [Expr1098]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([P].[MEETING_ID], [Expr1097]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([M].[MEETING_ID], [Expr1096]) WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1007], [M].[START_DATE], [M].[END_DATE], [Expr1095]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([MCMR].[MEETING_ID], [Expr1094]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([MCMR].[MEETING_ID], [Expr1093]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [CM].[COUNCIL_MEMBER_ID], [CM].[COUNCIL_ID], [Expr1092]) OPTIMIZED WITH
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([MCMR].[COUNCIL_MEMBER_ID], [Expr1091]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Filter(WHERE:([GLGLIVE].[dbo].[PAYMENT].[COUNCIL_MEMBER_ID] as [P].[COUNCIL_MEMBER_ID]=[GLGLIVE].[dbo].[MEETING_COUN
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([P].[PAYMENT_ID], [Expr1090]) OPTIMIZED WITH UNORDERED PREFETCH)
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([MCMR].[MEETING_ID], [Expr1089]) OPTIMIZED WITH UNORDERED PREFE
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |         |    |--Compute Scalar(DEFINE:([Expr1067]=isnull([GLGLIVE].[dbo].[MEETING_COUNCIL_MEMBER_RELATION].[MARKED_DO
    Last edited by Gagnon; 06-02-08 at 14:03.

  10. #10
    Join Date
    Jun 2005
    Posts
    319
    2nd half
    Code:
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |         |    |    |--Clustered Index Scan(OBJECT:([GLGLIVE].[dbo].[MEETING_COUNCIL_MEMBER_RELATION].[IX_CLUST_MEETING
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |         |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[PAYMENT].[IX_PAYMENT_MEETING_ID] AS [P]), SEEK:([P].[MEETING_ID]=
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |         |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[PAYMENT].[PK_PAYMENT] AS [P]), SEEK:([P].[PAYMENT_ID]=[GLGLI
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[COUNCIL_MEMBER].[PK_COUNCIL_MEMBER] AS [CM]), SEEK:([CM].[COUNCIL_MEMBER_ID]=[GL
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[COUNCIL_MEMBER].[COUNCIL_MEMBER2] AS [CM]), SEEK:([CM].[COUNCIL_ID]=[GLGLIV
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_COUNCIL_RELATION].[PK_MEETING_COUNCIL_RELATION] AS [CR]), SEEK:([CR].[ME
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING].[IX_MEETING] AS [M]), SEEK:([M].[MEETING_ID]=[GLGLIVE].[dbo].[MEETING_COUNCIL_MEMBER_R
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING].[IX_MEETING_1] AS [M]), SEEK:([M].[START_DATE]=[GLGLIVE].[dbo].[MEETING].[START_D
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_GROUP_MEETING_RELATION].[IX_MGMR_CHILD_MEETING_ID] AS [R]), SEEK:([R].[CHILD_MEETING_ID]=[GLGLIVE
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_USER_RELATION].[IX_MUR_MEETING_ID] AS [MUR]), SEEK:([MUR].[MEETING_ID]=[GLGLIVE].[dbo].[PAYMENT].[MEET
                     |    |                        |    |    |    |    |    |    |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_CONTACT_RELATION].[IX_MEETING_CONTACT_RELATION_2] AS [MCTR]), SEEK:([MCTR].[MEETING_ID]=[GLGLIVE].[dbo].[ME
                     |    |                        |    |    |    |    |    |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[CONTACT].[CD_dta_index_CONTACT_5_1415728146__K1_K17_K2_K6_3_4_13_26] AS [CT]), SEEK:([CT].[CONTACT_ID]=[GLGLIVE].[dbo].[
                     |    |                        |    |    |    |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[CLIENT].[PK_CLIENT] AS [L]), SEEK:([L].[CLIENT_ID]=[GLGLIVE].[dbo].[CONTACT].[CLIENT_ID] as [CT].[CLIENT_ID]),  WHE
                     |    |                        |    |    |    |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_REQUEST_MEETING_RELATION].[MRMR_MEETING_ID] AS [MRMR]), SEEK:([MRMR].[MEETING_ID]=[GLGLIVE].[dbo].[MEETING].[MEETING_ID] a
                     |    |                        |    |    |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_RSVP].[UQ_MEETING_RSVP] AS [RSVP]), SEEK:([RSVP].[MEETING_ID]=[GLGLIVE].[dbo].[MEETING].[MEETING_ID] as [M].[MEETING_ID]),  WHE
                     |    |                        |    |    |    |    |    |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_RSVP].[PK_MEETING_RSVP] AS [RSVP]), SEEK:([RSVP].[MEETING_RSVP_ID]=[GLGLIVE].[dbo].[MEETING_RSVP].[MEETING_RSVP_ID] as [RS
                     |    |                        |    |    |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[USER_TABLE].[PK_USER_TABLE] AS [U]), SEEK:([U].[USER_ID]=CASE WHEN [GLGLIVE].[dbo].[MEETING_RSVP].[USER_ID] as [RSVP].[USER_ID] IS NOT NULL THEN 
                     |    |                        |    |    |    |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[USER_TABLE].[IX_USER_ID] AS [U]), SEEK:([U].[USER_ID]=[GLGLIVE].[dbo].[USER_TABLE].[USER_ID] as [U].[USER_ID]) LOOKUP ORDERED FORWARD)
                     |    |                        |    |    |    |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_GLG_DELEGATE].[UQ_MEETING_GLG_DELEGATE] AS [MGD]), SEEK:([MGD].[MEETING_ID]=[GLGLIVE].[dbo].[MEETING].[MEETING_ID] as [M].[MEETING_ID]),  WHERE:([G
                     |    |                        |    |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_GLG_DELEGATE].[PK_MEETING_GLG_DELEGATE] AS [MGD]), SEEK:([MGD].[MEETING_GLG_DELEGATE_ID]=[GLGLIVE].[dbo].[MEETING_GLG_DELEGATE].[MEETING_GLG_D
                     |    |                        |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[COUNCIL_MEMBER_FLAG_RELATION].[IX_COUNCIL_MEMBER_FLAG_RELATION] AS [CMFR]), SEEK:([CMFR].[COUNCIL_MEMBER_ID]=[GLGLIVE].[dbo].[COUNCIL_MEMBER].[COUNCIL_MEMB
                     |    |                        |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_USER_RELATION].[IX_MUR_MEETING_ID] AS [MUR2]), SEEK:([MUR2].[MEETING_ID]=[GLGLIVE].[dbo].[MEETING_GROUP_MEETING_RELATION].[MEETING_ID] as [R].[MEETING_ID]) ORDERE
                     |    |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING_COUNCIL_RELATION].[PK_MEETING_COUNCIL_RELATION] AS [CR2]), SEEK:([CR2].[MEETING_ID]=[GLGLIVE].[dbo].[MEETING_GROUP_MEETING_RELATION].[MEETING_ID] as [R].[MEETING_ID]),  WHERE:([
                     |--Index Spool(SEEK:([P].[MEETING_GROUP_ID]=[GLGLIVE].[dbo].[PAYMENT].[MEETING_GROUP_ID] as [P].[MEETING_GROUP_ID]))
                          |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1046], [GLGLIVE].[dbo].[MEETING].[START_DATE], [GLGLIVE].[dbo].[MEETING].[END_DATE]))
                               |--Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING].[IX_MEETING_2]), SEEK:([GLGLIVE].[dbo].[MEETING].[MEETING_ID]=[GLGLIVE].[dbo].[PAYMENT].[MEETING_GROUP_ID] as [P].[MEETING_GROUP_ID]) ORDERED FORWARD)
                               |--Clustered Index Seek(OBJECT:([GLGLIVE].[dbo].[MEETING].[IX_MEETING_1]), SEEK:([GLGLIVE].[dbo].[MEETING].[START_DATE]=[GLGLIVE].[dbo].[MEETING].[START_DATE] AND [GLGLIVE].[dbo].[MEETING].[END_DATE]=[GLGLIVE].[dbo].[MEETING].[EN
    
    (59 row(s) affected)

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, the initial good news is that you are no longer having the tablescan on the PAYMENT table. It has moved to the following tables:

    [GLGLIVE].[dbo].[USER_TABLE]
    [GLGLIVE].[dbo].[MEETING_RSVP]
    [GLGLIVE].[dbo].[MEETING_COUNCIL_MEMBER_RELATION]

    I expect the relation table is the 3.5 million row table. it looks like you have a join condition in the where clause, but I can not be too certain, since that row got cut off.
    Code:
    |--Filter(WHERE:([GLGLIVE].[dbo].[PAYMENT].[COUNCIL_MEMBER_ID] as [P].[COUNCIL_MEMBER_ID]=[GLGLIVE].[dbo].[MEETING_COUN
    Is this the sum total of the where clause?

  12. #12
    Join Date
    Jun 2005
    Posts
    319
    Hey MCrowley -

    I am originally from Danvers, MA. Thanks for helping me out.

    Here is the full WHERE, and yes this table is the 3.5M row table:
    Code:
    --Filter(WHERE:([GLGLIVE].[dbo].[PAYMENT].[COUNCIL_MEMBER_ID] as [P].[COUNCIL_MEMBER_ID]=[GLGLIVE].[dbo].[MEETING_COUNCIL_MEMBER_RELATION].[COUNCIL_MEMBER_ID] as [MCMR].[COUNCIL_MEMBER_ID] AND [GLGLIVE].[dbo].[PAYMENT].[ACTIVE_IND] as [P].[ACTIVE_IND]=(1) AND [GLGLIVE].[dbo].[PAYMENT].[PAYMENT_STATUS_ID] as [P].[PAYMENT_STATUS_ID]=(1)))
    Here is a little more info...
    here is the original join:
    JOIN MEETING_COUNCIL_MEMBER_RELATION MCMR
    ON (MCMR.COUNCIL_MEMBER_ID = CM.COUNCIL_MEMBER_ID
    AND MCMR.MEETING_ID = M.MEETING_ID)
    and here is the full where for the query:
    WHERE P.ACTIVE_IND = 1
    AND P.MEETING_ID > 0
    AND P.PAYMENT_STATUS_ID = 1

    I have since removed this line:
    AND P.MEETING_ID > 0

    and created a new index on MEETING_COUNCIL_MEMBER_RELATION (named:IX_MEETING_COUNCIL_MEMBER_RELATION_1) as such:
    Code:
    index_name	index_description	index_keys
    IX_MEETING_COUNCIL_MEMBER_RELATION_1	nonclustered located on PRIMARY	COUNCIL_MEMBER_ID, MEETING_ID, MARKED_DOWN_RATE, MIN_PROJECT_AMOUNT
    the showplan now shows:
    1st scan: (1k rows in this table)
    Code:
                     |    |    |--Clustered Index Scan(OBJECT:([GLGLIVE].[dbo].[USER_TABLE].[IX_USER_ID] AS [U2]), ORDERED FORWARD)
    2nd scan: (9k rows in this table)
    Code:
                     |    |                   |--Clustered Index Scan(OBJECT:([GLGLIVE].[dbo].[MEETING_RSVP].[PK_MEETING_RSVP] AS [RSVP2]))
    3rd scan: (38k rows in this table - forgot that we pruned it on dev...)
    Code:
    |    |    |--Index Scan(OBJECT:([GLGLIVE].[dbo].[MEETING_COUNCIL_MEMBER_RELATION].[IX_MEETING_COUNCIL_MEMBER_RELATION_1] AS [MCMR]))
    Last edited by Gagnon; 06-02-08 at 16:33.

  13. #13
    Join Date
    Jun 2005
    Posts
    319
    After talking with another engineer we determined the suspeded status (with IO Pagelatch wait status) is coming up since our dev box is low on memory compared to production and is only running a 32-bit instance instead of a 64-bit instance. It makes tuning this query more challenging.

    Here are some counts, I am curious as to what people think - should I just focus on the query plan and largely ignore CPU and Reads since they do not seem to correlate to actual duration improvements?

    Code:
    -- Original
    0)CPU-3125       Reads-286k       Dur-122k
    -- Optimized passes (all 3 use a proc/UDF rewrite, each successive pass adds another index)
    Code:
    1)CPU-2391       Reads-362k       Dur-34k             
    2)CPU-1344       Reads-256k       Dur-52k             
    3)CPU-2437       Reads-400k       Dur-17k
    Last edited by Gagnon; 06-03-08 at 06:40.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In #1 and #2 you were having blocking, which effectively affected duration...or am I misreading your stats?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •