Indexes

[Ernst]

If this query suggest 246 indexes should be added and supplies command to a busy JIWA db. Should I add the indexes???

SELECT
UPPER(DB_Name()) as 'DATABASE',
Object_Name(SQLOPS_MsgIdxDetails.object_id) as 'OBJECT NAME',
Schema_Name(SQLOPS_SysObj.schema_id) as 'SCHEMA NAME',
'CREATE INDEX '+DB_Name()+'_SQLOPS_'+
Object_Name(SQLOPS_MsgIdxDetails.object_id)+'_' +
CONVERT (varchar, SQLOPS_MsgIdxGrp.index_group_handle) + '_' +
CONVERT (varchar, SQLOPS_MsgIdxDetails.index_handle) + ' ON ' +
SQLOPS_MsgIdxDetails.statement + '
(' + ISNULL (SQLOPS_MsgIdxDetails.equality_columns,'')
+ CASE WHEN SQLOPS_MsgIdxDetails.equality_columns IS NOT NULL
AND SQLOPS_MsgIdxDetails.inequality_columns IS NOT NULL
THEN ',' ELSE '' END + ISNULL (SQLOPS_MsgIdxDetails.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + SQLOPS_MsgIdxDetails.included_columns + ');', '')
AS 'CREATE INDEX COMMAND', --Online Index will work only if you have Enterprise edition
Cast(round(SQLOPS_MsgIdxGrpStats.avg_total_user_cost,2) as varchar)+'%'
as 'ESTIMATED CURRENT COST',
Cast(SQLOPS_MsgIdxGrpStats.avg_user_impact as varchar)+'%' as 'CAN BE IMPROVED',
SQLOPS_MsgIdxGrpStats.last_user_seek as 'LAST USER SEEK',
'SCRIPT PROVIDED BY HTTPS://SQLOPS.COM' as 'CREDITS'
FROM sys.dm_db_missing_index_groups AS SQLOPS_MsgIdxGrp
INNER JOIN sys.dm_db_missing_index_group_stats AS SQLOPS_MsgIdxGrpStats
ON SQLOPS_MsgIdxGrpStats.group_handle = SQLOPS_MsgIdxGrp.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS SQLOPS_MsgIdxDetails
ON SQLOPS_MsgIdxGrp.index_handle = SQLOPS_MsgIdxDetails.index_handle
INNER JOIN sys.objects as SQLOPS_SysObj
ON SQLOPS_MsgIdxDetails.object_id = SQLOPS_SysObj.object_id
ORDER BY 4 desc

[SBarnes]

I wouldn't just blindly adding indexes as it could have a number of side effects, any adding of indexes to a database should be done with some analysis by identifying where the speed issues are.

Where and what indexes should be put in a database can laregly depend on what data is in the database, the indexes that currently exist on the database are done from a standard generic point of view rather than aimed at a specific database.

Indexes can also slow things down as they are designed to improve read performance.

I wouild first look at this https://jiwa.atlassian.net/wiki/spaces/ ... erformance

I would also have a regular running maintnenace plan on the sql server as well.

[Mike.Sheen]

What Stuart said.

Anyone who is responsible for a database should have the query store enabled and be doing regular inspections (once a week or month) of the top 10 worst performing queries, and making a decision as to whether or not to create the recommended indexes - and documenting effectively in an easy to read log what was created, deleted or modified.

It HAS to be regular, because the indexes recommended to be created today, whilst it might improve performance today, in a month or year when the data weighting shifts those very indexes may be impacting performance.

Your query produces the same results as looking at the query store and implementing the recommendations from there, but the query store gives you nice graphs which make it really clear which bits would make a material impact on performance.

QueryStore_example.png (133.12 KiB) Viewed 7987 times

[pricerc]

While there is a better than fighting chance that some of those indexes will be useful, there is also opportunity for things to get worse, much worse. Been there, done that.

I've resolved some rather perplexing performance issues with specific queries in Jiwa with careful changes to indexes, but more often to the affected queries themselves.

I like to start by running some routine index maintenance - grab Ola Hallengren's utilities at https://ola.hallengren.com/ for something that's a bit more consultant-friendly than Microsoft's offerings.

Then you have to run the specific problem queries within SSMS with the SQL execution plan reporting turned on. And it will give you specific recommendations for *that* query.

I've had considerable success with some particularly problematic queries by switching the clustered index of some tables (as distinct from the primary key) to a 'natural' key, say InvoiceNo on SalesOrders and Quotes. And for custom form data, where there are many custom form fields, switching the clustered index to the underlying 'object' id.

But changing the indexes won't help if the query itself is flawed.

In contrast to most ERP vendors, Jiwa makes extensive use of stored procedures, which is brilliant.

Because while they mostly work well, some only work well until they don't.

But because they're stored procedures, they can be changed to suit a specific customer's requirements or performance problem.

There are a few "legacy" stored procedures which perform "less well" on large datasets, because they're using RBAR (What is RBAR? How can I avoid it?) to process things one transaction at a time using recursive calls to other stored procedures (I'm looking at you, usp_JIWA_Creditors_BalanceAsAt...). When re-coded out of RBAR they usually perform much better.

But you have to weigh up the cost of all that analysis and development vs the value your customer is getting. We had a creditor report that was taking at least 8 hours to run, and it often didn't complete at all. So it was worth the customer's money for me to spend several days investigating the problem and re-writing the stored procedure. He was happy waiting 1/2 an hour per month (which is what it was taking a few years prior), because he could leave it running while he did other things; but having it not work at all meant it needed 'fixing'.