April 29, 2014 at 9:27 am
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]
April 29, 2014 at 10:02 am
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
April 29, 2014 at 10:13 am
@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]
April 29, 2014 at 11:44 am
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
April 29, 2014 at 11:54 am
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
April 29, 2014 at 12:10 pm
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
April 29, 2014 at 12:56 pm
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]
April 29, 2014 at 2:26 pm
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
April 29, 2014 at 2:37 pm
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]
April 29, 2014 at 2:42 pm
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
April 29, 2014 at 2:44 pm
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]
April 29, 2014 at 3:21 pm
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
April 29, 2014 at 3:37 pm
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]
April 29, 2014 at 3:47 pm
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
April 30, 2014 at 7:23 am
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