Solved

SQL Server 2005 syscomments slowness issue

Posted on 2010-09-21
6
338 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
  • 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
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

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

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.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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