Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Server Side Trace Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 9:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:51 AM
Points: 233, Visits: 640
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]


  Post Attachments 
Server Side Trace.PNG (17 views, 48.43 KB)
Post #1566008
Posted Tuesday, April 29, 2014 10:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:02 PM
Points: 935, Visits: 2,686
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
Post #1566030
Posted Tuesday, April 29, 2014 10:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:51 AM
Points: 233, Visits: 640
@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]
Post #1566037
Posted Tuesday, April 29, 2014 11:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:02 PM
Points: 935, Visits: 2,686
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
Post #1566075
Posted Tuesday, April 29, 2014 11:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 14,029, Visits: 28,404
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566078
Posted Tuesday, April 29, 2014 12:09 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1566083
Posted Tuesday, April 29, 2014 12:56 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:51 AM
Points: 233, Visits: 640
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?

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]
Post #1566101
Posted Tuesday, April 29, 2014 2:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1566131
Posted Tuesday, April 29, 2014 2:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:51 AM
Points: 233, Visits: 640
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]
Post #1566136
Posted Tuesday, April 29, 2014 2:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1566143
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse