Web Site Down every 1 on the month inexplicably

See more:

C#

SQL-Server

MVC

, +

Hi everyone and thanks for reading this question.

i searched with my team a lot but we reached a dead end on this problem.

Shortly
i have a web app tha renders charts that every 1th of the month get blocked...

The situation
The Server (temporary production):
- Disk space over 30 gb available
- CPU intel xeon (4 cores) => load 20-30%
- 8 gb Ram
- IIS 7.5 and Sql server in this machine.

The DB: SQL server 2008 R2
- the main table we use is filled every 15 minute by another web app that works fine,
- the table has 30 milion rows, 1.5 Gb of data space and 3 Gb of index space,
- the table is used in a stored procedure (filters and group by)

The WEBAPP : MVC 4.0 application (.NET fw 4.5)
- The view: has a chart (jqplot with multiple series) and ajax calls to the controller to get the data
- The model: contains a list of series and a the list of filters made by user
- The controller: execute the stored using EF 6.02 (imported stored procedure) and in case of exception, we make a standard sql command with parameters.
- connection string : server=XXX\YYY;uid=WWW;pwd=ZZZ;database=KKK;MultipleActiveResultSets=True;Connection Timeout=180; => note we made an override in the EF constructor to set the timeout in the connection string also to the dbcontext.

The Users
the users that can access the app are in different part of the world so they can't be online at the same time,
in the worse scenario we can have 300-500 active users at the same time


The problem
every 1th of the month (probably because of high load) the charts stop working, the web app works fine (login logout navigation) but the ajax calls return error due to sql timeout
NOTE : at the same time, when the app is working bad, if we execute the same stored procedure on sql server management studio the query goes well (and fast about 2 seconds) so we think it is not a sql server problem.
The server cpu/ram and disk space seems normal.

What temporary fixes the problem :
-Alter stored procedure, if we do alter on the stored procedure the ajax calss begin to work normally.

What does not fix the problem :
- recycle the pool
- restart the webapp => so the number of user may be is not
- iis reset
- SERVER restar (so sql restart and iis restart)

what we think
may be, the problem is related to the way the web app connects to the database or to the number of multiple connection that ADO can handle.
anyone can give us a hint to solve this strange issue or at least to find the right path to focus our attention?

thanks again

_____________________________________________________
UPDATE 1 : LOG

we use log4net, the log said nothing, simply sql timeout
BUT with the profiler attached, the query (the stored) launched by the web application (with the process connected to the web app) does NOT exists..
and infact running the same query in sql man. studio works like a charme in 2 seconds..


_____________________________________________________
UPDATE 2 : the stored

ALTER PROC [dbo].[GetRegistrazioni]( @startDate datetime,  @endDate datetime, @idNegozio int)
AS
BEGIN
SET NOCOUNT ON;

WITH CTEOrario_base AS
(
            /*#####################################################################################################*/
            SELECT      [IdNegozio]
                        ,REPLACE(CONVERT(varchar(8),[OrarioApertura1], 14), ':', '') as OraApertura1
                        ,REPLACE(CONVERT(varchar(8),[OrarioChiusura1], 14), ':', '') as OraChiusura1
                        ,REPLACE(CONVERT(varchar(8),[OrarioApertura2], 14), ':', '') as OraApertura2
                        ,REPLACE(CONVERT(varchar(8),[OrarioChiusura2], 14), ':', '') as OraChiusura2
                        ,[GiornoSettimana]
            FROM        [NegozioOrario] WITH (NOLOCK)
            WHERE       IdNegozio = @idNegozio
            /*#####################################################################################################*/
)
,CTEOrario as 
(
            /*#####################################################################################################*/   
            SELECT      IdNegozio,  
                        OraApertura1,
                        CASE WHEN OraChiusura1  = '000000' THEN OraChiusura2 ELSE OraChiusura1 END As OraChiusura1, 
                        CASE WHEN OraApertura2 = '000000' THEN OraApertura1 ELSE OraApertura2 END As OraApertura2,
                        OraChiusura2,
                        GiornoSettimana
            FROM        CTEOrario_base
            /*#####################################################################################################*/

)
,CTENegoziPeriferiche as 
(
            /*#####################################################################################################*/
            SELECT      p.IdPeriferica
                        ,p.GUID
                        ,n.CodiceNegozio
                        ,n.IdNegozio
                        ,t.PositionDescription
                        ,t.PositionId
                        ,Coalesce(dit.DataInizioTrasmissione,'19000101') as DataInizioTrasmissioni
            FROM        Periferiche p WITH (NOLOCK)
            INNER JOIN  Negozi n WITH (NOLOCK) ON n.CodiceNegozio = p.CodiceNegozio
            LEFT JOIN   NegozioDataInizioTrasmissioni dit WITH (NOLOCK) on dit.IdNegozio = n.IdNegozio and dit.IdPeriferica = p.idPeriferica
            LEFT JOIN   DeviceTranscodification t WITH (NOLOCK) on t.IdPeriferica = p.IdPeriferica
            WHERE       n.IdNegozio = @idNegozio
            /*#####################################################################################################*/
) 
, CTE_In as 
(

                SELECT      ISNULL(i.IdPeriferica,id.IdPeriferica) as IdPeriferica, 
                            ISNULL(i.BDate,id.BDate) as BDate, 
                            ISNULL(i.TipoRegistrazione,id.TipoRegistrazione) as TipoRegistrazione, 
                            ISNULL(i.DataRegistrazione_Inizio,id.DataRegistrazione_Inizio) as DataRegistrazione_Inizio, 
                            (ISNULL(i.NumeroEventi,0) - ISNULL(id.NumeroEventi, 0)) as NumeroEventi,
                            REPLACE(CONVERT(varchar(8),ISNULL(i.DataRegistrazione_Inizio,id.DataRegistrazione_Inizio), 14), ':', '') as ora

                FROM        (
                                select  x.IdPeriferica
                                        ,x.BDate
                                        ,x.TipoRegistrazione
                                        ,x.DataRegistrazione_Inizio
                                        ,x.NumeroEventi
                                from    CounterTable x WITH (NOLOCK)
                                where   x.TipoRegistrazione = 3
                                and     x.DataRegistrazione_Inizio between @startDate and @endDate  
                            ) i

                FULL JOIN   (   
                                select  x.IdPeriferica
                                        ,x.BDate
                                        ,x.TipoRegistrazione
                                        ,x.DataRegistrazione_Inizio
                                        ,x.NumeroEventi
                                from    CounterTable x WITH (NOLOCK)
                                where   x.TipoRegistrazione = 5
                                and     x.DataRegistrazione_Inizio between @startDate and @endDate  
                            ) id    
                            ON  i.IdPeriferica = id.IdPeriferica
                            AND i.DataRegistrazione_Inizio = id.DataRegistrazione_Inizio
            /*#####################################################################################################*/
)
,CTE1 as
(
            /*#####################################################################################################*/
                SELECT  
                    min(c.GUID) as DeviceId,
                    min(c.CodiceNegozio)  as CodiceNegozio,
                    f.TipoRegistrazione as TipoEvento,
                    cast(f.DataRegistrazione_Inizio as date) as Data,
                    DATEPART(HOUR,f.DataRegistrazione_Inizio) as Ora,
                    SUM(f.NumeroEventi) as NumeroEventi,
                    c.PositionId,
                    Min(c.PositionDescription) as PositionDescription

                FROM CTE_In f
                INNER JOIN CTENegoziPeriferiche c on f.IdPeriferica = c.IdPeriferica
                INNER JOIN CTEOrario o ON o.IdNegozio = c.IdNegozio
                AND o.GiornoSettimana = DATEPART(weekday, f.DataRegistrazione_Inizio)
                AND 
                (
                    (
                        f.ora >= o.OraApertura1 
                        AND 
                        f.ora < o.OraChiusura1 
                    )
                    OR 
                    (
                        f.ora >= o.OraApertura2 
                        AND
                        f.ora < o.OraChiusura2 
                    )
                )   

                WHERE 
                c.IdNegozio = @idNegozio        
                AND f.DataRegistrazione_Inizio > c.DataInizioTrasmissioni
                AND f.DataRegistrazione_Inizio between @startDate and @endDate      


                GROUP BY 
                    c.IdPeriferica,
                    o.IdNegozio,
                    f.TipoRegistrazione,
                    c.PositionId,
                    CAST(f.DataRegistrazione_Inizio as Date),
                    DATEPART(HOUR,f.DataRegistrazione_Inizio)
            /*#####################################################################################################*/
)
SELECT  Data
        ,CAST(Ora as INT) as Ora
        ,CAST(SUM(NumeroEventi) as INT) as NumeroEventi
        ,min(PositionDescription) as PositionDescription
FROM    CTE1
Group By Data, Ora, PositionId
order by Data, Ora, min(PositionDescription)
END