Solved

SQL Server 2005 syscomments slowness issue

Posted on 2010-09-21
6
345 Views
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?

Thanks!
0
Comment
Question by:peiasp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
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>');
0
 

Author Comment

by:peiasp
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.

 
0
 
LVL 58

Assisted Solution

by:cyberkiwi
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.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher 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.
0
 
LVL 69

Expert Comment

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

Accepted Solution

by:
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!
0

Featured Post

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.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

622 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