Sp & parameters

Question

Sp & parameters

ramana3327

SSCoach

Points: 19722
More actions
May 30, 2014 at 10:10 pm

#287070

Hi,
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?

Viewing 15 posts - 1 through 15 (of 23 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

ramana3327 (5/30/2014)
Hi,
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?

You will have to dissect the procedure as there is no way of knowing where those defaults are coming from.

😎

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

ramana3327 (5/30/2014)
Hi,
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?

If I understand your question correctly, the defaults are assigned to the parameter variables in the proc. Simply do a SELECT from those parameter variables and compare them to the data in the sys.parameters catalog view.

You should also look up what @@PROCID does in Books Online.

You're turn. Why do you need to do this? I ask because you're question is a little ambiguous and I want to make sure you have the correct answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Jeff Moden (5/30/2014)
ramana3327 (5/30/2014)
Hi,
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
If I understand your question correctly, the defaults are assigned to the parameter variables in the proc. Simply do a SELECT from those parameter variables and compare them to the data in the sys.parameters catalog view.

Unfortunately that's not too useful, it will show if the parameter is nullable but that's about it.

😎

Quick example

USE tempdb;

GO

CREATE PROCEDURE dbo.USP_TEST_DEFAULT_PARAM

(

@DEFAULT_PARAM_1 INT = 1

,@DEFAULT_PARAM_2 INT = -1

)

AS

SELECT @DEFAULT_PARAM_2 =

CASE

WHEN @DEFAULT_PARAM_1 = 1 AND @DEFAULT_PARAM_2 < 0 THEN 2

ELSE 3

END;

SELECT

@DEFAULT_PARAM_1 AS P1

,@DEFAULT_PARAM_2 AS P2

;

GO

/* Query the sys.parameters */

SELECT * FROM sys.parameters SP

WHERE SP.object_id = OBJECT_ID('dbo.USP_TEST_DEFAULT_PARAM');

GO

DROP PROCEDURE dbo.USP_TEST_DEFAULT_PARAM;

GO

Results

object_id name parameter_id system_type_id user_type_id max_length precision scale is_output is_cursor_ref has_default_value is_xml_document default_value xml_collection_id is_readonly is_nullable

----------- ----------------- ------------ -------------- ------------ ---------- --------- ----- --------- ------------- ----------------- --------------- -------------- ----------------- ----------- -----------

709577566 @DEFAULT_PARAM_1 1 56 56 4 10 0 0 0 0 0 NULL 0 0 1

709577566 @DEFAULT_PARAM_2 2 56 56 4 10 0 0 0 0 0 NULL 0 0 1

ramana3327 SSCoach Points: 19722 More actions · Answer 4

Just to know what are the values they passing. And needs to pass different parameters for testing purpose.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Eirikur Eiriksson (5/30/2014)
Unfortunately that's not too useful, it will show if the parameter is nullable but that's about it.

I just love fully documented and supposedly supported features that don't work. :crazy: I believe we've found a bug.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

Jeff Moden (5/30/2014)
Eirikur Eiriksson (5/30/2014)
Unfortunately that's not too useful, it will show if the parameter is nullable but that's about it.
I just love fully documented and supposedly supported features that don't work. :crazy: I believe we've found a bug.

It is not a bug, it's a YAF (Yet Another Feature) 😀

From the documentation

[font="Courier New"]1 = Parameter has default value.

SQL Server only maintains default values for CLR objects in this catalog view; therefore, this column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.[/font]

In other words, grab the latex gloves and start dissecting......

😎

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

It turns out that we're not the first to encounter this lovely problem. There's been a CONNECT item open since 2006.

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=234143

The reason I assumed it would work is because I'm on my 2005 box for the night and BOL 2005 says...

Contains a row for each parameter of an object that accepts parameters. If the object is a scalar function, there is also a single row describing the return value. That row will have a parameter_id value of 0.

It doesn't say squat about this only being for CLR. Apparently the MS "fix" was to update BOL to say it was used for something else even though it clearly returns the parameters and datatypes, etc for all the parameters of stored procedures EXCEPT the bloody default values.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 8

I am wondering that usually when we execute the stored procedure several times. The execution time should be less but in my case the execution time is taking longer. Not able to figure out why it is happening like that?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

ramana3327 (5/31/2014)
I am wondering that usually when we execute the stored procedure several times. The execution time should be less but in my case the execution time is taking longer. Not able to figure out why it is happening like that?

I don't believe that determining whether or not default values were used or not are going to help you with that problem. You'll need to look at the actual execution plan and do some testing to figure it out. Also, look up "parameter sniffing" for the problem you describe above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 10

Thanks Jeff.

Actually here I think I don't need to consider the parameter sniffing. Because both times I use only default parameters but still the execution times are different.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 11

Quick question, can you provide the procedure source code and the execution plans? As Jeff said earlier, without further information it is hard to tell what is going on?

Furthermore do look into parameter sniffing, here is a place to start.

Looking at your initial posting and the lack of tangible information prompts another question, which is what is the actual problem?

😎

ramana3327 SSCoach Points: 19722 More actions · Answer 12

Hi thanks for your reply.

Code is confidential. So I am unable to provide the execution plan but I can explain what is the problem.

I have a Stored procedure and I executed and I created another duplicate for testing and did some small modifications. I executed the original one first time it is giving me one execution time and executed second & third time the original sp but it is taking long execution times compare to the first time. All the times I executed with default parameters only.

I am thinking that usually when we execute SP it has to take less time. Usually parameter sniffing is plan is cached for one particular parameter and the time may be different for another parameter but I am using same default parameter here. So how I find exact time if execution timings are varying

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

How much longer? And how long does the longest run take.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 14

ramana3327 (5/31/2014)
Hi thanks for your reply.
Code is confidential. So I am unable to provide the execution plan but I can explain what is the problem.
I have a Stored procedure and I executed and I created another duplicate for testing and did some small modifications. I executed the original one first time it is giving me one execution time and executed second & third time the original sp but it is taking long execution times compare to the first time. All the times I executed with default parameters only.
I am thinking that usually when we execute SP it has to take less time. Usually parameter sniffing is plan is cached for one particular parameter and the time may be different for another parameter but I am using same default parameter here. So how I find exact time if execution timings are varying

Understood:alien:

Have you tried adding WITH RECOMPILE,

by the looks of it, it could be a reuse of an obsolete execution plan.

😎