random lag time in SQL query
Hi, I have a customer that have a major problem that comes and go for months now. When doing query, all is going well most of the time, the request takes less than a second to return results. Problem is that from time to time the same query takes up to 10 seconds to return results, making the program very frustrating to use if not almost useless. This can happen many times during a day (more than 20 times at the worst days) or not at all. I'm using sql server 2012 running on a windows server 2012 essential. Problem can occur even when running directly on the server, it also happens even if there is just one computer opened.
I was not using indexs (except primary keys) and I thought this might be the problem, I have created some and the problem stopped right away for a month and came back for no reason. I have tried to use the ALTER INDEX... REBUILD command but this had no effect at all.
I have used up pretty much all solutions I had in mind, I don't know where to go from here. If anyone has any idea what this is or any suggestions on tools I can use to monitor this behavior and detect the source, that would be greatly appreciated.
I was not using indexs (except primary keys) and I thought this might be the problem, I have created some and the problem stopped right away for a month and came back for no reason. I have tried to use the ALTER INDEX... REBUILD command but this had no effect at all.
I have used up pretty much all solutions I had in mind, I don't know where to go from here. If anyone has any idea what this is or any suggestions on tools I can use to monitor this behavior and detect the source, that would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi ScottPletcher, I have looked at missing index stats but it is empty, I don't know if it's normal? I have used the command SELECT TOP 10 * FROM sys.dm_db_missing_index_gr
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
@Zberteoc, I'll be sure to take a look at the solarwind database monitor, I'll be installing this shortly on my customer's server. I'll see next week the results.
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;
@Zberteoc, I'll be sure to take a look at the solarwind database monitor, I'll be installing this shortly on my customer's server. I'll see next week the results.
ASKER
I have some news regarding this case, last case of lag was on oct 27th and there was no problem until november 16th, I had installed the monitoring software but since it was a 14 days trial the period was over I don't have much data.
Fortunatly I was logging in a database the PLE value everytime I'm calling the SQL query that is most used, I have seen that it was going up each days but then it dropped from 233432 to 8 causing a lag at this precise moment.
Since that moment, the PLE has begun to go up again, there has been 18 more lags, 3 on the same night that the PLE dropped and 15 the next day. The PLE of the last lag was at 72217.
Should I do something about the PLE, is it normal behavior that I'm experiencing? If so, what should I look for next?
Fortunatly I was logging in a database the PLE value everytime I'm calling the SQL query that is most used, I have seen that it was going up each days but then it dropped from 233432 to 8 causing a lag at this precise moment.
Since that moment, the PLE has begun to go up again, there has been 18 more lags, 3 on the same night that the PLE dropped and 15 the next day. The PLE of the last lag was at 72217.
Should I do something about the PLE, is it normal behavior that I'm experiencing? If so, what should I look for next?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The error has not been seen since october even though nothing was changed. I have installed a monitoring for a month and noticed a PLU drop sometimes when the problem occurs. This is yet to be solved but I have to close the question since no new development have occured for months.
ASKER
There is no maintenance for now of the indexs, I had done a rebuild once for every indexes to see if it makes any difference but it did not.