SQL Performance Issue

Question

SQL Performance Issue

suresh0534

SSCommitted

Points: 1966
More actions
February 6, 2012 at 4:20 pm

#267664

I have so many Stored Procedures in my DataBase, so,How to find which Stored Procedure is taking long time in Sql Server 2005.
Because, my sql server data base slow.user's can't accessing the web pages , which is taking a lot time of time to load the web page.
please send me a solution.
Thanks,
Suresh

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

You need to examine what is happening when the application is slow. SQL Trace is the best way to do this, along with something like WhoIsActive to find what is running at a slow time.

Here's an article on Profiler, but that is just the front end for trace: http://www.simple-talk.com/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/

WhoisActive: http://sqlblog.com/tags/Who+is+Active/default.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

For quick information on what's running slow, use sys.dm_exec_query_stats to pull out the most frequently called queries or the longest running queries. From there though, you need to know what to do to fix them. Query tuning is pretty involved proposition. I'd suggest picking up a copy of my book (below in my signature).

"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

gil 17845 SSC Enthusiast Points: 132 More actions · Answer 4

There are also a few free tools out there that will help you analyze the trace file and zero-in on the problematic procedure etc.

One such tool is Qure Analyzer. A full-functionality version is available here: SQL Server trace file analyzer

Hope this helps!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

suresh0534 (2/6/2012)
I have so many Stored Procedures in my DataBase, so,How to find which Stored Procedure is taking long time in Sql Server 2005.
Because, my sql server data base slow.user's can't accessing the web pages , which is taking a lot time of time to load the web page.
please send me a solution.
Thanks,
Suresh

Sounds like you have no experience tuning SQL Server. So my recommended "solution" is to hire a performance tuning consultant to a) find causes of poor performance, b) fix said causes and c) mentor you on how to do the same in the future. Win-Win-Win.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

glamourth SSC Enthusiast Points: 164 More actions · Answer 6

Hi All,

I have used sys.dm_exec_query_stats and sys.sysprocesses to find long running queries particulary more than 60 minutes. But, as am not gud at sql scripting not able to join these two dmv's that would give results with database name, host name, spid ,user name , elasped time taken in minutes and sql text. Also, it should automatically kill queries that are running more than 60 minutes.

Group: Awaiting for any response as early as possible.!

Thanks.!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

glamourth (6/13/2012)
Also, it should automatically kill queries that are running more than 60 minutes.

Why on earth would you want to do that, unless your goal is to seriously anger your users? What if that's a critical report for the big boss? An essential month end process?

Killing sessions is a bad idea in general. Killing automatially with no idea what they are and what they're doing is just asking for trouble.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 8

glamourth (6/13/2012)
Hi All,
I have used sys.dm_exec_query_stats and sys.sysprocesses to find long running queries particulary more than 60 minutes. But, as am not gud at sql scripting not able to join these two dmv's that would give results with database name, host name, spid ,user name , elasped time taken in minutes and sql text. Also, it should automatically kill queries that are running more than 60 minutes.
Group: Awaiting for any response as early as possible.!
Thanks.!

I'm with Gail, automatically killing sessions is a bad idea.

One point worth noting, sys.dm_exec_query_stats shows an aggregation for the queries currently in cache. It's not showing currently executing queries. For that you should be using sys.dm_exec_requests.

Also, just so you know, posting to an existing thread like this instead of starting your own, the only people who are going to see it are the people who have already posted here. You'll get more response if you start your own thread.

"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

glamourth SSC Enthusiast Points: 164 More actions · Answer 9

Thanks for your quick response Gail and Grant...!!

I understand and agree with you on a point that session should not be killed automatically unless we know what it is but reason behind this decision is we have approvals and request from app team to do so. They are okay and aware of consequences.

I am not able to find how to post/start a new thread to this site. Hence, thought of getting into the existing similar thread.

Sorry for the inconvenience caused, help me in my query ..

Thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Please start a new thread for your problem, don't hijack someone else's. On the main forum page there's a button at the top 'Add Topic'

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 11

GilaMonster (6/14/2012)
Please start a new thread for your problem, don't hijack someone else's. On the main forum page there's a button at the top 'Add Topic'

Actually, if you look up, there's an "Add Topic" button on this page too.

"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

glamourth SSC Enthusiast Points: 164 More actions · Answer 12

glamourth

SSC Enthusiast

Points: 164

June 14, 2012 at 5:02 am

#1500896

Thanks....