SQL Server Performance

Single Table - large select activity

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by bfarr23, May 16, 2008.

  1. bfarr23 New Member

    what is the best way to handle a table that gets a ton of select activity from one stored procedure? (indexes, io, etc)
  2. Luis Martin Moderator

    If the table is in normal form, then you have to play with indexes.
  3. satya Moderator

  4. FrankKalis Moderator

    Additionally you might also want to experiement with INCLUDEd columns to create covering indices if you have many rows in that table.
  5. bfarr23 New Member

    what about IO placement for this table?
  6. MichaelB Member

    If by IO placement you mean a seperate file group on another drive, that could help..maybe depending on your drive configuration. what is your proc doing? any way it could be made into a view to do the same thing?
  7. rohit2900 Member

    Hi,
    We have a single table with around 4 million records and it has been accessed by both web application (for select) and back end application (for insert & select) and no a single column in where clause are same in froth end and back end and I'm trying to implement a solution and need advise if its correct or not.
    I'm going to keep the clustered index on column where backend is using to insert on the basis of that column and apart from this all the other column which are been used by backend application in where claues Ihave non clustered indexes and I'll create a view on this table and include the columns required by front end application and will create the clustered and non clustered indexes on this view as per the queries used by web application.
    Please advise.....as its an urgent issue cos as of now I'm having unique clustered index on a varchar(100) column and I do have some non clustered indexes on other columns (changed from clustered to non clustered) which are been used by web application....now web application is dead slow (earlier there we lots or time outs in backend application logs that why we planned that change)
    We have SQL Server 2005 enterprise on win 2003 server
    Rohit



  8. Kewin New Member

    What you describe is the classic dilemma when designing an index strategy.
    First off, you must understand that there is no 'fixed formula' for what is 'correct' or 'incorrect'.
    It's a balancing act to get index-support 'good enough' so that both front- and back-end are happy.
    The tools you have at your disposal when designing the optimal solution for yourself, is knowledge, first and foremost.
    You need intimate knowledge of your front and backends, how they work, what type of queries they do etc.
    You also need to fully understand how indexes in SQL Server work.
    There is no free lunch.
    Whenever you gain something, rest assured that you pay a price somewhere else.
    This is the 'balancing'.
    You also must decide what to prioritize, where it's acceptable to cut back in order to gain something else.
    Indexing isn't a silver bullet either. Not all 'problems' can or should be solved by adding or modifying indexes.
    The SQL itself that is used is more often than not the 'true' source of performance problems.
    Badly written queries may be beyond any index-help.
    The databasemodel itself also plays a role. There may be 'bad' queries because the model itself is 'bad' from the start.
    A 'bad' model may also force indexes implemented to be 'bad' as well (ie non-optimal datatypes, unnecessary wide columns etc)
    They all connect together to make your system either fly or crawl.
    Again, I want to emphasize the importance of understandning how indexes work.
    The difference between clustered and nonclustered indexes, what impact there is for different statements
    (select, update, delete, insert - they all 'appreciate' indexes differently) and so on.
    The best weapon to arm oneself with, is knowledge and understanding of what makes those things 'tick'.
    Then you can make informed desicions for your system and decide what to do or change.
    /Kenneth

Share This Page