SQL Server 2005 syscomments slowness issue

Posted on 2010-09-21
Last Modified: 2012-05-10
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?

Question by:peiasp
  • 2
  • 2
  • 2
LVL 58

Expert Comment

ID: 33729087
I don't know if this helps, but I normally do it the other way around which should optimize better

select text from syscomments where id = object_id('<some name>');

Author Comment

ID: 33729165
Thanks for your comment cyberkiwi - but I can't control the app (they are changing it in a later version).

What I'm finding difficult is that I can run the same query in other databases (same version, same schema) and it returns back in 2-3 seconds.  I believe these might of been 2000 databases restored to/on a 2005 server, but I'm not sure.

LVL 58

Assisted Solution

cyberkiwi earned 400 total points
ID: 33729239
select text from syscomments where object_name(id) = '<some name>';

This is SCANNING the syscomments table.
For each row, it is using the function object_name to look up the name, then compares it to <some name>.
It scales poorly and degrades for databases with massive amounts of objects.

select text from syscomments where id = object_id('<some name>');

This one instead takes the name and pokes into sysobjects for the id, then does a single key lookup into syscomments for the text.

It is likely syscomments is not meant to be used by end users, so the locking controls are not very good when you start scanning the table, causing it to interfere with other processes.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 69

Assisted Solution

ScottPletcher earned 100 total points
ID: 33729582
Cyberwiki is right, as usual.

And sys.comments is also now just a view, so it may have to be materialized first and then scanned.
LVL 69

Expert Comment

ID: 33729595
(Well, I can't speak to how SQL might lock, or not lock, the underlying objects for sys.comments.)

Accepted Solution

peiasp earned 0 total points
ID: 33729951
Turns out that there were over 40,000 views (used by the app for user security).  Once the views were dropped - the query returned in 2-3 seconds.  

Thanks for all the comments!

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
user defined date datatype in SQL Server- can it be overdone.. 6 24
Permissions on Database 11 37
T-SQL: Nested CASE Statements 4 24
Sql Query with datetime 3 12
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now