Query Execution time take nearly 30 min

Question

Query Execution time take nearly 30 min

subha.v

SSC Journeyman

Points: 94
More actions
December 22, 2008 at 1:17 am

#204448

Hi All,
I have run the query like
Select ColB,ColC from table1 where ColD= 123.
It takes nearly 30 min to finish the execution.
In this table ColD have a non-clustered index. And ColA is the primary key and have a clusterd index.
Table1 contains nearly 700,00 records.
If anyone have idea please share with me.
Thanks
Subha

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

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

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

subha.v (12/22/2008)
Hi All,
I have run the query like
Select ColB,ColC from table1 where ColD= 123.
It takes nearly 30 min to finish the execution.
In this table ColD have a non-clustered index. And ColA is the primary key and have a clusterd index.
Table1 contains nearly 700,00 records.
If anyone have idea please share with me.
Thanks
Subha

70000 records in a table is not that much. If it was on my server the first thing that I'd look for is blocking. Did you check if your query was blocked by another session?

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/

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 2

subha.v (12/22/2008)
Hi All,
I have run the query like
Select ColB,ColC from table1 where ColD= 123.
It takes nearly 30 min to finish the execution.
In this table ColD have a non-clustered index. And ColA is the primary key and have a clusterd index.
Table1 contains nearly 700,00 records.
If anyone have idea please share with me.
Thanks
Subha

check the execution plan.

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

Sorry the table contain 700,000 records.

Thanks

Subha

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 4

subha.v (12/22/2008)
Sorry the table contain 700,000 records.
Thanks
Subha

700,000 records is not big size in table.

run the following DBCC commands:

> DBCC CHECKTABLE.

> DBCC UPDATEUSAGE ( ), if it is not a production server.

> DBCC CLEANTABLE

for more information see SQL BOL.

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

Kishore.P (12/22/2008)
> DBCC CHECKTABLE.
> DBCC UPDATEUSAGE ( ), if it is not a production server.
> DBCC CLEANTABLE

Why?

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

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

Hi,

Run DBCC DBREINDEX for your table and then try. 700K are not too much records to take 30 min. There must be some corruption in Index or Data pages. Try DBREINDEX, I am sure, it will solve your problem.

Best Regards

Nitin

http://www.enlinkURL.com

Regards,
Nitin

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

subha.v (12/22/2008)
Select ColB,ColC from table1 where ColD= 123.

How many rows will that return?

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

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

subha.v

SSC Journeyman

Points: 94

December 22, 2008 at 4:22 am

#916043

It's still running. Nearly 55 min.

Thanks

Subha

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

Hi,

Have you tried DBCC DBREINDEX ?

Best Regards

Nitin

http://www.enlinkURL.com

Regards,
Nitin

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

subha.v (12/22/2008)
It's still running. Nearly 55 min.
Thanks
Subha

Any reason that you don't want us to know if you checked for blocking as I asked you before?

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/

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 11

[font="Comic Sans MS"]

Subha,:)

Is it your local system or are you working on remote server??

and as Gail said how many rows it will return approximately and what is your machine configuration??

Cheers!

Sandy.

[/font]

--

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

subha.v (12/22/2008)
It's still running. Nearly 55 min.

Is it blocked? Is it wating for a resource, if so what's the wait type and wait resource?

How many rows out of the 700000 will that query return?

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

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

nitinpatel31 (12/22/2008)
There must be some corruption in Index or Data pages.

If there was corruption of any form the query would not run forever. It would fail with an error, typically 823 or 824.

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

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

It's either a really poorly written query or you have blocking. You need to look at, at least, sp_who2.

Also, can you get the execution plan? Query sys.dm_exec_query_plans if you can't execute it and get it. That will provide you with the xml plan. You can zip that & post it here.

Why should he run off and run DBREINDEX when he didn't even check for fragmentation first?

This can be run to check for fragmentation:

SELECT s.avg_fragmentation_in_percent

,s.fragment_count

,s.page_count

,s.avg_page_space_used_in_percent

,s.record_count

,s.avg_record_size_in_bytes

,s.index_id

FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2008'),

OBJECT_ID(N'Purchasing.PurchaseOrderHeader'), NULL, NULL,

'Sampled') AS s

WHERE s.record_count > 0

ORDER BY s.index_id;

Running all those DBCC checks before you diagnose the issue can be a very dangerous way to operate, especially around a production system that has an SLA on uptime and availability.

"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