SQL Server Object Dependencies

Paul Montgomery was kind enough to pass this one on. This is great for seeing what function or stored procedure is used by.

SELECT DISTINCT
o.name AS UsedBy
FROM Sys.sysdepends j
JOIN sys.objects o on o.object_id = j.id
JOIN sys.objects d on d.object_id = j.depid
WHERE d.name = 'MyFunctionOrStoredProcedureName'

Stored Proc that Runs Slow The 2nd Time

Recently we had a weird situation where we had a stored procedure that was being called twice with the same parameters. The first time, it executed in 3 seconds. The second time it actually timed out the ADO.NET connection because it took longer than 30 seconds. We were very puzzled by this and it took a long time to solve so I wanted to pass this information on.

Here is what was happening. SQL Server stores an execution plan for each combination of SET commands. We had the command SET ARITHABORT ON inside the stored procedure. The default for ARITHABORT is off for SQL Server. Basically when the connection logged in, the ARITHABORT was turned off. When the stored procedure ran, it was switched on. This switching forced SQL Server to use a different execution plan which was super slow.

The fix was to set the ARITHABORT on for the application user. You can set the ARITHABORT for the user or the database but not in a connection string. You can also set it right before the sproc is called.

Example:
SET ARITHABORT ON exec dbo.[MyGiantSproc]

See these other links for more information:
Set Database Options
Query Execution Plan Problems