November 21, 2002 at 8:58 am
I have a stored procedure that will execute in the same number of seconds even though I remove a covering index it uses.
When the covering index is on one of the tables used in the SP, SQL Server opts to not use parallelism. However, when I remove that index SQL Server decides to use parallelism. In any case the time to execute is the same.
In a case like this is it better for performance to have SQL Server use parallelism or to place the covering index and cause SQL Server to choose not to use parallelism?
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 21, 2002 at 9:02 am
I would recommend that you use the index and allow SQL to perform the SP without use of parallelism.
This will ensure that the other processor(s) are available for other users to make other calls on the database. Alter all having multiple processors is not just to run SPs faster, but also to provide more available threads which will support more users.
December 2, 2002 at 7:50 am
What type of statement(s) is the SP doing?
December 2, 2002 at 10:07 am
Some bugs with Parallelism. Bit me on one server and had to turn it off. There are some fixes in SP3 for this one.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 2, 2002 at 3:48 pm
What I think is, it is not the covered Index which is making the system not to choose parllelism but the effective execution plan which the system is generating is choosing parllelism when there is no covered Index.When the covered Index is on then it is choosing the covered Index to be more effecient over parllelism.
December 2, 2002 at 7:43 pm
There are plenty of issues surronding parallelism and the fact it tends to suffer at this point primarily for inconsitent data output for various reason (many have been fixed but they are still adding other fixes). Parallelism does have a better speed to processing time but I always test what happens in each condition makign sure my results are consitant.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply