Resolving SQL Server CPU high issues through IIS Request filtering

Original http://www.peterviola.com/solving-sql-server-high-cpu-with-iis-request-filtering/

Top Queries by Total CPU time

When the CPU is very high, it's possible that your reflex is to restart the service or recycle the app Pools.sql Server 2008 has built-in great reports to help us track CPU usage. I use the Top Queries by total CPU Time report. Select the appropriate report as a right-click service name.

The Top Queries by Total CPU Time report will take a few minutes to generate. The report enables you to get the most CPU for the queries that are 10 of the data consumed. Through the report we found that 4 different query for a database on this server consumes most of the CPU.

SQL Profiler and Database Tuning Advisor

Now I know which database is causing a high CPU problem, and start SQL Profiler to collect the data for a few minutes. You can see that the high reads is from "Internet information Services" This appliation was sent out.

Before concentrating on the site, I want to see if the Tuning Advisor through the Database Engine can be more efficient DTA analyzes some of the behavior of data by providing SQL scripts and improves an optimized scheme (including using indexes, partitioning ... Usually we can improve the performance by 5-10 by DTA . In this case we can see an improvement of 97%!

Preventing SQL injection with IIS Request Filtering

It is possible to reduce some CPUs by executing the DTA-enhanced optimization script. However, I know that there may be some suspicious visits to our website, so I use Log Parser to get access to some websites. By using the following query, it is found that the surveyed access is using QueryString for SQL injection.

LOGPARSER.EXE-I:IISW3C "SELECT Top count (*), cs-uri-query from Ex140702.log

Group BY Cs-uri-query ORDER by COUNT (*) desc "-rtp:-1 >file.txt

Usually we tend to shield the attacking IP. Unfortunately, some sophisticated attacks use a lot of different IPs to attack you. The best solution is to block these malicious requests through the request Filtering filter.

We blocked the SQL injection attack through IIS Request Filtering . Using the following Log Parser query we can see all the requested HTTP status codes.

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

When a querystring is rejected, request Filtering uses HTTP substatus 404.18. Through the log Parser report below, you can see that 50,039 requests are blocked.

Resolving SQL Server CPU high issues through IIS Request filtering