Large SQL script calls every 5min that is crashing IIS pool?

Question

Context:

I have a dozen of servers.

Each server have a IIS with a site that executes the following large SQL script every 5 minutes.

On some servers, the pool that hosts the site crash. The pool contains this site only.

I need to recycle the pool after each crash... with my hands currently.

So there is an issue with the site and, I think, with the large SQL script.

The C# code that calls the SQL script:

string root = AppDomain.CurrentDomain.BaseDirectory;
string script = File.ReadAllText(root + @"..\SGBD\select_user_from_all_bases.sql").Replace("$date", dtLastModif);
string connectionString = @"Data Source=(local);Integrated Security=SSPI";

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    var command = new SqlCommand(script, connection);
    var reader = command.ExecuteReader();

    var users = new List<UserModel>();

    while (reader.Read())
    {
        users.Add(new UserModel()
        {
            dbName = String.Format("{0}", reader[0]),
            idExternal = int.Parse(String.Format("{0}", reader[1])),
            firstname = String.Format("{0}", reader[2]),
            lastname = String.Format("{0}", reader[3]),
            login = String.Format("{0}", reader[4]),
            password = String.Format("{0}", reader[5]),
            dtContractStart = reader[6] != DBNull.Value ? (DateTime?)reader[6] : null,
            dtContractEnd = reader[7] != DBNull.Value ? (DateTime?)reader[7] : null,
            emailPro = String.Format("{0}", reader[8]),
            emailPerso = String.Format("{0}", reader[9])
        });
    }

    return users;
}

And the SQL script:

USE master

DECLARE db_names CURSOR FOR
    SELECT name FROM sysdatabases WHERE [name] LIKE 'FOO_%' AND [name] NOT LIKE 'FOO_TRAINING_%'

DECLARE @db_name NVARCHAR(100)
DECLARE @query NVARCHAR(MAX)
DECLARE @queryFinal NVARCHAR(MAX)
SET @query = ''
OPEN db_names 

FETCH NEXT FROM db_names INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @query = @query + 'SELECT ''' + @db_name + ''', id_salarie, nom, prenom, login COLLATE SQL_Latin1_General_CP1_CI_AS, password COLLATE SQL_Latin1_General_CP1_CI_AS, date_arrivee, date_depart, email COLLATE SQL_Latin1_General_CP1_CI_AS, persoMail COLLATE SQL_Latin1_General_CP1_CI_AS FROM [' + @db_name + '].dbo.utilisateurs WHERE dt_last_modif >= ''$date'' UNION '

    FETCH NEXT FROM db_names INTO @db_name

END

DEALLOCATE db_names 
SET @queryFinal = left(@query, len(@query)-6)
    EXEC sp_executesql @queryFinal

More information about servers:

• Server0 : 8 databases, 1050 users, no crash
• Server1 : 88 databases, 18954 users, crash often
• Server2 : 109 databases, 21897 users, crash often
• Server3 : 26 databases, 1612 users, no crash
• etc

Questions :

• What is the issue with the script ? Any idea how I can stop crashs ?
• And if no solution, how can I automatically recycle the pool?

Answer 1

Have you tried to make shure that the reader is cloesd after usage, too?

using(var reader = command.ExecuteReader()) { ...

I am not shure if the closed connection

using (var connection = new SqlConnection(connectionString))

takes care of the command and the reader resources.

Answer 2

I would do a few things here... if your problem is that persistent. First, I WOULD NOT generate one complete sql query trying to get data from all those tables all at once. Next, the queries are querying, and implied might be trying to LOCK the records associated with the query for POSSIBLE update... even though you are not probably going to be doing that.

I would add a WITH (NOLOCK) on the from tables.

select columns from yourTable WITH(NOLOCK) where...

This prevents any overhead with locking all the pages associated with the query.

Now, how to better handle your loop. Immediately BEFORE your fetch loop, I would create a temp table of the expected output results... something like

(unsure of column name lenghts for your structures...

create #C_TempResults
  ( fromDBName char(20), 
    id_salarie int,
    nom char(10),
    prenom char(10),
    login char(10),
    password char(10),
    date_arivee datetime,
    date_depart datetime,
    email char(60),
    persoMail char(60) );

then, in your loop where you are already cycling through all the tables you are querying, instead of building a concatenated SQL statement to execute at the end, just run ONE AT A TIME, and insert into the temp table like...

(same beginning to prepare your fetch cursor...)
BEGIN
       SET @query = 'INSERT INTO #C_TempResults '
                 + ' SELECT ''' + @db_name + ''' as fromDBName, id_salarie, nom, prenom, '
                       + 'login COLLATE SQL_Latin1_General_CP1_CI_AS, '
                       + 'password COLLATE SQL_Latin1_General_CP1_CI_AS, '
                       + 'date_arrivee, date_depart, '
                       + 'email COLLATE SQL_Latin1_General_CP1_CI_AS, '
                       + 'persoMail COLLATE SQL_Latin1_General_CP1_CI_AS '
                 +  'FROM [' + @db_name + '].dbo.utilisateurs WITH (NOLOCK) '
                 +  'WHERE dt_last_modif >= ''$date'' ';

        -- Run this single query now, get the data and release any "lock" resources
        EXEC sp_executesql @queryFinal

        -- now, get the next database to query from and continue
        FETCH NEXT FROM db_names INTO @db_name
END

DEALLOCATE db_names 

-- FINALLY, just run your select from the temp table that has everything all together...
select * from #C_TempResults;

-- and get rid of your "temp" table
drop table #C_TempResults;