.NET VB: Timeout expired SQL

[holygame]

Mijn vraag
Sinds een paar maanden (heel) langzaam mij te verdiepen in .Net en VB. Een beetje gedwongen
Nu is het probleem dat sinds een aantal maanden er problemen zijn met de website, heel langzaam laden van pagina's. Gelukkig hebben we error reporting en daar valt op, time outs en thread aborted meldingen.
De website server en de database server zijn gehost bij AWS en zitten in dezelfde region.

Ik pak even een voorbeeld naar voren, dit is in mijn ogen een heel erg basic query en toch produceert dit een timeout. Is er iemand die mij de juiste weg op kan wijzen?

Voorbeeld code:

code:

                If CustomerDiscGroup <> "" And ItemDiscGroup <> "" Then
                    cmd = New StringBuilder
                    cmd.Append("SELECT ISNULL([Line Discount %], 0) AS Discount, [Code1] AS TC, [Starting Date] AS Sdate, [Ending Date] AS Edate, GetDate() AS DN ")
                    cmd.Append(" FROM [Sales_Discount] ")
                    cmd.Append(" WHERE (RTRIM(Type) = 'Item Disc. Group') AND (RTRIM([Sales Type]) = 'Customer Disc. Group')")
                    cmd.Append(" AND ([Sales Code] = '@CustomerDiscGroup') AND ([Code1] = '@ItemDiscGroup')")
                    cmd.Append(" AND ([Starting Date] <= getDate()) AND ([Ending Date] IS NULL OR GetDate() <= [Ending Date])")

                    Using con As New SqlConnection(std.Navision)
                        Using SQLcmd As New SqlCommand(cmd.ToString(), con)
                            SQLcmd.Parameters.Add("@CustomerDiscGroup", SqlDbType.VarChar, 20)
                            SQLcmd.Parameters("@CustomerDiscGroup").Value = CustomerDiscGroup
                            SQLcmd.Parameters.Add("@ItemDiscGroup", SqlDbType.NVarChar)
                            SQLcmd.Parameters("@ItemDiscGroup").Value = ItemDiscGroup
                            Try
                                con.Open()
                                Discount = CDec(SQLcmd.ExecuteScalar)
                            Catch ex As Exception
                                std.ErrorEmail("Products.vb, line 2319: Could not get right discount from Navision Error<br />" & ex.ToString() & "<br />" & cmd.ToString() & "<br />" & SQLcmd.ToString())
                            Finally
                                con.Close()
                            End Try

                        End Using
                    End Using
                Else

Voorbeeld error:

code:

Products.vb, line 2319: Could not get right discount from Navision Error
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at Products.GetPrice(ItemType ProductType, Int32 ID, String Product, PriceType RetPrice, String CNN, Int32 CID, String CustomerDiscGroup, String ItemDiscGroup, Decimal UnitPrice)

Relevante software en hardware die ik gebruik
...

Wat ik al gevonden of geprobeerd heb
Dit is de time out in web.config, zo hoog gezet dat dit het niet meer kan zijn, denk ik.
<httpRuntime executionTimeout="3000" maxRequestLength="1048576" />

Beide servers zijn een keer herstart, schijfruimte is voldoende, geen rare event viewer meldingen.
MSSQL server versie: 10.50.2789.0.v1
Webserver 2012 R2, .NET v2.0

[Starck]

Zie je in de Query Analyzer van SQL Management studio ook dat de query heel lang bezig is? En als je de query handmatig doet in SQL Management studio hoe snel is hij dan.

EDIT: En voeg ook eens een with (nolock) toe aan de select query

FROM [Sales_Discount] with (nolock)

[ Voor 24% gewijzigd door Starck op 19-12-2018 10:58 ]

[holygame]

Starck schreef op woensdag 19 december 2018 @ 10:55:
Zie je in de Query Analyzer van SQL Management studio ook dat de query heel lang bezig is? En als je de query handmatig doet in SQL Management studio hoe snel is hij dan.

EDIT: En voeg ook eens een with (nolock) toe aan de select query

FROM [Sales_Discount] with (nolock)

De query varieert in snelheden, 13 sec, 20 sec etc.
Als ik een count doe op de tabel laat dit zien 4015 regels waarin hij zoekt.

[Starck]

De exception die je krijgt, komt van een SQL timeout. De timeout die je zet in de webconfig is de http timeout, dat is niet de goede. Je moet de timeout zetten op de connectionstring in de webconfig.

En die query duurt 13 tot 20 sec als je hem handmatig draait in SQL Management studio? Of time je het totdat je de exception krijgt.

[holygame]

Starck schreef op woensdag 19 december 2018 @ 11:11:
De exception die je krijgt, komt van een SQL timeout. De timeout die je zet in de webconfig is de http timeout, dat is niet de goede. Je moet de timeout zetten op de connectionstring in de webconfig.

En die query duurt 13 tot 20 sec als je hem handmatig draait in SQL Management studio? Of time je het totdat je de exception krijgt.

Ja handmatig in SQL SMS v17.

In de web.config zit dit stukje
<connectionStrings>
<add name="Portal" connectionString="blabla" providerName="System.Data.SqlClient" />
</connectionStrings>

[Starck]

Voeg eens in het connectionString gedeelte de timeout toe:
;Connection Timeout=60

[CodeCaster]

Starck schreef op woensdag 19 december 2018 @ 11:29:
Voeg eens in het connectionString gedeelte de timeout toe:
;Connection Timeout=60

"Dokter, het doet pijn als ik hier druk".
- "Druk daar dan ook niet".

Het verhogen van de timeout is symptoombestrijding. Zo'n basale query mag op courante hardware geen 10-20 seconden duren.

Er zijn twee mogelijkheden: de tabel is groot en slecht geïndexeerd, of er loopt een ander proces dat de tabel lockt.

Voer exact deze query uit en toon het execution plan. Waarschijnlijk kun je met een paar handige indexes deze query in enkele milliseconden laten uitvoeren.

Tenzij de tabel dus door andere queries wordt gelockt, waarbij je inderdaad WITH(NOLOCK) zou kunnen gebruiken.

[holygame]

CodeCaster schreef op woensdag 19 december 2018 @ 11:37:
[...]

"Dokter, het doet pijn als ik hier druk".
- "Druk daar dan ook niet".

Het verhogen van de timeout is symptoombestrijding. Zo'n basale query mag op courante hardware geen 10-20 seconden duren.

Er zijn twee mogelijkheden: de tabel is groot en slecht geïndexeerd, of er loopt een ander proces dat de tabel lockt.

Voer exact deze query uit en toon het execution plan. Waarschijnlijk kun je met een paar handige indexes deze query in enkele milliseconden laten uitvoeren.

Tenzij de tabel dus door andere queries wordt gelockt, waarbij je inderdaad WITH(NOLOCK) zou kunnen gebruiken.

Hierbij het executie plan:

[Starck]

Het is dus zoals @CodeCaster al zei. Je hebt geen goede index. Dus SQL server moet een full table scan doen


EDIT: Als je altijd een datum gebruikt in de query, begin dan eens met een index op de start datum. En maak daarna nog eens een execution plan.

[ Voor 39% gewijzigd door Starck op 19-12-2018 12:01 ]

[holygame]

Bedankt beide

Ik heb de query nu aangepast, hij zoekt eerst where datums en dan de rest.
Als ik nu een executie plan uitvoer dan geeft ie geen missing index meer aan.
Hopen dat dit de performance ten goede zal komen, we gaan even testen.

[CodeCaster]

holygame schreef op woensdag 19 december 2018 @ 12:14:
Ik heb de query nu aangepast, hij zoekt eerst where datums en dan de rest.

Dat maakt in principe niet uit, de query optimizer kijkt zelf wat een handige volgorde is, op basis van diverse parameters (o.a. bestaande indexes).

Als ik nu een executie plan uitvoer dan geeft ie geen missing index meer aan.

Dat klopt, want deze query is nog niet vaak genoeg uitgevoerd. Missing indexes worden niet on demand geanalyseerd, maar door SQL Server zelf opgeslagen wanneer een query vaak genoeg wordt uitgevoerd en baat zou kunnen hebben bij indexes.

Je verandering zal dus geen merkbare performancewinst opleveren. Bekijk de vorige voorgestelde index, en maak die aan als die nuttig lijkt.

[holygame]

CodeCaster schreef op woensdag 19 december 2018 @ 12:42:
[...]

Dat maakt in principe niet uit, de query optimizer kijkt zelf wat een handige volgorde is, op basis van diverse parameters (o.a. bestaande indexes).


[...]

Dat klopt, want deze query is nog niet vaak genoeg uitgevoerd. Missing indexes worden niet on demand geanalyseerd, maar door SQL Server zelf opgeslagen wanneer een query vaak genoeg wordt uitgevoerd en baat zou kunnen hebben bij indexes.

Je verandering zal dus geen merkbare performancewinst opleveren. Bekijk de vorige voorgestelde index, en maak die aan als die nuttig lijkt.

Oh OK volgorde maakt niet uit

Ik heb een non-clustered index aangemaakt en net een beetje rond gevraagd hier op de vloer en het lijkt merkbaar sneller te zijn.