performance of stored procedures

Question

I have a scenario where i am executing lot of queries to MS SQL Server database. My question is how can i see or verify the performance of a stored procedure instead of calling the query directly? Can i use only complex queries through stored procedure or do i need to go for stored procedures for all the queries which are being used in the application?

sunil · Answer

That was a good information. Thanks.

spgilmore · Answer

There are very few circumstances in which only a query will work. In general a stored procedure can do anything a query can do but more and faster. The stored procedure has already been processed and an execution plan created for it. Every time a query is run this task must be done. So the stored procedure has that performance advantage. The stored procedure can also do some things that queries cannot but can also use a lot of advanced stuff that queries cannot. Stored procedures are considered a different type of source so will work in different situations where queries won't and vice versa such as in unions. This is their greatest difference. In general a server costs a certain amount of money to buy for both the machine and the software license. The more work you can get it to do before upgrading the machine or buying a second machine altogether the more money you save. If your stored procedures run twice as fast or with half the resources and all your tasks are contained in stored procedures your total cost of ownership is cut in half. In addition if you use stored procedures your database administrator can modify the database to tune it for the task at hand and programs will not have to change to adapt. The stored procedures can be modified and not break anything. This is a huge reason to use stored procedures. Lastly if 10 programs all use the same query to perform the same task you have to keep them in synch when changes are made. With a stored procedure you only change it once and all the programs benefit from it. Yes you can mix and match stored procs and queries all you like but stored procs can also be used as recordsets inside queries or other stored procs where queries may not work. You can see the performance by simply running the queries repeatedly and timing them. I have done this and stored procedures have always been considerably faster for me.