October 21, 2014 at 1:10 pm
Hi,
yesterday our production server started acting quite strange.
PLE dropped from hours to less than ten minutes. CPU utilization is constantly around 60% and tempdb is using more than 50% memory buffer pool. At the same time tempdb has grown from 1.6 Gb to 9 Gb. Tempdb has 97% free space all the time.
Does anyone have where to look at why this happens? I cannot restart SQL server instance until Friday morning.
October 22, 2014 at 2:32 am
What monitoring did you have running at the time it happened?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2014 at 3:06 am
We are using Proactive DBA timeline for monitoring.
Unfortunately I have no records for last week since I was on vacation and our support team made a version release and restarted sql server during it. I have some older ones and I am comparing to those.
October 22, 2014 at 3:10 am
Ok, so what metrics/stats/info do you have for the period in question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2014 at 4:40 am
GilaMonster (10/22/2014)
Ok, so what metrics/stats/info do you have for the period in question?
I have nothing collected. I have been relying on Proactive DBA and it was shut down during the period.
I can only see metrics in august and now.
I think that this is related to spilling to tempdb. In august tempdb IO is around 10-30 ms and at the worse time it was around 3 s.
At the moment IO is around 1.5 s.
I added one missing index and changed one view to use UNION ALL instead of UNION.
October 22, 2014 at 4:50 am
If you've got no stats for the problematic period, you're going to have a very hard time figuring anything out.
First place I would have looked, with monitoring, would have been to see what was running against the DB that wasn't normally running, or was running with non-standard performance statistics
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply