February 3, 2009 at 2:52 am
The error is synonymous with having the connection reset.
Strange thought I just had but could it be possible that the data is stored on a partition\segment that is not working too well or defragged and the hosting service is killing your spid?
Max
February 3, 2009 at 3:26 am
Max (2/3/2009)
The error is synonymous with having the connection reset.Strange thought I just had but could it be possible that the data is stored on a partition\segment that is not working too well or defragged and the hosting service is killing your spid?
It's a single stand-alone DB server and the data isn't partitioned.
Thanks for the thought though!
Kev
February 3, 2009 at 3:34 am
Can you compare (or even post) the execution plans for both queries ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 3, 2009 at 3:45 am
ALZDBA (2/3/2009)
Can you compare (or even post) the execution plans for both queries ?
The execution plans are identical.
Attached is the screenshot of the 2 plans - I can post the .sqlplan if you really want
Kev
February 3, 2009 at 4:36 am
What's the effect if you alter the sproc using the
With recompile parameter ?
Seems to me cardinallity may be very different when using criteria 1 or 2.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 3, 2009 at 5:14 am
kevriley (2/3/2009)
Jeff Moden (2/2/2009)
I can't remember exactly what the phrase in the returned data was, but I've had this happen before. Turned out that one of the "switches" on the network was looking for something that the returned data just happened to return.Jeff, this certainly is what it 'feels' like
I tried from home last night and everything was fine!
Tried from another connection (our backup ADSL) at work this morning - and everything is fine!
So I'm certain now that I've eliminated the DB server and narrowed it down to the main internet connection - I've got our network guy checking the routers now.
Kev
Thanks for the feedback, Kev... also note that it may not be the data itself... I had an instance where I had a bloody comment in the code that happened to match a command the switch was looking for (again, I only remember the incident, not the detail as to what was in the comment). Every time I tried to run it from my work station connection, the code would try to pass from my work station to the server and the switch would go bonkers. It can be something that seemingly innocuous.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2009 at 5:26 am
ALZDBA (2/3/2009)
What's the effect if you alter the sproc using theWith recompile parameter ?
Seems to me cardinallity may be very different when using criteria 1 or 2.
Makes no difference - in fact I have now stripped this back to the raw query - same result!
Kev
February 3, 2009 at 6:03 am
Jeff Moden (2/3/2009)
Thanks for the feedback, Kev... also note that it may not be the data itself... I had an instance where I had a bloody comment in the code that happened to match a command the switch was looking for (again, I only remember the incident, not the detail as to what was in the comment). Every time I tried to run it from my work station connection, the code would try to pass from my work station to the server and the switch would go bonkers. It can be something that seemingly innocuous.
I'm pretty sure it is the data - when I profile the server (at the server) I see the query execute and complete - it's just that the result set never arrives.....
I am now trying to see if I can narrow it down to 1 or maybe a couple of the rows in the result set. I have also raised a support call with our leased line supplier to see if they are running any intrusion prevention on any of their hardware.
Kev
February 3, 2009 at 6:12 am
Another strange thought, can you re-index the table and check again?
Max
February 3, 2009 at 6:32 am
Max (2/3/2009)
Another strange thought, can you re-index the table and check again?
Done ! And no change!
I actually reindexed yesterday, and updated all the stats, as my first thought was bad plan, bad stats was causing a timeout.
Keep those thoughts coming!
Kev
February 3, 2009 at 6:51 am
Can you recompile the final select statement in the proc, to return only a single column at first and then a second, and so forth, for the troublesome id. That would limit the issue to recordset size or, as Jeff mentions (often), the actual data giving your switches IBS (irritable bowl syndrome).
Max
February 3, 2009 at 8:09 am
Max (2/3/2009)
Can you recompile the final select statement in the proc, to return only a single column at first and then a second, and so forth, for the troublesome id. That would limit the issue to recordset size or, as Jeff mentions (often), the actual data giving your switches IBS (irritable bowl syndrome).
Max - tried this and although it didn't reveal anything with a particular column (different columns broke different times), it did get me thinking about the data.
BTW - recordset size is only about 3k.
I have found a workaround. 😛
8 of the columns being returned are defined as money datatypes in the underlying table. The app treats them as decimal anyway, so why not cast them in the query to decimal? By subtly changing the data, I must be changing the data profile and preventing it from being stopped on the network.
I have no idea why this is the case, but at least now I can get a fix up onto live so that the app can be used again, AND I can recreate the issue as and when I feel (by just running the original query).
Thanks everyone for your inspiring comments. Once I get a final resolution, I'll post back!
Kev
February 3, 2009 at 8:49 am
Out of curiosity, are you formatting the data returned in any way before you casted it to decimal?
-Roy
February 3, 2009 at 8:51 am
Roy Ernest (2/3/2009)
Out of curiosity, are you formatting the data returned in any way before you casted it to decimal?
No - prior to casting to a decimal to 'fix' the issue, there was no formatting.
Kev
February 3, 2009 at 8:56 am
The only difference I can see between Money and decimal that could affect the Firewall would be the number of Bytes that is used by Money. 8 Bytes. Maybe it has something to do with that? Just a wild thought.
It is very strange that it works when you cast it to Decimal. Thats why I got this wild thought. 😛
-Roy
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply