locked
Creation of index stops parallel exection RRS feed

  • Question

  • Hello,

    I have a very large table that a process is extracting data from. The table was originally a heap so I created a clustered index and re-ran the extraction.

    Now that the index is there, the QP is no longer running in parallel. I lower the cost threshold of parallelism but even when set to 1 the query still does not run in parallel.

    Does anyone know why the creation of a clustered index would prevent parallelism?

    Any help or advice would be most appreciated.

    Andrew

    Thursday, July 11, 2013 4:41 PM

Answers

  • Did you mean serial plan degraded the performance?

    Does the response time for the same query is greater than it was before creating a CI?

    The join of other tables with the big table would have made Optimizer to chose a Serial plan.

    How many rows are returned with the JOIN query?

    Also try select top 10000* from <BIGTABLE>, does it still gives a serial plan?

    Friday, July 12, 2013 2:00 AM

All replies

  • IT Depends , SQL server may chose serial plan better than parallel plan.

    When it was a help, the QP would have Table scan and that would be the reason for parallelism.

    After CI creation, SQL would have got better plan with Serial execution . Why the concern about parallelism?

    Thursday, July 11, 2013 6:12 PM
  • Have you considered partitioning the table and index? A partitioned index should be able to enable seek in parallel across the multiple partitions.

    Is there a where clause that applies a function to a column value that could force the serial evaluation on the index as opposed to parallel workers acting on the heap?


    If you're happy and you know it vote and mark.

    Thursday, July 11, 2013 6:23 PM
  • Adding a clustered index to a heap does not prevent parallelism.

    Why the optimizer chooses a serial plan after the index creation I don't know. As Sarat mentions, the estimated cost of a serial plan using the clustered index may be lower than the best parallel plan.

    Also, when you create a clustered index on a heap, all unused space will be reclaimed, which can result in a substantially smaller table (measured in number of page).

    It also depends on your query. If the optimizer finds a trivial query plan it will not consider a parallel plan.


    Gert-Jan

    Thursday, July 11, 2013 6:52 PM
  • Hi Chaps,

    Thanks for the response. The reason I am looking at parallelism is that the query is basically a SELECT * from a big table (with 106 million rows) joined to a couple of smaller tables. I think that it running a parallel plan would improve the performance and I expected after I added the index.

    I cannot use partitioning as the database is in SQL Server 2005 Standard. I'm basically looking at trying to get the max performance out of the query.

    Andrew

    Thursday, July 11, 2013 9:18 PM
  • Did you mean serial plan degraded the performance?

    Does the response time for the same query is greater than it was before creating a CI?

    The join of other tables with the big table would have made Optimizer to chose a Serial plan.

    How many rows are returned with the JOIN query?

    Also try select top 10000* from <BIGTABLE>, does it still gives a serial plan?

    Friday, July 12, 2013 2:00 AM
  • Andrew, so probably running in parallel was a costly operation and now SQL Server performs better, have you check it? Anyway http://sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Friday, July 12, 2013 3:18 AM
    Answerer
  • Now that the index is there, the QP is no longer running in parallel. I lower the cost threshold of parallelism but even when set to 1 the query still does not run in parallel.

    If you set 1, then it will not use parallel plan. It will use only serial plan. You may set it as 0 and see the execution plan. If Optimizer thinks parallel plan is better choice for your query, then it would definitely show you parallel plan.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, July 12, 2013 4:04 AM
  • Thanks for the help chaps!

    The query seems to move to a serial plan at around 35 million records to be extracted. I'll have a word with our developers and see if we can batch the process.

    Andrew

    Friday, July 12, 2013 8:23 AM