MSSQL with full-text search slow after upgrade.
We are working with one of our vendors whose software stores images for retrieval. They just updated their software and we have noticed that retrieval has gotten very slow to the point the retrieval actually times out. The database is MSSQL and full-text search is turned on. What steps can I try to speed up image retrieval? Should I rebuild and/or reorganize indexes or do something with settings for full-text search? There are about 1,000,000 images and full-text search did not exist in their previous version. This is MS SQL 2019.
ASKER
ASKER
https://docs.microsoft.com/en-us/sql/tools/dta/dta-utility?view=sql-server-ver15
Capture a trace of SQL server queries, then analyze to see what modifications might help improve performance if index rebuild/reorganize does not improve things.
How big are the db's versus memory in the server?
ASKER
Oh, both questions are yours.
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
Some troubleshooting steps:
https://docs.microsoft.com/en-us/sql/relational-databases/search/improve-the-performance-of-full-text-indexes?view=sql-server-ver15