More Extremely weird SQL problem.

I believe SQL Server 2000 EE has a bug in it. I think this
because of the article here :
http://support.microsoft.com/default.aspx?scid=kb;en-us;274765
and my symptoms:
It started happening again. It lasted longer this time.
Here is the testing I did while the problem was happening.
INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
AND (Listmember.LM_L_ID = 253))
still produced all records where id_email was > or <
5000000 . around 600K
but if I do the this query
SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
AND (Listmember.LM_L_ID = 253))
it is only the records where id_email < 5000000 . I made
two identical new tables called recipient_test
and subscriber_test and filled them with the same data as
the others. Ran the above queries and they both worked
correctly. If I use the original Recipient but the new
subscriber_test table, it works, if I use Subscriber and
Recipient_test, it works. I did not create indexes or
Foreign key contstraints on the test tables. So that could
be a possibility.
Another clue is I could reconfigure the server to Max
degree of Parallelism = 1 and both worked during, then I
could reconfigure it back to MAXDOP = 0 (or 2 or 3, I tried
both) and it would go back to happening again.
So, it has something to do with MAXDOP, and maybe the
reason it happens sometimes and not others is because of
the cost of threshold for parallelism setting being 5. When
it thinks it needs parallelism it happens, and when the
server thinks it doesn't need parallelism, it doesn't.
It is embedded in a Java program call Accucast. The
Accucast software hasn't changed for two years. This only
started happening when we migrated from SQL standard SP3 on
W2K server to SQL Enterprise SP3 and Windows Server 2003.
the database we recreated. New tables, new indexes, FK's
etc. The two tables are in different filegroups. All
indexes except the pk clustered indexes on each are in
different filegroups as well.
Recipient table in recipient Filegroup
pk on recipient on rc_d_id, rc_c_id in recipient filegroup
other indexes on recipient are in recipient_ix filegroup
same set up on subscriber
id_email is pk
both pk are clustered.
It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon , 6 gb
ECC memory, 5 Maxtor 145 Gb 10K scsi drives configure as
RAID 10 with Dell Perc4/DC (spanned raid 1) with 1 hot
spare. Windows Server 2003 all patched up. SQL Server
Enterprise Edition SP3a. Everything seems to be fine but
this. I can't figure it out.
Any ideas?
ThanksNick,
A few thoughts:
- Does the query plan actually include a hash join? You say that the tables
are quite well indexed, and this would usually cause the Query Optimizer to
use either nested loops or Merge Joins rather than hash joins, and the
article you referred to talks about problems with hash joins only.
- Have you recently run DBCC CHECKTABLE on the affected tables? This kind of
behaviour is maybe caused by a corrupted non clustered index.
These are the two things I would look at first, with the information you
have given us.
--
Jacco Schalkwijk
SQL Server MVP
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:017601c3bf59$182ef130$a501280a@.phx.gbl...
> I believe SQL Server 2000 EE has a bug in it. I think this
> because of the article here :
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274765
> and my symptoms:
> It started happening again. It lasted longer this time.
> Here is the testing I did while the problem was happening.
> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> AND (Listmember.LM_L_ID = 253))
> still produced all records where id_email was > or <
> 5000000 . around 600K
> but if I do the this query
> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> AND (Listmember.LM_L_ID = 253))
> it is only the records where id_email < 5000000 . I made
> two identical new tables called recipient_test
> and subscriber_test and filled them with the same data as
> the others. Ran the above queries and they both worked
> correctly. If I use the original Recipient but the new
> subscriber_test table, it works, if I use Subscriber and
> Recipient_test, it works. I did not create indexes or
> Foreign key contstraints on the test tables. So that could
> be a possibility.
> Another clue is I could reconfigure the server to Max
> degree of Parallelism = 1 and both worked during, then I
> could reconfigure it back to MAXDOP = 0 (or 2 or 3, I tried
> both) and it would go back to happening again.
> So, it has something to do with MAXDOP, and maybe the
> reason it happens sometimes and not others is because of
> the cost of threshold for parallelism setting being 5. When
> it thinks it needs parallelism it happens, and when the
> server thinks it doesn't need parallelism, it doesn't.
> It is embedded in a Java program call Accucast. The
> Accucast software hasn't changed for two years. This only
> started happening when we migrated from SQL standard SP3 on
> W2K server to SQL Enterprise SP3 and Windows Server 2003.
> the database we recreated. New tables, new indexes, FK's
> etc. The two tables are in different filegroups. All
> indexes except the pk clustered indexes on each are in
> different filegroups as well.
> Recipient table in recipient Filegroup
> pk on recipient on rc_d_id, rc_c_id in recipient filegroup
> other indexes on recipient are in recipient_ix filegroup
> same set up on subscriber
> id_email is pk
> both pk are clustered.
> It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon , 6 gb
> ECC memory, 5 Maxtor 145 Gb 10K scsi drives configure as
> RAID 10 with Dell Perc4/DC (spanned raid 1) with 1 hot
> spare. Windows Server 2003 all patched up. SQL Server
> Enterprise Edition SP3a. Everything seems to be fine but
> this. I can't figure it out.
>
> Any ideas?
> Thanks
>|||One says this for dbcc checktable:
dbcc checktable ('Recipient') with all_errormsgs
DBCC results for 'Recipient'.
There are 37121847 rows in 160756 pages for
object 'Recipient'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I can't do a repair option at the moment because it is a
production db. However, would the above tell me if
something was wrong? Did the same thing for the
Subscriber table and Listmember table. All the same
message just different row count and page count.
here is the output from showplan_text on and then the
query:
StmtText
--
SET STATISTICS PROFILE ON
(1 row(s) affected)
StmtText
---
---
---
---
--
INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
SELECT DISTINCT 22000, subscriber.id_email, '2003-12-09'
FROM subscriber, Listmember
WHERE Listmember.LM_C_ID = subscriber.id_email
AND ((subscriber.banned = 0
AND subscriber.id_email < 5000000)
(1 row(s) affected)
StmtText
---
---
---
---
--
|--Index Insert(OBJECT:([accucast].[dbo].[Recipient].
[IX_Recipient]), SET:([RC_D_ID1014]=[Recipient].[RC_D_ID],
[RC_C_ID1013]=[Recipient].[RC_C_ID], [IdxBmk1012]=[Bmk1007]))
|--Sort(ORDER BY:([Recipient].[RC_C_ID] ASC,
[Recipient].[RC_D_ID] ASC, [Bmk1007] ASC))
|--Assert(WHERE:(If (NOT([Pass1011]) AND
([Expr1010] IS NULL)) then 0 else NULL))
|--Nested Loops(Left Semi Join, WHERE:
([Recipient].[RC_C_ID] IS NULL)OUTER REFERENCES:
([Recipient].[RC_C_ID]), DEFINE:([Expr1010] = [PROBE
VALUE]))
|--Clustered Index Insert(OBJECT:
([accucast].[dbo].[Recipient].[PK_Recipient]), SET:
([Recipient].[RC_CONT_ERR]=NULL, [Recipient].[RC_ADDR_ERR]
=NULL, [Recipient].[RC_READ]=NULL, [Recipient].[RC_DATE]
='Dec 9 2003 12:00AM', [Recipient].
| |--Top(ROWCOUNT est 0)
| |--Parallelism(Gather
Streams, ORDER BY:([Subscriber].[id_email] ASC))
| |--Merge Join(Inner
Join, MERGE:([Listmember].[LM_C_ID])=([Subscriber].
[id_email]), RESIDUAL:([Subscriber].[id_email]=[Listmember].[LM_C_ID]))
| |--Parallelism
(Repartition Streams, PARTITION COLUMNS:([Listmember].
[LM_C_ID]), ORDER BY:([Listmember].[LM_C_ID] ASC))
| | |--Index
Seek(OBJECT:([accucast].[dbo].[Listmember].
[IX_Listmember]), SEEK:([Listmember].[LM_L_ID]=138)
ORDERED FORWARD)
| |--Parallelism
(Repartition Streams, PARTITION COLUMNS:([Subscriber].
[id_email]), ORDER BY:([Subscriber].[id_email] ASC))
| |--Index
Seek(OBJECT:([accucast].[dbo].[Subscriber].
[IX_subscriber_1]), SEEK:([Subscriber].[banned]=0 AND
[Subscriber].[id_email] < 5000000) ORDERED FORWARD)
|--Row Count Spool
|--Clustered Index Seek(OBJECT:
([accucast].[dbo].[Subscriber].[PK_subscriber]), SEEK:
([Subscriber].[id_email]=[Recipient].[RC_C_ID]) ORDERED
FORWARD)
(14 row(s) affected)
StmtText
--
SET STATISTICS PROFILE OFF
(1 row(s) affected)
Any help would be appreciated.
Thanks.
Nick
>--Original Message--
>Nick,
>A few thoughts:
>- Does the query plan actually include a hash join? You
say that the tables
>are quite well indexed, and this would usually cause the
Query Optimizer to
>use either nested loops or Merge Joins rather than hash
joins, and the
>article you referred to talks about problems with hash
joins only.
>- Have you recently run DBCC CHECKTABLE on the affected
tables? This kind of
>behaviour is maybe caused by a corrupted non clustered
index.
>These are the two things I would look at first, with the
information you
>have given us.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Nick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:017601c3bf59$182ef130$a501280a@.phx.gbl...
>> I believe SQL Server 2000 EE has a bug in it. I think
this
>> because of the article here :
>> http://support.microsoft.com/default.aspx?scid=kb;en-
us;274765
>> and my symptoms:
>> It started happening again. It lasted longer this time.
>> Here is the testing I did while the problem was
happening.
>> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
>> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
>> FROM subscriber, Listmember
>> WHERE Listmember.LM_C_ID = subscriber.id_email
>> AND ((subscriber.banned = 0
>> AND subscriber.id_email < 5000000)
>> AND (Listmember.LM_L_ID = 253))
>> still produced all records where id_email was > or <
>> 5000000 . around 600K
>> but if I do the this query
>> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
>> FROM subscriber, Listmember
>> WHERE Listmember.LM_C_ID = subscriber.id_email
>> AND ((subscriber.banned = 0
>> AND subscriber.id_email < 5000000)
>> AND (Listmember.LM_L_ID = 253))
>> it is only the records where id_email < 5000000 . I made
>> two identical new tables called recipient_test
>> and subscriber_test and filled them with the same data
as
>> the others. Ran the above queries and they both worked
>> correctly. If I use the original Recipient but the new
>> subscriber_test table, it works, if I use Subscriber and
>> Recipient_test, it works. I did not create indexes or
>> Foreign key contstraints on the test tables. So that
could
>> be a possibility.
>> Another clue is I could reconfigure the server to Max
>> degree of Parallelism = 1 and both worked during, then I
>> could reconfigure it back to MAXDOP = 0 (or 2 or 3, I
tried
>> both) and it would go back to happening again.
>> So, it has something to do with MAXDOP, and maybe the
>> reason it happens sometimes and not others is because of
>> the cost of threshold for parallelism setting being 5.
When
>> it thinks it needs parallelism it happens, and when the
>> server thinks it doesn't need parallelism, it doesn't.
>> It is embedded in a Java program call Accucast. The
>> Accucast software hasn't changed for two years. This
only
>> started happening when we migrated from SQL standard
SP3 on
>> W2K server to SQL Enterprise SP3 and Windows Server
2003.
>> the database we recreated. New tables, new indexes, FK's
>> etc. The two tables are in different filegroups. All
>> indexes except the pk clustered indexes on each are in
>> different filegroups as well.
>> Recipient table in recipient Filegroup
>> pk on recipient on rc_d_id, rc_c_id in recipient
filegroup
>> other indexes on recipient are in recipient_ix filegroup
>> same set up on subscriber
>> id_email is pk
>> both pk are clustered.
>> It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon , 6 gb
>> ECC memory, 5 Maxtor 145 Gb 10K scsi drives configure as
>> RAID 10 with Dell Perc4/DC (spanned raid 1) with 1 hot
>> spare. Windows Server 2003 all patched up. SQL Server
>> Enterprise Edition SP3a. Everything seems to be fine but
>> this. I can't figure it out.
>>
>> Any ideas?
>> Thanks
>>
>
>.
>|||Hi Nick,
The results you get indicate that there are no problems with the indexes,
which I thought could be a cause of the problem you have. If something was
wrong you would have gotten error messages in Query Analyzer so that would
have been quite clear.
The showplan shows, as I expected, that the query does not use a hash join
but uses one nested loop and one merge join. So the information in the
knowledge base article does not apply to this problem.
If you can't find any more inforamtion about it in the Knowledge Base, it
might indeed be a bug and the best thing to do would be to open a case with
Microsoft Product Support. If it is a bug you won't be charged.
--
Jacco Schalkwijk
SQL Server MVP
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:073101c3bff4$193bf0f0$a001280a@.phx.gbl...
> One says this for dbcc checktable:
> dbcc checktable ('Recipient') with all_errormsgs
> DBCC results for 'Recipient'.
> There are 37121847 rows in 160756 pages for
> object 'Recipient'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I can't do a repair option at the moment because it is a
> production db. However, would the above tell me if
> something was wrong? Did the same thing for the
> Subscriber table and Listmember table. All the same
> message just different row count and page count.
>
> here is the output from showplan_text on and then the
> query:
> StmtText
> --
> SET STATISTICS PROFILE ON
> (1 row(s) affected)
> StmtText
>
>
> ---
> ---
> ---
> ---
> --
> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> SELECT DISTINCT 22000, subscriber.id_email, '2003-12-09'
> FROM subscriber, Listmember
> WHERE Listmember.LM_C_ID = subscriber.id_email
> AND ((subscriber.banned = 0
> AND subscriber.id_email < 5000000)
> (1 row(s) affected)
> StmtText
>
>
> ---
> ---
> ---
> ---
> --
> |--Index Insert(OBJECT:([accucast].[dbo].[Recipient].
> [IX_Recipient]), SET:([RC_D_ID1014]=[Recipient].[RC_D_ID],
> [RC_C_ID1013]=[Recipient].[RC_C_ID], [IdxBmk1012]=> [Bmk1007]))
> |--Sort(ORDER BY:([Recipient].[RC_C_ID] ASC,
> [Recipient].[RC_D_ID] ASC, [Bmk1007] ASC))
> |--Assert(WHERE:(If (NOT([Pass1011]) AND
> ([Expr1010] IS NULL)) then 0 else NULL))
> |--Nested Loops(Left Semi Join, WHERE:
> ([Recipient].[RC_C_ID] IS NULL)OUTER REFERENCES:
> ([Recipient].[RC_C_ID]), DEFINE:([Expr1010] = [PROBE
> VALUE]))
> |--Clustered Index Insert(OBJECT:
> ([accucast].[dbo].[Recipient].[PK_Recipient]), SET:
> ([Recipient].[RC_CONT_ERR]=NULL, [Recipient].[RC_ADDR_ERR]
> =NULL, [Recipient].[RC_READ]=NULL, [Recipient].[RC_DATE]
> ='Dec 9 2003 12:00AM', [Recipient].
> | |--Top(ROWCOUNT est 0)
> | |--Parallelism(Gather
> Streams, ORDER BY:([Subscriber].[id_email] ASC))
> | |--Merge Join(Inner
> Join, MERGE:([Listmember].[LM_C_ID])=([Subscriber].
> [id_email]), RESIDUAL:([Subscriber].[id_email]=> [Listmember].[LM_C_ID]))
> | |--Parallelism
> (Repartition Streams, PARTITION COLUMNS:([Listmember].
> [LM_C_ID]), ORDER BY:([Listmember].[LM_C_ID] ASC))
> | | |--Index
> Seek(OBJECT:([accucast].[dbo].[Listmember].
> [IX_Listmember]), SEEK:([Listmember].[LM_L_ID]=138)
> ORDERED FORWARD)
> | |--Parallelism
> (Repartition Streams, PARTITION COLUMNS:([Subscriber].
> [id_email]), ORDER BY:([Subscriber].[id_email] ASC))
> | |--Index
> Seek(OBJECT:([accucast].[dbo].[Subscriber].
> [IX_subscriber_1]), SEEK:([Subscriber].[banned]=0 AND
> [Subscriber].[id_email] < 5000000) ORDERED FORWARD)
> |--Row Count Spool
> |--Clustered Index Seek(OBJECT:
> ([accucast].[dbo].[Subscriber].[PK_subscriber]), SEEK:
> ([Subscriber].[id_email]=[Recipient].[RC_C_ID]) ORDERED
> FORWARD)
> (14 row(s) affected)
> StmtText
> --
> SET STATISTICS PROFILE OFF
> (1 row(s) affected)
>
> Any help would be appreciated.
> Thanks.
> Nick
> >--Original Message--
> >Nick,
> >
> >A few thoughts:
> >- Does the query plan actually include a hash join? You
> say that the tables
> >are quite well indexed, and this would usually cause the
> Query Optimizer to
> >use either nested loops or Merge Joins rather than hash
> joins, and the
> >article you referred to talks about problems with hash
> joins only.
> >- Have you recently run DBCC CHECKTABLE on the affected
> tables? This kind of
> >behaviour is maybe caused by a corrupted non clustered
> index.
> >
> >These are the two things I would look at first, with the
> information you
> >have given us.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Nick" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:017601c3bf59$182ef130$a501280a@.phx.gbl...
> >> I believe SQL Server 2000 EE has a bug in it. I think
> this
> >> because of the article here :
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;274765
> >>
> >> and my symptoms:
> >>
> >> It started happening again. It lasted longer this time.
> >> Here is the testing I did while the problem was
> happening.
> >>
> >> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> >> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> >> FROM subscriber, Listmember
> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> AND ((subscriber.banned = 0
> >> AND subscriber.id_email < 5000000)
> >> AND (Listmember.LM_L_ID = 253))
> >>
> >> still produced all records where id_email was > or <
> >> 5000000 . around 600K
> >> but if I do the this query
> >>
> >> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> >> FROM subscriber, Listmember
> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> AND ((subscriber.banned = 0
> >> AND subscriber.id_email < 5000000)
> >> AND (Listmember.LM_L_ID = 253))
> >>
> >> it is only the records where id_email < 5000000 . I made
> >> two identical new tables called recipient_test
> >> and subscriber_test and filled them with the same data
> as
> >> the others. Ran the above queries and they both worked
> >> correctly. If I use the original Recipient but the new
> >> subscriber_test table, it works, if I use Subscriber and
> >> Recipient_test, it works. I did not create indexes or
> >> Foreign key contstraints on the test tables. So that
> could
> >> be a possibility.
> >>
> >> Another clue is I could reconfigure the server to Max
> >> degree of Parallelism = 1 and both worked during, then I
> >> could reconfigure it back to MAXDOP = 0 (or 2 or 3, I
> tried
> >> both) and it would go back to happening again.
> >>
> >> So, it has something to do with MAXDOP, and maybe the
> >> reason it happens sometimes and not others is because of
> >> the cost of threshold for parallelism setting being 5.
> When
> >> it thinks it needs parallelism it happens, and when the
> >> server thinks it doesn't need parallelism, it doesn't.
> >>
> >> It is embedded in a Java program call Accucast. The
> >> Accucast software hasn't changed for two years. This
> only
> >> started happening when we migrated from SQL standard
> SP3 on
> >> W2K server to SQL Enterprise SP3 and Windows Server
> 2003.
> >> the database we recreated. New tables, new indexes, FK's
> >> etc. The two tables are in different filegroups. All
> >> indexes except the pk clustered indexes on each are in
> >> different filegroups as well.
> >>
> >> Recipient table in recipient Filegroup
> >> pk on recipient on rc_d_id, rc_c_id in recipient
> filegroup
> >> other indexes on recipient are in recipient_ix filegroup
> >>
> >> same set up on subscriber
> >> id_email is pk
> >>
> >> both pk are clustered.
> >>
> >> It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon , 6 gb
> >> ECC memory, 5 Maxtor 145 Gb 10K scsi drives configure as
> >> RAID 10 with Dell Perc4/DC (spanned raid 1) with 1 hot
> >> spare. Windows Server 2003 all patched up. SQL Server
> >> Enterprise Edition SP3a. Everything seems to be fine but
> >> this. I can't figure it out.
> >>
> >>
> >>
> >> Any ideas?
> >>
> >> Thanks
> >>
> >>
> >
> >
> >.
> >|||Hi Nick,
Can you report back here when you have had an answer? I'm curious.
--
Jacco Schalkwijk
SQL Server MVP
"Nick" <anonymous@.discussions.microsoft.com> wrote in message
news:0f0b01c3c006$8d231810$a401280a@.phx.gbl...
> Thanks man. I'll do that.
> >--Original Message--
> >Hi Nick,
> >
> >The results you get indicate that there are no problems
> with the indexes,
> >which I thought could be a cause of the problem you have.
> If something was
> >wrong you would have gotten error messages in Query
> Analyzer so that would
> >have been quite clear.
> >
> >The showplan shows, as I expected, that the query does
> not use a hash join
> >but uses one nested loop and one merge join. So the
> information in the
> >knowledge base article does not apply to this problem.
> >
> >If you can't find any more inforamtion about it in the
> Knowledge Base, it
> >might indeed be a bug and the best thing to do would be
> to open a case with
> >Microsoft Product Support. If it is a bug you won't be
> charged.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Nick" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:073101c3bff4$193bf0f0$a001280a@.phx.gbl...
> >> One says this for dbcc checktable:
> >>
> >> dbcc checktable ('Recipient') with all_errormsgs
> >>
> >> DBCC results for 'Recipient'.
> >> There are 37121847 rows in 160756 pages for
> >> object 'Recipient'.
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> I can't do a repair option at the moment because it is a
> >> production db. However, would the above tell me if
> >> something was wrong? Did the same thing for the
> >> Subscriber table and Listmember table. All the same
> >> message just different row count and page count.
> >>
> >>
> >> here is the output from showplan_text on and then the
> >> query:
> >>
> >> StmtText
> >> --
> >> SET STATISTICS PROFILE ON
> >>
> >> (1 row(s) affected)
> >>
> >> StmtText
> >>
> >>
> >>
> >>
> >> ---
> --
> >> ---
> --
> >> ---
> --
> >> ---
> --
> >> --
> >> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> >> SELECT DISTINCT 22000, subscriber.id_email, '2003-12-09'
> >> FROM subscriber, Listmember
> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> AND ((subscriber.banned = 0
> >> AND subscriber.id_email < 5000000)
> >>
> >> (1 row(s) affected)
> >>
> >> StmtText
> >>
> >>
> >>
> >>
> >> ---
> --
> >> ---
> --
> >> ---
> --
> >> ---
> --
> >> --
> >> |--Index Insert(OBJECT:([accucast].[dbo].[Recipient].
> >> [IX_Recipient]), SET:([RC_D_ID1014]=[Recipient].
> [RC_D_ID],
> >> [RC_C_ID1013]=[Recipient].[RC_C_ID], [IdxBmk1012]=> >> [Bmk1007]))
> >> |--Sort(ORDER BY:([Recipient].[RC_C_ID] ASC,
> >> [Recipient].[RC_D_ID] ASC, [Bmk1007] ASC))
> >> |--Assert(WHERE:(If (NOT([Pass1011]) AND
> >> ([Expr1010] IS NULL)) then 0 else NULL))
> >> |--Nested Loops(Left Semi Join, WHERE:
> >> ([Recipient].[RC_C_ID] IS NULL)OUTER REFERENCES:
> >> ([Recipient].[RC_C_ID]), DEFINE:([Expr1010] = [PROBE
> >> VALUE]))
> >> |--Clustered Index Insert(OBJECT:
> >> ([accucast].[dbo].[Recipient].[PK_Recipient]), SET:
> >> ([Recipient].[RC_CONT_ERR]=NULL, [Recipient].
> [RC_ADDR_ERR]
> >> =NULL, [Recipient].[RC_READ]=NULL, [Recipient].[RC_DATE]
> >> ='Dec 9 2003 12:00AM', [Recipient].
> >> | |--Top(ROWCOUNT est 0)
> >> | |--Parallelism(Gather
> >> Streams, ORDER BY:([Subscriber].[id_email] ASC))
> >> | |--Merge Join(Inner
> >> Join, MERGE:([Listmember].[LM_C_ID])=([Subscriber].
> >> [id_email]), RESIDUAL:([Subscriber].[id_email]=> >> [Listmember].[LM_C_ID]))
> >> | |--Parallelism
> >> (Repartition Streams, PARTITION COLUMNS:([Listmember].
> >> [LM_C_ID]), ORDER BY:([Listmember].[LM_C_ID] ASC))
> >> | | |--Index
> >> Seek(OBJECT:([accucast].[dbo].[Listmember].
> >> [IX_Listmember]), SEEK:([Listmember].[LM_L_ID]=138)
> >> ORDERED FORWARD)
> >> | |--Parallelism
> >> (Repartition Streams, PARTITION COLUMNS:([Subscriber].
> >> [id_email]), ORDER BY:([Subscriber].[id_email] ASC))
> >> | |--Index
> >> Seek(OBJECT:([accucast].[dbo].[Subscriber].
> >> [IX_subscriber_1]), SEEK:([Subscriber].[banned]=0 AND
> >> [Subscriber].[id_email] < 5000000) ORDERED FORWARD)
> >> |--Row Count Spool
> >> |--Clustered Index Seek
> (OBJECT:
> >> ([accucast].[dbo].[Subscriber].[PK_subscriber]), SEEK:
> >> ([Subscriber].[id_email]=[Recipient].[RC_C_ID]) ORDERED
> >> FORWARD)
> >>
> >> (14 row(s) affected)
> >>
> >> StmtText
> >> --
> >> SET STATISTICS PROFILE OFF
> >>
> >> (1 row(s) affected)
> >>
> >>
> >>
> >> Any help would be appreciated.
> >>
> >> Thanks.
> >>
> >> Nick
> >>
> >> >--Original Message--
> >> >Nick,
> >> >
> >> >A few thoughts:
> >> >- Does the query plan actually include a hash join? You
> >> say that the tables
> >> >are quite well indexed, and this would usually cause
> the
> >> Query Optimizer to
> >> >use either nested loops or Merge Joins rather than hash
> >> joins, and the
> >> >article you referred to talks about problems with hash
> >> joins only.
> >> >- Have you recently run DBCC CHECKTABLE on the affected
> >> tables? This kind of
> >> >behaviour is maybe caused by a corrupted non clustered
> >> index.
> >> >
> >> >These are the two things I would look at first, with
> the
> >> information you
> >> >have given us.
> >> >
> >> >--
> >> >Jacco Schalkwijk
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Nick" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:017601c3bf59$182ef130$a501280a@.phx.gbl...
> >> >> I believe SQL Server 2000 EE has a bug in it. I
> think
> >> this
> >> >> because of the article here :
> >> >>
> >> >> http://support.microsoft.com/default.aspx?scid=kb;en-
> >> us;274765
> >> >>
> >> >> and my symptoms:
> >> >>
> >> >> It started happening again. It lasted longer this
> time.
> >> >> Here is the testing I did while the problem was
> >> happening.
> >> >>
> >> >> INSERT INTO Recipient (RC_D_ID, RC_C_ID, RC_DATE)
> >> >> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> >> >> FROM subscriber, Listmember
> >> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> >> AND ((subscriber.banned = 0
> >> >> AND subscriber.id_email < 5000000)
> >> >> AND (Listmember.LM_L_ID = 253))
> >> >>
> >> >> still produced all records where id_email was > or <
> >> >> 5000000 . around 600K
> >> >> but if I do the this query
> >> >>
> >> >> SELECT DISTINCT 20000, subscriber.id_email, GETDATE()
> >> >> FROM subscriber, Listmember
> >> >> WHERE Listmember.LM_C_ID = subscriber.id_email
> >> >> AND ((subscriber.banned = 0
> >> >> AND subscriber.id_email < 5000000)
> >> >> AND (Listmember.LM_L_ID = 253))
> >> >>
> >> >> it is only the records where id_email < 5000000 . I
> made
> >> >> two identical new tables called recipient_test
> >> >> and subscriber_test and filled them with the same
> data
> >> as
> >> >> the others. Ran the above queries and they both
> worked
> >> >> correctly. If I use the original Recipient but the
> new
> >> >> subscriber_test table, it works, if I use Subscriber
> and
> >> >> Recipient_test, it works. I did not create indexes or
> >> >> Foreign key contstraints on the test tables. So that
> >> could
> >> >> be a possibility.
> >> >>
> >> >> Another clue is I could reconfigure the server to Max
> >> >> degree of Parallelism = 1 and both worked during,
> then I
> >> >> could reconfigure it back to MAXDOP = 0 (or 2 or 3, I
> >> tried
> >> >> both) and it would go back to happening again.
> >> >>
> >> >> So, it has something to do with MAXDOP, and maybe the
> >> >> reason it happens sometimes and not others is
> because of
> >> >> the cost of threshold for parallelism setting being
> 5.
> >> When
> >> >> it thinks it needs parallelism it happens, and when
> the
> >> >> server thinks it doesn't need parallelism, it
> doesn't.
> >> >>
> >> >> It is embedded in a Java program call Accucast. The
> >> >> Accucast software hasn't changed for two years. This
> >> only
> >> >> started happening when we migrated from SQL standard
> >> SP3 on
> >> >> W2K server to SQL Enterprise SP3 and Windows Server
> >> 2003.
> >> >> the database we recreated. New tables, new indexes,
> FK's
> >> >> etc. The two tables are in different filegroups. All
> >> >> indexes except the pk clustered indexes on each are
> in
> >> >> different filegroups as well.
> >> >>
> >> >> Recipient table in recipient Filegroup
> >> >> pk on recipient on rc_d_id, rc_c_id in recipient
> >> filegroup
> >> >> other indexes on recipient are in recipient_ix
> filegroup
> >> >>
> >> >> same set up on subscriber
> >> >> id_email is pk
> >> >>
> >> >> both pk are clustered.
> >> >>
> >> >> It's a Dell 2650 Dual Intel 3.06mb 1mb cache Xeon ,
> 6 gb
> >> >> ECC memory, 5 Maxtor 145 Gb 10K scsi drives
> configure as
> >> >> RAID 10 with Dell Perc4/DC (spanned raid 1) with 1
> hot
> >> >> spare. Windows Server 2003 all patched up. SQL Server
> >> >> Enterprise Edition SP3a. Everything seems to be fine
> but
> >> >> this. I can't figure it out.
> >> >>
> >> >>
> >> >>
> >> >> Any ideas?
> >> >>
> >> >> Thanks
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

I have my regional settings set to Canadian English. I am using Calender controls to pick up Start, End dates in my report. When I pick a date from calender it automatically converts it behind the scenes to US English I believe. Therefore when I run the report, I get the result set, say from Oct 8 instead of Aug 10. Further, if I select the date as 13 Aug, the following error gets thrown (as a result that the SSRS is not able to identify 13 being any month of course)

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

The SSRS controls comes with Calendar property you need to use that, because if you use the NOW SSRS will run your report with current date time in your box, I have used it. But I think you have mistaken TimeZone for DateTime, you can get DateTime in SQL Server while TimeZone is .NET there are work around solutions now and it is implemented in VS2008. Try the thread below for details.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

I never said I am using TimeZone. Let me try to explain once more. I have my regional settings in Control Panel >> Regional and Language Options>>Regional Options>>Standards and Formats - set to English(Canada) and Location set to Canada, further in the Advanced Tab its also set to English (Canada).

Now back in report, I have defined two report parameters: StartDate and EndDate. Their data type is set to DataTime. Therefore when I preview the report, the parameters display Calenders next to the boxes automatically. I pick a date, say 14 Aug 2007 from this calender. The date is formatted as 14/08/2007 in the text box (Canadian format dd/mm/yyyy). Then I hit the View Report button, and its then that the error gets thrown because for some reason report server/sql server does not accept this date format. Notice that I have not done any thing fancy with the date time parameter, simply out of the box usage.

So, I did this test. I switched the computer settings to English (United States) and wolla...everythings works fine. However thats not desirable as this would require the users to use US-English is all applications such as Word, Excel etc.

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?

|||

SQL Server can do what you want you just need the correct table design, 2005 also comes with GETUTDATE function you can use. Run a search in the BOL for GETUTCDATE and CAST and CONVERT, there is a guideline for SQL Server DateTime design and how to get datetime in the format you want in the links below.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.

|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

sql

More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

I have my regional settings set to Canadian English. I am using Calender controls to pick up Start, End dates in my report. When I pick a date from calender it automatically converts it behind the scenes to US English I believe. Therefore when I run the report, I get the result set, say from Oct 8 instead of Aug 10. Further, if I select the date as 13 Aug, the following error gets thrown (as a result that the SSRS is not able to identify 13 being any month of course)

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

The SSRS controls comes with Calendar property you need to use that, because if you use the NOW SSRS will run your report with current date time in your box, I have used it. But I think you have mistaken TimeZone for DateTime, you can get DateTime in SQL Server while TimeZone is .NET there are work around solutions now and it is implemented in VS2008. Try the thread below for details.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

I never said I am using TimeZone. Let me try to explain once more. I have my regional settings in Control Panel >> Regional and Language Options>>Regional Options>>Standards and Formats - set to English(Canada) and Location set to Canada, further in the Advanced Tab its also set to English (Canada).

Now back in report, I have defined two report parameters: StartDate and EndDate. Their data type is set to DataTime. Therefore when I preview the report, the parameters display Calenders next to the boxes automatically. I pick a date, say 14 Aug 2007 from this calender. The date is formatted as 14/08/2007 in the text box (Canadian format dd/mm/yyyy). Then I hit the View Report button, and its then that the error gets thrown because for some reason report server/sql server does not accept this date format. Notice that I have not done any thing fancy with the date time parameter, simply out of the box usage.

So, I did this test. I switched the computer settings to English (United States) and wolla...everythings works fine. However thats not desirable as this would require the users to use US-English is all applications such as Word, Excel etc.

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?|||

SQL Server can do what you want you just need the correct table design, 2005 also comes with GETUTDATE function you can use. Run a search in the BOL for GETUTCDATE and CAST and CONVERT, there is a guideline for SQL Server DateTime design and how to get datetime in the format you want in the links below.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

Monitoring SQL Server

I believe we are reaching some limitations with SQL Server and I have been monitoring certain items in the Performance Monitor such as: pages/sec; Bytes received/sec; Bytes sent/sec; % disk read time; % disk write time; % processor time; Log growths; percent log used; and transactions/sec. I notice quite a few spikes in Bytes sent/sec and when the % disk read time spikes for more than a few seconds, users notice a delay.

My thoughts are that 1: We need more memory on our SQL Server box (we currently have 768 meg, need a faster SQL Server box and need to distribute the load of some databases to another SQL Server; and 2: We also have a bottleneck when users are connecting via Citrix to SQL Server via our Terminal2 server (which has been tracked down to simply a slow Terminal2 box with a slow nic card - This has been confirmed that our Terminal2 is definately taking a toll and will time-out when large queries are executed.)

We also have been monitoring each of the server boxes. Are there any other recommendations for SQL Server Performance monitor that anyone could see which would be good to monitor (there are several things which can be selected to monitor?)

We've also noticed that bound MSAccess forms seem to play a significant role in the long spikes for Bytes sent/sec. I'm assuming this might be normal for bound forms and the slow SQL Server box with limited memory. Unbound MSAccess forms do not seem to present any problem and show as quick spikes for the Performance monitor.

Another problem is that I also can't seem to tie back the Performance monitor spikes with specific transactions in the SQL Profiler. Is there any way to pinpoint a spike in the Performance monitor with a specific transaction other than trying to catch the spike and quickly switching to SQL Profiler?

We are planning on upgrading our SQL Server box and also adding in another SQL Server box to help distribute the load with certain databases. We are also getting a faster box for our Terminal2 (citrix) server as these slow-downs/time-outs do not happen internally or when we use Remote Desktop Connection to connect externally (only when we connect externally via Terminal2).

Any help would be greatly appreciated! Thank you in advance.Have you done a sql trace?

1/2 GB of memory?

That's kinda low...

MS Access and SQL Server...hmmmm|||I'm sorry, I meant running a SQL Trace and the Performance Monitor. Is there a way to tie these 2 together to find spikes in Performance Monitor related to a specific transaction in the SQL Trace?