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 10 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.

    Arles

View as RSS news feed in XML