March 27, 2024 at 12:02 pm
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.
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.
March 28, 2024 at 6:20 am
"Bumping" this thread so the first 2nd page post above shows up.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2024 at 12:46 pm
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.
March 28, 2024 at 7:07 pm
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
Change is inevitable... Change for the better is not.
April 2, 2024 at 1:02 pm
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.
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
April 2, 2024 at 5:51 pm
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply