Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005 syscomments slowness issue

Posted on 2010-09-21
6
Medium Priority
?
351 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 1600 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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 70

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

715 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