CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008user-defined-functions
scarpacci
scarpacci

Reputation: 9204

Really strange issue with Table Valued Function

I was getting some timeouts on my app when I was trying to call a specific stored procedure. I isolated it down to a specific sproc and found that when I call a tablevalued function in that sproc with a parameter it takes forever to run. But if I pass in a specific value (like 804) it will return quickly.

If I run it by itself with the parameter it runs fine, but when I add it back into the query with the parameter it runs forever....then swap out the param with a static value and it runs quickly?

Steps I have taken:

  1. Dropped procedure and recreated.
  2. Ran DBCC FREEPROCCACHE on all plans containing a similar query string.
  3. Just a test db so I restored the db with last nights backup.

Another thing to note. On another db on the same server...about the same amount of data it runs fine......very strange.

What am I overlooking? I really don't get it.

Upvotes: 0

Views: 863

Answers (1)

gbn
gbn

Reputation: 432657

Parameter sniffing?

Sorry for the short answer, but worth reading some answers...

From me previously too

Upvotes: 1

Related Questions