Server Side Trace

  • I am wondering if someone a help a brother out? I will try to give as much information as possible.

    Problem: I was asked to look into the DB since we are having some performance issues. The page isn't loading, when you enter a data, it takes minutes to load or save etc(just a typical performance problem).

    I have no skill on performance tuning whatsoever, however I tried following this link https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/ and ran a trace. This is the result I got.

    We only have 1 instance with 1 DB on this VM: 2 cpu, 8 gb of memory, 6GB is set as MAX MEM and we also shrink DB on a regular basis(which is a terrible thing to do by the way)

    DB is only 6GB in size:

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Running a trace is only helpful when you know what you need to look for and I general start with the wait statistics on a server I have never touched. You can actually get a good beginner's guide on wait stats from SQLSkills.com here[/url]. Pretty much all performance issues can be seen or found by just looking at the highest wait stats that show up for your instance[/url]. Wait stats can help in pointing out if the issue is showing up on SQL Server side or on the application side. This is one of those topics I have tried to read up on the most this year, that and indexing.

    With your particular situation, though, since it is for a specific page in your application, ask the developers to give you the queries that are being executed. You might also ask to be provided with any parameters or variables that are passed into the query for examples, where you can run the code yourself in a test environment if available. You may find queries that need some query hints to force them to be handled a certain way by SQL Server (generally last resort for me).

    I would also look if there are specific times the page is having issues, possibly when your shrink operation is running, and ensure no adverse effects are coming from other things being run against the server or the specific database.

    When it is with a specific page or area of an application that is having issues I will try to pull the execution plan for those queries, just for reference. If there are pain points in the execution plan that show up as red flags then I will pay close attention to those during the whole process of troubleshooting (index scans, heap scans, etc.). It is all gathering information on the problem first, and then trying to figure out where to go from there.

    One more link is by RedGate that has some good troubleshooting steps: Troubleshooting SQL Server: A Guide for the Accidental DBA.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • @Shwan:

    Is there a way you can look at the attachment. I just wanted to know if those numbers look normal.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • New Born DBA (4/29/2014)


    @Shwan:

    Is there a way you can look at the attachment. I just wanted to know if those numbers look normal.

    No, not really. I have no clue if those numbers are good or bad unless you have a baseline of the data for me to reference.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • The query times aren't terribly long at 200-800 milliseconds or so. But the fact that you're using sp_trace_getdata to collect information means you gathered this using the GUI. Don't do that. There's a reason it's your number one slow query. Better to run a server-side trace using T-SQL, or better still go to extended events.

    But, that aside, the queries aren't fast, what we can see, but they're not egregiously slow either. Not sure what else to say about what's there.

    "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

  • New Born DBA (4/29/2014)


    I have no skill on performance tuning whatsoever, however I tried following this link https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/ and ran a trace.

    That article points out that just looking for the highest duration query is a waste of time, and it suggests aggregating by procedure name to get the overall highest impact queries.

    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
  • I will try to dig a little deeper but the problem is that I don't have anybody to talk to yet about what procedure it's calling and when it happens?

    All I was told that this is working slow, customer is complaining, can I fix it?:unsure:

    Is Server side trace the best option I have to find out what's going on with the performance. I don't think it's acting up like this because of the Hardware since I already mentioned that it's got 6GB memory and the DB is very small in size. And I also mentioned that we are shrinking the DB every week, so you guys don't think that's what's causing the problem right?

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • New Born DBA (4/29/2014)


    And I also mentioned that we are shrinking the DB every week

    Why on earth would you do that?

    There's nothing in what you show to go on. In fact, I can't see anything other than monitoring queries, nothing that looks like an application users use.

    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
  • GilaMonster (4/29/2014)


    Why on earth would you do that?

    We are putting a STOP to that. No more shrinking

    GilaMonster (4/29/2014)


    There's nothing in what you show to go on. In fact, I can't see anything other than monitoring queries, nothing that looks like an application users use.

    I understand there is nothing you can see because there is nothing to show. My question would be that where do I even start from?

    Let's say I come to you and say. Gila, the page isn't loading. We are experiencing terrible performance issue and the length of time it's taking for records to display and it also takes time when we try to save a record. What can be done? What would you ask me as a DBA? How would you approach? What are some of the things you will do to resolve these kind of problems?

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Assuming it's a regular problem and not suddenly today it's slow when it's usually fine...

    I'd run a server-side trace and do the analysis that was described in the article you mentioned in your initial post. I wrote that article for a reason.... 😀

    Or, if the person coming to me was the developer of this page and its slow during their development phase, get them to walk me through what the page does, step by step

    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
  • GilaMonster (4/29/2014)


    I'd run a server-side trace and do the analysis that was described in the article you mentioned in your initial post. I wrote that article for a reason.... 😀

    Or, if the person coming to me was the developer of this page and its slow during their development phase, get them to walk me through what the page does, step by step

    Got it. Thanks. I will write back to this post as soon as I find out more.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • New Born DBA (4/29/2014)


    And I also mentioned that we are shrinking the DB every week, so you guys don't think that's what's causing the problem right?

    I'm not saying this is the problem here, but it certainly could be part of the problem. Even with a really small database (on what sounds like a pretty small system) fragmentation, which you'll get from all the shrinks, can lead to pretty poor performance.

    "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

  • I ran this to find out how fragmented all the indexes were and it gave me only 2 indexes which were more than 80% fragmented. So I don't think fragmentation is an issue.

    SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,

    ind.name AS IndexName, indexstats.index_type_desc AS IndexType,

    indexstats.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

    INNER JOIN sys.indexes ind

    ON ind.object_id = indexstats.object_id

    AND ind.index_id = indexstats.index_id

    WHERE indexstats.avg_fragmentation_in_percent > 30 and

    Page_count > 10

    ORDER BY Table_Name

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Yeah, but that's index fragmentation. Shrinking and growing a database file fragments at the OS level. There's a decent little article about how to look at this over here at SQL Tips.[/url]

    "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

  • Grant Fritchey (4/29/2014)


    Yeah, but that's index fragmentation. Shrinking and growing a database file fragments at the OS level. There's a decent little article about how to look at this over here at SQL Tips.[/url]

    One more question and I will try to leave you guys alone. How do you unshrink the data files? I mean once you shrink the data files, does it stay shrink? We have a maintenance plan, ran 2 days ago which shrunk the data files, how long will it stay in that condition?

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

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

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