Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

SQL Server 2005 syscomments slowness issue

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?

Thanks!
0
peiasp
Asked:
peiasp
  • 2
  • 2
  • 2
3 Solutions
 
cyberkiwiCommented:
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>');
0
 
peiaspAuthor Commented:
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.

 
0
 
cyberkiwiCommented:
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.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Scott PletcherSenior DBACommented:
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.
0
 
Scott PletcherSenior DBACommented:
(Well, I can't speak to how SQL might lock, or not lock, the underlying objects for sys.comments.)
0
 
peiaspAuthor Commented:
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!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now