Good afternoon,
I'm desperate with a problem that I've been looking for a solution for a long time now and hope that someone can give me the decisive hint or at least send me in the right direction.
The situation is the following:
There are a number of advanced searches that are used to fill marketing lists. Some of these are quite complex. Various employees keep reporting that they could not get any results, but instead they got the error SQL Server timeout expired. When I execute these queries, I get the results in a short time (sometimes in about 10 seconds). The whole thing can then be reproduced. The users can be lucky and they get their results. But they can also get the timeout and this sometimes over several hours.
What has happened so far:
- A service provider has increased timeout values. Without success. Unfortunately, the contract with the service provider has ended, so I don't get any more help from them.
- We have simplified the queries. But apart from the fact that we then didn't get the expected results, it didn't help much either. It used to go well. And once again there was the timeout. But there was also the same behavior with the actual queries, so I can't even tell if it worked.
- According to my understanding I trigger an advanced search in the browser. The query goes to the CRM server, which sends it to the SQL server. There it is processed and the results come back to the CRM server. These are possibly processed a little bit and then returned to the browser. The timeout happens during the request from the CRM server to the SQL server and therefore different working environments are not important for the user. Either there is a general problem between the servers, which I should have then experienced by myself, or there is something specific at the user. Even if the environment might not be, we have also looked at the environment.
- One user tried the query from different devices. It did not work for him. I tried it from different devices. I had no problems.
- I tried it with different browsers on different devices. Without problems.
- I sat next to a user and we did the same query at the same time. I had a result after 10 to 20 seconds and he got the timeout. We could repeat this several times in a row.
- I tried it wired in the company network, but also in WLAN and via VPN from the home office. Almost all users are working there at the moment. It made no difference.
- After I had ruled out the working environment not only theoretically but also through experiments, I actually only saw one place where there was a difference between me and the users. These are the security roles. Besides some self-defined security roles, which the users have as well, I also have the role system administrator. A colleague who also has the role of system administrator and who also has no problems did some tests with me. She gave me exactly the same roles that the users have - she also removed the System Administrator role. Later she gave me the role back. We tried this several times. Every time I had the role, there were no problems. But if I didn't have it, I also got the timeouts or had to wait an unusually long time.
Even if I can't explain it to myself, it seems as if the problem has to do with the role. Giving all users the security role System Administrator could be a solution. But it is not reasonable and justifiable for me. We have already done a search in the existing roles for a possibly missing authorization. There is even full access to some of the used entities. We only have one organisational unit, so that only no authorization or on user level would be a possible explaination if there are too few authorizations. But this is not set anywhere. The settings are higher. A missing permission to access an entity (even if I just haven't found it yet) would seem logical to me, but then the users should never get results - not only sometimes.
- Does anyone already know this?
- Can anyone confirm the impression that the System Administrator security role also means something like a priority in the query or a guaranteed better performance? Does anyone have an explanation for this or an idea for a solution?
- Does anyone have a hint or an idea what I could investigate to get closer to this unusual problem?
Here is the description of the environment:
- Dynamics 365 - Version 1612 (8.2.1.176) On Premise
- SQL Server 2016
- both on Windows Server 2012 R2
Every helpful answer is welcome.
Greeting. V.S.
