slow running query/queries

Question

slow running query/queries

randhirdadwal

Old Hand

Points: 368
More actions
March 14, 2009 at 5:57 am

#201565

Query/Queries are running slow. Troubleshooting steps being taken are
Check CPU utilization which is fine
Also check whether query is using relevant indexes or not which is being used
Check index fragmentation and reorganize or rebuild the indexes and still the query is running slow
All hardware is fine and there is no network issue. Query execution plan is fine.
Any more troubleshooting steps. Please help!!!

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Does the query returns large no. of rows? How much time does it take to execute? How about adding the execution plan as an attachment, so to check for the operators causing the problem?

--Ramesh

randhirdadwal Old Hand Points: 368 More actions · Answer 2

the query is not returning large number of rows still it is taking a huge time to complete. And anything other than operators which could be causing issue

Thanks

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Does the queries are part of a procedure/function that were created using RECOMPILE option? This may be causing the server to re-compile every time on each execution.

--Ramesh

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 4

Such slowness is normally caused by a mistake somewhere in the query... formulas around columns in the WHERE clause will prevent good use of indexes... misunderstanding of the data may cause a partial cross join... etc, etc.

Start with the actual execution plan... check the connecting arrows themselves... look for really fat ones that, when you click on them, contain more rows that what are in the table and look for really skinny ones... especially ones that have one row... those are usually some form of performance robbing RBAR and may be in the form of recursion, poorly written UDF's, or some inappropriate correlation within the queries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Susantha Bathige Ten Centuries Points: 1040 More actions · Answer 5

Reasons for slowness will be a badly written query. Use the profiler to see the reads, writes, cpu and duration figures.

Then get the actual execution plan and check where it takes more time.

Susantha

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 6

Another possibility is memory shortage and/or slow I/O. The former will almost certainly cause the latter.

Look in perfmon at the following counters:

SQLServer:Buffer Manager - Page Life Expectancy

Physical Disk (all disks used by SQL Server) - Avg Disk sec/Read and Avg Disk sec/Write

If you're not sure how to read these, report the values back to this post.

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

%Idle from your individual physical disks.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 8

Could run the query through the Database Tuning Advisor just to see what it's recommendation would be.

Tim White

maechismo_8514 SSCoach Points: 17510 More actions · Answer 9

Good advise above, but above all this it is always a good practice to create a baseline of your workload , later you can compare the monitoring activity with this baseline and then quickly identify the bottlenecks.

kjplreddy SSC Veteran Points: 216 More actions · Answer 10

Hi

can u plz give me some more clarification about page life expectancy and physical disk counters sec/read,sec/write.how can i troubleshoot by using this

plz help me

Regards

Jayapal

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 11

What version of SQL server are you using?

Are you seeing any Key lookups or RID lookups, Table scans or Index scans in the query execution plans?

Execution plan looks fine? ..... It would be more helpful if you could attach the query exec plan.

Are you experiencing any memory pressures?

Are you using a lot of ad-hoc queries or stored procs?

and.... Is the SQL Server 32 bit or 64-bit?

Thank You,

Best Regards,

SQLBuddy

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 12

Krishna Potlakayala (3/16/2009)
Good advise above, but above all this it is always a good practice to create a baseline of your workload , later you can compare the monitoring activity with this baseline and then quickly identify the bottlenecks.

Heh... unless the baseline was bad to begin with...:-D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Derrick Smith SSChampion Points: 10833 More actions · Answer 13

What is your parallelism set to? Have you tried setting MAXDOP 1, 2, 4, 8, 0 to test if results are any different? How about memory usage? Did you cap it and leave ~4GB or more for the OS?