Query perfromance vary - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Query perfromance vary

Author

Topic

AustraliaDBA
Starting Member

38 Posts

Posted - 2011-06-20 : 00:41:28

Hi All,

again i need experts advice please help.
i came across a query and i had been asked why sometimes it returns result in 10mins and sometimes take hours.
first thing came to mind resources. when i looked at the server it is hardly in use and i tried the query few times. trust me soemtimes it finishes in 10-11 mins and sometime i have to cancel it.
please advise me from where i should start working to make the performance consistent.

Many Thanks

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-06-20 : 04:58:53

search here for comments on terms

"parameter sniffing"
"execution plan caching"

Seperately can you post table DDL and execution plans.

AustraliaDBA
Starting Member

38 Posts

Posted - 2011-06-21 : 23:58:10

sorry but i didn't get what you want me to do. Could you please elaborate. execution plan has 100 or 1000 of steps in it. when i looked at it i couldn't figure out what is going on.

Thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2011-06-22 : 01:41:59

1)SQL DDL , meaning Data Definition Language. Normally , this is a CREATE TABLE script.
2)With the Execution Plan - try to catch the text version of the plan. You can do this by setting SET SHOWPLAN_TEXT ON
SET SHOWPLAN_TEXT ON
GO
My query or stored procedure
GO
SET SHOWPLAN_TEXT OFF
GO

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Sachin.Nand

2937 Posts

Posted - 2011-06-22 : 08:32:56

Is this a adhoc query or a SP ?

PBUH

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-24 : 12:21:12

Is it all the time exactly the same query?

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-06-27 : 05:09:26

OP - "sorry but i didn't get what you want me to do."

I'm trying to get you to help yourself. My references to searching are meant to lead you to read other posts created here previously which sound like your problem. The DDL & execution plan, are items which would help us help you. We know so little about your problem it is guess work without a direct insight into the specific problem.

AustraliaDBA
Starting Member

38 Posts

Posted - 2011-06-27 : 20:16:05

yes it is same query all the time

Thanks

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-28 : 07:55:55

quote:
Originally posted by AustraliaDBA

yes it is same query all the time

Thanks

I see that execution plan is too large, but without posting at least query for the beginning, we don't have much info to work with.

Without any info, my first wild guess is: When it finishes in around 10 minutes, most of data might be in db cache, while the opposite could be true otherwise.

Or the reason might be different execution plan for "fast" and slow executions.

Or there might be locking involved for some executions.

Without more info one can just wild guess...

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-06-29 : 02:01:58

For a start I would start looking at wait_type when monitoring the query.My guess here would be that parallelism is the real culprit because I have seen the queries really taking a hit due to this.
As suggested by others you need to post the query and execution plan to move forward.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources