MS SQL Server Performance and tuning

Posted on 2011-04-29
Last Modified: 2012-06-21
I have a stored procedure that I need t have tuned...  It takes about 3 seconds to return the Rows...  According to the "BOSS" at my compaany... that's too slow...  There are other SPs that make up the page that this is attached to.  All in All the whole page takes about 11 seconds.  This SP is the most complex.

The proc is below....

      SELECT, p.project_name, p.project_Manager, p.project_Manager_email, p.project_start_date, p.project_end_date,                   
            COUNT(DISTINCT as TTLstaff,
            DATEDIFF(day,getDate(), p.project_end_date) AS DaysRemain,
            COUNT(DISTINCT d.deliverable_id) as lateDelivs,
            COUNT(DISTINCT t.task_id) as TTLTsk,                               
            COUNT(DISTINCT s.issue_id) as TTLIss,
            COUNT(DISTINCT rx.risk_id) as TTLRsk           
      FROM projects p, project_intermediate pi                      
            LEFT OUTER JOIN resources r ON ( = AND r.assigned = 1)                               
            LEFT OUTER JOIN deliverables d ON ( = AND
                  (( getDate() > d.due_date
                        and d.date_submitted IS NULL )
                                    ( d.date_submitted > d.due_date
                                          and d.date_submitted IS NOT NULL )))          
            LEFT OUTER JOIN tasks t ON ( = AND t.spid IS NULL                              
            LEFT OUTER JOIN issues s ON ( = AND s.spid IS NULL                               
            LEFT OUTER JOIN risks rx ON ( = AND rx.spid IS NULL           
      WHERE pi.user_id = 'XXX' AND =
      AND p.inactive = 0 AND pi.spid IS NULL  
      GROUP BY, p.project_name, p.project_Manager, p.project_end_date, p.project_Manager_email, p.project_start_date, p.project_end_date  
Question by:pborregg
    LVL 9

    Expert Comment

    how many tables you are joining here, and how many fields each table have and how many records?
    LVL 9

    Expert Comment

    create views for the tables u are using in this query with only the needed fields for this query. Then use those views in this query instead of querying the tables directly.
    LVL 9

    Expert Comment

    This is a really wide subject and there can be several optimizations can be done to this query. But by just looking at your query it is hard to say what to do without knowing your schema, data and your business requirment.

    But as a startup you can look at your query plan and see which part of the query is more costly. Then try to create indexes on columns used in WHERE clause and JOIN statements. For this you can use the Database Engine Tuning Advisor to get index recommendations. (

    LVL 21

    Expert Comment

    by:Alpesh Patel
    That does not make any difference.

    Author Comment


    With respect to the number of tables...There are 7

    The number of fields... There are 12

    Number of rows...So far, less than 20.

    When I break up this SP into smaller chunks, the time taken to get back the data is less than a second.

    Is there a way to daisy chain to the smaller selects, one after another, to get the same result?

    Yes, making VIEWs is something I've considered.  I'm currently working on that...

    Thanks all for your input so far...

    I ran an execution plan and boy oh boy... there is a cost doing those joins...the CPU is being tasked, in milliseconds, but some are only .000001 and others are .03434524 for example.

    LVL 9

    Accepted Solution

    12 fields not a problem I believe. But if its large no of fields table like 50/60+, then at that time creating this kind of view surely help (Also the volume of data). I faced it and solved like this.

    Go ahead and check for views in yours whether it helps or not.

    Author Comment

    Are the number of Left Outer Joins causing a problem?

    I've got indexes on all the columns I'm querying.

    I've got to solve this problem and fast.  My boss is furious that this query takes so long to process.

    LVL 1

    Expert Comment

    if i can get a the structure of your tables without any records , i can construct this query in an entire different manner.  

    Author Closing Comment


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now