Count is different on same table? Datetime column error?

Hi,
I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup
server. A Full Backup of the db (prototype) was taken from SV1 was
applied to SV2. One of the tables in the database has over 40 million
rows in it. My task is to keep any rows no older than 18 months.
SV1.Prototype.dbo.tblConsignment approx. 42,000,000 rows
SV2.Prototype.dbo.tblConsignment approx. 41,800,000 rows
this is correct as SV1 is constantly in use, so SV2 will always be a
day or two behind. The issue arrises when I perform my count on both
servers, when I run the following on SV1:
select count(*) from tblConsignments
where [Date] >= '2003-08-01 00:00:00.000'
the count is approx 25,500,000 rows, however when I run the exact same
statement on SV2 the count is 19,500,000. Why would there be a
discrepancy of 6,000,000? The collation is the same for both servers
(SV2 is a higher spec server, SV1 is 2K enterprise editions, SV2 2k
standard, both SP3a).
When I do a count year by year (on both servers) it comes to aprrox.
35,000,000 however the total for the tblConsignments is 42million.
Possibly the answer would be to check the data more throughly, however
I would like to know if Datetime columns can be set to different
versions?
Thanks
qh
Have you broken the data into smaller chunks? E.g. maybe a week or a month
is missing on one server...
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1115224865.478078.231810@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup
> server. A Full Backup of the db (prototype) was taken from SV1 was
> applied to SV2. One of the tables in the database has over 40 million
> rows in it. My task is to keep any rows no older than 18 months.
> SV1.Prototype.dbo.tblConsignment approx. 42,000,000 rows
> SV2.Prototype.dbo.tblConsignment approx. 41,800,000 rows
> this is correct as SV1 is constantly in use, so SV2 will always be a
> day or two behind. The issue arrises when I perform my count on both
> servers, when I run the following on SV1:
> select count(*) from tblConsignments
> where [Date] >= '2003-08-01 00:00:00.000'
> the count is approx 25,500,000 rows, however when I run the exact same
> statement on SV2 the count is 19,500,000. Why would there be a
> discrepancy of 6,000,000? The collation is the same for both servers
> (SV2 is a higher spec server, SV1 is 2K enterprise editions, SV2 2k
> standard, both SP3a).
> When I do a count year by year (on both servers) it comes to aprrox.
> 35,000,000 however the total for the tblConsignments is 42million.
> Possibly the answer would be to check the data more throughly, however
> I would like to know if Datetime columns can be set to different
> versions?
> Thanks
> qh
>
|||Whenever you perform a COUNT(*), I'd recommend you use a MAXDOP (1) in
the SELECT to make sure the parallelism "issue" doesn't cause the
rowcount to be off.
David Gugick
Imceda Software
www.imceda.com
|||Hi,
Can you update the statistics and see.
UPDATE STATISTICS <TABLE NAME>
Otherwise execute the below command in both databases replacing count(*)
with *, but you need more space in ur hard disk on both servers
select * into old_records from tblConsignments where [Date] >= '2003-08-01
00:00:00.000'
Once the statement is completed try
select count(*) from old_records
But first try updating the statistics and see the difference.
Thanks
Hari
SQL Server MVP
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1115224865.478078.231810@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have two servers, SV1 is a 24/7 OLTP server and SV2 is a backup
> server. A Full Backup of the db (prototype) was taken from SV1 was
> applied to SV2. One of the tables in the database has over 40 million
> rows in it. My task is to keep any rows no older than 18 months.
> SV1.Prototype.dbo.tblConsignment approx. 42,000,000 rows
> SV2.Prototype.dbo.tblConsignment approx. 41,800,000 rows
> this is correct as SV1 is constantly in use, so SV2 will always be a
> day or two behind. The issue arrises when I perform my count on both
> servers, when I run the following on SV1:
> select count(*) from tblConsignments
> where [Date] >= '2003-08-01 00:00:00.000'
> the count is approx 25,500,000 rows, however when I run the exact same
> statement on SV2 the count is 19,500,000. Why would there be a
> discrepancy of 6,000,000? The collation is the same for both servers
> (SV2 is a higher spec server, SV1 is 2K enterprise editions, SV2 2k
> standard, both SP3a).
> When I do a count year by year (on both servers) it comes to aprrox.
> 35,000,000 however the total for the tblConsignments is 42million.
> Possibly the answer would be to check the data more throughly, however
> I would like to know if Datetime columns can be set to different
> versions?
> Thanks
> qh
>
|||Hari,
Just for the record, the date string you give will be
interpreted as August 1, 2003 in some cases (such as us_english language
settings),
and as January 8, 2003 in others (such as french language setting). The two
SQL Server safe formats to use are (for August 1) '20030801' and
'2003-08-01T00:00:00.000'
Steve Kass
Drew University
Hari Prasad wrote:

>Hi,
>Can you update the statistics and see.
>UPDATE STATISTICS <TABLE NAME>
>Otherwise execute the below command in both databases replacing count(*)
>with *, but you need more space in ur hard disk on both servers
>select * into old_records from tblConsignments where [Date] >= '2003-08-01
>00:00:00.000'
>Once the statement is completed try
>select count(*) from old_records
>
>But first try updating the statistics and see the difference.
>Thanks
>Hari
>SQL Server MVP
>
>"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
>news:1115224865.478078.231810@.o13g2000cwo.googleg roups.com...
>
>
>
|||Hi ,
Update Statistics is used for statistics generation which is used for
determining the cost of the query , not sure how this will affect count(*) ,
please let me know if update statistics has any thing to do with count(*).
Regards
Vishal
"Hari Prasad" wrote:

> Hi,
> Can you update the statistics and see.
> UPDATE STATISTICS <TABLE NAME>
> Otherwise execute the below command in both databases replacing count(*)
> with *, but you need more space in ur hard disk on both servers
> select * into old_records from tblConsignments where [Date] >= '2003-08-01
> 00:00:00.000'
> Once the statement is completed try
> select count(*) from old_records
>
> But first try updating the statistics and see the difference.
> Thanks
> Hari
> SQL Server MVP
>
> "Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
> news:1115224865.478078.231810@.o13g2000cwo.googlegr oups.com...
>
>
|||Hi ,
Update Statistics is used for statistics generation which is used for
determining the cost of the query and selecting the appropriate index , not
sure how this will affect count(*) , please let me know if update statistics
has any thing to do with count(*).
Regards
Vishal