def test_object_name(aggregator, instance_docker, sqlserver):
init_config_object_name = {
'custom_metrics': [{
'name': 'sqlserver.cache.hit_ratio',
'counter_name': 'Cache Hit Ratio',
'instance_name': 'SQL Plans',
'object_name': 'SQLServer:Plan Cache',
'tags': ['optional_tag:tag1']
}, {
'name': 'sqlserver.active_requests',
'counter_name': 'Active requests',
'instance_name': 'default',
'object_name': 'SQLServer:Workload Group Stats',
'tags': ['optional_tag:tag1']
}]
}
sqlserver_check = SQLServer(CHECK_NAME, init_config_object_name, {},
[instance_docker])
sqlserver_check.check(instance_docker)
aggregator.assert_metric('sqlserver.cache.hit_ratio',
tags=['optional:tag1', 'optional_tag:tag1'],
count=1)
aggregator.assert_metric('sqlserver.active_requests',
tags=['optional:tag1', 'optional_tag:tag1'],
count=1)
def test_autodiscovery_matches_all_by_default(instance_autodiscovery):
fetchall_results, mock_cursor = _mock_database_list()
all_dbs = set([r.name for r in fetchall_results])
# check base case of default filters
check = SQLServer(CHECK_NAME, {}, [instance_autodiscovery])
check.autodiscover_databases(mock_cursor)
assert check.databases == all_dbs
def test_check_local(aggregator, init_config, instance_sql2017):
sqlserver_check = SQLServer(CHECK_NAME, init_config, {},
[instance_sql2017])
sqlserver_check.check(instance_sql2017)
expected_tags = instance_sql2017.get(
'tags', []) + ['host:{}'.format(LOCAL_SERVER), 'db:master']
_assert_metrics(aggregator, expected_tags)
def test_check_local(aggregator, init_config, instance_sql2008):
sqlserver_check = SQLServer(CHECK_NAME, init_config, {},
[instance_sql2008])
sqlserver_check.check(instance_sql2008)
expected_tags = instance_sql2008.get(
'tags', []) + ['host:(local)\SQL2008R2SP2', 'db:master']
_assert_metrics(aggregator, expected_tags)
def test_check_docker(aggregator, init_config, instance_docker):
sqlserver_check = SQLServer(CHECK_NAME, init_config, [instance_docker])
sqlserver_check.check(instance_docker)
expected_tags = instance_docker.get('tags', []) + [
'host:{}'.format(instance_docker.get('host')), 'db:master'
]
_assert_metrics(aggregator, expected_tags)
def test_autodiscovery_exclude_override(instance_autodiscovery):
fetchall_results, mock_cursor = _mock_database_list()
instance_autodiscovery['autodiscovery_include'] = ['t.*', 'master'] # remove default `.*`
instance_autodiscovery['autodiscovery_exclude'] = ['.*2020db$', 'm.*']
check = SQLServer(CHECK_NAME, {}, [instance_autodiscovery])
check.autodiscover_databases(mock_cursor)
assert check.databases == set(['tempdb'])
def test_autodiscovery_exclude_some(instance_autodiscovery):
fetchall_results, mock_cursor = _mock_database_list()
instance_autodiscovery['autodiscovery_include'] = ['.*'] # replace default `.*`
instance_autodiscovery['autodiscovery_exclude'] = ['.*2020db$', 'm.*']
check = SQLServer(CHECK_NAME, {}, [instance_autodiscovery])
check.autodiscover_databases(mock_cursor)
assert check.databases == set(['tempdb', 'AdventureWorks2017', 'CaseSensitive2018'])
def test_connection_cleanup(instance_docker):
check = SQLServer(CHECK_NAME, {}, [instance_docker])
check.initialize_connection()
# regular operation
with check.connection.open_managed_default_connection():
assert len(check.connection._conns) == 1
with check.connection.get_managed_cursor() as cursor:
cursor.execute("select 1")
assert len(check.connection._conns) == 1
assert len(
check.connection._conns) == 0, "connection should have been closed"
# db exception
with pytest.raises(Exception) as e:
with check.connection.open_managed_default_connection():
assert len(check.connection._conns) == 1
with check.connection.get_managed_cursor() as cursor:
assert len(check.connection._conns) == 1
cursor.execute("gimme some data")
assert "incorrect syntax" in str(e).lower()
assert len(
check.connection._conns) == 0, "connection should have been closed"
# application exception
with pytest.raises(Exception) as e:
with check.connection.open_managed_default_connection():
assert len(check.connection._conns) == 1
with check.connection.get_managed_cursor():
assert len(check.connection._conns) == 1
raise Exception("oops")
assert "oops" in str(e)
assert len(
check.connection._conns) == 0, "connection should have been closed"
def test_object_name(aggregator, init_config_object_name, instance_docker):
sqlserver_check = SQLServer(CHECK_NAME, init_config_object_name, {}, [instance_docker])
sqlserver_check.check(instance_docker)
aggregator.assert_metric('sqlserver.cache.hit_ratio', tags=['optional:tag1', 'optional_tag:tag1'], count=1)
aggregator.assert_metric('sqlserver.active_requests', tags=['optional:tag1', 'optional_tag:tag1'], count=1)
def test_autodiscovery_matches_some(instance_autodiscovery):
fetchall_results, mock_cursor = _mock_database_list()
instance_autodiscovery['autodiscovery_include'] = [
'master', 'fancy2020db', 'missingdb', 'fakedb'
]
check = SQLServer(CHECK_NAME, {}, [instance_autodiscovery])
check.autodiscover_databases(mock_cursor)
assert check.databases == set(['master', 'Fancy2020db'])
def test_check_adoprovider(aggregator, init_config, instance_sql2017, adoprovider):
instance = deepcopy(instance_sql2017)
instance['adoprovider'] = adoprovider
sqlserver_check = SQLServer(CHECK_NAME, init_config, {}, [instance])
sqlserver_check.check(instance)
expected_tags = instance.get('tags', []) + [r'host:(local)\SQL2017', 'db:master']
_assert_metrics(aggregator, expected_tags)
def test_autodiscovery_matches_none(instance_autodiscovery):
fetchall_results, mock_cursor = _mock_database_list()
# check missing additions, but no exclusions
mock_cursor.fetchall.return_value = iter(fetchall_results) # reset the mock results
instance_autodiscovery['autodiscovery_include'] = ['missingdb', 'fakedb']
check = SQLServer(CHECK_NAME, {}, [instance_autodiscovery])
check.autodiscover_databases(mock_cursor)
assert check.databases == set()
def test_check_adoprovider(aggregator, init_config, instance_sql2017, adoprovider):
instance = deepcopy(instance_sql2017)
instance['adoprovider'] = adoprovider
sqlserver_check = SQLServer(CHECK_NAME, init_config, [instance])
sqlserver_check.check(instance)
expected_tags = instance.get('tags', []) + ['host:{}'.format(LOCAL_SERVER), 'db:master']
_assert_metrics(aggregator, expected_tags)
def test_get_cursor(instance_sql2017):
"""
Ensure we don't leak connection info in case of a KeyError when the
connection pool is empty or the params for `get_cursor` are invalid.
"""
check = SQLServer(CHECK_NAME, {}, [])
with pytest.raises(SQLConnectionError):
check.get_cursor(instance_sql2017, 'foo')
def test_get_cursor(instance_docker):
"""
Ensure we don't leak connection info in case of a KeyError when the
connection pool is empty or the params for `get_cursor` are invalid.
"""
check = SQLServer(CHECK_NAME, {}, [instance_docker])
check.initialize_connection()
with pytest.raises(SQLConnectionError):
check.connection.get_cursor('foo')
def test_check_invalid_password(aggregator, init_config, instance_docker, sqlserver):
instance_docker['password'] = '******'
sqlserver_check = SQLServer(CHECK_NAME, init_config, {}, [instance_docker])
with pytest.raises(SQLConnectionError) as excinfo:
sqlserver_check.check(instance_docker)
assert excinfo.value.args[0] == 'Unable to connect to SQL Server'
aggregator.assert_service_check('sqlserver.can_connect', status=sqlserver_check.CRITICAL,
tags=['host:localhost,1433', 'db:master', 'optional:tag1'])
def test_async_job_enabled(dd_run_check, dbm_instance, activity_enabled):
dbm_instance['query_activity'] = {'enabled': activity_enabled, 'run_sync': False}
check = SQLServer(CHECK_NAME, {}, [dbm_instance])
dd_run_check(check)
check.cancel()
if activity_enabled:
assert check.activity._job_loop_future is not None
check.activity._job_loop_future.result()
else:
assert check.activity._job_loop_future is None
def test_async_job_enabled(dd_run_check, dbm_instance, statement_metrics_enabled):
dbm_instance['query_metrics'] = {'enabled': statement_metrics_enabled, 'run_sync': False}
check = SQLServer(CHECK_NAME, {}, [dbm_instance])
dd_run_check(check)
check.cancel()
if statement_metrics_enabled:
assert check.statement_metrics._job_loop_future is not None
check.statement_metrics._job_loop_future.result()
else:
assert check.statement_metrics._job_loop_future is None
def test_get_available_query_metrics_columns(dbm_instance, expected_columns, available_columns):
check = SQLServer(CHECK_NAME, {}, [dbm_instance])
check.initialize_connection()
_conn_key_prefix = "dbm-"
with check.connection.open_managed_default_connection(key_prefix=_conn_key_prefix):
with check.connection.get_managed_cursor(key_prefix=_conn_key_prefix) as cursor:
result_available_columns = check.statement_metrics._get_available_query_metrics_columns(
cursor, expected_columns
)
assert result_available_columns == available_columns
def test_missing_db(instance_sql2017):
instance = copy.copy(instance_sql2017)
instance['ignore_missing_database'] = False
with mock.patch('datadog_checks.sqlserver.connection.Connection.check_database', return_value=(False, 'db')):
with pytest.raises(ConfigurationError):
check = SQLServer(CHECK_NAME, {}, [instance])
instance['ignore_missing_database'] = True
with mock.patch('datadog_checks.sqlserver.connection.Connection.check_database', return_value=(False, 'db')):
check = SQLServer(CHECK_NAME, {}, [instance])
assert check.do_check is False
def test_activity_collection_rate_limit(aggregator, dd_run_check, dbm_instance):
# test the activity collection loop rate limit
collection_interval = 0.1
dbm_instance['query_activity']['collection_interval'] = collection_interval
dbm_instance['query_activity']['run_sync'] = False
check = SQLServer(CHECK_NAME, {}, [dbm_instance])
dd_run_check(check)
sleep_time = 1
time.sleep(sleep_time)
max_collections = int(1 / collection_interval * sleep_time) + 1
check.cancel()
metrics = aggregator.metrics("dd.sqlserver.activity.collect_activity.payload_size")
assert max_collections / 2.0 <= len(metrics) <= max_collections
def test_async_job_cancel_cancel(aggregator, dd_run_check, dbm_instance):
dbm_instance['query_activity']['run_sync'] = False
check = SQLServer(CHECK_NAME, {}, [dbm_instance])
dd_run_check(check)
check.cancel()
# wait for it to stop and make sure it doesn't throw any exceptions
check.activity._job_loop_future.result()
assert not check.activity._job_loop_future.running(), "activity thread should be stopped"
# if the thread doesn't start until after the cancel signal is set then the db connection will never
# be created in the first place
aggregator.assert_metric(
"dd.sqlserver.async_job.cancel",
tags=_expected_dbm_instance_tags(dbm_instance) + ['job:query-activity'],
)
def test_get_statement_metrics_query_cached(aggregator, dbm_instance, caplog):
caplog.set_level(logging.DEBUG)
check = SQLServer(CHECK_NAME, {}, [dbm_instance])
check.initialize_connection()
_conn_key_prefix = "dbm-"
with check.connection.open_managed_default_connection(key_prefix=_conn_key_prefix):
with check.connection.get_managed_cursor(key_prefix=_conn_key_prefix) as cursor:
for _ in range(3):
query = check.statement_metrics._get_statement_metrics_query_cached(cursor)
assert query, "query should be non-empty"
times_columns_loaded = 0
for r in caplog.records:
if r.message.startswith("found available sys.dm_exec_query_stats columns"):
times_columns_loaded += 1
assert times_columns_loaded == 1, "columns should have been loaded only once"
def test_check_stored_procedure(aggregator, init_config, instance_docker):
instance_pass = deepcopy(instance_docker)
proc = 'pyStoredProc'
sp_tags = "foo:bar,baz:qux"
instance_pass['stored_procedure'] = proc
load_stored_procedure(instance_pass, proc, sp_tags)
sqlserver_check = SQLServer(CHECK_NAME, init_config, [instance_pass])
sqlserver_check.check(instance_docker)
expected_tags = instance_pass.get('tags', []) + sp_tags.split(',')
aggregator.assert_metric('sql.sp.testa', value=100, tags=expected_tags, count=1)
aggregator.assert_metric('sql.sp.testb', tags=expected_tags, count=2)
def test_resolved_hostname(instance_docker, dbm_enabled, instance_host,
reported_hostname, expected_hostname):
instance_docker['dbm'] = dbm_enabled
instance_docker['host'] = instance_host
instance_docker['reported_hostname'] = reported_hostname
sqlserver_check = SQLServer(CHECK_NAME, {}, [instance_docker])
assert sqlserver_check.resolved_hostname == expected_hostname
def test_custom_metrics_alt_tables(aggregator, dd_run_check,
init_config_alt_tables, instance_docker):
instance = deepcopy(instance_docker)
instance['include_task_scheduler_metrics'] = False
sqlserver_check = SQLServer(CHECK_NAME, init_config_alt_tables, [instance])
dd_run_check(sqlserver_check)
aggregator.assert_metric('sqlserver.LCK_M_S.max_wait_time_ms',
tags=['optional:tag1'],
count=1)
aggregator.assert_metric('sqlserver.LCK_M_S.signal_wait_time_ms',
tags=['optional:tag1'],
count=1)
aggregator.assert_metric(
'sqlserver.MEMORYCLERK_BITMAP.virtual_memory_committed_kb',
tags=['memory_node_id:0', 'optional:tag1'],
count=1)
aggregator.assert_metric(
'sqlserver.MEMORYCLERK_BITMAP.virtual_memory_reserved_kb',
tags=['memory_node_id:0', 'optional:tag1'],
count=1)
# check a second time for io metrics to be processed
dd_run_check(sqlserver_check)
aggregator.assert_metric('sqlserver.io_file_stats.num_of_reads')
aggregator.assert_metric('sqlserver.io_file_stats.num_of_writes')
def test_check_stored_procedure_proc_if(aggregator, init_config, instance_docker):
instance_fail = deepcopy(instance_docker)
proc = 'pyStoredProc'
proc_only_fail = "select cntr_type from sys.dm_os_performance_counters where counter_name in ('FOO');"
sp_tags = "foo:bar,baz:qux"
instance_fail['proc_only_if'] = proc_only_fail
instance_fail['stored_procedure'] = proc
load_stored_procedure(instance_fail, proc, sp_tags)
sqlserver_check = SQLServer(CHECK_NAME, init_config, [instance_fail])
sqlserver_check.check(instance_fail)
# apply a proc check that will never fail and assert that the metrics remain unchanged
assert len(aggregator._metrics) == 0
def test_load_static_information(aggregator, dd_run_check, instance_docker):
instance = copy(instance_docker)
check = SQLServer(CHECK_NAME, {}, [instance])
dd_run_check(check)
assert 'version' in check.static_info_cache, "missing version static information"
assert check.static_info_cache[
'version'], "empty version in static information"
def test_autodiscovery_perf_counters(aggregator, dd_run_check,
instance_autodiscovery):
instance_autodiscovery['autodiscovery_include'] = ['master', 'msdb']
check = SQLServer(CHECK_NAME, {}, [instance_autodiscovery])
dd_run_check(check)
expected_metrics = [
'sqlserver.database.backup_restore_throughput',
'sqlserver.database.log_bytes_flushed',
'sqlserver.database.log_flushes',
'sqlserver.database.log_flush_wait',
'sqlserver.database.transactions',
'sqlserver.database.write_transactions',
'sqlserver.database.active_transactions',
]
master_tags = [
'database:master',
'optional:tag1',
]
msdb_tags = [
'database:msdb',
'optional:tag1',
]
base_tags = ['optional:tag1']
for metric in expected_metrics:
aggregator.assert_metric(metric, tags=master_tags)
aggregator.assert_metric(metric, tags=msdb_tags)
aggregator.assert_metric(metric, tags=base_tags)
def test_no_autodiscovery_service_checks(aggregator, dd_run_check, init_config,
instance_docker):
sqlserver_check = SQLServer(CHECK_NAME, init_config, [instance_docker])
dd_run_check(sqlserver_check)
# assert no database service checks
aggregator.assert_service_check('sqlserver.database.can_connect', count=0)
