Sql Server query performance

Question

I have a stored procedure on a busy database which constantly come out top in the list of expensive queries (by some way). The query is very simple, it takes a single parameter (@ID, int) which is the primary key of the table, and selects the record that matches that ID. The primary key is an identity field with a clustered index, so I am stumped as to how to optimise this any further?

The query is as follows

CREATE PROCEDURE [dbo].[P_Call_Get]

    @ID int = null

AS

    select ID,
        AppID,
        AgentID,
        AgentLogin,
        Ext,
        VDN,
        VDNName,
        Skill,
        SkillName,
        CallFrom,
        TelNoFrom,
        ParentCallID,
        CallStart,
        ACWStart,
        CallEnd,
        Outcome,
        StageID,
        TxTo,
        TxSuccess,
        ServiceID,
        DiallerID,
        CRC,
        TSCallID,
        CallDirection,
        [Manual],
        CallBackAgent,
        CallBackDateTime,
        Notes
    from P_Call
    where (ID = @ID or @ID is null)

Not sure the best way to post the execution plan - all it shows is that 100% of the operation is taken up by the clustered index scan

when you say it was the most expensive, how many reads was it doing? How often is it called? — Mitch Wheat
– Mitch Wheat, Commented May 7, 2010 at 11:28
could it be that it appears to be the most expensive due to the high frequency of use? If it's doing 10 logical I/O but running 10,000,000 times a day, it might look expensive, but really it's probably fine. — ninesided
– ninesided, Commented May 7, 2010 at 11:39
It is called a lot! it will be called at least once per agent per call in a call centre with 100 agents taking 3 - 400 calls each per day — Macros
– Macros, Commented May 7, 2010 at 11:40

Martin Smith · Accepted Answer · 2010-05-07 11:34:52Z

8

I think that by using where (ID = @ID or @ID is null) you are getting a sub optimal plan. Divide this into 2 separate queries so that in the case where @Id is not null it will just look it up directly and you will get a seek rather than a scan appear in the plan. You could maybe create a View with the columns you require to avoid the repetition (i.e. the Query without any where clause)

select ID,
    AppID,
    AgentID,
    AgentLogin,
    Ext,
    VDN,
    VDNName,
    Skill,
    SkillName,
    CallFrom,
    TelNoFrom,
    ParentCallID,
    CallStart,
    ACWStart,
    CallEnd,
    Outcome,
    StageID,
    TxTo,
    TxSuccess,
    ServiceID,
    DiallerID,
    CRC,
    TSCallID,
    CallDirection,
    [Manual],
    CallBackAgent,
    CallBackDateTime,
    Notes
from P_Call

edited May 7, 2010 at 11:34

answered May 7, 2010 at 11:29

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Macros Over a year ago

Splitting into 2 queries did exactly that - I now get a seek rather than a scan in my execution plan. Which scares me a little as I have used this approach in a number of SPs which search on a number of optional criteria. Was going to ask about a better approach but I think optimising these is a separate question

Martin Smith Over a year ago

I'm not sure if there is any way around the splitting into 2 queries approach. It can be a bit of a pain to keep complex queries in synch though especially if you are using this approach for multiple parameters in the query.

Justin Over a year ago

@Macros There is no way of avoiding the split - SQL server only normally maintains a single execution plan per statement.

HTTP 410 · Accepted Answer · 2010-05-07 11:42:29Z

1

Try cleaning out procedure cache and memory buffers:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Doing so before testing your procedure's performance will prevent the use of cached execution plans and previous results cache.

answered May 7, 2010 at 11:42

HTTP 410

17.7k14 gold badges80 silver badges130 bronze badges

Comments

sanket · Accepted Answer · 2010-05-07 11:27:21Z

0

can you use table partion. it may fixed the issue.

answered May 7, 2010 at 11:27

sanket

11 bronze badge

Comments

Mike Ritacco · Accepted Answer · 2010-05-08 00:18:26Z

0

How many rows are in the table? You do realize that a "clustered index scan" = full table scan.

answered May 8, 2010 at 0:18

Mike Ritacco

13.5k2 gold badges19 silver badges5 bronze badges

2 Comments

Macros Over a year ago

I didn't...but do now! have spent the last 2 days optimising SPs based on this

Mike Ritacco Over a year ago

Good deal. You probably already figured it out why but I should have explained why the optimizer was selecting a sub optimal plan. The reason the index cannot be used is the (or @ID is null) portion of the WHERE clause. NULL values are not indexed.

Collectives™ on Stack Overflow

Sql Server query performance

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related