June 29, 2010 at 12:43 pm
SQL Server 2005 Standard Edition 9.00.3042.00. I work with an application that uses it's own schema management sub-app. The sub-app queries sys.syscomments to determine actions needed for the app schema like column modifications, etc.
My problem: I have one database where the following query takes 2-5 seconds:
select text from syscomments where object_name(id) = '<some name>';
The problem is that this query is called several hundred times so a 2 minute process can take 20-30 minutes. I copied the database to a new database in the same server instance - the query is instant. I understand I can 'fix' this by simply export/importing the database to a new one - but I would really like to understand what the problem is.
Ideas?
Thanks!
June 30, 2010 at 9:11 am
Your query causes a full table scan.
To get good performance the query should look like this:
select text from syscomments where id=object_id('dbo.ObjectName')
June 30, 2010 at 9:41 am
Thank you for the response. I'm still concerned though, I understand a full scan would cause the difference in time. What I don't get (and I'm not finding the information on research) is why this system view would differ in results like that.
Here's a better explanation of the results:
use master
select text from syscomments where object_name(id) = '<some name>'
[ 0 rows; 0 sec]
use <bad db>
select text from syscomments where object_name(id) = '<some name>'
[ 0 rows; 2 sec]
use <copy of bad database from "SQL Management Object method" on the same lun>
select text from syscomments where object_name(id) = '<some name>'
[ 0 rows; 0 sec]
Index fragmentation is small - database is not busy.
July 2, 2010 at 3:36 am
Sounds like a fragmentation issue with some system tables, or perhaps some kind of database corruption.
To me the best fix seems to be to simply avoid full scans of the syscomments view
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply