SQL SERVER – A Funny Cartoon on Index

Performance Tuning has been my favorite subject and I have done it for many years now. Today I will list one of the most common conversation about Index I have heard in my life.

Every single time, I am at consultation for performance tuning I hear following conversation among various team members. I want to ask you, does this kind of conversation happens in your organization?

Any way, If you think Index solves all of your performance problem I think it is not true. There are many other reason one has to consider along with Indexes. For example I consider following various topic one need to understand for performance tuning.

  • žLogical Query Processing
  • žEfficient Join Techniques
  • žQuery Tuning Considerations
  • žAvoiding Common Performance Tuning Issues
  • Statistics and Best Practices
  • žTempDB Tuning
  • žHardware Planning
  • žUnderstanding Query Processor
  • žUsing SQL Server 2005 and 2008 Updated Feature Sets
  • žCPU, Memory, I/O Bottleneck
  • Index Tuning (of course)
  • žMany more…

Well, I have written this blog thinking I will keep this blog post a bit easy and not load up. I will in future discuss about other performance tuning concepts. Let me know what do you think about the cartoon I made.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

19 thoughts on “SQL SERVER – A Funny Cartoon on Index

  1. Dear sir,

    That picture tells me that,the GREEN MAN knows the structure of the database and the BLACK MAN’s knowledge on the subject……

  2. Yes, indexes aren’t silver bullets. One should consider them carefully and check that after index has been created it is actually used. Otherwise you end up index which slows inserts and does no good on selects.

    But I’m seeing more and more totally non-indexed databases these days. Even databases which has tables without clustered index. There’s one database which was crawling because a single select could do thousands table scans. In these cases it is totally safe to blurt “create index on it”.

  3. Yes It is very nice cartoon presented. In Most of cases when we get issue on query performance we seen same answer that is indexing.

  4. Cartoons are Great way to communicate the message, you have used cartoons in your earlier posts also and are very nice!

    Indexing is a critical issue and Planning of them should be done carefully. Which fields to be made as clustered indexed and their data type(ideally should be of fix width and non nullable). Which fields to be used in non clustered index? Also Covering Indexes and Filtered Indexes. Creating Indexes is important but too many indexes can also cause performance issues. Monitoring, Indexes that are not in use and fragmentation of Indexes. Using Re-Organize and Re-Build(should be used when fragmentation is quite high as it can cause huge transaction logs, in full recovery mode). In all it is a very important aspect of performance tuning.

  5. I agree with everyone on expanding on each point mentioned above.
    If you can expand on each of those above mentioned performance tuning points as a series of posts, you’ll be a real life saver for many..

  6. Hi Pinal,

    Nice cartoon..

    Always I can see, you suggest people to tune up Indexes for query performance, and that is good practice indeed.
    But I believe joins used in query also affects in different ways. Tuning up Joins in proper way can improve the performance.

    Sometimes we use Join with select query with join statement and sometimes we use table name with join statement.

    Can you please write about using smart ways of using joins to improve performance.
    When not to use sub queries and when not to use joins.

    You wrote about basics of joins here
    http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

    It will be my pleasure if you write or refer some article for best ideas of using joins and sub queries, when to use and not to use.

    Can you write about how to identify and understand “Execution Plans”

    Thanks a lot for all your Posts..

    Hi all readers, please give your suggestions and ideas about my question.

    Thanks.

  7. I found dealing with a database in which some tables gained 2 to 3 millions rows per day really focused the mind on indexing. With that amound of data loading, too many indexes can be a big problem too. I have come accross developers who just index every column in a database, “just to be on the safe side”. Once you have the painful experience of trying to insert large amounts of data regularly into tables with lots of indexes it focuses the mind.

    I also found different versions of SQL Server behave differently. When loading millions of rows into SQL Server 2000 it proved best to drop the indexes, do the insert and rebuild them. With the same system on SQL Server 2005 it is no longer neccessary to do that.

    • “I have come accross developers who just index every column in a database”

      I wrote a script that goes through every index in the db, checks if they aren’t used and returns how many kilobytes they take disk space. I ran it through one of our badly behaving database and got some interesting results. Unused indexes, that were probably inserted “just to be sure”, takes space A LOT and are updated a lot. So they are there just ruining performance.

      At the same time there are some queries that needs indexing badly. Although tables has indexes they aren’t used because all the joins and indexes doesn’t match.

      • Marko,

        Your script sounds very interesting. I did write an application that did a similar things using SQL SMO, reporting on the tables and indexes in a database and how much space they used. In fact, it could do this for all databases on all Servers on a network . However, I did not even think about the issue of checking if an index is used or not. I would love to know how you checked in an index is used or not.

        • I don’t have the original one at hand but I used something like this.

          select o.name table_name, i.name index_name, user_seeks, user_scans, user_lookups, user_updates, used_page_count * 8 used_kb
          from sys.dm_db_index_usage_stats s
          join sys.objects o on o.object_id = s.object_id
          join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
          join sys.dm_db_partition_stats ps on ps.object_id = i.object_id and ps.index_id = i.index_id
          where s.database_id = db_id() — only current database
          and s.index_id > 1 — omit heaps
          and (user_seeks = 0 and user_scans = 0)
          order by used_kb desc

  8. Hi Pinal,
    This cartoon is OK but if query is not working, this is always good to first check if indexes are required for the given query and if required, then if indexes are present or not. Indexes are not the fix for everything but they come first into the line of fire. In other words, if you have a table of a million rows and you select from this table with a column in WHERE clause, then what can you do, to get the results faster, other than creating an index?
    Again, as obvious, optimization on creation of indexes is required to depending on amount of SELECTs vs INSERTs/UPDATEs/DELETEs in the system.

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s