• Tac11 (8/24/2016)


    Thanks Eric and SQL Guru.

    Eric, so you think I can run "sys.dm_db_index_usage_stats" and find if there are index scan taking place?

    or any other step should I take?

    or this is not even a issue?

    There is nothing wrong with your page buffer; it just works as intended. If you are encountering performance issues, and it seems specific tables are consuming the bulk of your buffer pool, then focus on optimizing these objects and queries to reduce scanning operations.

    Here is an explanation of table / index scans:

    https://www.mssqltips.com/sqlservertutorial/277/index-scans-and-table-scans/

    You can capture metrics about server level page scans / sec which will give you an idea of how much of an impact this is overall:

    http://www.keepitsimpleandfast.com/2011/11/how-to-find-table-scans-in-your.html

    Querying dm_db_index_usage_stats and sorting by user_scans will identify what table(s) are getting the most scans.

    select object_name(object_id), user_scans, user_seeks, user_lookups, user_updates

    from sys.dm_db_index_usage_stats

    order by user_scans desc;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho