14 Replies Latest reply on Jul 25, 2016 9:07 AM by DanJones

    query timeouts linked to w3wp usage

    DanJones Apprentice

      Since upgrading our dev environment from 7.8.3 to 2016.1 I am having lots and lots of issues with query timeouts, (the system is basically unusable)

      it seems if the w3wp (iis worker) process goes over 900000 k the queries stop functioning,

       

      They will work again if I end the process (or restart iis) until the process again hits that limit, which is about 40 seconds.

       

      Has anyone had any experience with these timeouts since upgrading?

        • 1. Re: query timeouts linked to w3wp usage
          Paul Hyman Expert

          Hi Dan

           

          Have you got the latest hotfix applied ?

          Also what is the command timeout value set.

          You can see this in the tps.config file for the interface you are using.  i.e the screenshot seems to be webdesk, do you see a key like

          <add key="CommandTimeout" value="60000" />

          Thanks

          Paul

          • 2. Re: query timeouts linked to w3wp usage
            DanJones Apprentice

            I have just applied the latest hotfix and the timeout is correct in the tps.config, however I still get the error

            • 3. Re: query timeouts linked to w3wp usage
              Paul Hyman Expert

              HI Dan

               

              Are you getting any other errors in the event logs ?

              Do you know what SQL it is trying to run before the timeout ?  You can find this out by putting on the Database access trace on from configurationcenter.  set it too "All".

               

              Also could you please check the indexes health, you should be able to use the script from How to check Database fragmentation

               

               

              Thanks

              Paul

              • 4. Re: query timeouts linked to w3wp usage
                avasile Apprentice

                I currently have a ticket open with support regarding this same issue. I experienced it with 2016.1, we never went live with that version and then upgraded to 2016.2 and are still experiencing it. I have been able to pin point it to specific searches too. So for example, in an Incident Search if I search "activate" in the description it will error out every time. However, I can search "activation" - or I can search "activate" with a smaller date range with no issues. The time out happens at 30 seconds.

                 

                If we run the query directly in the database it does pull the records and takes 34 seconds. We have tried increasing the timeout period in iis (which was set to 120 and then we increased to 500 - but it was timing out at 30 so this had no effect either way) as well as in some config file (I forgot the name of it but it did not help either.) I have a call scheduled with support here in just a few minutes and if I get any closer, will definitely update thread. Glad to know I am not the only one.

                • 5. Re: query timeouts linked to w3wp usage
                  Paul Hyman Expert

                  30 seconds direct in the SQL seems a lot could you please let me know what the sql you are executing ?

                  also could you give some details of the execution plan that is used ?

                   

                  Thanks

                  Paul

                  • 6. Re: query timeouts linked to w3wp usage
                    DanJones Apprentice

                    I seem to have lots of errors, depending on server

                    Web Server 1:

                     

                    Web Server 2

                     

                    (all the same)

                     

                    Application Server

                     

                     

                    The SQL Trace provides the following information

                     

                     

                     

                    table_nameindex_idpartition_numberavg_fragmentation_in_percentfragment_countpage_count
                    cf_config_item1199.0486318921892
                    im_incident1195.39877315326
                    im_incident_assignment1199.2592664796480
                    im_incident_attachment1198.2906117117
                    im_incident_closure1194.444443636
                    im_incident_esc_point1197.47899119119
                    im_incident_note1196.20098796816
                    im_incident_resolution1196.551728687
                    im_reminder1199.151344171241713
                    im_task_reminder1190.909094244
                    km_article11882425
                    km_knowledge_message1198.78788165165
                    lc_action1197.260277373
                    lc_action_value1197.34513225226
                    lc_object_template_value11901920
                    lc_transition1191.666672424
                    lc_transition_step1197.85408233233
                    md_attribute_behaviour1195.23812121
                    md_attribute_template1195.833332424
                    md_attribute_type1197.97688686692
                    md_business_function1191.666672424
                    md_class_type1198.41276363
                    md_database_column1197.4359156156
                    md_foreign_key1198.305085959
                    md_foreign_key_column11985050
                    md_form_description1178.048783441
                    md_localised_resource1199.5848721682168
                    md_privileged_item1195.454556666
                    md_query_attribute1196.66667150150
                    md_query_condition1188.888893536
                    md_query_template1193.939393333
                    pm_process1196.15912713729
                    pm_process_assignment1199.152472194621946
                    pm_process_workload1196.93252162163
                    rm_reminder1198.245615757
                    Segment1155.555562236
                    sysjobhistory1151.428572035
                    sysmail_log1115.555561245
                    tps_application_setting1177.358494553
                    tps_application_user_setting1167.647062434
                    tps_audit_trail1199.18853209196209251
                    tps_end_user1198.24561114114
                    tps_html_image1198.648657474
                    tps_password1198.57143280280
                    tps_phone_number1199.06323854854
                    tps_privilege1197.85408233233
                    tps_scheduled_result1198.4556259259
                    tps_scheduled_result_detail1199.143411809518095
                    tps_user1199.011321232124
                    tps_user_group1178.08219286365
                    tps_user_message1197.8662324112437
                    tps_user_message_recipient1198.4125125
                    tps_user_network_login1198.52941136136
                    tps_user_role1183.75451233277
                    ui_dashboard1192.1568697102
                    ui_form1194.285713435
                    ui_form_handler1196.842119595
                    ui_form_item1196.9529114101444
                    ui_form_item_handler1199.0534517961796
                    ui_personalised_dashboard1196.923086465
                    ui_shortcut_item1198.148155454
                    usr_ticoordinatorassignment1199.1701966286628
                    usr_workstation1198.78604659659
                    • 7. Re: query timeouts linked to w3wp usage
                      Paul Hyman Expert

                      Hi Dan

                       

                      To me that looks like you might need to ask your DBA about the maintenance plan and reindexing.

                       

                      Thanks

                      Paul

                      • 8. Re: query timeouts linked to w3wp usage
                        avasile Apprentice

                        COUNT(*) FROM pm_process T1 INNER JOIN im_incident T2 ON T2.pm_guid=T1.pm_guid LEFT OUTER JOIN tps_user T3 ON (T1.pm_raise_user_guid=T3.tps_guid) INNER JOIN lc_status T4 ON (T1.pm_status_guid=T4.lc_guid) LEFT OUTER JOIN im_incident_category T5 ON (T2.im_category_guid=T5.im_guid) LEFT OUTER JOIN im_incident_assignment T6 ON (T2.im_current_assignment_guid=T6.pm_guid) LEFT OUTER JOIN pm_process_assignment T7 ON T7.pm_guid=T6.pm_guid WHERE T1.pm_description '%activate%'

                         

                         

                        Just re-ran it and it took 53 seconds this time but did return results. In the WebDesk it times out at 30 seconds. Our next step is having our DBA remove some additional indexes. However, I'm just not confident that this is going to be a database issue since we have these same indexes/setup in our current environment on 7.7.3 and don't experience these issues.

                        • 9. Re: query timeouts linked to w3wp usage
                          DanJones Apprentice

                          Currently Reindexing, I will let you know how it goes.

                          • 10. Re: query timeouts linked to w3wp usage
                            avasile Apprentice

                            We rebuilt the indexes, dropped 6 of the 18w e had and added a daily maintenance job. Now searching “activate” returns results after 18 seconds (whereas it was not returning any). However, when I test searched “license” it still times out.

                            • 11. Re: query timeouts linked to w3wp usage
                              Paul Hyman Expert

                              Hi

                               

                              So 18 seconds is a improvement in one sense.

                              What are you doing to generate that SQL.

                               

                              Could you please give details of this query, how is it built in ServiceDesk

                               

                              Thanks

                              Paul

                              • 12. Re: query timeouts linked to w3wp usage
                                DanJones Apprentice

                                My database has now been reindexed, however I still see the same issue. by the time I have logged in to webdesk the queries do not work.

                                 

                                Would uninstalling and reinstalling Servicedesk on the 3 servers (2x web + App) be a wise move?

                                • 13. Re: query timeouts linked to w3wp usage
                                  avasile Apprentice

                                  Have you made any progress Dan?

                                  • 14. Re: query timeouts linked to w3wp usage
                                    DanJones Apprentice

                                    Unfortunately not, I have reinstalled the 2 web and 1 application servers then run the upgrade against the db again and I still get the same issue, as soon as a user connects to one of the web servers the IIS spikes and all queries time out.

                                    If I restart iis I can just about log in and load the queries before the timeout occurs but any query after that is broken.