A checklist for fixing .NET applications to SQL Se

Question

A checklist for improving execution time between .NET code and SQL Server. Anything from the basic to weird solutions is appreciated.

Code:

Change default timeout in command and connection by avgbody.

Use stored procedure calls instead of inline sql statement by avgbody.

Look for blocking/locking using Activity monitor by Jay Shepherd.

SQL Server:

Watch out for parameter sniffing in stored procedures by AlexCuse.

Beware of dynamically growing the database by Martin Clarke.

Use Profiler to find any queries/stored procedures taking longer then 100 milliseconds by BradO.

Increase transaction timeout by avgbody.

Convert dynamic stored procedures into static ones by avgbody.

Check how busy the server is by Jay Shepherd.

Answer 1

A few quick ones...

• Check Processor use of server to see if it's just too busy
• Look for blocking/locking going on with the Activity monitor
• Network issues/performance

Answer 2

In the past some of my solutions have been:

1. Fix the default time out settings of the sqlcommand:

   Dim myCommand As New SqlCommand("[dbo].[spSetUserPreferences]", myConnection)

   myCommand.CommandType = CommandType.StoredProcedure

   myCommand.CommandTimeout = 120

2. Increase connection timeout string:

   Data Source=mydatabase;Initial Catalog=Match;Persist Security Info=True;User ID=User;Password=password;Connection Timeout=120

3. Increase transaction time-out in sql-server 2005

   In management studio’s Tools > Option > Designers Increase the “Transaction time-out after:” even if “Override connection string time-out value for table designer updates” checked/unchecked.

4. Convert dynamic stored procedures into static ones

5. Make the code call a stored procedure instead of writing an inline sql statement in the code.