I have an OLTP database using platform Sql Server 2012, This application issues certificate No when transaction completed. I have faced a problem two time within a week that Issuing of certificate No. has stopped suddenly while there is no lock on any tables but when doing re-indexing on transaction table again application works fine. Kindly suggest what can be possible cause.
it might be due to a timeout from the app? Maybe you can try to see how long it takes you to run that query directly to DB. As you said after re-index it works again, so most likely is the timing or it pick the wrong execution plan.
You may also want to schedule of process overnight to UPDATE STATISTICS on your transaction table WITH FULLSCAN.
@dennisc, I have checked the query, its execution time is less than 1 sec. My application was working fine since last one year. Last week suddenly we have faced such problem that Issuing of certificate No. (i.e. policy) has stopped suddenly while there is no lock on any tables and we are able to do all activities other that issuing of certificate number such as fetching report and update in other tables also , however in application log error was recorded " timeout expired" but after reindexing of transaction table its started to issuing of certificate again.Thanks
in that case, if you are sure that its not the query plan issue and no blocking occurs, you might need to runs a trace to capture the error (or extended events). Where you can see exactly what happen when the application issuing the query to SQL and what its actually doing. You will need to capture all the warnings as well as the start event of the query, so that you can link them in your analysis.
Hope this helps