January 3, 2014 at 3:15 pm
I am having some performance issue when one of a developer is running a simple query from a view. He is complaining that the DB is really slow. What are some of the things I should look for?
"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]
January 3, 2014 at 3:40 pm
Things that usually cause problems for me
- Amount of data in source table
- View pulling from another server
- Need an index hint
- View contains bad joins
- Available Server space is low
- Other tasks on server blocking table/views
January 4, 2014 at 3:50 am
Since you know the specific query, your job got easier. You want to look at the code to ensure it's not using functions on columns in the WHERE clause or JOIN criteria. You'll want to look at the execution plan to understand how the optimizer is resolving the query so you can see if it's using the indexes you have or you need to add or modify indexes. There's actually tons and tons of stuff you can dig into in queries and query tuning. You can see more in the two books in my signature.
"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
January 4, 2014 at 12:55 pm
New Born DBA (1/3/2014)
I am having some performance issue when one of a developer is running a simple query from a view. He is complaining that the DB is really slow. What are some of the things I should look for?
Do the JOIN columns or WHERE clauses refer to any aggregated columns in the view?
If you really want a good answer on this one, see the second link in my signature line below under "Helpful Links".
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2014 at 9:18 am
New Born DBA (1/3/2014)
I am having some performance issue when one of a developer is running a simple query from a view. He is complaining that the DB is really slow. What are some of the things I should look for?
Hi. Jeff suggested that you look at his second link. Part of what that tutorial says is that you should supply the execution plans. In SQL 2008+ this is easy, and Jeff's tutorial shows exactly how to supply the XML to us. Moreover, sometimes the query optimizer will show you that you are missing an index. This is important!
For Example:
select
COL1
,COL2
,COL3
from FOOBAR
where COL1 between 5453701 and 5458101;
on a table with 300 million rows but no index on COL1 will require a table scan of all 300 million rows. An index on COL1 would likely improve performance by over 99 percent, and Management Studio will show you the suggested index even on an estimated execution plan.
Also, we can look at the query for other problems, like:
select
COL1 -- it's a varchar
,COL2
,COL3
from FOOBAR
where CONVERT(COL1,BIGINT) between 5453701 and 5458101;
This will likely result in a full table scan no matter what index is on COL1.
What we're trying to nicely say is that if you want specific help, ask for it with details. 😀
You asked for general directions, however. The two things above, missing indexes and wrapping the where clause check column in a function, are the two things I see most often, especially from end-user ad hoc queries.
You mentioned a view. The things that hold true for tables usually hold true in views. You can optimize the underlying query in a view to an extent. However, things like UNION will ruin your day every time. Analyzing the underlying query plan (even an estimated one) is the obligatory place to start.
Thanks
John.
January 8, 2014 at 9:49 am
This view is really small. I ran it production and it executed in 1 sec. Developers were having some other issues not related to DB. Thanks for help.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply