Monitor SP Parm Lengths

Question

Monitor SP Parm Lengths

nfs_john

Ten Centuries

Points: 1275
More actions
September 6, 2011 at 12:14 pm

#149584

Hello,
We are trying to get an idea of the avereage length of a parameter being passed in. Currently the sp's use varchar(max) so that it doesn't break but I'd like to get a better feel for if max is really needed. One idea I had was to add some code to the sp to calculate the length of the paramater then store that in some table etc. The problem with that is that it requires altering production code so it would have to go through the whole testing process and then when no longer needed the elimination of the capture code tested again.
Is there another way, perhaps using a trace etc. where I can get a feel for the real length needed?
TIA
JB

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could run a server side trace (PROFILER trace, but not using the GUI) to capture the calls of the sproc. This will show the actual parameter used.

Another option could be a code review: How are the parameter used within the sproc? Do they end up being inserted into a column or used in any comparison against a column? If so, what's the related data type?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nfs_john Ten Centuries Points: 1275 More actions · Answer 2

Well my chief aim is really further down the road to get them migrated to XML and eventually table based parameters...in the meantime MOST of the times they pass in a string then do a WHERE XYZ IN() check...ohh yes you can't quite throw a varchar list into a TSQL IN statement like that so they use dynamic sql.

So they build the tsql query as a giant string concatenating the varchar list passed in and then execute the dynamic statement. Step one for me was just to eval and the data types being used and move to refactoring next.

LutzM SSC Guru Points: 107049 More actions · Answer 3

I just hope the logic that's being used is protected against SQL injection... But that's a different story.

In the scenario described I would run a server side trace. While running it, I would read Erland Sommarskogs Blog to get a few more ideas how to pass tables (or lists) to stored procedures.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nfs_john Ten Centuries Points: 1275 More actions · Answer 4

Agrred on the table but awaiting upgrade to 2008. Till the refactor and tune as best as I can. FWIW passing tables and merge statement in 2008 would take alot of the sp's from hundreds of lines down to sub 20's.

I'll try the server side trace....what I mean is I'll try to convince the DBA's to do a server side trace for me.

Thanks

JB