How to trace slow running stored procedure?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hi Expert,

For My Winform application, I have Used sql server 2005 as Backend.

There are more than 9 lacs entries in a Companyinfo table.and Around 10 lacs data in emailinfo table which is secondory Table.

Currently i am inserting 50 rows using stored procedure,

This sp is very complex,in which i am checking duplicate entries,updating status,category,date,user etc of duplicate entries.

Currently there are 10 users.

from last 15 days, this sp working slow,and showing

timeout expired the timeout period elapsed

error usually.

I have traced sp using sql profiler,it shows

CPU : 4094

, reads 236310 writes 40

i set server transaction timeout=40

My other heavy stored procedure for same databse working fine,from which i am inserting 1000 rows within seconds.

Please Help me to sort this problem.

Thanks in advance

And sorry for poor english.

Posted 30-May-13 1:49am

udusat13

Add a Solution

Comments

Rockstar_ 30-May-13 7:52am

You can use sql server Profiler, which is available in Tools menu of SSMS...

udusat13 30-May-13 8:13am

Thanks for suggestion.

2 solutions

Solution 1

read this articles. this will help you..
Speed up SQL Server database queries with these tips[^]
Speed up SQL Server queries with PREFETCH[^]

Posted 30-May-13 1:56am

amnk.info

Updated 30-May-13 5:31am

v4

Comments

udusat13 30-May-13 8:12am

thanks for links and reply.

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)

Sridhar Patnayak · Accepted Answer · 2013-05-30T02:07:00

Solution 2

Hi udusat1,

I think this is not the problem with your SP, it is not properly indexed, first check integrity constraints are their on the tables or not, and implement indexing on the tables, so that it will increase speed of crud operations on your database.

Thanks
SP

Posted 30-May-13 2:07am

Sridhar Patnayak

Updated 30-May-13 2:14am

v2

Comments

udusat13 30-May-13 8:12am

Thanks For your reply,
I have indexed the table.

udusat13 7-Jun-13 2:30am

The table was poorly indexed.

Now Its working fine.