Much bigger result from Count(*) than Max(table identity number)

The problem I have is the count(*) or count(table identity column) show a
much bigger number than the actual number of rows.
When I run
select count(*) from tablename I get ~87,000,000 in return.
But the MAX number of table identity is in 10 M range also rowcnt from
sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will not
help me.
Why I get such different result. What should I do to correct this.
Thank you,
ktfWhat do you get when you try COUNT(YourIdentityColumn) ?
Assuming they're different, can you see if COUNT(*) is using a different
index?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||select count(IdentityColumn) from tablename
I get ~87,000,000 in return
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
and see if that works. It sounds like this bug
FIX: A parallel query may return unexpected results
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> The problem I have is the count(*) or count(table identity column) show a
> much bigger number than the actual number of rows.
> When I run
> select count(*) from tablename I get ~87,000,000 in return.
> But the MAX number of table identity is in 10 M range also rowcnt from
> sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> not
> help me.
> Why I get such different result. What should I do to correct this.
> Thank you,
> ktf|||also to just be sure:
Select count(*) IdentityColumn
group by IdentityColumn
having count(*)>1
Returns 0
Thank you
ktf
"Adam Machanic" wrote:
> What do you get when you try COUNT(YourIdentityColumn) ?
> Assuming they're different, can you see if COUNT(*) is using a different
> index?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||It is:
SQL enterprise 2000 clustered
NT.5.0.(2195)
8.00.760. SP3
7GB memory
4 processor
"Jasper Smith" wrote:
> What service pack are you on? Also try doing a count with OPTION(MAXDOP 1)
> and see if that works. It sounds like this bug
> FIX: A parallel query may return unexpected results
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> > The problem I have is the count(*) or count(table identity column) show a
> > much bigger number than the actual number of rows.
> >
> > When I run
> > select count(*) from tablename I get ~87,000,000 in return.
> >
> > But the MAX number of table identity is in 10 M range also rowcnt from
> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> > not
> > help me.
> >
> > Why I get such different result. What should I do to correct this.
> >
> > Thank you,
> > ktf
>
>|||Sounds like you're running into the bug then. Did you try doing a count with
option(maxdop 1) ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> It is:
> SQL enterprise 2000 clustered
> NT.5.0.(2195)
> 8.00.760. SP3
> 7GB memory
> 4 processor
>
> "Jasper Smith" wrote:
>> What service pack are you on? Also try doing a count with OPTION(MAXDOP
>> 1)
>> and see if that works. It sounds like this bug
>> FIX: A parallel query may return unexpected results
>> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> > The problem I have is the count(*) or count(table identity column) show
>> > a
>> > much bigger number than the actual number of rows.
>> >
>> > When I run
>> > select count(*) from tablename I get ~87,000,000 in return.
>> >
>> > But the MAX number of table identity is in 10 M range also rowcnt from
>> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
>> > not
>> > help me.
>> >
>> > Why I get such different result. What should I do to correct this.
>> >
>> > Thank you,
>> > ktf
>>|||Before I do that:
Is it going to reconfigure and change the server setting or it is only
within the session?
Is it going to put a big impact on the server?
Because I do not want to make that change yet.
Do I have to install SP4? Because we are not on 64-bit server.
Thank you,
"Jasper Smith" wrote:
> Sounds like you're running into the bug then. Did you try doing a count with
> option(maxdop 1) ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> > It is:
> > SQL enterprise 2000 clustered
> > NT.5.0.(2195)
> > 8.00.760. SP3
> > 7GB memory
> > 4 processor
> >
> >
> > "Jasper Smith" wrote:
> >
> >> What service pack are you on? Also try doing a count with OPTION(MAXDOP
> >> 1)
> >> and see if that works. It sounds like this bug
> >>
> >> FIX: A parallel query may return unexpected results
> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> > The problem I have is the count(*) or count(table identity column) show
> >> > a
> >> > much bigger number than the actual number of rows.
> >> >
> >> > When I run
> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >
> >> > But the MAX number of table identity is in 10 M range also rowcnt from
> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE will
> >> > not
> >> > help me.
> >> >
> >> > Why I get such different result. What should I do to correct this.
> >> >
> >> > Thank you,
> >> > ktf
> >>
> >>
> >>
>
>|||It's a query hint, it only affects the specific query in question. It won't
impact anything else. To avoid any issues just run
select count(*)
from tablename with(nolock)
option(maxdop 1)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> Before I do that:
> Is it going to reconfigure and change the server setting or it is only
> within the session?
> Is it going to put a big impact on the server?
> Because I do not want to make that change yet.
> Do I have to install SP4? Because we are not on 64-bit server.
> Thank you,
>
> "Jasper Smith" wrote:
>> Sounds like you're running into the bug then. Did you try doing a count
>> with
>> option(maxdop 1) ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> > It is:
>> > SQL enterprise 2000 clustered
>> > NT.5.0.(2195)
>> > 8.00.760. SP3
>> > 7GB memory
>> > 4 processor
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> What service pack are you on? Also try doing a count with
>> >> OPTION(MAXDOP
>> >> 1)
>> >> and see if that works. It sounds like this bug
>> >>
>> >> FIX: A parallel query may return unexpected results
>> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> > The problem I have is the count(*) or count(table identity column)
>> >> > show
>> >> > a
>> >> > much bigger number than the actual number of rows.
>> >> >
>> >> > When I run
>> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >
>> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> > from
>> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
>> >> > will
>> >> > not
>> >> > help me.
>> >> >
>> >> > Why I get such different result. What should I do to correct this.
>> >> >
>> >> > Thank you,
>> >> > ktf
>> >>
>> >>
>> >>
>>|||Jasper,
It gave me the correct number.
the config_value and run_value are set to 0 for "max degree of parallelism.
do you think I should turn it on.
Is it necessary to install sql sp4. The site does not say much about sp4.
Thank you,
ktf
"Jasper Smith" wrote:
> It's a query hint, it only affects the specific query in question. It won't
> impact anything else. To avoid any issues just run
> select count(*)
> from tablename with(nolock)
> option(maxdop 1)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
> > Before I do that:
> > Is it going to reconfigure and change the server setting or it is only
> > within the session?
> > Is it going to put a big impact on the server?
> > Because I do not want to make that change yet.
> > Do I have to install SP4? Because we are not on 64-bit server.
> >
> > Thank you,
> >
> >
> > "Jasper Smith" wrote:
> >
> >> Sounds like you're running into the bug then. Did you try doing a count
> >> with
> >> option(maxdop 1) ?
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
> >> > It is:
> >> > SQL enterprise 2000 clustered
> >> > NT.5.0.(2195)
> >> > 8.00.760. SP3
> >> > 7GB memory
> >> > 4 processor
> >> >
> >> >
> >> > "Jasper Smith" wrote:
> >> >
> >> >> What service pack are you on? Also try doing a count with
> >> >> OPTION(MAXDOP
> >> >> 1)
> >> >> and see if that works. It sounds like this bug
> >> >>
> >> >> FIX: A parallel query may return unexpected results
> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Jasper Smith (SQL Server MVP)
> >> >> http://www.sqldbatips.com
> >> >> I support PASS - the definitive, global
> >> >> community for SQL Server professionals -
> >> >> http://www.sqlpass.org
> >> >>
> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
> >> >> > The problem I have is the count(*) or count(table identity column)
> >> >> > show
> >> >> > a
> >> >> > much bigger number than the actual number of rows.
> >> >> >
> >> >> > When I run
> >> >> > select count(*) from tablename I get ~87,000,000 in return.
> >> >> >
> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
> >> >> > from
> >> >> > sysindexes shows the correct number of ~10 M. So DBCC UPDATEUSAGE
> >> >> > will
> >> >> > not
> >> >> > help me.
> >> >> >
> >> >> > Why I get such different result. What should I do to correct this.
> >> >> >
> >> >> > Thank you,
> >> >> > ktf
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||The fix for this bug is in SP4. Changing the server wide maxdop settings
will obviously affect all other queries so should only be done after
extensive testing. If you are not seeing any application related issues due
to this bug and it is only affecting "DBA" type activities then you don't
necessarily need to get on SP4 however it's probably worth doing anyway to
keep upto to date with the latest bug fixes
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ktf" <ktf@.discussions.microsoft.com> wrote in message
news:30CBAF3D-FC19-4F06-89A8-ED074AF96A98@.microsoft.com...
> Jasper,
> It gave me the correct number.
> the config_value and run_value are set to 0 for "max degree of
> parallelism.
> do you think I should turn it on.
> Is it necessary to install sql sp4. The site does not say much about sp4.
> Thank you,
> ktf
> "Jasper Smith" wrote:
>> It's a query hint, it only affects the specific query in question. It
>> won't
>> impact anything else. To avoid any issues just run
>> select count(*)
>> from tablename with(nolock)
>> option(maxdop 1)
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> news:806778F5-FE2A-4720-8225-8DED896DAB44@.microsoft.com...
>> > Before I do that:
>> > Is it going to reconfigure and change the server setting or it is only
>> > within the session?
>> > Is it going to put a big impact on the server?
>> > Because I do not want to make that change yet.
>> > Do I have to install SP4? Because we are not on 64-bit server.
>> >
>> > Thank you,
>> >
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> Sounds like you're running into the bug then. Did you try doing a
>> >> count
>> >> with
>> >> option(maxdop 1) ?
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> news:FED8A09D-5010-4044-BDF5-BE893A926CBD@.microsoft.com...
>> >> > It is:
>> >> > SQL enterprise 2000 clustered
>> >> > NT.5.0.(2195)
>> >> > 8.00.760. SP3
>> >> > 7GB memory
>> >> > 4 processor
>> >> >
>> >> >
>> >> > "Jasper Smith" wrote:
>> >> >
>> >> >> What service pack are you on? Also try doing a count with
>> >> >> OPTION(MAXDOP
>> >> >> 1)
>> >> >> and see if that works. It sounds like this bug
>> >> >>
>> >> >> FIX: A parallel query may return unexpected results
>> >> >> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b814509
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >>
>> >> >> Jasper Smith (SQL Server MVP)
>> >> >> http://www.sqldbatips.com
>> >> >> I support PASS - the definitive, global
>> >> >> community for SQL Server professionals -
>> >> >> http://www.sqlpass.org
>> >> >>
>> >> >> "ktf" <ktf@.discussions.microsoft.com> wrote in message
>> >> >> news:5FE1B605-B559-462F-9481-957936FDE24C@.microsoft.com...
>> >> >> > The problem I have is the count(*) or count(table identity
>> >> >> > column)
>> >> >> > show
>> >> >> > a
>> >> >> > much bigger number than the actual number of rows.
>> >> >> >
>> >> >> > When I run
>> >> >> > select count(*) from tablename I get ~87,000,000 in return.
>> >> >> >
>> >> >> > But the MAX number of table identity is in 10 M range also rowcnt
>> >> >> > from
>> >> >> > sysindexes shows the correct number of ~10 M. So DBCC
>> >> >> > UPDATEUSAGE
>> >> >> > will
>> >> >> > not
>> >> >> > help me.
>> >> >> >
>> >> >> > Why I get such different result. What should I do to correct
>> >> >> > this.
>> >> >> >
>> >> >> > Thank you,
>> >> >> > ktf
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>sql