Problem ScenarioWe can reduce traffic by using URL rewrite at the same time making use of Request filtering will ensure unnecessary requests are stopped at IIS web server level itself thus not hitting any database server for further processing. To a greater extent increasingly different combination of useless querystring for given url also a cause of sql server performance degradation.
Parse IIS log using Logparser tool
Using the query below I could see the most frequently used querystring values and it was obvious the site experiencing a SQL Injection attack.
logparser.exe -i:iisw3c “select top 20 count(*),cs-uri-query from ex140702.log
group by cs-uri-query order by count(*) desc” -rtp:-1 >file.txt
By viewing the output we can add rules for request that is coming in for webserver in IIS request filtering.
We can keyword for querystring which are identified in the logs by doing this in a way we can restrict unnecessary request and provide access denied to such request. Status code 404.
SELECT STRCAT(TO_STRING(sc-status), STRCAT(‘.’, TO_STRING(sc-substatus))) AS Status, COUNT(*)
AS Total FROM w3svc.log to TopStatusCodes.txt GROUP BY Status ORDER BY Total DESC
Some important areas to be looked for performance stats
For more details
- SQL Profiler
- Database Tuning Advisor