SQL Server Performance

SQL Server 2008 Indexing

Discussion in 'ALL SQL SERVER QUESTIONS' started by yugant, Dec 18, 2011.

  1. yugant New Member

    Hi , I am running stocks Database on SQL 2008 .. There are 2 main Tables in this Database called Stocks_Master and Stocks_Intraday .

    StocksMaster has the following coloums
    STOCKID, NAME,OPEN , HIGH,LOW ,CLOSE and TIMESTAMP.
    Primary Coloumn in the above Table is StockID . and there is no other Indexing on this Table . This table gets around 100 updates per second when the stocks exchange opens for 8 hours. there are 6000 records in the Table

    and STOCKS_INTRADAY has all the above records and all the rows gets appended to this via the insert / Update trigger on Stocks Master Table . it hold around 10 million records at the end of day. Every morning this table is truncated to hold the fresh data for the current day. This table is just a Heap Table with no Primary key also.

    Issue i am facing these days is database Query results slow / or Deadlocks after couple of hours of stock exchange open. The solution which i propose is to have an non clustered index on STOCKID coloumn in StocksMaster Table .

    I want to know would the response time improve or further deteriorate if i make an index on the coloumn which updates around 50/ 100 times a second ? .. or should i stop inserting rows in intraday table from update / Insert trigger in StocksMaster.

    regards
  2. FrankKalis Moderator

    If STOCKID in StocksMaster is already the PRIMARY KEY, is it also the clustered index? By default it would be so, but I am just asking to be sure. If it is the clustered index, there is no point in adding a nonclustered index on the same column. It would just contain redundant data and waste storage space and possibly even contribute to further poor performance. What type of queries do you run against the database? What exactly is deadlocking?

    Apart from this, I'm not entirely sure about the purpose of this database and what it is trying to model, but I'm tempted to say, something is strange there. It would seem to be more accurate to have a Stocks table, a Quotes table, and maybe an aggregation table that contains information like Open/High/Open/Close and would be maintained by an asynchronous process, but not from within a trigger. But to definitely suggest something useful, you would have to provide more information.
  3. yugant New Member

    HI Kalis , thanks for a kind reply. Yes StockID column in Stocks Master Table is clustered index . and we have no other indexing on this Table .There are around the everyday 10 million update queries run on this table , we also Queries this table to find out the Top Stocks in terms of Highest Volume / Biggest Gainers or Losers of the Day. ALL the updates on StocksMaster gets saved in the Stocks Intraday Table through update / Insert Trigger . This helps in plotting charts / Line Graphs throughout the day .

    what exactly do you mean by asynchronous Process .. do you mean we should try and update intraday Table from the application and not from the Triggers?Intraday Table has only insert statements running on it ..

    regards
    Yugant
  4. satya Moderator

    Did you check the executioon plan for that query which is extending the deadlocks in this case?

Share This Page