SQL Server 2005 syscomments slowness issue
Posted on 2010-09-21
SQL Server 2005 Standard Edition. I work with an application that uses it's own schema management app. This app queries sys.syscomments to determine actions needed for the app schema like column modifications, etc. I understand this is not recommended in 2005, but the app is setup for compatibility with 2000.
My problem: I have some (not everyone) database where the following query takes 2-5 minutes:
select text from syscomments where object_name(id) = '<some name>';
If I build a database and load the data with SSIS (export), this is not an issue. If I create a clone of the database by restoring it to another database, the issue persists.
Any ideas what the issue is and how to fix it?