Sql Timeout error

Question

Sql Timeout error

pols

SSC Eights!

Points: 935
More actions
December 19, 2019 at 7:25 am

#3708971

Hi,
we have one production database which is 700 GB.
Through Microsoft dynamics jobs were running and connecting to these dbs. This job is scheduled at 9pm every night. The job will run for 30 min after it stops executing. In the dynamics logs we able to see below error.
the following sql error was unexpected: execution timeout expired. The timeout period elapsed prior to completion of the opeartion or the server is not responding.\program returned error database connectivity.
I dont see disconenctivity in sql server logs. or anykind of timeoput error message in the db logs. But in application dynamics logs we see the above error message.
I enabled sql server browser,remote time out setting to 0 in sql server connectivity properties. but still the issue same. some days the job runs with any issue. But some days this error comes frequently.
I am not into dynamics. Please can anyone suggest me what else i can see in the SQL db aspect. any blocking or deadlock happening during the time timeout error in application?
anything else i need to check in sql db?
Thanks,
Jo
September 4, 2020 at 9:24 am

This was removed by the editor as SPAM
September 4, 2020 at 9:25 am

This was removed by the editor as SPAM
September 4, 2020 at 9:32 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Jo

A query (or certainly one that doesn't involve a remote server) will never time out - it will be the application that kills it, not SQL Server. You need to increase the timeout in the application, do some tuning on the server (maxdop, indexing, updating statistics are examples of what may help) or tune the code in the application. The vendor may be able to help with the latter option. If you want to see what queries are actually causing the pain, you could use an Extended Events session to capture the activity while (and after) the job runs, or you could query the plan cache to look for the most resource-intensive queries.

John

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

I don't know Dynamics well at all, so I did a quick search. Based on this, there are three possible timeout settings that you may need to adjust. I'd look into that.

However, another option would be to monitor the server to see which queries are taking the longest. Check their execution plans to see why. A new index may be indicated (I know you can't adjust Dynamics code except for custom reports). If it is a custom report, maybe adjust the T-SQL.

Also, make sure you have good statistics maintenance on your SQL Server instances. If stats get out of date, it frequently causes performance problems.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

I've not worked with Dynamics... is that one of those MS applications/databases that uses random GUIDs for keys?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Jeff Moden wrote:

I've not worked with Dynamics... is that one of those MS applications/databases that uses random GUIDs for keys?

Yes!

With database designs that would make Codd break down weeping. They practically build them as a "how not to" manual. The negative example we should all avoid.

No lie, I once just went to the missing index tables and built all of them. It helped. I felt dirty for days.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

Grant Fritchey wrote:

Jeff Moden wrote:
I've not worked with Dynamics... is that one of those MS applications/databases that uses random GUIDs for keys?
Yes!
With database designs that would make Codd break down weeping. They practically build them as a "how not to" manual. The negative example we should all avoid.
No lie, I once just went to the missing index tables and built all of them. It helped. I felt dirty for days.

I think I may be able to help. I have about 400 hours of testing I've done with Random GUIDs. The results of all that testing have been summarized in my “Black Arts” Index Maintenance #1: How the “Best Practice” Methods are Silently Killing Performance presentation and the fix is demonstrated in my “Black Arts” Index Maintenance #2: Better Methods that Can Actually Prevent Fragmentation for Weeks presentation.

The first session demonstrates how the use of REORGANIZE totally trashes the underlying page structure of all indexes that have a Random GUID as the leading column. Such trashing causes the indexes to go into a perpetual bad page-split mode. Ironically, just when the index starts to recover from that train wreck, the normal supposedly "Best Practice" index maintenance actually removes the critical free space required at and above the Fill Factor that is responsible for the very long term prevention of such page-splits. Using REORGANIZE on the fragmented indexes will cause MASSIVE amounts of blocking (a prime source of timeouts) and log file usage ESPECIALLY the day after such awful index maintenance is performed.

There is actually a way to make it so that such ALL page-splits (good or bad) are prevented for WEEKs and even MONTHs for Random GUID indexes. It goes against all current logic and supposed "Best Practices", which were only meant as a "starting point" (and is documented as such in Books Online).

Here's how to handle index maintenance for Random GUIDs and, I have to tell you, it bloody well works a treat.

Step 1 is to first make sure that the SQL Server service hasn't been restarted for at least 3 weeks and that at least 1 month-end has occurred.
Check all indexes using sys.dm_db_index_usage_stats for "User_Updates". REBUILD all indexes that show less than 5-10 User_Updates at 100% to mark them as "Static or Nearly Static" indexes.
For all other Random GUID indexes in the system that don't qualify as "Static or Nearly Static" indexes, REBUILD them at 81% to mark them as "Evenly Distributed" indexes.
Here comes the part that everyone disbelieves but I've proven works. Setup your index maintenance to REBUILD any index that has more than 1% logical fragmentation and run your index maintenance EVERY NIGHT! It will NOT rebuild all of your indexes every night. Because of the even distribution of Random GUIDs, the area of free space on all pages will slowly fill over time. Once the pages finally reach the point where they're full, they just start splitting and you MUST catch it just when that starts, which is at 1% logical fragmentation. While they're filling, the Non-Clustered indexes (which are generally narrow) will go for MONTHs with no page splits and absolutely ZERO fragmentation. The wider indexes and, certainly, Clustered Indexes (which are wider) will fragment more quickly but, depending on the insert rate of new rows, can easily go for several weeks with absolutely no page-splits and ZERO fragmentation. The three important things are NEVER use REORGANIZE on Random GUID indexes, do your index maintenance every night, and REBUILD as soon as an index goes over 1% logical fragmentation. If you wait any longer, you'll have the same massive page splits as if you did a REORGANIZE.

There are other issues that people have with all indexes whether they're based on Random GUIDs or not. One problem is with "ExpAnsive Updates" (you've got to find and fix those because even low Fill Factors might not be enough and low Fill Factors are almost totally useless for "ever-increasing keyed" indexes. The other is "in-row" LOBs, which can "trap" ultra low density pages (<30% full and even < 1% full) between wider rows caused by the in-row lobs. I have a presentation on that, as well. The fix is to change the table option to force storage of LOBs out of row and then do an "in-place Update" to force existing LOB data to go out of row. You also need to add a default of a single blank to such columns to prevent the GUID pointer from being "ExpAnsive" in nature.

You can get incredible performance gains when it comes to necessary or troublesome Clustered Index scans by doing that out of row thing. I've got almost a hundred hours of testing in that area, as well.

And, yeah... that's what I've personally implemented on some of my databases (along with much more because most of my databases don't even have a GUID column in them).

Ah... almost forgot. You also need a fairly aggressive stats maintenance plan. I went almost 3 years (as an extreme test) on one of my larger production systems without doing ANY index maintenance with only the occasional page density problem (a table that suffered a lot of DELETEs) with no real ill effects just by doing stats maintenance on an aggressive schedule.

I don't recommend NOT doing any index maintenance on Random GUID indexes because they'll settle out at a "natural Fill Factor" of about 68% and they WILL go into the "perpetual page split" mode. They MUST be maintained as I described or they'll continue to be a problem that you might not ever think of but will know the problem in the form of bloated log files and blocking.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16155 More actions · Answer 6

I work with it on my shop as we have a huge CRM installation - both in users and db size (3 TB) - you have for sure to play around with indexes in order to get performance up to what it needs to be. Although with some queries it is nearly impossible to improve it.

As for timeouts - you need to change them on the application server - registry settings and others - as you didn't tell us enough details it is hard to know which one it is - although if it is a CRM job it is most likely the OLEDB timeout.

some links

https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/271420/getting-crm-session-timeout-after-15-20-minutes-even-if-we-are-using-crm-at-that-time/770073

https://community.dynamics.com/crm/b/crmdavidjennaway/posts/sql-timeouts-in-crm-generic-sql-error

and yes they use uniqueidentifiers - but taking in consideration that it is an application setup for people to be able to work disconnected and then connect to the server to send/get updates and also taking in consideration that it is a multi-tenant database its design is not as bad as it seems.

There are issues for sure - but most of those that I have identified are to do with how the "users" setup their entities and their relationships - and most do not take enough effort to "improve" them - always ways to avoid particular pitfalls of CRM Dynamics.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 7

John Mitchell-245523 wrote:

Jo
A query (or certainly one that doesn't involve a remote server) will never time out - it will be the application that kills it, not SQL Server.
John

I'm not sure that it qualifies as an exception to that rule but I've found that there is a timeout associated with whatever server the SQL Server is on. In my case, it occurred when trying to do direct backups to an AWS "gateway" drive... the backups would run fine and SQL Server would report the backup had completed at 100% but we'd get an error saying that the backup failed during the final "resize" even if we set it so it wouldn't presize the backup file for compression. We ultimately made a change to the time out for "LanMan" to fix it.

My point is, that it might not be the application (other than SQL Server itself)... it could be a server setting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)