Post reply

query plan with "parallelism"...

  • November 11, 2013 at 2:37 pm

    #279978

    hi everybody

    I have a query that runs in 2 seconds

    when I add another inner join, the same query lasts 120 seconds...

    I've added non clustered index to columns involved in joins with the new table

    but it continues slow...

    I've observed that the SS engine make a plan with several "parallelism" steps

    what is happening?...

    thanks

  • November 11, 2013 at 4:27 pm

    #1665613

    are the indexes in use in the plan? Are statistics up to date?

    Without code and an exec plan, hard to guess here.

  • November 11, 2013 at 4:36 pm

    #1665615

    There are, as Steve says, almost limitless issues. One of them is most likely that you have the default value set for "Cost Threshold for Parallelism." That value is 5. On just above every system I've ever dealt with, that is extremely low. I'd suggest bumping that value up to 35 to start with.

    That will help some. But you're likely hitting other issues that we could suggest help with if we at least saw the actual execution plan (but do it after you change the cost threshold).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 11, 2013 at 5:21 pm

    #1665622

    thanks

    I attach the exec plan, in xml format beacuse it's a bit large

    please check that, in "plan 2", the estimated number of rows reach 128 millions

    when the actual number should be about 13000 ...

    I've updated statistics for tables "Plan_Materia" and "Simu"

    and dropped indexes (pks) too

    and added some individual non clustered index

    in an old "clon" machine, with poor memory and an old microprocessor

    the query runs in up to 6 seconds (?!?!?)

    thanks again

  • November 11, 2013 at 5:25 pm

    #1665623

    thanks,

    I tested it

    but it doesn't improve the query

    I've attached 2 exec plans

  • November 12, 2013 at 4:14 am

    #1665690

    Your query is extremely simple, but the execution plan is very complex. So, it looks like you're joining or nesting views. You've got a "Reason for early termination" of the execution plan as "Timeout." That means that the query joining the views together was sufficiently complex that the optimizer couldn't finish optimizing the query. The very first step should be to simplify the heck out of the query. You're returning a single value. It's not likely that you really need all the tables in all the views. Joining and nesting views is a common code smell that leads to poor performance.

    The estimated cost is 13.2491. That's way beyond the default value of 5, so that can be the cause of the parallelism.

    Most of your data is coming from two sources, [Academic].[dbo].[CursoActivo_Alumno].[PK_CursoActivo_Alumno] and [Academic].[dbo].[Fecha].[IX_Fecha_2]. These are both scans. That means you either don't have good indexes on these tables or you have something in the code in the views (which I can't see) that prevents the indexes from getting used or, you don't have any kind of filtering criteria on these tables at all. You also have a series of key lookups. This means your non-clustered indexes you do have are not covering indexes for these queries. A covering index provides all the data needed to satisfy the query.

    That's about all I can tell from what I see. If your estimated rows are not matching the actual rows you either still have out of date statistics (use a FULL SCAN to update them) or you're using something like a multi-statement table valued user defined function in your code (I didn't see evidence of one, so I don't think you did).

    Those are my suggestions based on what I can see.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 12, 2013 at 10:50 am

    #1665814

    thanks Grant

    this morning I executed the query again...

    but I replaced the view (as you've suggested) with an explicit join between 2 tables

    (the view is designed precisely to join these 2 tables)

    the query runs in 1 second now

    I attach the new plan, please explain me what happened!

    THANKS!

    this is what I did yesterday late in the night

    I tested your first suggestion "cost...= 35" but it doesn't change at all

    I've also added redundant, indivudual nonclustered indexes for each column that appear in conditions

    I dropped and recreated all indexes

    the only view on the query is "v_Cursada", which joins two tables with near 100 rows each one

    I attach tables and indexes DDL now

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply