locked
Noobie Index Question RRS feed

  • Question

  • Hi There,

    I've created a simple database (about 30 tables and a couple of views) and every table has an identity integer column which is used as a primary key.

    I have no created any indexes for this database whatsoever but I'm beginning to wonder if this will be taking a toll on performance. My questions are:

    1. For a database like this is it worth creating any indexes? I've always believed that you get a freebie index from primary keys anyway but not sure if this is true or if these are enough on their own?
    2. If it is then how should I go about designing these? The database is for a web-based CRUD system and the queries are mostly simple selects, inserts, and updates using the primary key to isolate a record. However there is searching/filtering across some of the main tables
    3. Any other advice or tips (or links to articles) to help optimize the database would be much appreciated

    Cheers,

    Olly


    Olly

    Tuesday, September 9, 2014 2:07 PM

Answers

  • Hi Olly,

    Non-clustered indexes will help reducing I/O usage for queries which seek data based on a predicate, therefore speeding up the process. A query predicate is any condition in which data should be fetched, and mostly includes join predicates and filters - predicates in the where clause.

    If you do not have a lot of in-depth knowledge about database tunning, your best bet is to create indexes for columns often used in the predicates I mentioned, if they are not already in a clustered index.

    Is your application being used in a production environment already? If so, and the lack of indexes is impacting performance, you can query the following DMV:

     select * from sys.dm_db_missing_index_details where database_id = db_id('your_database')
    This will return a row for everytime a query was run and the optimizer found that an index would have increased query performance. Each row contains information about the predicate on which the index should be created, and columns to be included in the index to avoid lookups and buffering bigger, full-column data pages from the table itself.

    After you created the indexes, you may monitor their usage by the following query:


    select * from sys.dm_db_index_usage_stats where database_id = db_id('your database')
    This way you can verify if your indexes are being used, and disable/drop those that are not, to save disk space.

    • Marked as answer by O11y Thursday, September 11, 2014 1:35 PM
    Tuesday, September 9, 2014 2:54 PM

All replies

  • Hi Olly,

    Non-clustered indexes will help reducing I/O usage for queries which seek data based on a predicate, therefore speeding up the process. A query predicate is any condition in which data should be fetched, and mostly includes join predicates and filters - predicates in the where clause.

    If you do not have a lot of in-depth knowledge about database tunning, your best bet is to create indexes for columns often used in the predicates I mentioned, if they are not already in a clustered index.

    Is your application being used in a production environment already? If so, and the lack of indexes is impacting performance, you can query the following DMV:

     select * from sys.dm_db_missing_index_details where database_id = db_id('your_database')
    This will return a row for everytime a query was run and the optimizer found that an index would have increased query performance. Each row contains information about the predicate on which the index should be created, and columns to be included in the index to avoid lookups and buffering bigger, full-column data pages from the table itself.

    After you created the indexes, you may monitor their usage by the following query:


    select * from sys.dm_db_index_usage_stats where database_id = db_id('your database')
    This way you can verify if your indexes are being used, and disable/drop those that are not, to save disk space.

    • Marked as answer by O11y Thursday, September 11, 2014 1:35 PM
    Tuesday, September 9, 2014 2:54 PM
  • Generally indexing should be considered on JOIN ON clause columns and WHERE clause columns:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Tuesday, September 9, 2014 3:32 PM
  • "For a database like this"

    That... is a really big question. 30 tables and a couple of views.. with 10 rows per table. Not a problem. Any tables have thousands, millions, or billions of rows, definitely.

    Well designed, normalized, properly formed tables will change the answer a bit too.

    Oh, and how the users are doing selects, and what value you are using to find the rows that get updated, deleted, and selected, will certainly make a difference.

    My #1 advice is to make sure you have a natural key on all of your tables, using a unique constraint (PK and Unique constraints apply indexes, since 99% of the time, your key values will also be the value you do most of your searching on).

    Look at relationships. If you have join criteria where both sides of the join are very selective (very few duplicate values), this MAY be a place to go ahead and add an index...

    The rest should be up to you and your testers to determine by testing, looking at the plans of queries, looking at index usage stats (http://sqlblog.com/blogs/louis_davidson/archive/2007/07/22/sys-dm-db-index-usage-stats.aspx), missing index information (http://www.mssqltips.com/sqlservertip/1634/using-sql-server-dmvs-to-identify-missing-indexes/) can help, but it is a balance between how useful any index is versus how it harms performance (as it is just one more place to have a lock, disk contention, etc).

    All in all, it is a complex equation, that requires real usage to really know if you are getting it right or not, but getting it "good enough" isn't too difficult, if you do your indexing based on usage patterns and not try to tune every possible situation before it never happens.


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Wednesday, September 10, 2014 12:08 AM
  • My #1 advice is to make sure you have a natural key on all of your tables, using a unique constraint (PK and Unique constraints apply indexes, since 99% of the time, your key values will also be the value you do most of your searching on).

    I concur. If you post table DDL we may be able to assist you in identifying the NATURAL KEYs.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, September 10, 2014 12:13 AM
  • Olly

    People write books about this :-) . You need to learn how to read the execution plan of the query . So you understand how SQL Server Engine proceed the query,.

    Start with 

    http://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 10, 2014 5:42 AM
  • "For a database like this is it worth creating any indexes? I've always believed that you get a freebie index from primary keys anyway but not sure if this is true or if these are enough on their own?"

    Depends on situation :For example if the table have large amount of transactional data then indexing is not enough ,have to do table partition.

    If table containing only master data and data volume is not enough then indexing will be right choice.

    Please see the topic on optimization like:

    http://www.codeproject.com/Articles/34372/Top-steps-to-optimize-data-access-in-SQL-Server


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Wednesday, September 10, 2014 5:53 AM
  • Thanks all, plenty of info to go on here, thanks especially Samir, I will run these queries and see what optimization I can do

    Olly

    Thursday, September 11, 2014 1:37 PM