Recently migrated a database from SQL2K to SQL2K8. Access adp/ade front end. We're getting some hanging. I suspect locking issues. Any experience or advice in what to look for? User says they open a drop down (under 100 records) and the app freezes for minutes.
Not sure what the problem would be. When I switched our users over from a SQL2K over to SQL2K8 (and 2005), we didn't have any issues, and I have a mixed environment of MS Access, going from 2000, 2003, and 2007. The only issues I had with performance was coming from the server itself. Old, needed to be retired.
First off, I'd make sure all the users have the latest updates for Office and Windows.
Oh, you could also try deleting the DSN connection on the workstation, and reset it. SQL2K8 may be doing something different when setting up the initial connection.
"Forget Jesus! Stars died so you could be here today." -- Dr. Lawrence Krauss, theoretical physicist
I'll run a Profiler on locks this morning and see if that shows something or other. Tried a profiler on long running queries, didn't show much. But we had a failover in the middle, so that cut it short.
The analyst tells me that the app behaved better under SQL2K than under SQL28K.
I'll see what the profiler shows. There seem to be mixed opinions on whether to use the NOLOCK hint on views, when using an Access front end.