slow query performance

  • vsamantha35

    SSChampion

    Points: 11176

    Hi All,

    I have a SELECT query which is taking more 8 mins. Can you please help me in making the query run faster (if possible) . There is no blocking . Seeing PAGEIOLATCH_SH waittype for most time. The table is a clustered index table. There is no non-clustered idx on mule_batch_id column.

    Query:

    use <dbname>

    go

    set statistics io on

    go

    set statistics time on

    go

    select TOP 1 * from <dbname>.dbo.<tname> where mule_batch_id='EAIf200dg373B1'

    go

    -- output

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 5144 ms.

    (1 row(s) affected)

    Table 'tname'. Scan count 1, logical reads 2043149, physical reads 1, read-ahead reads 1719247, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 13672 ms, elapsed time = 356953 ms.

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    More details

    =============

    Table size : 15GB

    Database size : 3.5TB

    Memory : 32 GB

    max server memory : 27 GB

    wait_info : PAGEIOLATCH_SH

    Note: Its a test server

    Please attached Actual Execution plan and table structure

    Attachments:
    You must be logged in to view attached files.
  • DesNorton

    SSC-Insane

    Points: 23329

    According to your table structure, there is no index on [MULE_BATCH_ID].  This means that SQL has to do a table scan to find the record.

    You are doing a select *, so a covering index is simply going to duplicate the entire table.

    At a minimum, you can try an index on [MULE_BATCH_ID]

    CREATE NONCLUSTERED INDEX IX_tname_MuleBatchID
    ON dbo.tname (MULE_BATCH_ID)
    INCLUDE (ID);

    [MULE_BATCH_ID] is also defined as NULLable, so it is not an option for a CLUSTERED index.

  • Sergiy

    SSC Guru

    Points: 109884

    vsamantha35 wrote:

    There is no non-clustered idx on mule_batch_id column.

    It seems you already know what to do.

    At least you identified the cause of the problem correctly.

  • vsamantha35

    SSChampion

    Points: 11176

    DesNorton wrote:

    According to your table structure, there is no index on [MULE_BATCH_ID].  This means that SQL has to do a table scan to find the record.

    You are doing a select *, so a covering index is simply going to duplicate the entire table.

    At a minimum, you can try an index on [MULE_BATCH_ID]

    CREATE NONCLUSTERED INDEX IX_tname_MuleBatchID
    ON dbo.tname (MULE_BATCH_ID)
    INCLUDE (ID);

    [MULE_BATCH_ID] is also defined as NULLable, so it is not an option for a CLUSTERED index.

     

    Hi ,

    I don't think we need to INCLUDE "ID" column because whenever we create a non-clustered idx , the clustering key (i.e. ID in this case) will be part of the non-clustered index.

    Thanks,

    Sam

     

  • Johan Bijnens

    SSC Guru

    Points: 134309

    oh by the way, if you know [MULE_BATCH_ID] should be unique in that table, create a unique constraint or index.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • ScottPletcher

    SSC Guru

    Points: 98626

    vsamantha35 wrote:

    DesNorton wrote:

    According to your table structure, there is no index on [MULE_BATCH_ID].  This means that SQL has to do a table scan to find the record.

    You are doing a select *, so a covering index is simply going to duplicate the entire table.

    At a minimum, you can try an index on [MULE_BATCH_ID]

    CREATE NONCLUSTERED INDEX IX_tname_MuleBatchID
    ON dbo.tname (MULE_BATCH_ID)
    INCLUDE (ID);

    [MULE_BATCH_ID] is also defined as NULLable, so it is not an option for a CLUSTERED index.

    Hi ,

    I don't think we need to INCLUDE "ID" column because whenever we create a non-clustered idx , the clustering key (i.e. ID in this case) will be part of the non-clustered index.

    Thanks,

    Sam

    You should still INCLUDE it, not just for documentary purposes, but in case the clustered index key were to change.

    Btw, that table looks like it had no logical design done at all, resulting in an unnormalized mishmash.  You really should consider normalizing that data structure.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • vsamantha35

    SSChampion

    Points: 11176

    ScottPletcher wrote:

    vsamantha35 wrote:

    DesNorton wrote:

    According to your table structure, there is no index on [MULE_BATCH_ID].  This means that SQL has to do a table scan to find the record.

    You are doing a select *, so a covering index is simply going to duplicate the entire table.

    At a minimum, you can try an index on [MULE_BATCH_ID]

    CREATE NONCLUSTERED INDEX IX_tname_MuleBatchID
    ON dbo.tname (MULE_BATCH_ID)
    INCLUDE (ID);

    [MULE_BATCH_ID] is also defined as NULLable, so it is not an option for a CLUSTERED index.

    Hi ,

    I don't think we need to INCLUDE "ID" column because whenever we create a non-clustered idx , the clustering key (i.e. ID in this case) will be part of the non-clustered index.

    Thanks,

    Sam

    You should still INCLUDE it, not just for documentary purposes, but in case the clustered index key were to change.

    Btw, that table looks like it had no logical design done at all, resulting in an unnormalized mishmash.  You really should consider normalizing that data structure.

     

    Scott,

    We couldn't do much about the database design as it is a vendor specific database.

    We had a meeting with the app team and they had to archive some years of data. They will do that and let us know.

    Later , we will try to add this additional non-clustered idx on mule_batch_id.

    Thanks everyone for pitching in and sharing your thoughts.

    -Sam

     

  • ScottPletcher

    SSC Guru

    Points: 98626

    Quote: "We couldn't do much about the database design as it is a vendor specific database."

    Understood.  Lol, the only thing potentially worse than a "programmer (non)'designed' table" is a "vendor (non)'designed' table".

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • carsten.saastamoinen

    SSC Veteran

    Points: 242

    It's allowed to have a clustered-key as nullable.

    CREATE TABLE dbo.t
    (
    ID1 INT NOT NULL
    CONSTRAINT PK_t PRIMARY KEY NONCLUSTERED,
    ID2 INT NULL
    INDEX cl_t_ID2 CLUSTERED,
    Txt VARCHAR(20) NOT NULL
    )
    GO
    INSERT INTO dbo.t (ID1, ID2, Txt) VALUES
    (1, 11, 'aaa'),
    (2, 12, 'bbb'),
    (3, 13, 'ccc');

    INSERT INTO dbo.t (ID1, ID2, Txt) VALUES
    (4, NULL, 'ddd'),
    (5, NULL, 'eee'),
    (6, NULL, 'fff');
    GO
    SELECT *
    FROM dbo.t;
  • Johan Bijnens

    SSC Guru

    Points: 134309

    Just keep in min , SQLServer "uniquifies" every clustered index key !

    Clustered Index Uniquifier Existence and Size

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] but most of the time this is me

  • carsten.saastamoinen

    SSC Veteran

    Points: 242

    An "uniquifies" is only an INT (4 byte).  It can be a better solution for a table with a lot of NVARCHAR-columns instead of a nonclustered index, if many rows are returned and not only TOP (1). If other nonclustered index are created on this NVARCHAR columns the "uniquifies" don't matter.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply