title: SQL CPU peaks with SqlDataAdapter.Fill Command
body: <p>I've noticed some peculiar behaviour getting SQL data into a c# application.  I used a dataset xsd but this began to time out.  I then changed my approach as I already had a class which would generate and return a datatable for other operations I tried that.  This too timed out.</p>
  
  <p>I had opened the Activity Monitor in SSMS to get an idea of what was happening in SQL when the code ran and noticed that which ever way I run it the fill command casuses the SQL Sever to peak at 100% CPU and stay there until the command is cancelled.  This is a good server with plenty of oomph 240Ghz processors and 30GB RAM .  The query is not exactly zippy but returns 100k rows in under 3 seconds in SSMS.</p>
  
  <p>Here is my code for the dataset:</p>
  
  <pre><code>        public DataTable UKDataRefresh ()
      {
          UKREFRESH.UKNewContactsDataTable dt = 
              new UKREFRESH.UKNewContactsDataTable();
          UKREFRESHTableAdapters.UKNewContactsTableAdapter ta = 
              new UKREFRESHTableAdapters.UKNewContactsTableAdapter();
  
          ta.Connection.ConnectionString += ";Password = xxxxxx;";
  
          try
          {
          ta.Fill(dt, global::SilverPopTransfer.Properties.Settings.Default.UKDATELASTACTION);
  
          }
          catch (SqlException )
          {
  
              throw;
          }
  
          return dt;
  
      }
  </code></pre>
  
  <p>Here is my code for building it on the fly:</p>
  
  <pre><code>public DataTableOperations (string strconnection, string strSelect,string tablename)
  {
          SqlConnection c = new SqlConnection(strconnection);
          connection = c;
          SqlDataAdapter da = new SqlDataAdapter(strSelect, connection);
          DataSet ds = new DataSet();
          //added this to see what would happen.
          da.SelectCommand.CommandTimeout = 0;
          connection.Open();
          da.Fill(ds, tablename);
          connection.Close();
          Datatable = ds.Tables[tablename];
          _disposed = false;
      }
  </code></pre>
  
  <p>Im looking for clues as to what might cause the problem, not a full solution.<br>
  Incidentally I ran a similar function in a pre-existing console application before posting and it connected and ran without error:  the query was almost identical.</p>
  
  <p>The sole difference is that for the pre-existing application I use Integrated security but in this case I specify the user and password.  I <strong>have</strong> confirmed the login credentials.</p>