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 conversations about Index I have heard in my life. Let us see a funny cartoon on the Index and Performance Tuning.
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 happen in your organization?
Anyway, If you think the Index solves all of your performance problems I think it is not true. There are many other reasons one has to consider along with Indexes. For example, I consider following various topics 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 easier and not load up. I will in the future discuss about other performance tuning concepts. Let me know what do you think about the cartoon I made.
Reference: Pinal Dave (https://blog.sqlauthority.com)
18 Comments. Leave new
Hi,
Can you elaborate each point with example ?
Anyone ?
Thanks.
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……
Very nice cartoon… Indexes are not the painkillers.
I gues you meant indexes are not the ONLY painkillers
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”.
We nice cartoon indeed. Thanks for solving the confusion. Would like to hear on those points in more detail in future post. Thanks
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.
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.
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..
The cartoon clearly illustrates how performance issues are dealt with, like a quick fix without analyzing the root cause.
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
Weicco,
Thanks for this, I will try it out.
Could you share the script you used?
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.