slow stored procedure


  • i have so many table to join,

    i m using 2 cte to make it work.

    butmy sp is running 2 slow,

    any other solution

    Monday, February 18, 2013 6:40 PM

All replies

  • It would help if you could provide some more information...but in general the first thing I would look at is the actual execution plan (CTRL-M). See if that indicates any missing indexes or table scans.

    After switching on the execution plan, run your query again and you will see an extra tab in the bottom pane showing the execution plan.

    Monday, February 18, 2013 6:45 PM
  • You need to find out what is slowing the query down.  Check out to see what it is in your query that is slow.  Do you need to indexes?

    If you posted the actual SQL something about your query may stand out.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog:

    Monday, February 18, 2013 6:56 PM
  • well, i tried my estimated execution plan,

    the primary key is taking 86%,clustered index scan

    it recommend some non clustered index in some of the column on my  table.

    what should i do

    Monday, February 18, 2013 9:04 PM
  • I would consider adding that non-clustered index.  But don't implement it yet.  Are you the DBA or is there some DBA that you can discuss this with?

    Do you know the cost and benefit of adding that non-clustered index?  Cost:  that index is going to take space on the disk.  If you are indexing a column that takes 4 bytes (say an integer) and you have a clustered index that is an integer (4 bytes) and there are one million rows in your table, then your index will take at least 8 million bytes of space on your disk.  Second cost:  every time you insert a record into that table or you change the column that you are indexing on, SQL will have to write to the index as well.  You need to be aware that adding an index might slow your updates on this table.  If there is a large amount of change to the data, especially the column being indexed, this may be a factor.

    Benefit:  if SQL uses the index you may find that your queries run faster.

    If you think that the benefit outweighs the cost, and you have adequate space for the index (and the transaction log entries creating when you add the index), I would implement the non-clustered index.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog:

    Monday, February 18, 2013 9:44 PM
  • Without knowing the full context of your system in it hard to be a 100% certain but if it is recommending further indexes they will probably help your immediate problem a bit but it does not sound like it will be a dramatic improvement. The potential downside is that they could slow down and INSERTS and UPDATES. I would experiment to see if the recommended indexes help and do not cause any side affects elsewhere on your system. Naturally this is not something you would do on a production environment.

    Further information would assist in identifying the problem.

    Monday, February 18, 2013 9:49 PM
  • You MUST add some kind of index to change index scan to index seek.

    If you do not know what kind of index to add then try to use Tuning Advisor

    Monday, February 18, 2013 9:52 PM
  • well what i can see is ,

    its using clustered index scan and its talking max perc of all,

    it is clustered primary key column

    what is  the option

    Tuesday, February 19, 2013 8:30 PM