CODEWITHSUNDEEP
c#sql.netsql-servert-sql
Steven Borges
Steven Borges

Reputation: 411

SqlCommand execution slower if Management Studio Query Window is not open

I've been currently working on an application that is supposed to test certain scenarios while SQL Server is running, like for example, PC reset as if power went down.

The code is fairly simple, I declared a static class that contains the declaration of the SqlConnection:

public static void CreateConnection(string connectionString)
{
    connection = new SqlConnection (connectionString);
}

And two classes that both Open and Close the connection (closing irrelevant for this question but still inserted it for completion purposes):

public static void OpenConnection()
{
    if (connection == null) return;
    connection.Open ();
}

public static void CloseConnection()
{
    if (connection == null) return;
    connection.Close ();
}

The connectionString is nothing special either, just the Data Source, login and password, no more parameters set for that one.

So, the process of the application is the following.

  1. Start app
  2. Create Connection
  3. Open Connection
  4. Create Database if it does not exist
  5. Create Table if it does not exist and add an index to a column
  6. Start a Timer that executes a class that contains the INSERT statement
  7. Execute closing app (irrelevant)
  8. Closing event closes connection (irrelevant)
  9. App got closed (irrelevant)

Sorry for step 7 through 9, couldn't resist inserting them.

The Timer that is executing that very INSERT statement is running it everytime in a 50ms interval, and of course, from step 3 through 6 never did I close the app or connection, I just let it be open.

Now here comes the strange behavior which I found interesting and I decided to ask here since I couldn't find anything online about it: You can literally notice the stuttering in the counter I inserted on the GUI to track how fast it is being executed, example: 1 2 3 4 .. 5 6 7 8 .. 9 10 11 12....and so on, there is a visible stutter.

Now here comes the strange thing, as soon as I open Management Studio 2014, navigate to my Database and open the "New Query" window, the INSERT statement becomes blazing fast on my app! What previously had stuttering was running like a Golf MK2 with 800 horsepower, absolutely fluid like I was expecting the moment I wrote it, no more stuttering like explained above.

And after I close Management Studio, it goes back to stuttering until I do the same process of getting into New Query again. What could be the cause to it?

Oh, of course, here is my INSERT statement, sorry for the long read:

public static void InsertValues(string tableName, string dbName)
{
    string query = @"INSERT INTO " + dbName + ".dbo." + tableName + " VALUES ('" + Text + "','" + Date + "')";
    using (var command = new SqlCommand (query, connection)) {
        command.ExecuteNonQuery ();
    }
}

Upvotes: 2

Views: 137

Answers (3)

Steven Borges
Steven Borges

Reputation: 411

Thanks to cynic I managed to fix the stuttering by setting AUTO_CLOSE to OFF. I used the following query in case somebody needs it in the future:

EXECUTE sp_MSforeachdb
'
IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))
   EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')
'

Which I got from here.

Of course I only did that because I had several DBs, if you only need to do that to a particular one:

ALTER DATABASE DBNAME SET AUTO_CLOSE OFF

Upvotes: 0

cynic
cynic

Reputation: 5415

Set the AUTO_CLOSE database option to OFF using the ALTER DATABASE statement. The effect you're seeing may be caused by the database being closed after execution of your command due to the connection being cleanly closed, making it necessary to fully reopen the entire database for the subsequent execution of the command. Opening a query window opens and maintains a connection, thus preventing the automatic closing of the entire database.

Upvotes: 3

Vivek Nuna
Vivek Nuna

Reputation: 1

because Management Studio and SqlConnection have different SET defaults , a these set commands are automatically run to set up the execution environment. SQL Profiler can be used to monitor which SET commands are issued by both SSMS and .NET so you can find other differences. You can monitor the difference between these set commands by using Profiler.

You can try these Set Statements in your C# code like this and compare the results.

SqlCommand comm = new SqlCommand("SET ARITHABORT ON", connection)

Upvotes: 0

Related Questions