| Author |
Topic |
|
jimdurnford
Starting Member
4 Posts |
 Posted - 2011-06-09 : 14:11:07
|
| We have an issue where it seems that indexes or at least one certain index is becoming corrupted. When we rebuild the index our problem is fixed but only for a short period of time until the problem comes back (days at most, hours at worst).Some history:- The app was created several years ago, long before I got here, with an access front end connecting to SQL Server 2000. It has been running fine for many years, other than performance problems, until recently.- About 6 weeks ago we migrate to SQL Server 2008. The migration went fine and everything was working well until about 2 weeks ago.- Two weeks users started reporting serious performance problems i.e. taking 20s to return a list of items which used to take <2s.- Then it stopped working properly at all. Instead of returning records assigned to one individual it would return records assigned to any individual (this is an activity tracking DB). i.e. So instead of returning my tasks (60ish) it would return 100s of items most of which are assigned to other members.- I had seem something similar years ago in which rebuilding indexes fixed the problem. In that case we rebuilt them once and the problem never came back.- In this case the problem reoccurs within hours to days.We are using Access 2003 and there were no changes to the app other than to change the DB connection when the migration occurred.We migrated from SQL Server 2000 to SQL Server 2008.When he problem occurs we have run DBCC CHECKDB which indicated no errors and yet rebuilding the indexes fixes the problem immediately.- DBCC CHECKDB (db name) WITH NO_INFOMSGS, ALL_ERRORMSGS produced no output.- DBCC CHECKDB (db name) WITH ALL_ERRORMSGS" gave a list that included: "CHECKDB found 0 allocation errors and 0 consistency errors"What could be causing this issue?Why does CHECKDB report no problems and yet rebuilding the index at least temporarily fixes the problem?Why does SQL Server return incorrect data to a query when there is a problem with an index? (I'm assuming the problem is with the index since rebuilding the index fixes the problem)The index is a clustered index.Any help on this will be greatly appreciated.SQL Server version info:Version 10.0.2531.0Product Level: SP1Edition: Standard EditionAccess Version info:MS Access 2003 (11.8321.8333) SP3thanks,JimWhy does rebuilding an index fixJim |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-09 : 14:19:15
|
| You don't have a corruption problem then.By any chance, do your queries contain the old-style *= , =* syntax for doing outer joins? If so, that will cause problems in SQL 2008, and they need to be rewritten as LEFT and RIGHT JOINs, respectively. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-09 : 14:43:00
|
| Is Auto-Update Statistics on?Does the table get a lot of updates/inserts throughout the day?Tried manually updating statistics? |
|
|
|
jimdurnford
Starting Member
4 Posts |
Posted - 2011-06-09 : 17:22:26
|
quote:
Originally posted by robvolk
You don't have a corruption problem then.By any chance, do your queries contain the old-style *= , =* syntax for doing outer joins? If so, that will cause problems in SQL 2008, and they need to be rewritten as LEFT and RIGHT JOINs, respectively.
Thanks for your speedy reply, I will certainly check on the join syntax used as soon as I can.Out of curiosity, if the index isn't corrupted why does rebuilding it fix the problem?Jim |
|
|
|
jimdurnford
Starting Member
4 Posts |
Posted - 2011-06-09 : 17:26:49
|
quote:
Originally posted by russell
Is Auto-Update Statistics on?Does the table get a lot of updates/inserts throughout the day?Tried manually updating statistics?
Auto-Update Statistics is on.There is not a lot of updates to the affected table and far less to other tables. I believe the number of updates was in the hundreds while queries were higher.We have not tried updating statistics manually but will next time this happens.Jim |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-09 : 17:33:26
|
quote:
Out of curiosity, if the index isn't corrupted why does rebuilding it fix the problem?
My guess is it removes query plans from the procedure cache, as would updating statistics. The plans recompile as they are executed, and it's likely the compiled plan works for one set of parameters but is junk for another set (parameter sniffing). This is just a theory based on some past experience, although it didn't involve data issues like you're seeing. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-06-10 : 03:54:10
|
| Did you run the upgrade advisor before upgrading? The other guys here are a lot more experienced than me but from the sound of your problem the first thing that popped to my mind was use of deprecated / undocumented features. There are specific events in Profiler to monitor for deprecated feature usage...- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
jimdurnford
Starting Member
4 Posts |
Posted - 2011-06-16 : 13:15:49
|
| There are issues to be resolved before running the upgrade adviser so we haven't be able to do that yet. Mainly the original server is gone. But we did run start tracing queries and found the following for on user seeing erroneous data. Can anyone tell me what this means?Notice that the final call to vPendingList at StartTime=33:53.7 has a Error=2.Jim |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-07-04 : 02:13:28
|
| Is "Notice that the final call to vPendingList at StartTime=33:53.7 has a Error=2." the actual error emssage? Or is the error message just "2"? Your post is inclear... Nevertheless; if you have a profiler trace you should also have the actual query that was run when the error was produced. You can just copy/paste it in to a query window, run it and then see what happens...- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
Index corruption/problems