4

Somewhat Related

What are the best practices in writing a sql stored procedure

Question

I have written a sql-xml stored procedure in Microsoft SQL server express 2005, now how does one find how many executions it can do in a second, time it takes for the same and how to do load testing on the same. Are there any open source tool for the same? Is there any pattern i can follow for improving the execution count? What performance bottlenecks do stored procedure generally face?

Community
  • 1
  • 1
Deeptechtons
  • 10,945
  • 27
  • 96
  • 178
  • 1
    [How Can I Log and Find the Most Expensive Queries?][1] [1]: http://stackoverflow.com/questions/257906/how-can-i-log-and-find-the-most-expensive-queries – Mitch Wheat Oct 08 '11 at 07:12
  • This belongs on Database Administrators SE. (http://dba.stackexchange.com) –  Oct 08 '11 at 13:49
  • @Surfer513 Database Administrators profile queries? i thought the developer was in charge of it.[I do] – Deeptechtons Oct 09 '11 at 16:44
  • @MitchWheat The related post does not relate to my question, but does fall under profiling and designing queries – Deeptechtons Oct 09 '11 at 16:45
  • @Deeptechtons: actually it does relate to your question. – Mitch Wheat Oct 10 '11 at 00:17
  • @MitchWheat could you rewrite that post for sql server 2005 express as the answer for me to accept, because i was not possible to find few items from that post. – Deeptechtons Oct 10 '11 at 06:01

2 Answers2

2

What you can do is utilize SET STATISTICS TIME ON to see the duration of the stored procedure. That'll give you a good idea of how many iterations will be able to take place in a second. Then to optimize/tune that stored procedure just view the actual execution plan and see what the most expensive portions of the stored procedure are.

And it'll heed you to use the stored procedure as a workload in the Database Engine Tuning Advisor. You may get a few hints as to missing indexes, possible improvements, etc.

2

Rather than using duration, I would examine the reads performed by setting SET STATISTICS IO ON, as this is not sensitive to server environmental load.

Include the 'Actual Execution Plan'. Examine the plan and look for:

  • Large Table or Index scans
  • Bookmark lookups that can be eliminated by creating covering indexes (that are not too wide...)
  • Large Discrepancies in "Actual Rows. versus 'Estimated Rows' (usually an indication that statistics are out of date).

Also see: How Can I Log and Find the Most Expensive Queries?

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • when you mean `Examine plan for Large Table or Index scans` could you put it in more detail so when i see the execution plan i know where to look at[i mean like does SSSms show these in red colors, big blocks etc] – Deeptechtons Oct 10 '11 at 08:05
  • will try to update in a few hours... – Mitch Wheat Oct 10 '11 at 08:07