We have some performance troubles with a database of our biggest customer.
Foxhound did not report any problems. Usage of CPU(1-15%), Disk and Active requests (mostly around 5, very rare a unschelded task, very very rare a blocked connection).
When I open Performance Monitor I see some things that I do not understand:
1. In the tabpage "Statements" I see a slow query that should be very fast, there are 120 rows in the tabel. Strange is that there is a large Idle time. (see below a copy of the screen *1)
What does that Idle time mean and how can I prevent that large idle times?
- In the tabpage "Analysis" I see under section 'Is performance limited by workers?' a few times:
Worker-bound for x sec
However the mulitpogrammaging level is set between 24 and 80, and when I look in foxhound it is most of the times 24 with a low active requests.
How can I prevent that worker-boud?
In Foxhound I see nothing special at the same time. All seems to be ok.
*1)
Execute Statement Properties
============================
General
-------
Operation type: Execute Statement
Date: Today 8:38:39
Execution time: 4,19 sec
Connection: 729443 (WRO)
Statement timing:
Prepare statement < 1 ms
Execute statement < 1 ms
Fetch results < 1 ms
**Idle 4,19 sec**
Blocked 0 ms
Statement handle: 668839
Cursor handle: 79016
SQL
---
select "event_jobs"."nummer","event_jobs"."user","event_jobs"."sql","event_jobs"."status","event_jobs"."omschrijving","event_jobs"."usertxt","event_jobs"."result","event_jobs"."start","event_jobs"."stop","event_jobs"."scheduled_for","event_jobs"."window","event_jobs"."id",cast(cast("datediff"("second","start","stop") as integer)/60 as integer) as "minuten",cast("datediff"("second","start","stop") as integer)-(cast(cast("datediff"("second","start","stop") as integer)/60 as integer))*60 as "seconden" from "event_jobs" where("event_jobs"."status" <= 3) and("event_jobs"."user" = 160)
Plan
----
event_jobs<seq>{4}
Stored SQL
----------
This operation did not run any stored SQL.
Blocking
--------
This operation did not block.
Connection
----------
Connected: Before profiling started
Disconnected: Still connected
max_temp_space: 0
max_priority: normal
timestamp_format: yyyy-mm-dd hh:nn:ss.ssssss
preserve_source_format: On
optimization_workload: Mixed
time_zone:
optimization_goal: First-row
post_login_procedure: dbo.sa_post_login_procedure
debug_messages: Off
st_geometry_on_invalid: Error
AppInfo: IP=192.168.87.34;HOST=TSNW02;OSUSER=wessel;OS='Windows 2012R2 Build 9200 ';EXE='C:\\Program Files (x86)\\TSD\\WinTreeClient\\bin\\wintree.exe';PID=0x1001c;THREAD=0xae6c;VERSION=17.0.10.6057;API=ODBC;TIMEZONEADJUSTMENT=120
max_plans_cached: 20
string_rtruncation: Off
update_statistics: On
subsume_row_locks: On
global_database_id: 2147483647
extended_join_syntax: On
first_day_of_week: 7
login_procedure: DBA.login_check
checkpoint_time: 60
ansi_permissions: On
timestamp_with_time_zone_format: yyyy-mm-dd hh:nn:ss.ssssss +hh:nn
http_session_timeout: 30
conn_auditing: On
request_timeout: 0
cooperative_commit_timeout: 250
synchronize_mirror_on_commit: Off
st_geometry_asbinary_format: WKB
TimeZoneAdjustment: 120
date_order: YMD
min_role_admins: 1
prevent_article_pkey_update: On
max_query_tasks: 1
remote_idle_timeout: 15
audit_log:
blocking: On
max_recursive_iterations: 100
st_geometry_astext_format: WKT
oem_string:
user_estimates: Override-magic
SessionTimeout: 0
ansi_close_cursors_on_rollback: Off
db_publisher: -1
http_connection_pool_timeout: 60
integrated_server_name:
sql_flagger_error_level: Off
continue_after_raiserror: Off
webservice_sessionid_name: SessionID
return_date_time_as_string: Off
java_location:
st_geometry_interpolation: relative-tolerance-percent=.3
pinned_cursor_percent_of_cache: 10
UserID: WRO
fire_triggers: On
precision: 30
st_geometry_describe_type: char
cooperative_commits: On
materialized_view_optimization: Stale
conversion_error: On
disk_sandbox: Off
tsql_variables: Off
for_xml_null_treatment: Omit
progress_messages: Off
updatable_statement_isolation: 0
verify_password_function:
ansi_blanks: Off
isolation_level: 0
sort_collation: Internal
date_format: yyyy-mm-dd
divide_by_zero_error: On
on_charset_conversion_failure: Ignore
ansi_update_constraints: Off
auditing: Off
optimization_level: 9
max_statement_count: 50
row_counts: Off
max_client_statements_cached: 10
Database: bootenco
priority: normal
close_on_endtrans: On
st_geometry_asxml_format: GML
chained: On
odbc_distinguish_char_and_varchar: Off
allow_write_client_file: Off
blocking_timeout: 0
allow_read_client_file: Off
suppress_tds_debugging: Off
cis_option: 0
query_mem_timeout: -1
delayed_commits: Off
time_zone_adjustment: 120
truncate_timestamp_values: Off
default_dbspace:
delayed_commit_timeout: 500
nearest_century: 0
uuid_has_hyphens: On
tsql_outer_joins: Off
collect_statistics_on_dml_updates: On
non_keywords:
recovery_time: 2
rollback_on_deadlock: On
tds_empty_string_is_null: Off
auto_commit_on_create_local_temp_index: Off
java_class_path:
http_connection_pool_basesize: 10
read_past_deleted: On
time_format: hh:nn:ss
Name: WINTREE-11#1
wait_for_commit: Off
reserved_keywords:
allow_snapshot_isolation: Off
scale: 6
dedicated_task: Off
extern_login_credentials: Effective_user
prefetch: Conditional
quoted_identifier: On
log_deadlocks: On
odbc_describe_binary_as_varbinary: Off
max_cursor_count: 50
min_password_length: 0
blocking_others_timeout: 0
default_timestamp_increment: 1
IdleTimeout: 0
ansinull: On
ansi_substring: On
allow_nulls_by_default: On
max_connections: 32766
trusted_certificates_file:
webservice_namespace_host:
HasSecuredFeature: No
java_vm_options:
temp_space_limit_check: On
background_priority: Off
on_tsql_error: Conditional
cis_rowset_size: 50
sql_flagger_warning_level: Off
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.