c# - ADO .NET vs. SQL Server Management Studio - ADO performs worse

Question

I'm running the same commands in ADO.NET C# and Sql Server Management studio. The SQL that runs via C# performs significantly worse - memory usage is worse (using up all available memory) and thus causing the database executing time to increase. The management studio isn't perfect (it too causes sql server to use up memory) but it's not as bad as via ADO.NET.

I am running: Windows 7, Sql Server 2008 R2, 10.50.1600. C# .NET 3.5. Sql Server management Studio 2008 R2. All programs and databases are on my local dev machine.

The SQL I am running is 40 create view's and 40 create unique indexes on 2 database's. I need to do this on the fly as we are running a database compare between 2 databases (for reasons that aren't relevant we need to compare views and not tables). And since performance is an issue we cannot leave the views and indexes around all the time.

The SQL looks like this:

create view [dbo].[view_datacompare_2011106] with schemabinding as ( 
SELECT t.[ID], t.[Column1], t.[Column2], t.[Column3],  FROM dbo.Table t WHERE t.[ID] in ('1','2','3','4') )
go
create unique clustered index [index_datacompare_2011106] on [dbo].[view_datacompare_2011106] (ID)
go
...

The only difference is that the C# code does not call Go. Each create cmd is wrapped up in a using statement and called via ExecuteNonQuery() e.g.

using (SqlCommand cmd = new SqlCommand(sql, this.connectionActualDb))
{
cmd.CommandTimeout = Int32.Parse(SqlResources.TimeoutSeconds);
cmd.ExecuteNonQuery();
}

P.S. SET ARITHABORT must be ON when you are creating or changing indexes on computed columns or indexed views.

Answer 1

Use Waits and Queues methodology to investigate the performance bottleneck. You'll find the root cause and then we can advice accordingly. Most likely your C# application runs into concurrency due to locks, very likely held by the application itself. Typically one blames plan changes due to parameter sniffing, as in Slow in the Application, Fast in SSMS, but with DDL statements this is unlikely.