August 27, 2009 at 7:01 am
I have one SP that shows up as using the most resources on my SQL Server and have looked at trying to optimize it. It's dynamic, so I can't go through the execution plan (I think), and I've looked at all of the joins to try and see if there are any indexes that can be added. Are there any other tips that I can use to try and improve the performance for this SP, or dynamic SP's in general?
Thanks
August 27, 2009 at 7:09 am
Mostly the same rules apply to dynamic SQL as to normal code. Make sure that the query can use indexes, make sure that there are appropriate indexes.
I really can't say much more without seeing the query, the table definition, the index definitions and the exec plan. You definitely can get the exec plan for dynamic SQL, exactly the same way as for anything else - run in management studio with the 'include actual execution plan' option on.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 7:18 am
August 27, 2009 at 7:37 am
Like Gail said, you can get the execution plan in Management Studio. You can also query the cache through DMV's to look at the execution plan stored there.
I'd suggest building the dynamic query and executing it with sp_executesql. Doing this you can use parameters rather than just executing a string. This will help you avoid SQL injection and it will lead to plan re-use since the same dynamic query could be generated multiple times only using different values in the parameters.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply