Often when a stored-procedure is executed I want to know the parameters that were input. Which is handy for performance tuning.
There is a mechanism to automatically save input parameters with the cached execution plans, but quite often this does not work well.
On this occasion I embedded the facility right into the procedure as a temporary measure (please, don’t talk to me about triggers brrr).
CREATE PROCEDURE [dbo].[sp_SomeName]
@ID UNIQUEIDENTIFIER = NULL,
@Record VARCHAR(50) = NULL
AS
BEGIN
/* log parameters for performance tuning 1 of 2 */
IF OBJECT_ID('[SomeDatabase].[dbo].[tbl_SomeTable]') IS NULL
SELECT GETDATE() STIME, GETDATE() ETIME, @ID ID, @Record RC
INTO [dbo].[tbl_SomeTable]
ELSE
INSERT INTO [dbo].[tbl_SomeTable]
SELECT GETDATE(), GETDATE(), @ID, @Record
/* log parameters for performance tuning 1 of 2 */
...
Overkill really, but at the end of the procedure I added …
...
/* log parameters for performance tuning 2 of 2 */
UPDATE [dbo].[tbl_SomeTable]
SET ETIME = getdate()
WHERE ETIME = STIME;
/* log parameters for performance tuning 2 of 2 */
END
GO
Note: the real procedure had many more input parameters, and I suspected they are all set to null. Which would explain the poor performance.
Still, best to know what we’re optimizing for 🙂
Database Consultant 