Indexing a Large Table

  • Rofty

    SSCrazy

    Points: 2585

    I have the following table:

    The table stores daily stock market prices for over 8000 instruments(stocks) per day

    DateKey INT PK

    InstrumentKey INT PK

    Op FLOAT

    Cl FLOAT

    Hi FLOAT

    Lo FLOAT

    WAVG FLOAT

    Vol FLOAT

    Status CHAR(1)

    Cap FLOAT

    I use a composite key (DateKey,Instrumentkey) because one instrument can only have one record per day. it currently contains over 13,000,000 records

    The problem I came across is that selecting data from this table takes over 5min, considering no one else is using the same database or server. Last week running a normal SELECT query with a basic WHERE clause, the SQL Server basically slowed down to a snails pace. Resulting in some users unable to connect to SQL Server.

    The index on this table was created with the composite key and is set to clustered.

    What can i possibly do so speed up the table and improve performance.

    [font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
    😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
    -----------------------------------------------------------------------------------------------------------------------[/font]

  • Chandra Sekhara Vyas Dhara

    SSChampion

    Points: 10207

    Check fragmentaion on the table. It should improve the speed after attaining good fragmention.

  • Rofty

    SSCrazy

    Points: 2585

    The Total Fragmentation is currently 15.2%.

    I have to add that last week over 10,000,000 was added with SSIS package from 1500 csv files.

    [font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
    😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
    -----------------------------------------------------------------------------------------------------------------------[/font]

  • arr.nagaraj

    SSCertifiable

    Points: 6518

    Yes..The table may be heavily fragmented. Perhaps you can Rebuild the clustered index offline, if you have nobody else using it.

    There are quite a few threads in this forum which can provide info on rebuilding indexes..search and go thro them..

  • arr.nagaraj

    SSCertifiable

    Points: 6518

    /* SELECT query with a basic WHERE clause, the SQL Server basically slowed down to a snails pace. Resulting in some users unable to connect to SQL Server. */

    Are u sure there was no other activity in SQL Server. If there is another heavy process eating up all the memory and CPU, then these issues can raise.

    Also check the basic perfmon counters for CPU,Memory used,Disk quelength,

    Lock wait,user Connections etc..

    Also post ur system configuration.

  • Rofty

    SSCrazy

    Points: 2585

    SERVER SPECS:

    HP ProLiant ML350

    2x Intel Xeon 2.50GHZ processors

    4GIG RAM

    I have reorganized, rebuild the index and even after that it still shows no change.

    [font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
    😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
    -----------------------------------------------------------------------------------------------------------------------[/font]

  • arr.nagaraj

    SSCertifiable

    Points: 6518

    hmm..I kind of guessed this when you said users cudnt connect..

    Please check the following..(some of the stuff u wud have already checked..but anyways..)

    > on System task manager check how much of CPU and memory SQL Server is consuming..

    Is there any there process consuming CPU/memory... If nt wats overall usage and SQL'susage..

    > Check the number of connections using sysprocesses. Also check how many are on runnable state.

    how many are sleeping. USing last batch column check how long they have been sleeping.

    > Check whether are there any queries that are blocking either using activity monitor on SSMS or using

    query provided here :-http://strictlysql.blogspot.com/2009/07/check-currently-running-statements.html

    > using perfmon check the values of the following counters.

    Physical disk . avg disk queue length

    Processor queue length

    SQL Server Locks -> Average wait time

    Buffer manager obj -> Buffer cache hit ratio

    SQL Server memory mananger -> Total Server Memory

    Target Server Memory

    I believe u have set max number of connections ServerProperties ->connections ( default setting of 0 )..

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Please post the query, table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SQLBOT

    SSCrazy Eights

    Points: 8014

    first off, do as Gail says. It will help greatly in the event that your query isn't covered by an index. We'll be able to help then.

    Also, 13 mil isn't that many records for an appropriate disk volume (SAN or other striped disk)

    As far as users being locked out, it's probably a combination of the isolation level you're choosing and the lack of an appropriate index that covers the columns in your query.

    This spiffy article can explain Isolation levels and you can choose one that's best.

    http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

    Nothing performs better than read uncommitted, but understand what you're getting with read uncommitted.

    Good luck with that part, and send your query and execution plans as Gail suggests.

  • Shriji

    SSCommitted

    Points: 1881

    roelofsleroux (2/1/2010)


    I have the following table:

    The table stores daily stock market prices for over 8000 instruments(stocks) per day

    DateKey INT PK

    InstrumentKey INT PK

    Op FLOAT

    Cl FLOAT

    Hi FLOAT

    Lo FLOAT

    WAVG FLOAT

    Vol FLOAT

    Status CHAR(1)

    Cap FLOAT

    I use a composite key (DateKey,Instrumentkey) because one instrument can only have one record per day. it currently contains over 13,000,000 records

    The problem I came across is that selecting data from this table takes over 5min, considering no one else is using the same database or server. Last week running a normal SELECT query with a basic WHERE clause, the SQL Server basically slowed down to a snails pace. Resulting in some users unable to connect to SQL Server.

    The index on this table was created with the composite key and is set to clustered.

    What can i possibly do so speed up the table and improve performance.

    Can you confirm that where clause does NOT have any other columns but DateKey and InstrumentKey ?

  • S t e f

    SSC Eights!

    Points: 867

    You may also need to think about Statistics, i.e. with such a large data load you should set a job to update statistics, also check to see if auto update statistics is enabled, this may have kicked off the extra SQL load you have seen.

    This article describes more:

    http://support.microsoft.com/kb/195565

  • Jeff Moden

    SSC Guru

    Points: 997353

    GilaMonster (2/1/2010)


    Please post the query, table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    This will probably be something fairly easy to fix but unless you can help us help you by doing what Gail suggests above, all we can do is make suggestions.

    As SQLBot suggests, 13 million rows really isn't that big and most queries should be nearly instantaneous... we just can't help unless we can see.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • arr.nagaraj

    SSCertifiable

    Points: 6518

    @S t e f

    He has Rebuild the index which implies stats are updated.

    Also, the Original poster claims that people cant login or access the server which implies the problem is not just a missing index on 13 million row table.

  • Jeff Moden

    SSC Guru

    Points: 997353

    I agree with that. That's why we want to see that slow query and the execution plan for it. We're just shooting in the dark until that happens.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • krayknot

    SSCertifiable

    Points: 7998

    check the query execution plan and based on that you will come to know the reason probably.

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

Viewing 15 posts - 1 through 15 (of 35 total)

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