July 28, 2015 at 1:46 am
Hi all
I'm experiencing a performance issue on the whole instance. Queries used to execute very well recently and in the last couple of weeks they perform poor.
A SP that was executing in just a second, now executes in minutes.
Update of the statistics of tables is done very frequently. When I run sp_updatestats for the database it says that there is no need of updateing the stats...
This is very strange ...
Igor Micev,My blog: www.igormicev.com
July 28, 2015 at 2:57 am
What's changed?
Compare query plans from before and now, see what's different. Compare your wait stats baseline to what you see now, see where the waits have increased.
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
July 28, 2015 at 3:02 am
GilaMonster (7/28/2015)
What's changed?Compare query plans from before and now, see what's different. Compare your wait stats baseline to what you see now, see where the waits have increased.
I did all that, the plans, the stats, the waits, ... until checked the disks just a few moments ago.
I have three disks. Two raids (RAID1) and one SSD.
The response time of the RAIDs was between 0-100ms and for the SSD was over 500ms sometimes reaching 2000ms.
I'm now going to move the files to the RAIDs to temporary get rid of the slow SSD 🙁
Igor Micev,My blog: www.igormicev.com
July 28, 2015 at 3:12 am
I'm not all that familiar with SSDs, but that doesn't sound at all healthy...
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
July 28, 2015 at 4:20 am
Any maintenance plans for reindexing or index reorg schedules?
July 28, 2015 at 10:40 am
If the disk has gone bad, that should have shown up in the wait statistics. You didn't see any changes tehre at all?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 28, 2015 at 4:13 pm
Were there any server changes? Are there new instances? Did your hardware change?
Do you have any server monitoring set up (not just sql monitoring)?
Is your CPU/Hard Drive/Memory bottlenecked?
Have you checked the event viewer for errors?
Did you do an update to SQL Server recently?
Have you rebooted SQL Server recently?
It sounds like the SSD is bad. But I would double check everything to make sure if it's not resolved when you move everything off.
July 29, 2015 at 9:14 am
Only time I have seen SSD performance degrade is when TRIM is not working. But I wouldn't have expected it to be that bad.
July 29, 2015 at 9:29 am
Something must have changed. Are you sure data volumes haven't caused execution plan changes? Are you positive that the hardware hasn't changed? No new schemas objects added?
Do you have a monitoring process/application in place? Most of the time I see complaints like this, I can check a monitoring system to know when things might have changed. If you don't have that, then it's possible this is degraded over time and someone is now noticing.
I'd suggest if you don't have something, you look at Monitoring performance with something like SQL Monitor[/url], and certainly monitor schema change with something like DLM Dashboard.
Disclosure: I work for Redgate Software
July 29, 2015 at 9:50 am
Hi All,
Thanks for replying.
Issue is resolved.
The ssd disk has been going working worse and worse. Its response time has been changing in the last couple of weeks having increments.
Today it went worst having avg resp time of almost 1.5 sec.
I moved the databases on the other disks and the apps do function normally now.
Igor Micev,My blog: www.igormicev.com
July 29, 2015 at 9:53 am
Grant Fritchey (7/28/2015)
If the disk has gone bad, that should have shown up in the wait statistics. You didn't see any changes tehre at all?
Exactly that. I later saw that it's a disk hardware problem.
Igor Micev,My blog: www.igormicev.com
July 29, 2015 at 3:35 pm
Igor Micev (7/29/2015)
Hi All,Thanks for replying.
Issue is resolved.
The ssd disk has been going working worse and worse. Its response time has been changing in the last couple of weeks having increments.
Today it went worst having avg resp time of almost 1.5 sec.
I moved the databases on the other disks and the apps do function normally now.
I would test and make sure this is the SSD and not a problem with the port.
July 29, 2015 at 3:49 pm
JoshDBGuy (7/29/2015)
Igor Micev (7/29/2015)
Hi All,Thanks for replying.
Issue is resolved.
The ssd disk has been going working worse and worse. Its response time has been changing in the last couple of weeks having increments.
Today it went worst having avg resp time of almost 1.5 sec.
I moved the databases on the other disks and the apps do function normally now.
I would test and make sure this is the SSD and not a problem with the port.
Hm, this is interesting that we discussed too. But now I cannot know that.
Igor Micev,My blog: www.igormicev.com
July 29, 2015 at 3:55 pm
Igor Micev (7/29/2015)
JoshDBGuy (7/29/2015)
Igor Micev (7/29/2015)
Hi All,Thanks for replying.
Issue is resolved.
The ssd disk has been going working worse and worse. Its response time has been changing in the last couple of weeks having increments.
Today it went worst having avg resp time of almost 1.5 sec.
I moved the databases on the other disks and the apps do function normally now.
I would test and make sure this is the SSD and not a problem with the port.
Hm, this is interesting that we discussed too. But now I cannot know that.
Well, if you have an extra piece of hardware and sata slot (i'm assuming sata) you can just put it in and test it.
July 29, 2015 at 4:05 pm
JoshDBGuy (7/29/2015)
Igor Micev (7/29/2015)
JoshDBGuy (7/29/2015)
Igor Micev (7/29/2015)
Hi All,Thanks for replying.
Issue is resolved.
The ssd disk has been going working worse and worse. Its response time has been changing in the last couple of weeks having increments.
Today it went worst having avg resp time of almost 1.5 sec.
I moved the databases on the other disks and the apps do function normally now.
I would test and make sure this is the SSD and not a problem with the port.
Hm, this is interesting that we discussed too. But now I cannot know that.
Well, if you have an extra piece of hardware and sata slot (i'm assuming sata) you can just put it in and test it.
I'll definitely recom that to be checked.
Igor Micev,My blog: www.igormicev.com
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply