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
qhHave 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.googlegroups.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.googlegroups.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.googlegroups.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
>>
>
>|||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.googlegroups.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
> >
>
>|||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|||Hi Guys,
many thanks for the replies, I have taken your suggestions and tried
both UPDATE STATISTICS and the OPTION (MAXDOP 1) however I am still
returning the same rowcount anomoly. The main thing that has me
stumped is that the db on SV2 is a backup from the db on SV1!! I have
even created a small table based on a rowcount between certain years.
SV1 SV2
older 6,486,356 6,486,356
2002 9,893,118 9,893,118
2003 10,402,588 10,402,588
2004 11,268,420 11,268,419
2005+ 4,014,450 3,862,644
Totals 35,578,576 35,426,769
However as I originally mentioned there is over 41,000,000 rows in both
the corresponding tables? My next plan is to perform a simple data
complare on each table.
Thanks again
qhsql