IIS Web server log parsing - URLs which takes highest percentage of time and payload

Identifying where to start fixing performance issues to increase throughput is a tricky question, if we are new to the web application. One way to find out what to fix is to find the costly URL operations in the application. If the IIS logs are available we can check what are the URLs taking highest % time in a given duration they are costly in the system 

Sometimes each URL may be taking 1 second but there are millions of instances and some other times there are less instances but each instance might be taking 10s of seconds. So finding the % of time a particular URL or pattern taking from the total request time if a good start than taking a long running but less in number URL request.

This helps if we need to increase the throughput of existing system or want to reduce the hardware maintaining the same throughput.

Below are the queries to find out the candidate URL. The fix has to be done by analyzing the URL and what and how it does internally.

Percentage of time spent time-taken

/*This script finds out what are the requests taking highest % of time in the entire duration of IIS logs
Better place the URLs to remove ids, GUIDs to find unique URLs*/

SELECT TOP 500  cs-uri-stem,
  COUNT(cs-uri-stem) AS TotalRequests,
  SUM(time-taken) AS SumTime,
  MUL(PROPSUM(time-taken), 100.0) AS PercentTime,
  MAX(time-taken) AS MaxTime,
  MIN(time-taken) AS MinTime,
  AVG(time-taken) AS AvgTime
FROM '[LOGFILEPATH]'
// WHERE condition for proper filtering
GROUP BY cs-uri-stem
ORDER BY PercentTime DESC

Percentage of % payload size 

SELECT TOP 500  cs-uri-stem,

  COUNT(cs-uri-stem) AS TotalRequests,

  SUM(sc-bytes) AS SumSCBytes,

  MUL(PROPSUM(sc-bytes), 100.0) AS PercentSize,

  MAX(sc-bytes) AS MaxSCBytes,

  MIN(sc-bytes) AS MinSCBytes,

  AVG(sc-bytes) AS AvgSCBytes

FROM '[LOGFILEPATH]'

// WHERE condition for proper filtering

GROUP BY cs-uri-stem

ORDER BY PercentSize DESC