Here's the Problem
In a recent blog post I discuss some difficulties that one of our customers had when attempting to index-tune a poorly performing report query: adding one new index to one table caused the report query to blaze , but it messed up a bunch of other SQL operations that the DBA team had to fix. Has that ever happened to you?
Index tuning is best done not by looking at an individual SQL statement or two, but by looking at the concept of a SQL workload (many SQL statements). Ah, but just finding new indexes and how they can be grouped—let alone testing which ones are the best—can be very difficult and time-consuming.
If you haven’t read the blog, I encourage you to read it, but even if you haven’t, it's ok...the quiz questions below might give you an appreciation for how difficult it can be to consider new indexes and their combinations.
Here's the scenario.
Suppose you have a SQL workload (say, a list of SQL statements). Those SQL statements only access five small tables. Only five. Each table has only three columns. Only three. Your goal? Find the best combination of indexes that maximize the performance of SQL in the workload against those five tables and their columns.
Sounds easy, right? You may be surprised. See if you can guess the answers to the questions below. To keep it simple, let’s consider only “vanilla” column-based indexes. Composite indexes are ok, but don’t consider clustered-type indexes, function-based, or other index types.
Here's the Quiz
Post your replies here in the forum by July 17, and we’ll send you some cool Toad stickers. Answers will be included in part 3 of my Turnpike blog post in July. Stay tuned!
What is the total number of possible indexes on just one table? How about on all five tables?
(Two questions in one because they’re easy!)
What is the total number of different combinations of these indexes, choosing up to, say, three indexes at a time?
How about the total number of index sets choosing up to, say, 10 indexes at a time?
(Extra Credit) What is the total number of index sets, regardless of the number of indexes in each set?