high cached_page_count - when to start to worry and what can I do about it?

Question

Below are my top 10 cached page counts.

We're having query performance issues with the top 2 tables; the queries never change but the run times are very volatile.

I cant seem to work out were the issues are and suspect through a bit of online digging its got something to do with available space within SQL Server itself but really have no clue where to best start the investigation.

We have seen a pattern in that the database is growing as expected daily and appears to be hitting its limit routinely forcing an extra GB to be added.

Yesterday we gave it a bit of extra overhead and it's 2 hours ahead of schedule which leads us to believe maybe when it's at its limit and is trying to pinch resources from elsewhere to re-allocate thus the reason for volatile build times rather than consistent build times.

We also noticed there is a Sunday job that runs to re-index fragmented indexes and frees up 40GB but within a day this is swallowed back up, so maybe something there to look into also.

╔════════════════════╦═══════════════════════════════════════╦══════════╗
║ cached_pages_count ║                 name                  ║ index_id ║
╠════════════════════╬═══════════════════════════════════════╬══════════╣
║            3507035 ║ CDI_OP_APPOINTMENT                    ║        1 ║
║            2588843 ║ CDO_OP_APPOINTMENT                    ║        1 ║
║            2035499 ║ __tblDataServicesEventDetail          ║        1 ║
║            1552008 ║ CDI_RTT_PATIENT_PATHWAY_EVENT         ║        1 ║
║            1369916 ║ CDO_RTT_PATIENT_PATHWAY_EVENT         ║        1 ║
║            1136325 ║ WRK_CDI_OP_APPOINTMENT_CODES_CHECKSUM ║        1 ║
║            1120941 ║ LZO_SCHEDULE                          ║        1 ║
║             941882 ║ LZO_CNSPATIENTCLINICALNOTE            ║        1 ║
║             890873 ║ CDO_CLINIC_SESSION_SLOT               ║        1 ║
║             848078 ║ CDI_CLINIC_SESSION_SLOT               ║        1 ║
╚════════════════════╩═══════════════════════════════════════╩══════════╝

Answer 1

The fact that these tables both cause performance problems and use lots of pages in the cache isn't at all surprising.

I would rather find it expected. You call a couple of tables that (probably) both are on the larger side, and you also do lots of activity on those tables.

Perhaps you have lots and lots of inserts, which quickly causes the clustered index to become fragmented (hence freeing up space after rebuild)? If that is the case, then perhaps the key for the clustered index might have been badly chosen.

I would start by collecting more information. Possibly running a trace to see what activity you have going on.