Query Execution time take nearly 30 min

Question

Query Execution time take nearly 30 min

Viewing 15 posts - 16 through 30 (of 30 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Look at waitstats on the query.

If you are querying 700K records from a local client against a server you could be running into serious network slowness while it tries to bring those 700k records back to your machine.

Shamless self promotion - read my blog http://sirsql.net

subha.v SSC Journeyman Points: 94 More actions · Answer 2

Sorry. I am waiting for my PL approval.

Could you please confirm is searching based on the non-clustered index cause the performance problem.

Thanks

Subha

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

subha.v (12/22/2008)
Sorry. I am waiting for my PL approval.

?

Could you please confirm is searching based on the non-clustered index cause the performance problem.

No. Not enough information.

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

What datatype is ColD? Is it a computed column? Persisted?

N 56°04'39.16"
E 12°55'05.25"

subha.v SSC Journeyman Points: 94 More actions · Answer 5

subha.v

SSC Journeyman

Points: 94

December 22, 2008 at 6:40 am

#916112

ColD is integer datatype.

Thanks

Subha

subha.v SSC Journeyman Points: 94 More actions · Answer 6

I am selecting column from TABLE1 depending on ColD (having noe clustered index).

Selecting columns from table on non-clustered index cause the performance problem.

Advanced thaks for all.I didn't expect this much reply from my post. It's really helpful for me to know more about the problem.

Thanks

Subha

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

Without at least an execution plan, we're just not going to be very specific in helping you.

Yeah, you might be accessing the clustered index, but you might not. It depends on what is happening within the query. The code can be read to see if there is anything that would prevent index usage, the execution plan will show what was actually used. If you need further information then you can look at the selectivity of the index, the data types stored, fragmentation, contention... But right now, it's just guesses.

"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

subha.v SSC Journeyman Points: 94 More actions · Answer 8

This is the error I got when I have run the DBCC CHECKTABL query

Msg 8966, Level 16, State 2, Line 1

Unable to read and latch page (3:319) with latch type SH. Latch failed.

CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.

is the minimum repair level for the errors found by DBCC CHECKTABLE (TABLE1).

Please help me.

Thanks

Subha

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 9

This is consistancy error. You just try with Index rebuid. It should solve the problem.

Best Regards

Nitin

Regards,
Nitin

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

subha.v (12/22/2008)
Advanced thaks for all.I didn't expect this much reply from my post. It's really helpful for me to know more about the problem.

If you answered all the questions we've asked, we might actually be able to help you solve this problem.

Unable to read and latch page (3:319) with latch type SH. Latch failed.
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
is the minimum repair level for the errors found by DBCC CHECKTABLE (TABLE1).

Looks like some problems with the database, though not explicitly with that table. You may want to do a full checkDB when you have some downtime, make sure there's nothing lurking.

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 11

GilaMonster (12/22/2008)
subha.v (12/22/2008)
Advanced thaks for all.I didn't expect this much reply from my post. It's really helpful for me to know more about the problem.
If you answered all the questions we've asked, we might actually be able to help you solve this problem.

Maybe he can't answer the questions that we've asked because he didn't get his PL approval yet?:D

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

noeld SSC Guru Points: 96590 More actions · Answer 12

Is the index in ColD being used ?

Is the server overloaded ?

Can you post the execution plan ?

Can you post the DDL of the table ?

.... Too many questions ... not enough info 🙁

* Noel

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 13

[font="Comic Sans MS"]

Subha,:)

I really surprised what type of approval:D you need from your TL/PL for this...It's funny....:):P;):D:):w00t::cool::hehe:

Anyway, Subha, if you are really facing issue on your query and run sp_updatestats on your Database, then you can check the query depth level...i mean to say if it consists more while loops then it may be a issue for execution....

Check all the pros n cons and you will get the success...and I wants to say one more thing if its blocked then it will not run and it will not return output at all....

Cheers!

Sandy.

[/font]

--

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 14

Hi;

I think run this query and look at the your sql statment wait type if wait type start LCK it means lock if another think post it and we will explain. Firstly we find the reason.

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 15

Sorryy query is here....

SELECT wait_duration_ms,sys.dm_os_waiting_tasks.session_id,

sys.dm_os_waiting_tasks.wait_type,STATUS,

(select text from sys.dm_exec_sql_text(sql_handle))

FROM sys.dm_os_waiting_tasks

INNER JOIN sys.dm_exec_requests ON sys.dm_os_waiting_tasks.session_id=sys.dm_exec_requests.session_id

where STATUS <> 'background'

ESAT ERKEÇ

not DBA not MVP