| Ignoring robot attacks to help SQL Server response time | Helpful |
Ignoring robot attacks to help SQL Server response timeFriday, 27 June 2014 4:14 PM Hi all,
Just a helpful tidbit that may help some of you. Our SQL Server that hosts our WebComm database was not very responsive (it was doing a lot of IO paging), which was resulting in poor performance on WebComm and our other corporate databases. We could tell over a the course of a year that performance was decreasing, but we obviously didn't perform enough research and put it down to network storage latency (which we had done on the cheap and were expecting to upgrade anyway).
Eventually the performance impact was starting to become really quite noticable so we finally performed some more intensive investigation. After some further digging in SQL Server, we found that there was some fairly continuous activity on the WebComm database (to put it very mildly). We always thought the problem was SQL Server being unable to retrieve data quickly enough, so we turned our attention to the IIS server instead.
Each daily IIS log would range anywhere from 35M to 100M in size (usually about 50M). After browsing the logs, we could clearly see that there were repeated and continuous requests to non-existent pages on the site, which fortunately for us were all attempting to "GET /Page/SiteLink.asp link=SOME_DODGY_URL".
Now here is the thing...that particular page doesn't exist in the current incarnation of WebComm, so WebComm would dutifully generating a nice 404 error page with all the site header/navigation/footer content. This of course meant that it would keep querying SQL Server to retrieve the necessary information to generate this page. However with several such requests being received each and every second, SQL Server would simply become bogged down by these continuous requests.
To get around this, we installed the IIS URL Rewrite module (Microsoft equivalent to the Apache Mod Rewrite). For the WebComm site, we added URL rewrite rules to Abort these requests. As a result, IIS now gets the request and logs it, but does nothing else (so it doesn't hassle SQL Server to make a nice 404 page).
Since implementing this change, the invalid requests have continued however they have reduced down in intensity such that the daily IIS logs are now about 10M in size. SQL Server still has some sluggish periods, but nothing like the continuous delays previously.
I now await for the robots to try some other tactic. Hope this tidbit helps some of you out there struggling with poor performance. |
|