December 7, 2007 at 9:05 am
I am having problem tuning a proc. I changed everything I could in the code but found out that the Proc is using different execution plan everytime depending on what date(Parameter) we pass. When it uses index seek performance is good but when it does index scan it is very slow. This is the structure of table CommandId -Clustered Primary Key, RequestId -(Foreign Key) has nonclustered index. both commandId and RequestId are frequently used in proc. I created Composite primary key on CommandId and RequestId which uses index seek all the time but that is not acceptable to user and I created nonclustered primary key on CommandId and Clustered Index on RequestId which works fine but my team lead laughed at me saying you are not suppose to create nonclustered primary key. How can I make it work?
December 7, 2007 at 10:14 am
clustered keys and primary keys have nothing to do with each other. the PK can be clustered or non-clustered and if someone thinks it's always clustered, either he doesn't understand what it means or doesn't realize the performance implications.
You can use hints to force a plan. You also might have bad statistics. I had someone update statistics with a higher sampling (90-100% of rows) and this helped as well.
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 7, 2007 at 12:51 pm
Thank you, Steve. I was able to get index seek with hints which I didn't want to do. But whatever works is fine for now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply