SQL 2008 Server - performance loss possibly connected with a very large table

Question

We have SQL Server 2008 with 3 active databases running on it.

• DB1 - cca 400 MB size
• DB2 - cca 8 GB size
• DB3 - cca 42 GB size - but the majority of records are not used at all

In DB2 we have a this table

CREATE TABLE [dbo].[PenData](
    [IndicatorID] [smallint] NOT NULL,
    [Time] [datetime2](0) NOT NULL,
    [Value] [real] NULL,
    [ValueMax] [real] NULL,
    [ValueMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IndicatorID] ASC,
    [Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This table by itsel occupies cca 8 GB and has 283 029 812 records. Vast majority of records in this table are historical records and are accessed very seldom or never. But a small part of recent records is used quite a lot and every hour many new records are getting inserted in this table.

The problem is that we recently observe performance problems in DB3. Though performance of DB2 and PenData is OK.

My question is:

1.could the size of the table PenData be important factor for the overall server performance? How do these many unused table records affect memory allocated by the server?

2.Could I get significant performance gain on the server (in DB3) if I delete half of the records from the very large table PenData?

3.And are there any tools to monitor performance when I do not have permissions to access Activity monitor?

EDIT

I was quite terrified to see (using scripts provided in the answers) that the PenData table took 60-70% of whole SQL Server memory (which is relatively low cca 6 GB). I am not sure why, since this is the application I have programmed myself and I do not see any reason, why should so many rows from this table remain cached in memory. It had also been my mistake to run SELECT COUNT(*) FROM PenData before I had tried to see, how much of PenData remained cached in memory.

I have omitted one foreign key I have in this table, so I present it here:

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Indicator] FOREIGN    KEY([IndicatorID])
REFERENCES [dbo].[Indicator] ([IndicatorID])

I have deleted milions of records in batches of 100 000 records using SET RECORDCOUNT 100000 from PenData. Now it has 211 120 425 records. I have run DBCC SHRINKDATABASE (PenData, 20) - only after this has the memory consumption by PenData decreased significantly.

The performance and memory consumption by other databases got better.

But after one day the table PenData occupies again almost all the memory...

EDIT

I have changed one single SQL command in one single stored procedure and now everything is perfect, database SupervisionP takes only 184 MB in cache! See detail here

Index seek much slower with OR condition compared with separate SELECTs

Thanks for your help.

Answer 1

I will take you question point wise

1.In my opinion its higly unlikey for an unused large table to cause issue with query running for diffrent database. SQL Server memory is dynamic in nature if suppose large portion of memory is occupied by datapages of DB1 Lazy writer and checkpoint pages will work together to age out pages which are not used recently or have committed records. So if DB2 data pages require memory they would be granted and I dont think memory crunch would be there

2.No, I dont think so like I said a query for particular database(DB2) will have no affect with records present in other database(DB1) IF that database tables of DB1 are not used in this query. Can you define your performance issue is it query slowness, disk slowness memory crunch what?. Please use this link for analysing slow running queries

3.Yes there are lot of monitoring tools available in market I have Spotlight in my enviroenment you can use SCOM as well.

You would liek to refer this whitepaer Troubleshooting performance problems in SQL Server

Answer 2

You can use the following query to determine how much RAM is being used by each database:

USE master;

SELECT d.name, CAST(COUNT(1) AS BIGINT) * 8192 / 1048576 AS MBinMemory
FROM sys.dm_os_buffer_descriptors bd
    INNER JOIN sys.databases d ON bd.database_id = d.database_id
GROUP BY d.name
ORDER BY d.name;

To see memory used by objects in a specific database, do:

USE <DB_NAME_IN_QUESTION>

SELECT 
    obj.name AS ObjectName
    , index_id
    , CAST(count(*) AS BIGINT) * 8192E0 / 1048576 AS CachedMB
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id 
ORDER BY count(*) DESC;

The last column in the above query shows the amount of RAM being used by the given database object.

You can see query stats by looking at: (among a great deal of other things)

SELECT qs.execution_count
    , qs.total_worker_time
    , qs.total_elapsed_time
    , qs.total_logical_reads
    , qs.total_physical_reads
    , qs.total_logical_writes
    , qt.text /* This is the actual query text */
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.execution_count DESC;