optimizer shows high record count

We recently have found a query that started to run long (30 seconds as
opposed to <1). No significant change in data, statistics updated,
etc. SQLServer 2000 sp3
When looking at the rowcount on one of the execution plan tasks it
shows >9 million rows, the table only has 14K rows in it.
We've done dbcc on the table/indexes with no results/corruption. In
our development environment, this works as expected, that is the row
count matches the estimated row count.
Any thoughts?
Thanks in advance.
Jason
Have you updated the statistics or reindexed that table lately?
Andrew J. Kelly SQL MVP
"fc_celtic_fan" <jasonb@.brandes.com> wrote in message
news:1107996642.783750.242070@.g14g2000cwa.googlegr oups.com...
> We recently have found a query that started to run long (30 seconds as
> opposed to <1). No significant change in data, statistics updated,
> etc. SQLServer 2000 sp3
> When looking at the rowcount on one of the execution plan tasks it
> shows >9 million rows, the table only has 14K rows in it.
> We've done dbcc on the table/indexes with no results/corruption. In
> our development environment, this works as expected, that is the row
> count matches the estimated row count.
> Any thoughts?
> Thanks in advance.
> Jason
>
|||Yes we have been in the boat as well and we used to run DBCC
updateusage('dbname','tablename')
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1xTABxDFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Have you updated the statistics or reindexed that table lately?
> --
> Andrew J. Kelly SQL MVP
>
> "fc_celtic_fan" <jasonb@.brandes.com> wrote in message
> news:1107996642.783750.242070@.g14g2000cwa.googlegr oups.com...
>
|||What does sysindexes say? Also, did you do UPDATEUSAGE with ROW_COUNT?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:Owi8xM0DFHA.3596@.TK2MSFTNGP10.phx.gbl...
> Yes we have been in the boat as well and we used to run DBCC
> updateusage('dbname','tablename')
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1xTABxDFHA.2676@.TK2MSFTNGP12.phx.gbl...
>

optimizer shows high record count

We recently have found a query that started to run long (30 seconds as
opposed to <1). No significant change in data, statistics updated,
etc. SQLServer 2000 sp3
When looking at the rowcount on one of the execution plan tasks it
shows >9 million rows, the table only has 14K rows in it.
We've done dbcc on the table/indexes with no results/corruption. In
our development environment, this works as expected, that is the row
count matches the estimated row count.
Any thoughts?
Thanks in advance.
JasonHave you updated the statistics or reindexed that table lately?
Andrew J. Kelly SQL MVP
"fc_celtic_fan" <jasonb@.brandes.com> wrote in message
news:1107996642.783750.242070@.g14g2000cwa.googlegroups.com...
> We recently have found a query that started to run long (30 seconds as
> opposed to <1). No significant change in data, statistics updated,
> etc. SQLServer 2000 sp3
> When looking at the rowcount on one of the execution plan tasks it
> shows >9 million rows, the table only has 14K rows in it.
> We've done dbcc on the table/indexes with no results/corruption. In
> our development environment, this works as expected, that is the row
> count matches the estimated row count.
> Any thoughts?
> Thanks in advance.
> Jason
>|||Yes we have been in the boat as well and we used to run DBCC
updateusage('dbname','tablename')
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1xTABxDFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Have you updated the statistics or reindexed that table lately?
> --
> Andrew J. Kelly SQL MVP
>
> "fc_celtic_fan" <jasonb@.brandes.com> wrote in message
> news:1107996642.783750.242070@.g14g2000cwa.googlegroups.com...
>|||What does sysindexes say? Also, did you do UPDATEUSAGE with ROW_COUNT?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:Owi8xM0DFHA.3596@.TK2MSFTNGP10.phx.gbl
..
> Yes we have been in the boat as well and we used to run DBCC
> updateusage('dbname','tablename')
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1xTABxDFHA.2676@.TK2MSFTNGP12.phx.gbl...
>sql

optimizer shows high record count

We recently have found a query that started to run long (30 seconds as
opposed to <1). No significant change in data, statistics updated,
etc. SQLServer 2000 sp3
When looking at the rowcount on one of the execution plan tasks it
shows >9 million rows, the table only has 14K rows in it.
We've done dbcc on the table/indexes with no results/corruption. In
our development environment, this works as expected, that is the row
count matches the estimated row count.
Any thoughts?
Thanks in advance.
JasonHave you updated the statistics or reindexed that table lately?
--
Andrew J. Kelly SQL MVP
"fc_celtic_fan" <jasonb@.brandes.com> wrote in message
news:1107996642.783750.242070@.g14g2000cwa.googlegroups.com...
> We recently have found a query that started to run long (30 seconds as
> opposed to <1). No significant change in data, statistics updated,
> etc. SQLServer 2000 sp3
> When looking at the rowcount on one of the execution plan tasks it
> shows >9 million rows, the table only has 14K rows in it.
> We've done dbcc on the table/indexes with no results/corruption. In
> our development environment, this works as expected, that is the row
> count matches the estimated row count.
> Any thoughts?
> Thanks in advance.
> Jason
>|||Yes we have been in the boat as well and we used to run DBCC
updateusage('dbname','tablename')
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1xTABxDFHA.2676@.TK2MSFTNGP12.phx.gbl...
> Have you updated the statistics or reindexed that table lately?
> --
> Andrew J. Kelly SQL MVP
>
> "fc_celtic_fan" <jasonb@.brandes.com> wrote in message
> news:1107996642.783750.242070@.g14g2000cwa.googlegroups.com...
> >
> > We recently have found a query that started to run long (30 seconds as
> > opposed to <1). No significant change in data, statistics updated,
> > etc. SQLServer 2000 sp3
> >
> > When looking at the rowcount on one of the execution plan tasks it
> > shows >9 million rows, the table only has 14K rows in it.
> >
> > We've done dbcc on the table/indexes with no results/corruption. In
> > our development environment, this works as expected, that is the row
> > count matches the estimated row count.
> >
> > Any thoughts?
> >
> > Thanks in advance.
> >
> > Jason
> >
>|||What does sysindexes say? Also, did you do UPDATEUSAGE with ROW_COUNT?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:Owi8xM0DFHA.3596@.TK2MSFTNGP10.phx.gbl...
> Yes we have been in the boat as well and we used to run DBCC
> updateusage('dbname','tablename')
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1xTABxDFHA.2676@.TK2MSFTNGP12.phx.gbl...
>> Have you updated the statistics or reindexed that table lately?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "fc_celtic_fan" <jasonb@.brandes.com> wrote in message
>> news:1107996642.783750.242070@.g14g2000cwa.googlegroups.com...
>> >
>> > We recently have found a query that started to run long (30 seconds as
>> > opposed to <1). No significant change in data, statistics updated,
>> > etc. SQLServer 2000 sp3
>> >
>> > When looking at the rowcount on one of the execution plan tasks it
>> > shows >9 million rows, the table only has 14K rows in it.
>> >
>> > We've done dbcc on the table/indexes with no results/corruption. In
>> > our development environment, this works as expected, that is the row
>> > count matches the estimated row count.
>> >
>> > Any thoughts?
>> >
>> > Thanks in advance.
>> >
>> > Jason
>> >
>>
>

optimizer problem

Hi,
We have a table having 3.2 million rows having primary key
clustered index on id column ...update statistics is done
with fullscan(100%)...
when we are running:
select count(*) from table1 ...it is taking about 4
minutes to return the result...when i see the statistics
io it shows that it is doing scan count:728...
How can this be doing scan count 728 on 2 cpu machine and
takes 4 min just to return count?
Thanks
--HarvinderIf it actually is a scan count of 728, that is not the same as Logical
reads. It means that SQL Server is accessing the table 728 times, and this
usually implies some sort of join.
Can you SET STATISTICS PROFILE ON and show us the output so we can see the
query plan in addition to the statistics?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> Hi,
> We have a table having 3.2 million rows having primary key
> clustered index on id column ...update statistics is done
> with fullscan(100%)...
> when we are running:
> select count(*) from table1 ...it is taking about 4
> minutes to return the result...when i see the statistics
> io it shows that it is doing scan count:728...
> How can this be doing scan count 728 on 2 cpu machine and
> takes 4 min just to return count?
> Thanks
> --Harvinder
>|||That was my other question...howcome it is doing 728 scan
count instead of 1 clustered index scan...i am pasting the
output of showplan :
select count(*) from tab1
|--Compute Scalar(DEFINE:([Expr1002]=Convert
([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM
([partialagg1003])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:
([partialagg1003]=Count(*)))
|--Clustered Index Scan(OBJECT:([dm].
[dbo].[tab1].[pk_tab1]))
Thanks
--Harvinder
>--Original Message--
>If it actually is a scan count of 728, that is not the
same as Logical
>reads. It means that SQL Server is accessing the table
728 times, and this
>usually implies some sort of join.
>Can you SET STATISTICS PROFILE ON and show us the output
so we can see the
>query plan in addition to the statistics?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"harvinder" <hs@.metratech.com> wrote in message
>news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
>> Hi,
>> We have a table having 3.2 million rows having primary
key
>> clustered index on id column ...update statistics is
done
>> with fullscan(100%)...
>> when we are running:
>> select count(*) from table1 ...it is taking about 4
>> minutes to return the result...when i see the
statistics
>> io it shows that it is doing scan count:728...
>> How can this be doing scan count 728 on 2 cpu machine
and
>> takes 4 min just to return count?
>> Thanks
>> --Harvinder
>
>.
>|||I was actually hoping for the STATISTICS PROFILE output in addition to the
exact STATISTICS IO that I assumed you were already collecting.
My guess at this point (without seeing the STATISTICS IO output) is that
the high scan count is related to the fact that the query is being processed
in parallel.
The large amount of time is probably because of the clustered index scan. A
clustered index scan is exactly the same as a table scan, so to get the
results of count(*) SQL Server has to look at every row on every page. How
many rows and how many pages are in this table? Does the query include a
WHERE clause? What is the result of your count(*) query?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:051501c35230$b8aed800$a301280a@.phx.gbl...
> That was my other question...howcome it is doing 728 scan
> count instead of 1 clustered index scan...i am pasting the
> output of showplan :
> select count(*) from tab1
> |--Compute Scalar(DEFINE:([Expr1002]=Convert
> ([globalagg1004])))
> |--Stream Aggregate(DEFINE:([globalagg1004]=SUM
> ([partialagg1003])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:
> ([partialagg1003]=Count(*)))
> |--Clustered Index Scan(OBJECT:([dm].
> [dbo].[tab1].[pk_tab1]))
> Thanks
> --Harvinder
> >--Original Message--
> >If it actually is a scan count of 728, that is not the
> same as Logical
> >reads. It means that SQL Server is accessing the table
> 728 times, and this
> >usually implies some sort of join.
> >Can you SET STATISTICS PROFILE ON and show us the output
> so we can see the
> >query plan in addition to the statistics?
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"harvinder" <hs@.metratech.com> wrote in message
> >news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> We have a table having 3.2 million rows having primary
> key
> >> clustered index on id column ...update statistics is
> done
> >> with fullscan(100%)...
> >> when we are running:
> >> select count(*) from table1 ...it is taking about 4
> >> minutes to return the result...when i see the
> statistics
> >> io it shows that it is doing scan count:728...
> >> How can this be doing scan count 728 on 2 cpu machine
> and
> >> takes 4 min just to return count?
> >>
> >> Thanks
> >> --Harvinder
> >>
> >
> >
> >.
> >|||if your system is a Xeon or Xeon MP, and HT is enabled,
and you have a parallel execution plan
try OPTION (MAXDOP 1)
better yet, disabled HT
>--Original Message--
>Hi,
>We have a table having 3.2 million rows having primary
key
>clustered index on id column ...update statistics is done
>with fullscan(100%)...
>when we are running:
>select count(*) from table1 ...it is taking about 4
>minutes to return the result...when i see the statistics
>io it shows that it is doing scan count:728...
>How can this be doing scan count 728 on 2 cpu machine and
>takes 4 min just to return count?
>Thanks
>--Harvinder
>.
>