hash warning, hash recursion errors

sql2000 sp3.
strange problem here. i've got an extremely complex query written by a
developer. the query works and it comes back in a reasonable amount of
time. however, the query generates a "hash warning/hash recursion"
error when it runs. after reading bol, these errors don't really seem
to be very bad. i decided to investigate it further by restoring the
production db to a test server and try it there. guess what. no hash
warning errors on the test server.
prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
allocated for sql server).
test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
allocated for sql server).
the query runs slower on the test server (as expected) even with little
to no traffic and no hash warnings.
my next guess was that on the prod server, all of sql server's ram was
being used by other objects. i did a dropcleanbuffers and a
freeproccache. query still generates hash warnings.
any ideas as to what would cause the hash warnings on prod server but
not test server?
i can't reboot the prod server and i can't stop sqlserver on prod
server. are there any things i can try in sqlserver to free up
resources other than dropcleanbuffers and freeproccache?Does SQL choose the same execution plan on the production and test server?
It's possible that the plans aren't the same...
also... hashing algorithtms take amount of memory into consideration. So
it's entirely possible that the prod server is making a mistake when it
guesses how much memory will ultimately be available for it.
You might also want to make sure statistics are up to date on prod. Out of
date stats might make the optimizer come up with bad hashing (and other)
decisions...
--
Brian Moran
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FBB6FB8.C5FF3195@.dontemailme.com...
> sql2000 sp3.
> strange problem here. i've got an extremely complex query written by a
> developer. the query works and it comes back in a reasonable amount of
> time. however, the query generates a "hash warning/hash recursion"
> error when it runs. after reading bol, these errors don't really seem
> to be very bad. i decided to investigate it further by restoring the
> production db to a test server and try it there. guess what. no hash
> warning errors on the test server.
> prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> allocated for sql server).
> test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> allocated for sql server).
> the query runs slower on the test server (as expected) even with little
> to no traffic and no hash warnings.
> my next guess was that on the prod server, all of sql server's ram was
> being used by other objects. i did a dropcleanbuffers and a
> freeproccache. query still generates hash warnings.
> any ideas as to what would cause the hash warnings on prod server but
> not test server?
> i can't reboot the prod server and i can't stop sqlserver on prod
> server. are there any things i can try in sqlserver to free up
> resources other than dropcleanbuffers and freeproccache?
>
>|||I have a bizarre "Hash Warning" performance issue I am trying to
resolve - any help, explanations or thoughts appreciated:
Configuration:
sql2000 sp3 & as2000 sp3
1Gb RAM, Single 1GHz CPU
Note: All queries/testing described below is performed on the same
database on the same server. Statistics are up to date for this
database.
When drilling through on a cube, Analysis Server generates a T-SQL
SELECT and executes it via a call to sp_prepexec.
For a specific drill-through I am testing (see below for actual
T-SQL), this code takes between 28 and 40 seconds to execute
(regardless of caching).
If I copy the exact query (captured via SQL Profiler) into Query
Analyzer, the same query executes in less than 5 seconds - sub-second
on subsequent executions (i.e. with cached data).
The result is the same slow execution using either Analysis Server's
cube browser or a web-based OLAP client application.
I ran SQL Profiler to capture the execution of this code from both
Analysis Server on Drill-Through and Query Analyzer to determine any
differences, and the only differences I can find are:
1) Query Analyzer event is captured as "SQL:BatchCompleted", whereas
the Analysis Server event is captured as "RPC:Completed"
2) No Warning or Error events are generated by Query Analyzer
executing the code, however the Analysis Server-based execution
generated 3 "HASH WARNING" events. The ObjectIDs captured by SQL
Profiler for these 3 Hash Warnings are 1, 12 and 12 again for the
third event, which map to the "sysobjects" and "sysdepends" tables!!
The specific code being executed for this test case is as follows:
================================================================declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 tinyint,@.P2 char(3),@.P3 char(3),@.P4
char(3),@.P5 char(3),@.P6 char(3),@.P7 char(3),@.P8 char(3),@.P9
char(3),@.P10 char(3),@.P11 char(3),@.P12 char(3),@.P13 char(3),@.P14
char(3),@.P15 char(3),@.P16 char(3),@.P17 char(3),@.P18 char(3),@.P19
char(3),@.P20 char(3),@.P21 char(3),@.P22 char(3),@.P23 char(3),@.P24
char(3),@.P25 char(3),@.P26 char(3),@.P27 char(3),@.P28 char(3),@.P29
char(3),@.P30 char(3),@.P31 char(3),@.P32 char(3),@.P33 char(3),@.P34
char(3),@.P35 char(3),@.P36 char(3),@.P37 char(3),@.P38 char(3),@.P39
char(3),@.P40 char(3),@.P41 char(3),@.P42 char(3),@.P43 char(3),@.P44
char(3),@.P45 char(3),@.P46 char(3),@.P47 char(3),@.P48 char(3),@.P49
char(3),@.P50 char(3),@.P51 char(3),@.P52 char(3),@.P53 char(3),@.P54
char(3),@.P55 char(3),@.P56 char(3),@.P57 char(3),@.P58 char(3),@.P59
char(3),@.P60 varchar(13),@.P61 int,@.P62 int,@.P63 varchar(7)', N'SELECT
"dbo"."mr_Activity"."DetectionDate",
"dbo"."mr_Activity"."ReversalFlag", "dbo"."mr_Activity"."VenueID",
"dbo"."mr_Activity"."ProductCode",
"dbo"."mr_Activity"."ActivityAmount", "dbo"."mr_Venue"."VenueName",
"dbo"."mr_ProductStructure"."ProductHierarchyName",
"dbo"."mr_AccountRef"."XrefNumber", "dbo"."mr_CaseRef"."WIN" FROM
"dbo"."mr_Activity", "dbo"."mr_ActivityReason", "dbo"."mr_Calendar",
"dbo"."mr_ProductStructure", "dbo"."mr_AccountRef",
"dbo"."mr_CaseRef", "dbo"."mr_Venue" WHERE
(mr_ProductStructure.ProductHierarchyID=mr_Activity.ProductCode AND
mr_AccountRef.DataWarehouseAccountID=mr_Activity.DataWarehouseAccountID
AND mr_CaseRef.DataWarehouseCaseID=mr_Activity.DataWarehouseCaseID)
AND (("dbo"."mr_Venue"."VenueID"="dbo"."mr_Activity"."VenueID")) AND
("dbo"."mr_Activity"."ActivityTypeID"=@.P1) AND
("dbo"."mr_Activity"."ProductCode" IN
(@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P12,@.P13,@.P14,@.P15,@.P16,@.P17,@.P18,@.P19,@.P20,@.P21,@.P22,@.P23,@.P24,@.P25,@.P26,@.P27,@.P28,@.P29,@.P30,@.P31,@.P32,@.P33,@.P34,@.P35,@.P36,@.P37,@.P38,@.P39,@.P40,@.P41,@.P42,@.P43,@.P44,@.P45,@.P46,@.P47,@.P48,@.P49,@.P50,@.P51,@.P52,@.P53,@.P54,@.P55,@.P56,@.P57,@.P58,@.P59))
AND ("dbo"."mr_ActivityReason"."BusinessType"=@.P60) AND
("dbo"."mr_Activity"."ReasonID"="dbo"."mr_ActivityReason"."ReasonID")
AND ("dbo"."mr_Activity"."ActivityTypeID"="dbo"."mr_ActivityReason"."ActivityTypeID")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 100) +
DatePart(month,"dbo"."mr_Calendar"."CalendarDate")=@.P61) AND
("dbo"."mr_Calendar"."CalendarDate"="dbo"."mr_Activity"."DetectionDate")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 10) +
"dbo"."mr_Calendar"."FiscalQtr"=@.P62) AND (''Measure''=@.P63)', 1,
'-36', '-39', '261', '222', '-40', '042', '-42', '259', '221', '-43',
'141', '-45', '030', '025', '026', '027', '028', '029', '-46', '035',
'032', '024', '-47', '282', '283', '284', '285', '286', '287', '-11',
'288', '289', '290', '291', '292', '293', '-11', '294', '295', '296',
'297', '298', '299', '-11', '-11', '-48', '-51', '220', '215', '216',
'217', '218', '219', '-53', '257', '214', '139', '-54', 'New
Business', 200307, 20031, 'Measure'
select @.P1
================================================================
The fact that Hash Warnings are appearing against sysobjects and
sysdepends in the database I find quite bizarre. I suspect these hash
warnings are related to the performance discrepancy betweeen the two
"modes" of execution (1 second vs. 30 seconds).
Any help on resolving (or even explaining) this performance
discrepancy issue is greatly appreciated.
Piquet.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message news:<epmCOPqrDHA.536@.tk2msftngp13.phx.gbl>...
> Does SQL choose the same execution plan on the production and test server?
> It's possible that the plans aren't the same...
> also... hashing algorithtms take amount of memory into consideration. So
> it's entirely possible that the prod server is making a mistake when it
> guesses how much memory will ultimately be available for it.
> You might also want to make sure statistics are up to date on prod. Out of
> date stats might make the optimizer come up with bad hashing (and other)
> decisions...
> --
> Brian Moran
>
> "chxxx" <chxxx@.dontemailme.com> wrote in message
> news:3FBB6FB8.C5FF3195@.dontemailme.com...
> > sql2000 sp3.
> >
> > strange problem here. i've got an extremely complex query written by a
> > developer. the query works and it comes back in a reasonable amount of
> > time. however, the query generates a "hash warning/hash recursion"
> > error when it runs. after reading bol, these errors don't really seem
> > to be very bad. i decided to investigate it further by restoring the
> > production db to a test server and try it there. guess what. no hash
> > warning errors on the test server.
> >
> > prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> > allocated for sql server).
> > test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> > allocated for sql server).
> > the query runs slower on the test server (as expected) even with little
> > to no traffic and no hash warnings.
> >
> > my next guess was that on the prod server, all of sql server's ram was
> > being used by other objects. i did a dropcleanbuffers and a
> > freeproccache. query still generates hash warnings.
> >
> > any ideas as to what would cause the hash warnings on prod server but
> > not test server?
> >
> > i can't reboot the prod server and i can't stop sqlserver on prod
> > server. are there any things i can try in sqlserver to free up
> > resources other than dropcleanbuffers and freeproccache?
> >
> >
> >sql