Query wont use any index - Cannot understand why

  • Jonathan AC Roberts wrote:

    I would guess that it would give the same recommendation in your scenario of moving it to a local machine compressing the tables.

    Ok thank you for the confirmation, unfortunately I cannot take the database at home on my own server, where I have enough space.

    I was thinking about trying finding some dummy data on the net, after all those are standard SAP tables, but on my server I don't have SQL server 2019/2022 nor SSMS application.

    Jonathan AC Roberts wrote:

    If you just run the query with the execution plan this might also show suggested indexes and if you look in the XML there might be some more suggested.

    Well, I am always using the execution plan, so I can understand what's happening, which indexes are used, which are not and I see the suggestions, but I have to say that a lot of the time there is no suggestion for the indexes, even when there is no index used for the query, that's why I want to take a different approach. Also I would like to know more about Clustered indexes, ColumnStore indexes and so on.

    I don't know how/where to find XML and see if there are some suggestions.

  • "Bumping" this thread so the first 2nd page post above shows up.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You can download and install SQL Server Developer edition for free. Also, SSMS is free.

    You can access the XML for an execution plan from the display of the plan. I think you just right click it to get a popup menu to view the  XML, then just search it for missing indexes.

  • Martass wrote:

    Well, I am always using the execution plan, so I can understand what's happening, which indexes are used, which are not and I see the suggestions, but I have to say that a lot of the time there is no suggestion for the indexes, even when there is no index used for the query, that's why I want to take a different approach. Also I would like to know more about Clustered indexes, ColumnStore indexes and so on.

    Be very careful about following such index hints (Brent Ozar refers to them as "Clippy", from the old MS Office days).  They are usually a good hint as to the gazintas for an index but they are also very frequently not correct because they list column names in left to right order instead of the best or even correct order.

    Also, here's a link to what I think is the best book on the entire planet for how to read execution plans and a whole lot more.  I had the honor of reviewing his first release and Grant Fritchey has kept his level of very high quality information up for every release.

    https://www.red-gate.com/products/sql-monitor/entrypage/execution-plans

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    Be very careful about following such index hints (Brent Ozar refers to them as "Clippy", from the old MS Office days).  They are usually a good hint as to the gazintas for an index but they are also very frequently not correct because they list column names in left to right order instead of the best or even correct order.

    Thank you for the recommendation, usually what I am doing is checking if there is recommended Index, which table needs and check how the index should look. I don't create the recommended indexes, usually I only check first the recommended, then reading the plan and trying to come up with new/better indexes by myself.

    Jeff Moden wrote:

    Also, here's a link to what I think is the best book on the entire planet for how to read execution plans and a whole lot more.  I had the honor of reviewing his first release and Grant Fritchey has kept his level of very high quality information up for every release.

    https://www.red-gate.com/products/sql-monitor/entrypage/execution-plans

    It seems like quite interesting book and I definitely will read it. I can see there is separate chapter to Data Reading Operators, where there are separate topics, which is great, and in general there are lots of interesting topics there.

    I have tried and tested some time ago ColumnStore indexes and there was 1 specific reason why I stopped, and it was that our Application cannot read from table with such an index when someone sends manually written Statement. The statements going automatically, basically we have "views" which replicates SAP t-codes/reports, and those were working, but most of the clients wants also using Manually written SQL statements using a functions we call "AdHoc Query" which basically sends the text as Query, and they can develop the code themselves.. For some reason didn't work and we decided that, we are not going to use ColumnStore and we will stick to NonClustered indexes.

    The question is, does ColumnStore is good for database which will stay static for some time, later only will be deleted data (Small amount of data, for example 1000 rows per day, even in some cases, we have to keep the data for 20 years without changes, and the data will be only read, never changed or added new data)? I have read some pretty strong words that ColumnStore is the best indexation for Data Wearhouse, and we have pretty much the same situation. Is it something worth looking at? I know that there are many many factors to be considered, but still, I have the feeling that in my case would be really good.

    Thank you

    • This reply was modified 2 weeks ago by  Martass.
    • This reply was modified 2 weeks ago by  Martass.
  • Martass wrote:

    but most of the clients wants also using Manually written SQL statements using a functions we call "AdHoc Query" which basically sends the text as Query, and they can develop the code themselves.

    That sounds like the perfect recipe for an SQL Injection attack.

    As for column store... it's not good for single row lookups nor even small sets.  It's meant for large aggregate queries and, IMHO, doesn't do such a good job there, either.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply