sql gira veloce in ssms lentamente in asp.net

Ho avuto questo problema per un paio di settimane. Il problema è che la query impiega 4-5 minuti per essere eseguita sul sito Web e al massimo 2 o 3 secondi per essere eseguita in ssms. Inoltre ho scoperto che dopo aver apportato una modifica a questa query, ad esempio aggiungendo la variabile customerId, verrà eseguita rapidamente sulla pagina Web ma il giorno successivo sarà nuovamente lenta. La query in questione è questa:

DECLARE @customerID INT SET @customerID = @CustID DECLARE @MyTable table( Iden int NOT NULL IDENTITY(1,1), ProductID int) INSERT INTO @MyTable(ProductID) SELECT P.ProductID FROM Product P WITH (NOLOCK) left join Compunix_ProductMMY cpmmy with (nolock) on p.ProductID = cpmmy.ProductID left join Compunix_CustomerMMY ccmmy with (nolock) on ccmmy.mmyid = cpmmy.mmyid WHERE P.Deleted=0 AND P.Published=1 and (ccmmy.customerid = @customerID OR cpmmy.productid IS NULL) SELECT c.Name, c.SeName, c.CategoryID FROM Category c WITH (NOLOCK) JOIN ProductCategory PC With (NOLOCK) ON C.CategoryID = PC.CategoryID JOIN @MyTable MT ON PC.ProductID=MT.ProductID WHERE C.Published = 1 GROUP BY c.Name, c.SeName, c.CategoryID ORDER BY c.Name 

Ho la stessa query in esecuzione su altri 2 siti che funzionano bene. L’unica differenza tra i siti è che vengono eseguiti su database diversi e il sito lento ha un po ‘più del doppio dei prodotti (54000 prodotti) su di esso rispetto all’altro 2. Tutti e tre i siti e i loro database sono ospitati sulla stessa macchina .

È probabile che tu stia colpendo un problema con lo sniffing dei parametri.

Suggerisco di leggere Slow nell’applicazione, veloce in SSMS? da Erland Sommarskog per avere una piena comprensione del problema (articolo lungo ma molto buono).

Dai un’occhiata a sys.dm_exec_sessions per la tua applicazione ASP.Net e per la tua sessione SSMS. Mi azzardo a indovinare che almeno una delle tue impostazioni SET è diversa. Questo può contribuire a piani diversi (in definitiva questo viene attribuito allo sniffing dei parametri) e il lato app di solito finisce peggio.

Vedi queste altre domande per molti più dettagli:

Procedura memorizzata lenta quando chiamata dal web, veloce da Management Studio

La procedura scade da ADO.NET ma non in SSMS

La query si interrompe quando viene eseguita dal Web, ma super veloce quando viene eseguita da SSMS

ADO .NET e SQL Server Management Studio – ADO si comporta in modo peggiore

Ho avuto lo stesso problema, nel mio caso era correlato a MARS, quindi ho rimosso MultipleActiveResultSets=True; dalla stringa di connessione e ora il tempo di esecuzione sono quasi gli stessi (differenza di 0,2 secondi rispetto a 4,5 s)

Nota: MARS = Set di risultati attivi multipli. Se si imposta questa proprietà su una stringa di connessione, è ansible eseguire più query sulla stessa connessione in modo interlacciato. È principalmente inteso a consentire all’utente di inviare dichiarazioni UPDATE mentre si sta iterando attraverso un set di risultati.

Per quello che vale, ogni tanto ci imbattiamo nello stesso problema; può essere una volta all’anno Puoi passare una buona settimana a leggere e digerire tutte quelle meravigliose risorse menzionate nelle altre risposte, oppure puoi fare quello che facciamo; interrompere e avviare SQL Server.

Funziona a meraviglia.

Abbiamo notato che questo problema si verifica in genere dopo varie mod di schema / sp / view che potrebbero non essere direttamente correlate al problema in questione.

Stai usando qualche orm ? Se stai utilizzando nhibernate , puoi abilitare la traccia del db in nhibernate e vedere quale potrebbe essere il problema. Di seguito sono riportati alcuni degli scenari che ho osservato in tali scenari:

  1. conversione implicita che porta a una ctriggers scelta del piano (si usa nvarchar invece di varchar ). È ansible osservare la mapping dei parametri nhibernate nei suoi registri.
  2. Mancanza di indice

Nhibernate usa log4net e devi solo aggiungere un appender come menzionato qui: https://devio.wordpress.com/2010/04/14/logging-sql-statements-generated-by-nhibernate/