MS SQL Server - Simple Query hangs!!!!

Author

Simple Query hangs!!!!

Jim_OLAP

2006-01-26, 4:53 pm

All,

This query hangs --even though there are only 710,000 records in the table.

select count(*) from pos_raw

DBCC CHECK does not return any errors-

DBCC results for 'POS_Raw'.

There are 710765 rows in 8775 pages for object 'POS_Raw'.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'DSD'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

Uri Dimant

2006-01-26, 4:53 pm

Jim
What does an execution plan show? Do you have any indexes defined on the
table?
This example had written by Steve Kass shows that you can create an indexed
view to improve such queries

create table T (
i int,
filler char(1000) default 'abc'
)
go

create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go

create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view

go
drop view T_count
drop table T

"Jim_OLAP" <JimOLAP@discussions.microsoft.com> wrote in message
news:5D1237BC-8F82-46C6-8714- EDC79D6C2EDB@microso
ft.com...
> All,
>
> This query hangs --even though there are only 710,000 records in the
> table.
>
> select count(*) from pos_raw
>
>
> DBCC CHECK does not return any errors-
>
> DBCC results for 'POS_Raw'.
>
> There are 710765 rows in 8775 pages for object 'POS_Raw'.
>
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'DSD'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
>

Ryan

2006-01-26, 4:53 pm

Use sp_who2 active to see if you are blocked behind another user.

Does the count have to be gauranteed 100% accurate...? If not sp_spaceused
tablename may be of use to you

--
HTH. Ryan
"Jim_OLAP" <JimOLAP@discussions.microsoft.com> wrote in message
news:5D1237BC-8F82-46C6-8714- EDC79D6C2EDB@microso
ft.com...
> All,
>
> This query hangs --even though there are only 710,000 records in the
> table.
>
> select count(*) from pos_raw
>
>
> DBCC CHECK does not return any errors-
>
> DBCC results for 'POS_Raw'.
>
> There are 710765 rows in 8775 pages for object 'POS_Raw'.
>
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'DSD'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
>

Rick Sawtell

2006-01-26, 4:53 pm

"Jim_OLAP" <JimOLAP@discussions.microsoft.com> wrote in message
news:5D1237BC-8F82-46C6-8714- EDC79D6C2EDB@microso
ft.com...
> All,
>
> This query hangs --even though there are only 710,000 records in the
> table.
>
> select count(*) from pos_raw
>
>
> DBCC CHECK does not return any errors-
>
> DBCC results for 'POS_Raw'.
>
> There are 710765 rows in 8775 pages for object 'POS_Raw'.
>
> CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'DSD'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
>

Run sp_who2 active to see if you are being blocked by someone.

You may also try the (NOLOCK) hint.

If there are no indexes, then it may be doing a table-scan. You might want
to check the execution plan as well.

Rick Sawtell
MCT, MCSD, MCDBA

Jens

2006-01-26, 4:53 pm

If you are doing the query on a multiproc machines, try to make sure,
that the different processors are not blocking each other, use the
MAXDOP option for this then.

But that should be visible in the execution plan.

-Jens Suessmeyer.

Jim_OLAP

2006-01-26, 4:53 pm

Thanks all,

I had to restore the database from a backup -- there was some corruption
somewhere in that database, and I could not find it -- all is well after
restore.

Thanks again,

Jim

"Ryan" wrote:

> Use sp_who2 active to see if you are blocked behind another user.
>
> Does the count have to be gauranteed 100% accurate...? If not sp_spaceused
> tablename may be of use to you
>
> --
> HTH. Ryan
> "Jim_OLAP" <JimOLAP@discussions.microsoft.com> wrote in message
> news:5D1237BC-8F82-46C6-8714- EDC79D6C2EDB@microso
ft.com...
>
>
>

Sponsored Links