SQL Server Performance Forum – Threads Archive

Parallelism Blues

Hi- I turned off parallelism (set the MAXDOP =1) to check one or two queries. The parallelism was removed from the execution plan, as expected. However, it actually made the timings slower, so I turned the parallelism back on. Unfortunately, the execution plan is ‘stuck’ on this new one (without the parallelism). I need to get it back to where it was prior to my change. Things I’ve tried:
1) Clearing the cache by using DBCCs: FREEPROCCACHE, FLUSHPROCINDB, and DROPCLEANBUFFERS
2) Updating statistics on the two tables (with fullscan)
3) Adding a parallelism hint ‘OPTION (MAXDOP 4)’ to the end of the query. None of these items seem to help. The last thing I need to do is bounce the SQL Server services, but that takes some political process to do. Does anyone have any ideas that can help? Thanks, Donger

exec sp_recompile <yoursphere> This will mark it for recompilation at next execution…

I don’t know the way to force parallelism. However, parallel execution plan is usually used when huge number of rows is returned or when there is no good index to support the query. Make sure query is written the way indexes can be used effectively and there are actually indexes that can speed up the query.

One of the first checks to perform when you are experiencing slow query execution times is an index analysis. If you are investigating a single query, you can use the Perform Index Analysis option in SQL Query Analyzer. Check the execution plan for correct index usage, join order & join types. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>