September 21, 2018 at 9:41 am
Hi all,
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.
Problem solved!!
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.
September 25, 2018 at 2:39 pm
mig28mx - Friday, September 21, 2018 9:41 AMHi all,
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.
Problem solved!!
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.
Did you script out the statistic before deleting it? You'd probably need that to try to figure out the specifics.
It's not very straightforward to do it - you have to select the database, generate scripts, select the table, click on advanced for the script options and then change script statistics from the default of Do Not Script Statistics to Script statistics or Script statistics and Histogram. Wouldn't everyone do that? 🙂
Sue
September 26, 2018 at 9:05 am
Hi Sue,
No, I did not. Thank you for the advice!
I will try to reproduce the issue on my development environment.
Regards.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply