sql server - Azure SQL frequent connection timeouts -

-

we're running web app (2 instances) on azure, backed sql azure database. @ given time there 50-150 users using website. database runs @ s2 performance level. dtu around 20% on average.

however, few times every day hundreds of errors in logs timeouts, this:

an error occurred while executing command definition. see inner exception details.

the wait operation timed out.

timeout expired. timeout period elapsed prior completion of operation or server not responding. failure occurred while attempting connect routing destination. duration spent while attempting connect original server - [pre-login] initialization=1; handshake=21; [login] initialization=0; authentication=0; [post-login] complete=1;

we're using ef6 queries default command timeout. i've configured execution strategy:

setexecutionstrategy("system.data.sqlclient",              () => new sqlazureexecutionstrategy(10, timespan.fromseconds(15)));

the database (about 15gb total) heavily indexed. these errors occur on place, dozens hundreds within 1-2 minutes.

what steps can take prevent happening?

the fact happens in 1-2 minutes might mean burst in activity or process might locking tables.

if dtu during times @ 20% not cpu issue, can find bottlenecks running query on db:

select top 10  total_worker_time/execution_count avg_cpu_time         ,execution_count         ,total_elapsed_time/execution_count avg_run_time         ,(select               substring(text,statement_start_offset/2,(case                                                            when statement_end_offset = -1 len(convert(nvarchar(max), text)) * 2                                                             else statement_end_offset                                                         end -statement_start_offset)/2                        ) sys.dm_exec_sql_text(sql_handle)          ) query_text  sys.dm_exec_query_stats  order avg_cpu_time desc

even if db heavily indexed, indexes fragmented, i'd advice running check current fragmentation:

select a.*,b.averagefragmentation  (               select tbl.name [table_name], tbl.object_id, i.name [name], i.index_id, cast(case i.index_id when 1 1 else 0 end bit) [isclustered],  cast(case when i.type=3 1 else 0 end bit) [isxmlindex], cast(case when i.type=4 1 else 0 end bit) [isspatialindex]                               sys.tables tbl                inner join sys.indexes on (i.index_id > 0 , i.is_hypothetical = 0) , (i.object_id=tbl.object_id))a inner join (               select tbl.object_id, i.index_id, fi.avg_fragmentation_in_percent [averagefragmentation]                               sys.tables tbl                inner join sys.indexes on (i.index_id > 0 , i.is_hypothetical = 0) , (i.object_id=tbl.object_id)                inner join sys.dm_db_index_physical_stats(db_id(), null, null, null, 'limited') fi on fi.object_id=cast(i.object_id int) , fi.index_id=cast(i.index_id int) )b on a.object_id=b.object_id , a.index_id=b.index_id order averagefragmentation desc

you can use azure automation schedule automatic rebuilding of fragmented indexes, see answer at: why azure sql database indexes still fragmented?


Comments

Post a Comment

Popular posts from this blog

java - Run spring boot application error: Cannot instantiate interface org.springframework.context.ApplicationListener -

-
i have spring project , try make use spring boot , run @ embadded tomcat following : https://spring.io/guides/gs/rest-service/ this application //@configuration //@enableaspectjautoproxy @springbootapplication @componentscan(basepackages = "gux.prome") public class application { public static void main(string[] args) { springapplication.run(application.class, args); } } if use maven command: mvn spring-boot:run , project starts fine, need debug , run main method @ intelij, exception occurs: exception in thread "main" java.lang.illegalargumentexception: cannot instantiate interface org.springframework.context.applicationlistener : org.springframework.boot.logging.classpathloggingapplicationlistener @ org.springframework.boot.springapplication.createspringfactoriesinstances(springapplication.java:414) @ org.springframework.boot.springapplication.getspringfactoriesinstances(springapplication.java:394) @ org.springframework.bo...
Read more

reactjs - React router and this.props.children - how to pass state to this.props.children -

-
i'm using react-router first time , don't know how think in yet. here's how i'm loading components in nested routes. entry point .js reactdom.render( <router history={hashhistory} > <route path="/" component={app}> <route path="models" component={content}> </route> </router>, document.getelementbyid('app') ); app.js render: function() { return ( <div> <header /> {this.props.children} </div> ); } so child of app content component sent in. i'm using flux , app.js has state , listens changes, don't know how pass state down this.props.children. before using react-router app.js defines children explicitly, passing state natural don't see how now. using couple of react helper methods can add state, props , whatever else this.props.children render: function() { var children = react.ch...
Read more

Excel VBA "Microsoft Windows Common Controls 6.0 (SP6)" Location Changes -

-
i have excel workbook used generate quotations customers. there main page user inputs data, , there buttons generate various pdfs , send them customer. however, 1 user consistently getting error preventing generating , sending pdfs. i beleive "microsoft windows common controls 6.0 (sp6)". it's installed on users' computers. on mine, location "c:\windows\syswow64\mscomctl.ocx" on others "c:\windows\system32\mscomctl.ocx" the user experiencing issues on 32-bit machine, "mscomctl.ocx" file should in system32. have manually put in there, , pointed common controls reference in vba it. issue seems keep reverting looking in syswow64. since folder doesn't exist, can't find it, , can't produce pdfs. i thought perhaps issue user running older version of office (2007) there users on 2013 , 2016. thought perhaps mixture of 64-bit , 32-bit users. user gets sent workbook other users (as quotation process goes further, gets passed ...
Read more