October 21, 2006 at 10:39 pm
My application peforms an UPDATE to to a table that SQL performs a "sp_prepexec" on. The problem is that it results in a "constant scan" instead of using the clustered index. When I perform the same update without all of the sp_prepexec parameters through query analyzer, it uses the clustered index and runs much faster. The application doesn't give me the control to force indexes etc... Any thoughts?
I am still learning exactly what a constant scan means. There is not a ton of good information on sp_prepexec or constant scans out there.
Thanks!
October 26, 2006 at 4:58 am
try http://support.microsoft.com/Default.aspx?id=224587
I think it would have to use a constant scan to recover parameters passed in the statements. I'd suggest you use profiler to capture the query plan, rather than QA, although the detail is overwhelming you may find it more useful. I generally use QA to produce plans but if I'm really trying to find something I always use profiler. Make sure you include the binary data column in profiler.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply