x

Multiple Table reads

I've run into a few issues where a developer may have unintentionally created a stored procedure that hits a table twice for the same info. Usually, this is due to logic in the procedure and it's not that hard of a fix once it's found.

If I have the query I can go with SET STATISTICS IO ON and see the double work, but what if you don't have the query to start with.

One way I've gone about it is by using profiler to capture the scans and then drilling down from there.

Does anyone know of other ways to track down stored procedures using profiler that may be doing double work?

more ▼

asked Nov 10, 2009 at 10:59 AM in Default

Jason Crider gravatar image

Jason Crider
453 10 10 12

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

I know this doesn't directly answer your question, but if you want to find every procedure that references a table more than once, this will work.

In SQL Server 2000, the syscomments system table contains the text for all stored procedures. For SQL Server 2005 and 2008, this has been replaced by sys.sql_modules. The biggest difference is that syscomments contains multiple rows for the same procedure if it is over 4000 characters. This is not necessary with sys.sql_modules because it uses the nvarchar(max) data type.

Please note: The following script will search the procedure text for whatever you specify for @TableName. If the name is something like "SpecialCustomerTable", you will probably get good results. However, it could return too matches if your table name is a common word used in comments or other places.

DECLARE @TableName varchar(200) SET @TableName = ''-- procedures which contain the same word or phrase twice in less than 4000 characters SELECT o.name FROM syscomments sc  INNER JOIN sys.objects o  ON sc.id = o.object_id WHERE o.type = 'P'  AND sc.text LIKE '%' + @TableName + '%' + @TableName + '%' UNION -- procedures which contain the same word or phrase twice in over than 4000 characters SELECT o.name FROM syscomments sc  INNER JOIN sys.objects o  ON sc.id = o.object_id WHERE o.type = 'P'  AND sc.text LIKE '%' + @TableName + '%' GROUP BY o.name HAVING COUNT(*) > 1 ORDER BY o.name 
more ▼

answered Nov 10, 2009 at 11:57 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

It's definitely difficult to do this sort of thing without a full-on parser - for instance, this would return a procedure which included the table name in a comment as well as the main code. +1 for effort though :)
Nov 10, 2009 at 12:32 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

It's kind of hard to define "double work" because a given query might legitimately hit a table multiple times. However, one approach you could use is to query the plan cache using XQuery. You could look for plans that reference a table more than once. That's only going to capture the stuff in cache. Depending on your system and how fast it's flushing the cache, you might need to run the query more than once a day.

more ▼

answered Nov 11, 2009 at 09:35 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x473
x34
x33

asked: Nov 10, 2009 at 10:59 AM

Seen: 1481 times

Last Updated: Nov 10, 2009 at 10:59 AM