October 21, 2020 at 3:50 pm
sp runs in 1 second sometimes and sometimes it takes forever and returns null even though there is data
thought it was parameter sniffing and started using option recompile..but still runs the same.
What could be the reason for this behavior?
Thanks!
October 21, 2020 at 3:55 pm
Locking?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 21, 2020 at 4:00 pm
Check your execution plans. That will be the best way to tell what is changing with performance.
As for returning NULL when there is data, chances are the stored procedure is filtering out the data based on a passed in parameter or possibly a bug in the stored procedure (not sql, but in the code behind the stored procedure) and thus you are getting NULL.
When it runs fast and slow, are you using the same parameters? For example "EXEC testSP @intInput = 10" each time?
And what does the stored procedure do? There is so much that can cause a stored procedure to be fast or slow. If you are pulling back 1 B of data vs 1 GB of data, the second will be slower. Or a table/index seek vs a table/index scan. Or different sorting of the data via ORDER BY.
Can you post the stored procedure and some sample data and the execution plans for fast, slow, and NULL when you expect data as well as the EXEC command you are running for all 3 use cases?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 21, 2020 at 4:13 pm
I am seeing a lot of PAGEIOLATCH_SH wait types when the sp runs.
October 28, 2020 at 2:07 pm
I've compared the execution plan.. it looks the same. I don't see any difference.
October 28, 2020 at 2:27 pm
I've compared the execution plan.. it looks the same. I don't see any difference.
Entertain us, post those execution plans; either as an attachment or using a tool like Paste the Plan. The definition of the Procedure would be nice too.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 30, 2020 at 2:14 pm
Are you able to see whether it's blocked by another transaction? The fact that it returns null on the occasions when it takes a long time suggests some other process may be updating the data it uses.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply