If you are a database developer or a database-savvy software engineer, you have probably heard a thing or two about indexes – indexes are usually used to find values matching a given query quickly, but the downside of them is that they require space, so essentially you trade-off space for speed.
With that being said, not all database indexes are very useful – if your indexes are obsolete, they would most likely do more harm than good.
What Are Indexes and How Can dbWatch Help?
Indexes are simply database structures that allow for faster retrieval of data – data retrieval is usually faster because when indexes are in use, database instances usually don’t have to scan through the entire data set to find rows relevant to the query.
The maintenance of indexes can sometimes be costly both time and resource-wise. Database administrators and developers might find that some of the indexes they use are more useful than others: some indexes might be obsolete. If you find that making use of the indexes present in your SQL Server, MySQL, MariaDB, or Oracle database instance is getting more difficult, make use of dbWatch – for example, it can offer some index usage statistics that can give you information on how a certain index has been used to resolve queries. Import a SQL Server instance into dbWatch, expand the Performance jobs category, and run the Index usage statistics job:
As you can see, this database job was able to run ,and dbWatch was able to provide us with some details related to it: dbWatch checked the statistics of a couple (in this case, 179) of objects. The query execution time was 0 seconds, meaning that if indexes are in use, they do their job – they are making our queries very fast.
When dealing with indexes, a job above the index usage statistics (the High activity monitor) can also be very useful: the high activity monitor would collect some statistics about CPU usage, active processes, and logical and physical reads, letting you decide whether indexes would be useful for your database instance or not: for example, if you see that some of your queries related to reading are slowing down, keep an eye on it as well – it will provide you with how much CPU your SQL instance takes up, how many active processes are running, and, perhaps most importantly, how many logical reads per second is your database dealing with letting you decide whether to ditch indexes or not:
The high activity monitor, as you can see, will provide how much is used by SQL, and how many logical reads per second your instance is running. If some (or all) of these numbers seem to be abnormally high, make sure to inspect the performance of your SQL Server instances and make sure that all of the indexes that you are using are absolutely necessary for your database instances to function: if you are using MySQL, for example, you can also make use of the EXPLAIN statement which will provide you with some details related to the execution of your database queries including what indexes are used by your query, what is the length of your indexes, and some other information letting you decide whether your database indexes are obsolete or not.
On the other hand, obsolete indexes in the SQL world can be observed manually as well – simply observe the amount of data you have in your table, and if it doesn’t exceed a couple of hundred thousand (say, 500,000 rows or more), or if you mainly use INSERT, UPDATE, or DELETE queries, your indexes would probably not be that necessary to begin with – in this case, keep in mind that indexes would make INSERT, UPDATE, and DELETE performance even worse, because when rows are inserted or updated, the index has to be updated together with them and the more rows you are updating, the slower the process becomes.
Anyway, we hope that this blog post has given you some more insight into the world of indexes, but do keep in mind that dbWatch can be useful not only for SQL developers and not only for indexing your databases (for example, dbWatch can let you observe the information related to your database instances if you make use of its database management capabilities by heading over into Monitor -> Management):
Observe both the left and the right side: the left side allows you to delve into the configuration, performance, or session activity on your database node. Sessions, by the way, can be very relevant to indexes, because if you see that the performance of certain queries is slowing down, you could just navigate to it and kill the query, then look into how you can optimize it to make use of the indexes inside of your database instance instead – indexes would only be redundant if they are not used by your queries:
The right side, on the other hand, will provide you with some of the statistics relevant to your database nodes, you will be able to observe the amount of logical reads per second (proper usage of indexes should decrease the number of rows your database instance reads), and some of the memory objects inside of your database instances.
We hope that this capability of dbWatch also proves to be useful at least from time to time – make sure to keep an eye out on the blog, index your database instances properly, and see you in the next one.
About the Author:
Lukas Vileikis is an ethical hacker and a frequent conference speaker.
Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.
He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.