Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server Side Trace


Server Side Trace

Author
Message
New Born DBA
New Born DBA
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 929
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:

[i]"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]
Attachments
Server Side Trace.PNG (17 views, 48.00 KB)
Shawn Melton
Shawn Melton
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 3495
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. Pretty much all performance issues can be seen or found by just looking at the highest wait stats that show up for your instance. 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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
New Born DBA
New Born DBA
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 929
@Shwan:
Is there a way you can look at the attachment. I just wanted to know if those numbers look normal.

[i]"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]
Shawn Melton
Shawn Melton
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 3495
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
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17553 Visits: 32253
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
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


New Born DBA
New Born DBA
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 929
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?

[i]"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]
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
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


New Born DBA
New Born DBA
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 929
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?

[i]"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]
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47181 Visits: 44356
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.... :-D

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search