Query tuning

Question

Post reply

Query tuning

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
July 25, 2011 at 11:19 pm

#254849

Hi All,
I am new to query tuning.
Can you please let know the process, steps which needs to take if any body comes to us and asks, that they have a query, its running for 5 mins, and we need to tune that query so that it should complete 1 or 2 mins.
What should we need to take into the considerations.
Thank You.
Regards,
Raghavender Chavva

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jannie-186227 SSCrazy Points: 2319 More actions · Answer 1

Hi

Your question is much like asking to summarize this entire forum into a few lines limited to that of a post.

However, a good start is understanding a query plan. Since you have a query you can use the plan to delve deeper.

First have a look at what all the operations mean in a query plan , this will introduce you to other concepts such as indexes , statistics and much more.

Bol is a valueble resource for this info!

Good luck!

Jannie

koustav_1982 SSCommitted Points: 1788 More actions · Answer 2

Jannie-186227 (7/26/2011)
Hi
However, a good start is understanding a query plan.

Yes that is what is a good starting point.

Steps:

1. Analyse the query plan in case its a single query: Go through the query plan, identify what type of indexes are being used [clustered/non-clustered], what type of search on the indexes is being perfromed [seek, scan], what are the joins involved and what type of join algorithm is SQL Server using to resolve it, what datatypes are the comparisions being made on etc etc and the list goes on

2.In case of a batch or SPROC, execute it and run a trace on the profiler with start and end time set so that you can catch the criminal query. Then you can take that single query and do tuning.

There are some other environmental factors like , server load when the query is being run or the server by itself or the lockings involved , but these can be looked into at a later point of time.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

People have written entire books on that. Literally.

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

stockholm Default port Points: 1468 More actions · Answer 4

GilaMonster (7/26/2011)
People have written entire books on that. Literally.

Best answer so far.

GilaMonster - you are best & real monster.

Jack Vamvas SSChasing Mays Points: 651 More actions · Answer 5

Adding to comments already made.

1) Identify , whether it's a pre deployment or post deployment query. If pre- then it must be optimised, if post, then optimise if a problem.

2) Analyse waits at the instance level

3) Match waits with queues

4)Drill down to the db level | process level

5)Tune indexes and queries

Aim to create a repeatable process - that at least , will give you an approach when problems arise.

Jack Vamvas
sqlserver-dba.com

jasonwi1202 SSC Veteran Points: 291 More actions · Answer 6

You can visit this site and it will take you through a lot of the introductory steps. There is also a troubleshooting tool that can help to identify some of the basic performance issues/fixes.

SQL Performance

Jason

Webmaster at SQL Optimizations School

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 7

I don't you will be given time to read the books and do your work but it is good to follow those books.

But for right now,

Check query plan for any missing indexes on those table, you can find it by putting your query in DB tuning advisor for indexes.

Check any query hints you can include to improve performance depends type of query.(Nolock), (Maxdop), with recompile if it is stored procedure.

Check for changes you can make to query to get the same results with elimination unnecessary coding.

If the datasets are really large and something you cannot do anything with that then what ever is there is good.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

@SQLFRNDZ (7/26/2011)
Check any query hints you can include to improve performance depends type of query.(Nolock), (Maxdop), with recompile if it is stored procedure.

Nolock if you don't mind potentially incorrect, duplicate or missed data

Maxdop if you've identified the problem is inappropriate parallelism and the query has been tuned as much as possible

recompile if you've identified that the problem is coming from execution plans which are not optimal for all possible executions.

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 9

M.Kahn (7/26/2011)
GilaMonster (7/26/2011)
People have written entire books on that. Literally.
Best answer so far.
GilaMonster - you are best & real monster.

I can't agree more with you:-D . Her name is Gail but I always address her as Gila Monster. you just post a query for performance problem. She will smell it from far far distance and inform you in a single breathe about a implicit data conversions taking place causing poor performance. :hehe:

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Let's take this from the top. Please post the actual execution plan. Table(s) DDL including indexes and keys.

If you don't mind, I preffer aiming at sub-second times. I usually stop when my only option left is to buy faster servers.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 11

Raghavender (7/25/2011)
Hi All,
I am new to query tuning.
Can you please let know the process, steps which needs to take if any body comes to us and asks, that they have a query, its running for 5 mins, and we need to tune that query so that it should complete 1 or 2 mins.
What should we need to take into the considerations.

Ninja has asked you to provide some schema details. If your issue has been resolved, never mind else please post the schema. Ninja has been helpful to me too in the past!