Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Migrated from http://serverfault.com/questions/615129/sql-2008-server-performance-loss-possibly-connected-with-a-very-large-table

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.

share|improve this question
1  
Instead of creating a duplicate question, you can "flag" your question and have it moved to another Stack Exchange site. Thanks! –  Max Vernon Jul 24 at 13:58
    
Sounds like the hot working set of this data is starting to exceed the buffer pool size. That manifests itself in latency spikes that are becoming more and more frequent. Performance falls off a cliff. –  usr Jul 24 at 21:36
    
After one day the memory consumption of PenData table grew from 64% to 71% of total SQL Server memory, which is 5814 MB. It shows that it really may be problematic... –  VDohnal Jul 25 at 8:42
    
I decided to delete the older records completely, then I found the partitioning and partition switching exists in SQL Server, which may be very good for this situation mssqltips.com/sqlservertip/1200/… –  VDohnal Jul 26 at 6:58

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
1  
Yes, that is what I thought. Because until now it worked very well regardless of the size of DB2 (which is the only one that really grows fast), and now it suddenly became slow. I have had problems getting physical RDP access to the server, but now I got there and there is one service stuck in "Starting" state and taking 25% cpu - result of bad migration to virtualized environment... –  VDohnal Jul 24 at 13:33
    
Thank you for telling the issue it would be helpful to others –  Shanky Jul 24 at 13:34
2  
Regarding point 1, it is possible that the large unused table is actually sitting in memory all the time and not getting flushed out if queries are performing scans on the entire table - this really depends on the nature of the queries ran against the table, its structure, and proper indexing. –  Max Vernon Jul 24 at 14:01
    
I hope not, because the only criteria used is the primary clustered key based on Time and IndicatorID field. Every query has Time and IndicatorID condition so I suppose it should access only pages that contain most recent records... Could that be somehow verified? –  VDohnal Jul 24 at 14:04
    
Max what you say is possible but if Lazywriter finds that memory is required for other pages to come and it will surely see because there are large unused pages, it will start flushing it so slowness will be for moment. This is what Lazywriter is designed. –  Shanky Jul 24 at 14:10

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;
share|improve this answer
    
First query returns: Arithmetic overflow error converting expression to data type int –  VDohnal Jul 24 at 14:19
    
Second query returns 4494866225,55469 TotalMb and 4494169745,50781 UsedMB and 4408,1015625 BufferMBUsed, does not seem very realistic, this is much bigger than the entire database... –  VDohnal Jul 24 at 14:23
    
When I remove the part "* 8192 / 1048576" from query 1 it states "555587" for DB2. For DB3 it is only 1062 and DB1 has 433. –  VDohnal Jul 24 at 14:30
    
I have run the query from here dba.stackexchange.com/questions/44541/…, and it states 3985,85 MB occupied, this could be true, cached pages count is 510189... –  VDohnal Jul 24 at 14:33
1  
I've modified the queries to prevent the arithmetic overflow issue. –  Max Vernon Jul 24 at 14:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.