|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, July 07, 2009 5:48 AM
Points: 1,
Visits: 6
|
|
Hi,
We have observed during a benchmark that SQL SERVER 2008 is around 30% slower than SQL SERVER 2005.
in fact some user actions may be 5 time slower. This is due to high disks IO .
after investigation, It seems to me that the management of the cache is different from SQLSERVER 2005.
With SQL server 2005, each time a page is referenced in the cache (by a select for example), a counter is incremented . This help to keep the page in memory longer. SQL server 2008 doesn't seem to take care of this counter .
I have create a simple case to show this point.
I have create 2 tables (A, B) with around 2 GB of data each.
The memory limit of SQL server is set to 3 GB.
I do 1 FULL SCAN on table A, to put all pages of this table in the cache.
I do 2 FULL scan on table A to increment by 2 the counter pages of table A in memory
On SQL 2005, If I do a FULL SCAN on table B, some pages of table B will be added in the cache but not all, because memory is missing (only 3 GB). (I need to do 3 FULL SCAN on table B to add all pages in memory : Some pages of table A will me removed)
On SQL 2008, If I do a FULL SCAN on table B, all pages of this table will be added in the cache. Some pages of table A will be removed from cache
Do you think This is a bug from SQL server 2008. In any case this increase the disk IO.
Fabio
PS : Here is the request I used to check how many pages are in cache
SELECT count(*)AS cached_pages_count
,name ,index_id
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.hobt_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 cached_pages_count DESC
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 6:27 AM
Points: 1,905,
Visits: 1,876
|
|
Well that's the problem with benchmarks isn't it? 
You don't say in your post whether this is causing you problem on a real system or not?
You might be interested to know that the Lazy Writer (which acts as the clock hand for the data cache) uses a LRU-K algorithm, not the simple counting method you refer to. There are a good number of technical articles on LRU-K available via your favourite search engine.
That's all academic, and I mention it just in case you are curious. You should never rely on internal implementation details - these can and do change with each new version, edition, service pack, cumulative update, hot fix...
If you are having problems in a real environment, please post the full details and someone will advise you.
Paul
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 1,892,
Visits: 1,475
|
|
If you are doing performance testing, then you need to make sure your environments are the same. This can be difficult.
Are you using the same type of computer hardware for your SQL 2005 and SQL 2008 tests. Are your disks the same type.
Is the Windows version the same. Do you have the same configuration for page size, etc. Are you using a desktop or server operating system - it is only worth doing this type of testing on a server OS.
Is the SQL configuration the same. Is the maximum and minimum server memory set the same. Are your databases the same. Are they located in the same part of the disk (for DAS disks), or have the same speed of access for SAN disks. Are their indexes in the same state. Have you got up-to-date statistics for your tables.
If you are sure the environments are the same, then you can do some meaningful performance tests. If you are concerned about the results, let us know about how your systems are set up and the results you get. We may be able to offer some advice.
Author: SQL Server FineBuild 1-click install and configuration of SQL Server 2005 and 2008.
1 October 2009: now over 7,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Fun: You can lead a user to data, but you can't make them think (Anon).
|
|
|
|
