December 15, 2005 at 9:49 am
I have inherrited a huge stored procedure that calls a number of string manipulation functions to produce a description.
The procedure is non-destructive and our test environment is very tightly locked down. That is when you run a test you can be sure that ONLY you are running that test so no-one else can interfere with your data.
The problem that I have is that if I run the query twice then a tiny percentage of records will have a slightly different descriptions for the records. We are talking about 1 in 5000.
If I restore and start the test again then an equally tiny percentage but not necessarily the same records will have slightly different descriptions.
Because the data doesn't change the query should always produce identical results.
We have tried putting WITH RECOMPILE on the stored procedure and puting OPTION(MAXDOP 1) as the query hint but to no avail.
Because there are functions in the SELECT statement the query is very heavy in its operation so it is almost as if the query drops data when it is under heavy load. I could be clutching at straws with this.
Has anyone come across this sort of problem before?
December 15, 2005 at 10:44 am
Not this exactly, but Andy had some queries that would run fine for weeks and then all of a sudden start grabbing different query plans.
I suspect that you have some functions that can possibly return different results in some cases, but it sounds very weird. Have you captured the query plan (SHOWPLAN TEXT) in both executions and compared to see if something might be different?
Also, can you break down some sections and see if you can isolate which sections are returning different data? Also, check for corruption in the tables, might get some weird results of some pages are having linkage issues.
December 15, 2005 at 11:31 am
I would check for SELECT's that use the TOP operator without an ORDER BY, where it is assumed (incorrectly) that records always come back in a certain order.
I would also look for ORDERed By SELECTs into #TEMP tables with an IDENTITY column, where it is assumed (incorrectly) that the ordering of the identity column will match the ORDER BY.
December 15, 2005 at 12:24 pm
So if I have a temp table with id IDENTITY, field1....field n
and I write
INSERT mytemptable(field1...fieldn)
SELECT field1....fieldn
FROM dbo.mysourcetable
ORDER BY field2, field4
The IDENTITY values won't be in order of field2 and 4?
December 15, 2005 at 12:36 pm
>>The IDENTITY values won't be in order of field2 and 4?
Yes, they will, as long as the table is pre-created and you use INSERT INTO ... SELECT FROM.
The problem occurs if you create the table via SELECT INTO:
SELECT IDENTITY(int,1,1) As IDCol, {Other Columns}
INTO NewTempTable
...
ORDER BY {Other Columns}
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply