Post reply

Slow performance

  • August 31, 2009 at 5:44 pm

    #138525

    Slow performance on db … SQL Server usage spiking for long periods of time.What should I check, how can I find out what's wrong

  • August 31, 2009 at 6:00 pm

    #1047244

    Hi

    Execute the below query to see what are the SPIDs with more CPU

    USE [master]

    GO

    SELECT * FROM sysprocesses

    ORDER BY cpu DESC

    And run DBCC INPUTBUFFER(SPID) or fn_get_sql() to find more about what SPID is executing.

    Lets us know the results.

    Thanks,

    Raj

  • August 31, 2009 at 6:57 pm

    #1047252

    Raj has a very good suggestion.

    If you know when it is actually happening you can look at sp_who2 or else the Activity Monitor if you prefer a gui. Of course, if it is truly running slowly the activity monitor may take a long time to open or fail to pen at all.

    Of course, do not forget to look at what other processes are running on that machine, especially if you are running SQL Server under a virtual instance.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • August 31, 2009 at 8:06 pm

    #1047279

    Here is my results.What should I look for?

  • August 31, 2009 at 8:27 pm

    #1047285

    vv

  • September 1, 2009 at 12:29 am

    #1047320

    Was this captured during one of the slow periods? It shows very little running and does not seem to have any particularly long running queries at all (of course that is a relative term. For something meant to be executed dozens of times a minute a query taking an entire second could be far too long, while for other things that is more than acceptable).

    Try to capture one during a slow period. If that does not give you a clue as to where to look next, consider using the profiler for a while. The profiler will itself cause a slow down while it is running, but it will give you a wealth of information if you can let it run for 24 hours (or other relevant time depending on your local business and usage cycle.)

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • September 1, 2009 at 1:05 am

    #1047330

    As Timoty said Profiler uses resources..You can go for Server side Trace to track the issue.

  • September 1, 2009 at 5:58 am

    #1047432

    Krasavita (8/31/2009)

    Here is my results.What should I look for?

    Looking at that data, I don't see anything running especially slowly. There are no blocked processes. Are you sure that the server is running slow? Was this query taken while it was running slow? Have you gathered performance metrics using PerfMon to look at things like page life expectancy (for memory bottlenecks) cpu or disk wait queues, etc? And yes, setting up a server side trace so you can capture the queries as they're run. You need a lot more information before a solution can be proposed.

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

  • September 1, 2009 at 6:33 am

    #1047462

    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

  • September 1, 2009 at 6:38 am

    #1047465

    When I ran the query,it wasn't running slow

  • September 1, 2009 at 6:46 am

    #1047472

    You said when you ran the query it wasnt slow? Who/what else ran the specific query (WinUI/Web app???)?

    Did you run it @ the same time of day?

  • September 1, 2009 at 6:50 am

    #1047475

    Krasavita (9/1/2009)

    When I ran the query,it wasn't running slow

    Ah, well, that's why there wasn't any useful information in the query. You need to gather the information when the slow performance is actually occurring.

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

  • September 1, 2009 at 7:56 am

    #1047533

    Yes, for sp_who2 to be useful you need to run it during the trouble. If that is not possible/impractical then consider either the server side trace or profiler. Also, if you happen to know that the problem revolves around one specific query/procedure than that is invaluable information.

    The two articles Gail linked provide an excellent overview to locating causes for poor performance. One thing I think is worthy emphasizing from personal experience is also looking at what else is running on the server besides sql.

    Ideally, SQL should run on a dedicated server, but that is not always possible so anything else being run on that server can affect its performance indirectly. Similarly, even if SQL is on a server by itself its apparent performance can be slowed down if there is heavy network traffic at the moment.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • September 1, 2009 at 10:06 am

    #1047640

    timothyawiseman (9/1/2009)

    Similarly, even if SQL is on a server by itself its apparent performance can be slowed down if there is heavy network traffic at the moment.

    I've seen a SQL Server brought to its knees by an admin copying a large backup file off the server.

    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

  • September 1, 2009 at 3:26 pm

    #1047869

    GilaMonster (9/1/2009)

    timothyawiseman (9/1/2009)

    Similarly, even if SQL is on a server by itself its apparent performance can be slowed down if there is heavy network traffic at the moment.

    I've seen a SQL Server brought to its knees by an admin copying a large backup file off the server.

    This reminds me of the last 3 major server outages we had. We had a network/server admin performing some "routine" tasks on the server and SAN.

    Oops

    Try gathering the information once again during a slow period and post the results again.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 20 total)

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