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

<p><strong>Code:</strong></p>

<p>Change default timeout in command and connection by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67438">avgbody</a>.</p>

<p>Use stored procedure calls instead of inline sql statement by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67438">avgbody</a>.</p>

<p>Look for blocking/locking using Activity monitor by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67387">Jay Shepherd</a>.</p>

<p><strong>SQL Server:</strong></p>

<p>Watch out for parameter sniffing in stored procedures by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#68183">AlexCuse</a>.</p>

<p>Beware of dynamically growing the database by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67660">Martin Clarke</a>.</p>

<p>Use Profiler to find any queries/stored procedures taking longer then 100 milliseconds by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67598">BradO</a>.</p>

<p>Increase transaction timeout by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67438">avgbody</a>.</p>

<p>Convert dynamic stored procedures into static ones by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67438">avgbody</a>.</p>

<p>Check how busy the server is by <a href="https://stackoverflow.com/questions/67366/a-checklist-for-fixing-net-apps-to-sql-server-timeout-problems-and-improve-exec#67387">Jay Shepherd</a>.</p>

[{'answer_id': 67381, 'author': 'dpollock', 'author_id': 7884, 'author_profile': 'https://Stackoverflow.com/users/7884', 'pm_score': 1, 'selected': False, 'text': '<p>First and foremost - Check the actual query being ran. I use SQL Server Profiler as I setup through my program and check that all my queries are using correct joins and referencing keys when I can.</p>\n'}, {'answer_id': 67387, 'author': 'Jay Shepherd', 'author_id': 7511, 'author_profile': 'https://Stackoverflow.com/users/7511', 'pm_score': 0, 'selected': False, 'text': "<p>A few quick ones...</p>\n\n<ul>\n<li>Check Processor use of server to see if it's just too busy</li>\n<li>Look for blocking/locking going on with the Activity monitor</li>\n<li>Network issues/performance</li>\n</ul>\n"}, {'answer_id': 67414, 'author': 'Asaf R', 'author_id': 6827, 'author_profile': 'https://Stackoverflow.com/users/6827', 'pm_score': 2, 'selected': False, 'text': '<p>A weird "solution" for complaints on long response time is to have a more interesting progress bar. Meaning, work on the user\'s feeling. One example is the Windows Vista wait icon. That fast rotating circle gives the feeling things are going faster. Google uses the same trick on Android (at least the version I\'ve seen).</p>\n\n<p>However, I suggest trying to address the technical problem first, and working on human behavior only when you\'re out of choices.</p>\n'}, {'answer_id': 67438, 'author': 'avgbody', 'author_id': 8737, 'author_profile': 'https://Stackoverflow.com/users/8737', 'pm_score': 3, 'selected': False, 'text': '<p>In the past some of my solutions have been:</p>\n\n<ol>\n<li><p>Fix the default time out settings of the sqlcommand:</p>\n\n<p>Dim myCommand As New SqlCommand("[dbo].[spSetUserPreferences]", myConnection)</p>\n\n<p>myCommand.CommandType = CommandType.StoredProcedure</p>\n\n<p><strong>myCommand.CommandTimeout = 120</strong></p></li>\n<li><p>Increase connection timeout string:</p>\n\n<p>Data Source=mydatabase;Initial Catalog=Match;Persist Security Info=True;User ID=User;Password=password;<strong>Connection Timeout=120</strong></p></li>\n<li><p>Increase transaction time-out in sql-server 2005</p>\n\n<p>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. </p></li>\n<li><p>Convert dynamic stored procedures into static ones</p></li>\n<li><p>Make the code call a stored procedure instead of writing an inline sql statement in the code.</p></li>\n</ol>\n'}, {'answer_id': 67441, 'author': 'Rob Gray', 'author_id': 5691, 'author_profile': 'https://Stackoverflow.com/users/5691', 'pm_score': 0, 'selected': False, 'text': '<p>Run Profiler to measure the execution time of your queries.<br>\nCheck application logging for any deadlocks.</p>\n'}, {'answer_id': 67598, 'author': 'Brad Osterloo', 'author_id': 9162, 'author_profile': 'https://Stackoverflow.com/users/9162', 'pm_score': 0, 'selected': False, 'text': '<p>I like using <a href="http://msdn.microsoft.com/en-us/library/ms181091.aspx" rel="nofollow noreferrer">SQL Server Profiler</a> as well.  I like to setup a trace on a client site on their database server for a good 15-30 minute chunk of time in the midst of the business day and log all queries/stored procs with an duration > 100 milliseconds. That\'s my criteria anyway for "long-running" queries.</p>\n'}, {'answer_id': 67660, 'author': 'Martin Clarke', 'author_id': 2422, 'author_profile': 'https://Stackoverflow.com/users/2422', 'pm_score': 0, 'selected': False, 'text': "<p>Weird one that applied to SQL Server 2000 that might still apply today:</p>\n\n<p>Make sure that you aren't trying to dynamically grow the database in production. There comes a point where the amount of time it takes to allocate that extra space and your normal load running will cause your queries to timeout (and the growth too!)</p>\n"}, {'answer_id': 68183, 'author': 'AlexCuse', 'author_id': 794, 'author_profile': 'https://Stackoverflow.com/users/794', 'pm_score': 2, 'selected': False, 'text': '<p>Are you using stored procedures?  If so you should watch out for parameter sniffing.  In certain situations this can make for some very long running queries.  Some reading:</p>\n\n<p><a href="http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx</a></p>\n\n<p><a href="http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx</a></p>\n'}]
['https://Stackoverflow.com/questions/67366', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/8737/']