August 16, 2018 at 3:21 am
Hi
Thanks to everyone for their help and suggestions. I have managed to fix the issue. I simply moved the tempdb files (6 files plus the log) to a newly created folder on the root of the d drive. After doing this the query ran in under 4 secs from 1 min plus. As this is hosted in AWS and our disc is part of Shared SAN then this leads me to the conclusion that the original location for the tempdb files was on some kind of disc 'hot spot'?? If anybody can offer any thought on this then i'd welcome them. Does creating a new folder on your drive mean that you now use a different area of the SAN and thus move away from the potential 'hot spot'?
Anyway the problem is fixed so i will mark this post as solved
August 16, 2018 at 6:25 am
PearlJammer1 - Thursday, August 16, 2018 3:21 AMHi
Thanks to everyone for their help and suggestions. I have managed to fix the issue. I simply moved the tempdb files (6 files plus the log) to a newly created folder on the root of the d drive. After doing this the query ran in under 4 secs from 1 min plus. As this is hosted in AWS and our disc is part of Shared SAN then this leads me to the conclusion that the original location for the tempdb files was on some kind of disc 'hot spot'?? If anybody can offer any thought on this then i'd welcome them. Does creating a new folder on your drive mean that you now use a different area of the SAN and thus move away from the potential 'hot spot'?
Anyway the problem is fixed so i will mark this post as solved
I would think that such a move on a SAN would make little or no difference, even on AWS. The necessary restart of the SQL Services may have had more to do with it than anything else although I have doubts even there except that it may have broken some form of abhorrent connection.
The only way to know for sure would be to repeat the experiment by moving the TempDB files back to the original folder and running the same tests. Of course, on a SAN, that may not be a "sure and repeatable" test because you just don't know where the hell the data is actually going to end up living.
As a bit of a side bar, I'd also go back and fix the growth settings on the staging box. A PERCENTAGE based growth is never a good thing, IMHO.
--Jeff Moden
August 16, 2018 at 8:42 am
Jeff Moden - Thursday, August 16, 2018 6:25 AMPearlJammer1 - Thursday, August 16, 2018 3:21 AMHi
Thanks to everyone for their help and suggestions. I have managed to fix the issue. I simply moved the tempdb files (6 files plus the log) to a newly created folder on the root of the d drive. After doing this the query ran in under 4 secs from 1 min plus. As this is hosted in AWS and our disc is part of Shared SAN then this leads me to the conclusion that the original location for the tempdb files was on some kind of disc 'hot spot'?? If anybody can offer any thought on this then i'd welcome them. Does creating a new folder on your drive mean that you now use a different area of the SAN and thus move away from the potential 'hot spot'?
Anyway the problem is fixed so i will mark this post as solvedI would think that such a move on a SAN would make little or no difference, even on AWS. The necessary restart of the SQL Services may have had more to do with it than anything else although I have doubts even there except that it may have broken some form of abhorrent connection.
The only way to know for sure would be to repeat the experiment by moving the TempDB files back to the original folder and running the same tests. Of course, on a SAN, that may not be a "sure and repeatable" test because you just don't know where the hell the data is actually going to end up living.
As a bit of a side bar, I'd also go back and fix the growth settings on the staging box. A PERCENTAGE based growth is never a good thing, IMHO.
Hi Jeff - literally as soon as i announced it was fixed as a result of moving tempdb, the problem came back!!!! It came as soon as I had restarted the services that run our sql server monitoring software. After continually running the query with the sql monitoring services on and off I can safely conclude this is causing the issue. We have, for now, kept the services off so i'm back to old school monitoring with my regular jobs queuing the dmv's and logging into my dba database. I'm going to be in contact with the vendor to see if its a configuration setup error on my part or a bug in their software.
August 16, 2018 at 12:22 pm
Good to hear you've pinpointed the cause. FWIW, this is one of the reasons I always advocate running the same sort of monitoring on a test environment that you would in live if at all possible, the more you can make your test systems behave "as live" the earlier you are likely to identify things that will go wrong.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply