I have noticed a relatively high fragmentation level and rate of fragmentation in the automatically generated Ignition database for tag history. I have not noticed a performance impact in the apps, yet.
Tuning Questions:
Do we need to add standard sql server maintenance to the default databases for Ignition?
Why would Ignition not maintain its own databases?
Would Ignition perform better with some SQL Server side tuning of its database?
Query Questions:
My database workload seems predominantly to be Ad Hoc queries.
Does SQL Server not consider a Named Query to be a Prepared Process?
Is a system.db.runPrepQuery run as an Ad Hoc query or a prepared process?
Short of writing EVERY query as a stored procedure, how would I help Ignition use more Prepared Procedures as opposed to Ad Hoc?
Because it is a cross platform product, where available database technologies vary. And customer preferences vary, too. (I, for one, think SQL Server is both overpriced and fatally flawed.)
Absolutely. Each workload is unique, so tuning is on you.
As to your queries questions, I would expect everything that uses proper JDBC parameter substitution to be a prepared statement. How that translates into SQL Server is a JDBC implementation detail.
For small single application (dedicated) projects, SQL Server IoT edition could be a very cost effective approach (about $100) for all the features of SQL Standard. The caveat being that this DB only serve a single use, single application…not hosting multiple instances or serving multiple different apps. If you are willing to put the fatally flawed aspects aside, of course.