How to Check Stored Procedure Performance in Sql server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server

SQL-Server-2008

How to Test performance of Query in Sql server 2008

Posted 18-Jul-12 2:20am

shriti 3

Add a Solution

Comments

[no name] 18-Jul-12 8:28am

By profiling it.

shriti 3 18-Jul-12 8:47am

Thanx

shriti 3 18-Jul-12 8:48am

Thanx

2 solutions

Solution 2

If you want to view the time consumed by the query you can view it using the properties window after each query is executed.

You can also use SQL Profiler for the same.

Enabling the "Include Actual Execution Plan" will give you the reason for the time taken to execute the query and if the solution is known to the SSMS then it will also tell you what can be done to improve the performance of the query

Posted 18-Jul-12 2:47am

Nisarg S Shah

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

StianSandberg · Accepted Answer · 2012-07-18T02:29:00

You can use Profiler to se how many reads, how much cpu etc.
MSDN: How To use SQL Profiler[^]

If you just want to measure speed you can use this technique:

SQL

DECLARE @start DateTime
DECLARE @end DateTime
SET @start = getDate()

-- Do your stuff

SET @end = getDate()
SELECT DATEDIFF(ms, @start, @end) -- Will show how many milliseconds your query used.

Another good tip is to turn on the execution plan:

SQL

SET SHOWPLAN_ALL ON
GO

That will give you a lot of interesting data about performance

Remember to turn it off after you are done:

SQL

SET SHOWPLAN_ALL OFF
GO

You can read more about what all those columns and numbers mean at MSDN: http://msdn.microsoft.com/en-us/library/aa259203(v=sql.80).aspx[^]