Indexes

Support for Microsoft SQL Server in the context of Jiwa installations.

Indexes

Postby Ernst » Tue Nov 14, 2023 7:57 pm

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
User avatar
Ernst
Kohai
Kohai
 
Posts: 233
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 12

Re: Indexes

Postby SBarnes » Wed Nov 15, 2023 4:51 pm

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.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1646
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 182

Re: Indexes

Postby Mike.Sheen » Wed Nov 15, 2023 6:48 pm

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
QueryStore_example.png (133.12 KiB) Viewed 14258 times
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2486
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 770

Re: Indexes

Postby pricerc » Fri Dec 08, 2023 12:43 pm

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'.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 509
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron