Mssql lock or timeout solution

If there are more and more SQL databases, there may be a lot of problems such as read timeout and deadlocks. Based on experience, the data structure design is unreasonable and views are often used. How can we solve these problems?

1. Timeout when SQL database adds data due to database design issues

Symptoms:

A,
Microsoft ole db Provider for SQL Server error '80040e31' [odbc SQL Server Driver] timeout expired)

B. Low CPU and memory usage on servers;

C. The event log prompts: * files in the database '*' are automatically increased after 453 milliseconds. Use alter database to set a smaller FILEGROWTH or a new size.

Cause:

When the database is set, [file growth] increases by percentage. When the database file is large (more than 1 GB), a timeout is reported for the new operation, at this time, the CPU and memory usage are very low.

Solution:

Set the above file growth here to a lower percentage or directly specify how many megabytes are added.

2. SQL Server database timeout settings

Modify the connection timeout settings of the client. By default, the timeout setting for registering another SQL Server through the Enterprise Manager is 4 seconds, and the query analyzer is 15 seconds.

Settings in enterprise manager:

A. In Enterprise Manager, select "tools" from the menu and then "options ";

B. In the displayed "SQL Server Enterprise Manager properties" window, click the "Advanced" tab;

C. Enter a large number, such as 30, in the "login timeout (seconds)" box on the right under "connection settings.

Query settings in Analyzer:

Click "tools"-> "options"-> "connection"; set logon timeout to a large number, and set connection timeout to 0.