Bad Idea Jeans Week: Dynamically Generating 999 Indexes on a Table

Let’s say, just theoretically, you wanted to demo a query that takes SQL Server a long time to compile.

And in order to make SQL Server’s job tougher, you wanted to create the maximum number of indexes possible on a table.

You might do something like this:

Which would generate output like this:

Look, you're the one who comes here to "learn stuff"

Look, you’re the one who comes here to “learn stuff”

The CHARACTER_MAXIMUM_LENGTH <> -1 OR IS NULL stuff is because I don’t want to try to index NVARCHAR(MAX) fields, but I do want to index NVARCHAR(50) fields.

The ORDER BY is because I wanted to get a wide variety of leading fields. If I just ordered by c1.COLUMN_NAME, I wouldn’t get all of the first fields covered in the first 999 indexes. (And I’m not even using a large table.)

I could have dumped all these into strings and executed them, but depending on the speed of the system, the demos I’m trying to do, the fields in the indexes, etc, I may not want them all created right away. Sometimes I experiment with just 25-50 indexes before moving on to, uh, 999.

Previous Post
Bad Idea Jeans Week: Dynamically Generating Long Queries
Next Post
How to Run DBCC CHECKDB on a Log Shipping Subscriber

18 Comments. Leave new

  • So is this to demo why it is not a good idea to create loads of Indexes??

    Reply
  • You have proven the need for new job title: Data Mad Scientist

    Reply
  • Hmmm, is it just me or this query returns nothing in SQL Server 2014 SP2 EE and Std ?

    Reply
    • Garrett Geier
      August 25, 2016 12:54 pm

      Did you change the WHERE clause to specify the schema/table name?

      Reply
      • Paulo Nascimento
        August 25, 2016 5:53 pm

        Yes Garret, shortly after Tim’s unproductive comment I even posted here the T-SQL code with the last 2 statements commented/disabled. Guess BrentO in his infinite wisdom does not allow comments to contain T-SQL code (code injection concerns maybe).

        Reply
        • The only filter is for jr DBA code. Weird, hm.

          Reply
          • Paulo Nascimento
            August 26, 2016 4:47 am

            Reproducing issue again:
            SELECT TOP 999 ‘CREATE INDEX [IX_’ + c1.COLUMN_NAME + ‘_’ + c2.COLUMN_NAME + ‘_’ + c3.COLUMN_NAME + ‘]’
            + ‘ ON [‘ + c1.TABLE_SCHEMA + ‘].[‘ + c1.TABLE_NAME + ‘] ‘
            + ‘([‘ + c1.COLUMN_NAME + ‘], [‘ + c2.COLUMN_NAME + ‘], [‘ + c3.COLUMN_NAME + ‘]);’
            FROM INFORMATION_SCHEMA.COLUMNS c1
            INNER JOIN INFORMATION_SCHEMA.COLUMNS c2 ON c1.TABLE_SCHEMA = c2.TABLE_SCHEMA AND c1.TABLE_NAME = c2.TABLE_NAME
            AND c1.COLUMN_NAME c2.COLUMN_NAME
            AND (c2.CHARACTER_MAXIMUM_LENGTH -1 OR c2.CHARACTER_MAXIMUM_LENGTH IS NULL)
            INNER JOIN INFORMATION_SCHEMA.COLUMNS c3 ON c1.TABLE_SCHEMA = c3.TABLE_SCHEMA AND c1.TABLE_NAME = c3.TABLE_NAME
            AND c3.COLUMN_NAME NOT IN (c1.COLUMN_NAME, c2.COLUMN_NAME)
            AND (c3.CHARACTER_MAXIMUM_LENGTH -1 OR c3.CHARACTER_MAXIMUM_LENGTH IS NULL)
            — WHERE c1.TABLE_SCHEMA = ‘dbo’ AND c1.TABLE_NAME = ‘Users’
            AND (c1.CHARACTER_MAXIMUM_LENGTH -1 OR c1.CHARACTER_MAXIMUM_LENGTH IS NULL)
            — ORDER BY c3.COLUMN_NAME, c2.COLUMN_NAME

          • Paulo Nascimento
            August 26, 2016 4:48 am

            Now, that’s weird.

          • It works fine for me. Where are you running it? With the schema and table name quoted out it returns 999 rows in every database except for Model, which returns 0. Similarly, if I create a new database that’s totally empty and run it there, it returns nothing. I think context is your issue, Paulo.

        • Paulo – that’s a common thing out on the web. A lot of user-entered input is sanitized. It’s nothing I do personally, just built into WordPress.

          Reply
  • Tim Cartwright
    August 25, 2016 11:59 am

    Learn “stuff” indeed! 🙂

    Reply
  • I haven’t seen anyone notice the reason behind doing these “things” that Brent is doing (unless they have and just not said anything….)

    I was told years ago that if you find someone who never makes mistakes and is perfect in everything they do – they aren’t really trying anything new and aren’t making an effort.

    Using that as a basis, one thing that contributes towards being a good DBA is someone who has either:
    a) seen the problems – and fixed them, or
    b) created the problems (whether it be intentional or in production through laziness) – and fixed them.

    Knowing how to fix problems in theory is helpful.
    Knowing how to get yourself out of the pile of poop that you put yourself in is invaluable (provided that you learn from the mistakes…. 🙂 )

    Reply
  • Brent, you’ve been having me think of all kinds of funny and bad ideas. Like a SQL Server game, where each of the database pages is a little box in the game. Then you have a sniper rifle that you shoot at one of the little boxes. As you shoot it, DBCC WRITEPAGE makes a “hole” somewhere in the page by writing zeroes.

    Plus, you could unleash your forkbomb script with one of those old WW I bombers. Oh, the possibilities…

    On a more serious note, it might be fun to gamify fixing database issues. Keep up the good work!

    Reply
  • Paulo Nascimento
    August 26, 2016 8:57 am

    It started running fining for me too after 3 minutes of my initial post, as soon as I realized I had to comment the last 2 lines (lack of attention of course). All ok with the context and all good now, I just failed to realize the “security mechanism” implemented in the end of T-SQL code 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}