October 30, 2006 at 9:14 am
We are currently running SQL Server 2000 on single box for the last 2 years. There were some performance issues on the database and after analysing I saw that the database required defragmentation. So I executed DBCC INDEXDEFRAG on all the tables and things went back to normal in terms of the performance. After two days our application users complained about some un expected results. After the analysis following is the information.
The table that has 3 columns
1. TarifTypeKey, LanguageKey and Description
2. There is a composite clustered index on TariffTypeKey and LanguageKey
3. The table returns only 5 rows in the following format
1. Download
2. Upgrade
3. Deletion
4. Force
5. Print
4. These values are returned back from the database from a Stored Procedure and following is the code
SELECT TariffTypeKey, Description
FROM TariffTypesDescription
WHERE LanguageKey = 'EN'
The same query worked fine since the first day. But since I have executed the script to defragment the indexes we get back results in the following order
5. Print
3. Deletion
1. Download
2. Upgrade
4. Force
Nothing has changed in the stored procedure. I did some more research and tried to break down the query into two parts
1. SELECT TariffTypeKey, Description
FROM TariffTypesDescription (until here we get the desired results)
2. WHERE LanguageKey = 'EN' (as soon as we use this the results are changed)
Kindly provide some of your knowledge on this as this is on the live database. Please do not hesitate to contact me if you need further information.
October 30, 2006 at 9:42 am
The defragmentation made the indexes more usable because they would get faster data access than doing a table scan. This means that the server changed its execution plan on the select which changed the order in which the data was accessed by the query which changed the order of the results.
That in itself is not a bug because you never specified any order for those results so you can expect the right results in any order. However since you need a specific order for that query, you will have to use the order by clause in the select. This is the only way to guaratee a consistant order of the results.
October 31, 2006 at 8:14 am
Order by is the only way to be sure of the results.
October 31, 2006 at 8:26 am
Thanks a ton for everyone who replied to this question as I already fixed the problem using ORDER BY clause but I was wondering how come the orders of the rows changed.
I appreciate it.
October 31, 2006 at 8:34 am
As I already said, the order of the results changed because the query plan changed, which itself changed most likely because of the index defrag you ran.
You can see the execution plan in query analyser (menu, query, display execution plan). Then just run both versions of the query to see the different access paths.
October 31, 2006 at 8:39 am
Ninja's RGR's! Thank you very much I got it.
November 2, 2006 at 6:02 am
Just to reinforce the importance of the ORDER BY clause ...
Even with the same query plan, there is NO guarantee that results will come out in the same order every time unless you specify ORDER BY.
The server will grab the relevent data pages out of memory or from disk as necessary. The order in which these pages are retrieved is completely unspecified, and the data will be returned in that same order unless your query instructs otherwise (i.e. using our old friend the ORDER BY clause).
It is one of the fundamental tenents of relational database theory that data order is irrelevent and cannot be guaranteed.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply