Post reply

SQL Performance Issue

  • 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

  • February 6, 2012 at 4:53 pm

    #1443500

    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

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • February 6, 2012 at 5:07 pm

    #1443503

    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

  • February 7, 2012 at 5:40 am

    #1443701

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 22, 2012 at 7:43 am

    #1450296

    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!

  • February 23, 2012 at 7:37 am

    #1450861

    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

  • June 13, 2012 at 1:21 am

    #1500178

    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.!

  • June 13, 2012 at 4:23 am

    #1500243

    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

  • June 13, 2012 at 4:32 am

    #1500251

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 13, 2012 at 9:29 pm

    #1500773

    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!

  • June 14, 2012 at 2:43 am

    #1500831

    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

  • June 14, 2012 at 4:40 am

    #1500888

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 14, 2012 at 5:02 am

    #1500896

    Thanks....

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

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