Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Are You Using All Indexes of Your Database?

DZone's Guide to

Are You Using All Indexes of Your Database?

Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

It is an odd week at work when I don't learn something new. However, every once you learn something so obvious that you wonder exactly why did you miss this so long. This note is about one of those embarrassing "aha" moments.

I have never worked on a project that did not have some database behind it. DB2, Oracle, Sybase etc all the usual suspects. However, I have always had one or two members in the team who were the db nerds. While I wrote queries, stored procs etc etc when things got difficult with db related issues, they stepped up and I was only too happy to hand it over to them. This is all good. I like to play to the strengths of the team. However, this means I did miss a few low hanging fruits on the way. And here I talk about one such.

So, we delivered this nice project and we were doing a soft launch. As usual our DB2 came under fire. Someone asked a innocuous question. Are all our heavy lifting queries using indexes? Given the number of queries that we have it was not a quick investigation. So, the next question was are all the indexes being used? Or some of them are just hanging around eating resources and not doing anything at all. Surely I knew the answer.

Umm, no, I did not. I should have. This is something that I should have known. Not only for this application after someone asked but for all the applications that I have ever worked with. But the fact is I did not. I suspect strongly that many of the readers are in the same boat. And hence this half rant, half "how-to" note.

You have read the rant part already. Now is the "how-to" part.

Which indexes of your DB2 database are not being used?

    select   
     INDSCHEMA,   
     INDNAME,   
     TABSCHEMA,   
     TABNAME,   
     INDEXTYPE,   
     LASTUSED  
    from SYSCAT.INDEXES  
    where INDSCHEMA = 'yourschema'  
    order by LASTUSED ;   

This should give you a list of all indexes of your database in the order of your last usage. In case they are not used at all the LASTUSED will be '0001-01-01'. There you go. As simple as that. You will get a much more knowledgeable note on this subject at this link. That's all for today. If you liked what you read or not liked :) it will help if you leave a comment here.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
java ,sql ,database ,tips and tricks ,tools & methods ,index

Published at DZone with permission of Partha Bhattacharjee, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}