I don´t know if the subject it is correctly named, because all performance issue it is weird by definition. I choosed it on purpose to attract you attention on a issue that experienced on my production database server.
I have a stored procedute that performs an update for a table under certains conditions. The weird thing is that, suddently from nowhere, the first time when the sp is executed, takes 8 minutes to update one value on the table. After this, all the times the update takes less than one second.
Piece of cake to solve.
Check the execution plan. No issues. Check the indexes. No issues. Check for parameter sniffing. No issues. Check statistics. Statistics up to date. Temp DB?. Plenty of space. Bad cardinality? No issues.
Check on development environment the same issue. Not showing that behaivour.
What the heck?
Emergency. Run Brent Ozar´s performance BLITZ scripts. It found some issues, but none related with this. Missing indexes? No. Too many indexes? No. Index fragmentation? Less than 10%.
Desespered maneouvers. Reindex. No solution. Index reorg. No solution. Modify sp to with recompile. Worst case scenario: the sp freezes it until first time update.
Calm down and review again. Check the table structure. It is ok. How many records? aprox 100,000. Not a big table. Check the indexes. Three indexes. PK is the choosed one for the update. Check statistics. Seems ok, and it was updated a night before. Hey what is that statistics started with _WA_Sys_xxxxxx? Humm..check this link https://www.mssqltips.com/sqlservertip/2734/what-are-the-sql-server-wasys-statistics/
Remove user created statistics. Check again.
Now I´m doing some forensic investigation to know why and how this statistics affects the sp or why the query optimizer hanged on it.
I hope this can help you in some similar issue.