You are viewing limited content. For full access, please sign in.

Question

Question

Delete from searchresults is slowing Laserfiche

Laserfiche Administration
Updated April 18, 2016
asked on March 4, 2016 • Show version history

Our system is running at a crawl due to SQL server being at 100%. Our IT group reported:

"The application server is working fine but the MS SQL database server is running at almost 100% consistently. The result from the MS SQL Activity Monitor shows that a query is taking up almost all the CPU cycle. The query is "delete from searchresult13 where locid in (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16)."

This isn't the first time this has come up. Is there any way to prevent SQL from bogging down on these queries? Or at least prevent them from running in the middle of the day?

0 0

Replies

replied on March 4, 2016

There have been several improvement in version 9.2.1 and 10 in regards removing data from the database, although we will need to do further troubleshoot to provide an accurate response.  For this issue, I suggest to contact your reseller to submit a case with Technical Support on your behalf.

Please make sure the following information get submitted with your case, so we can expedite the solution for the issue.

1. Exact version and build number of the laserfiche server . file lfs.exe from the server folder.

2. Exact version of the SQL server that you are using. To get the exact version of sql run this query against master database : select @@version.

3. submit a copy of the application and system event logs (unfiltered ) and in evtx format from the Laserfiche Server machine.

4. ensure the database has at least a weekly maintenance plan where the transaction log file is shrink and indexes are rebuild.

 

0 0
replied on April 18, 2016

We observed the same behavior and found that the delete statements were not using an index. Consequently, these deletes were performing full table scans and consuming a considerable amount of resources.  The following index will dramatically improve the performance of the delete. However, keep in mind that there are 14 of these search results tables and of of these indexes would need to be added for each table. 

 

CREATE NONCLUSTERED INDEX CUST_DELETE_INDEX
ON [dbo].[searchresult13] ([tocid])
INCLUDE ([rsid])

 

You probably also want to investigate what is generating all of that search activity. Keep in mind that the search activity could be created by end users or background workflows. 

 

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...