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?
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...
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
Thanks for your help.