2

As part of Sitecore 9.1 Azure hosting setup, I'm seeing few SQL DB Advisor recommendations in Azure to drop unused indexes on certain Sitecore tables that were created by Web deploy packages.

Since these tables/indexes are used by xConnect, reporting services, I'm not sure if it is a good idea to drop them even though they are not used.

Any recommendations on this?

0

We always turn on "Automatically apply recommendations" including both Create and Delete.

There are lots of ways to use Sitecore and structure your data. Different setups will have subtly different influences on SQL Server.

You should enable the SQL Query Store for all your databases:

    ALTER DATABASE AdventureWorks2012 SET QUERY_STORE (OPERATION_MODE = READ_WRITE); 

This will allow SQL to monitor performance and give you suggestions if you have queries that are executing a lot, or are performing badly. In my experience there are a few indexes it always adds. Sometimes it takes them away again a bit later.

If you see a lot of executions of a particular query you should think about adjusting your caching. Database requests, fast as they are, are 1000x slower than memory requests.

Also, a bit off topic, but don't forget to rebuild your indexes and heap tables periodically as they can get very fragmented, thanks to the use of UNIQUEIDENTIFIER columns as clustered index fields.

  • Thanks Richard for your great recommendations. Just curious to know if you have seen any performance improvement/negative impact with these auto apply Azure recommendations? – Chandra Sekaran V Aug 2 at 13:50
  • I've not seen very noticeable changes either way really. But the performance profiler tells me things are going faster so I'm inclined to believe it. It won't remove indexes it didn't create, at least I've never seen it do that. – Richard Hauer Aug 3 at 1:23
  • That's good to know. I'm planning to apply these settings to my Prod databases/server. – Chandra Sekaran V Aug 5 at 16:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.