VB.Net Application and Sql Server 2005, Best Practice

See more:

SQL-Server-2005

VB

SQL-Server

Hello everyone

I have an application having a server machine and around 150 clients. server machine is the domain controller as well as sql server.

Two connection strings are used in the application to create connection objects.

For normal quering
(1) Data Source=<servername>;Initial Catalog=<databasename>;User ID=admin;Pooling=True;Min Pool Size=5;Max Pool Size=200

For queries that take large time (only 3 reports are using this connection string)
(2) Data Source=<servername>;Initial Catalog=<databasename>;Integrated Security=False;User ID=admin;Asynchronous Processing=True;MultipleActiveResultSets=True;Connect Timeout=0

Problem:
Some times like once in a month, the application becomes very slow, even showing time-out. The stored procedures execute at normal speed at server machine during this time(all sps). This will solved as soon as the MS SQL Server 2005 is restarted.

This is a sample of code which i generally use to select data...
(soon after adapter.Fill(dt) is executing the application halts, where as the same stored procedure runs with ease.)

public DataTable CollectionByTerminal(DateTime from, DateTime to)
      {
          SqlCommand command = new SqlCommand("sp_name", connection);
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.AddWithValue("@From", from);
          command.Parameters.AddWithValue("@To", to);

          SqlDataAdapter adapter = new SqlDataAdapter(command);
          DataTable dt = new DataTable();

          try
          {
              adapter.Fill(dt);
              return dt;
          }
          catch (Exception error)
          {
              throw new Exception(error.Message);
          }
          finally
          {
              adapter.Dispose();
              CloseConnection();
          }
      }

Please help me in following areas
Is there any problem by using the same machine for database server as well as domain controller?
Is there any problem with the connection strings?
Is there any problems of using connection pooling, Asynchronous Processing or MultipleActiveResultSets?
Is there any problem in the calling function?

I believe answers to these questions will help me as well as thousands of new programmers

Thank you very much
Anoop