Click here to monitor SSC

What makes SQL to hit 100% CPU?

Last post 03-30-2012, 9:21 AM by Arles. 1 replies.
Sort Posts: Previous Next
  •  03-01-2012, 6:25 AM Post number 106252

    What makes SQL to hit 100% CPU?

    Hi Guys,
    I have a report (Stored procedure) which hit 50% CPU utilization.

    So if i have 2 users using accessing the same report then it hits 100% CPU utilization. It results in time out errors in other pages.

    The report was taking 2 minutes to execute. Then i fine tuned the Stored procedure removing unneccessary Functions been used (Distinct and UPPER) and Limiting column size in temp table (hah table) with some 500 characters (nvarchar(500))exactly what i may require, earlier it was nvarchar(max)

    This helps to save 1.5 minutes of response time. Now it takes 30 minutes to execute the SP.

    Do you have recommendations what else i need to check to improve preformance. and use less CPU.

    Thanks in advance.
  •  03-30-2012, 9:21 AM Post number 107098 in reply to post number 106252

    • Arles is not online. Last active: 05-17-2013, 1:50 PM Arles
    • Top 25 Contributor
    • Joined on 10-20-2008
    • Nashville, TN
    • Power MAC 6100

    Re: What makes SQL to hit 100% CPU?

    A lot of performance issues may be tied back to the lack of index usage.  Try to see if you have an index for every field that's tied to a parameter in the report, keys used for joining your tables and any field that's used in sorting or grouping.

    See if this helps.


View as RSS news feed in XML