Posts tagged Express Edition

Zajawka nie na temat

Dzień 23 sierpnia 2010 roku – ależ to był poniedziałek! Najpierw z samego rana awaria bloga. Przez chwilę myślałem, że to jakiś atak DDOS albo serwer nie wytrzymał naporu czytelników (czy to możliwe, żeby blog po jednym wpisie stał się aż tak popularny?). Ale kolega z firmy hostującej bloga wyjaśnił mi, że to nie był czynnik ludzki. Grunt, ze blog odżył i duskusja pod najbardziej obleganym wpisem na moim blogu trwała w najlepsze :-) 

Zajawka na temat – użytkownik zasiewa niepewność

Najlepsze jednak nadeszło po południu. Na forum WSS.pl użytkownik lechuCC zapytał o limit pamięci RAM w SQL Server 2008 R2 Express Edition. Udzieliliśmy z Krzyśkiem Stachyrą standardowej odpowiedzi wyczytanej na oficjalnej stronie produktu SQL Server 2008 (ale nie na stronie SQL Server 2008 R2! tam nic na ten temat nie ma, ale przed udzieleniem odpowiedzi koledze nawet nie szukałem…), że limit pamięci wynosi 1GB dla bufora danych. Kolega sprawdzał na wiele sposobów, ale zawsze wychodziło mu blisko 1,4GB, a to jednak trochę więcej niż rzekome 1GB… 

Postanowiłem się przekonać, jaka jest szara rzeczywistość :-) Opis wykonanych czynności przedstawiam poniżej. 

Test szarej rzeczywistości

Na instancji SQL Server 2008 R2 Express Edition, w której wynik zapytania: 

SELECT @@VERSION;

wyglądał tak: 

założyłem sobie bazę danych o nazwie Test. Powiększyłem plik danych tej bazy do 2GB i plik dziennika transakcji do 100MB (asekuracyjnie). W tejże bazie puściłem w ruch taki kawałek kodu: 

USE Test;
GO
IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
  DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t (a char(8000));
GO
INSERT INTO dbo.t (a)
SELECT REPLICATE('a', 8000)
FROM sys.all_objects WHERE is_ms_shipped = 1; -- 1981 wierszy
CHECKPOINT;
GO 100

Po 100 wykonaniach wsadu z poleceniem INSERT mam sporą tabelę – z liczbą wierszy równą 198100, a każdy wiersz rezyduje na osobnej stronie danych (bo wstawiłem tyle danych w jeden wiersz, że drugi już na tych samych 8 kilobajtach się nie zmieści). 

Teraz opróżniam cały bufor danych: 

DBCC DROPCLEANBUFFERS;
GO

I wykonuję zapytanie, którego celem jest wrzucenie do bufora danych wszystkich danych z tabeli dbo.t: 

SELECT COUNT(*) FROM dbo.t; -- wynik: 198100, wykonany został skan tabeli

Zaglądam do bufora zapytaniami, które już prezentowałem na blogu. Na początek informacja, która baza ile megabajtów zajmuje w buforze: 

SELECT
  CASE
    WHEN database_id = 32767 THEN 'mssqlsystemresource'
    ELSE DB_NAME(database_id)
  END AS [Database],
  CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [In buffer cache (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC;
GO

Wynik: 

Database            In buffer cache (MB)
------------------- --------------------
Test                1383.83
tempdb              0.24
mssqlsystemresource 0.19
master              0.06

Czyli że baza Test zajmuje blisko 1,4GB… 

Skoro tak, to przekonajmy się, który obiekt tyle miejsca zajął spośród obiektów z bazy Test: 

USE Test;
GO
SELECT
  QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
  QUOTENAME(OBJECT_NAME(p.object_id)) AS Object,
  CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [In buffer cache (MB)]
FROM sys.dm_os_buffer_descriptors AS d
INNER JOIN sys.allocation_units AS u
ON d.allocation_unit_id = u.allocation_unit_id
INNER JOIN sys.partitions AS p
ON (u.type IN (1,3) AND u.container_id = p.hobt_id)
OR (u.type = 2 AND u.container_id = p.partition_id)
WHERE d.database_id = DB_ID()
GROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
  QUOTENAME(OBJECT_NAME(p.object_id))
ORDER BY 2 DESC;
GO

Wynik: 

Object                   In buffer cache (MB)
------------------------ --------------------
[dbo].[t]                1382.40
[sys].[sysobjvalues]     0.12
[sys].[syscolpars]       0.07
[sys].[sysschobjs]       0.02
[sys].[syssingleobjrefs] 0.02
[sys].[sysiscols]        0.02
[sys].[sysallocunits]    0.01
[sys].[sysrowsets]       0.01
[sys].[sysidxstats]      0.01

I proszę. Jedna duża tabela zajmuje w buforze rzeczone blisko 1,4GB. 

Niespodzianka? Pomyślałem, że może moje zapytania są błędnie napisane. Zatem pora zajrzeć do liczników monitora wydajności (perfmona): 

SELECT
  object_name,
  counter_name,
  cntr_value / 1024 AS cntr_value_MB
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%'
AND counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');
GO

Wynik: 

object_name                           counter_name              cntr_value_MB
------------------------------------- ------------------------- -------------
MSSQL$SQL2008R2EXPRESS:Memory Manager Target Server Memory (KB) 1410
MSSQL$SQL2008R2EXPRESS:Memory Manager Total Server Memory (KB)  1410

Zatem Perfmon mówi, że instancja zajęła 1410 megabajtów na bufor danych i – co ciekawe – tyle właśnie zamierzała… 

Dla pewności jeszcze: 

DBCC MEMORYSTATUS;
GO

Gdzie w wyniku znalazłem między innymi: 

Buffer Pool Value
----------- -----------
Committed   180480
Target      180480

Kalkulator (T-SQL) w ręce i liczymy ile wychodzi z pomnożenia 180480 przez 8 (tyle kilobajtów ma strona danych) i podzielenia wyniku przez 1024 (żeby wynik wyszedł w megabajtach): 

SELECT 8 * 180480 / 1024 AS [Buffer pool in MB]; -- 1410

I w tym momencie już nie mam wątpliwości, że ta instancja wzięła 1,4GB pamięci RAM na bufor danych! A napisano, że miała wziąć nie więcej niż 1GB… 

Jak jedno się nie zgadza…

… to trzeba sprawdzić, czy inny limit opisany w reklamowych folderach jest prawdziwy (do osiągnięcia i nie do przekroczenia). Tym limitem jest 10GB na pliki danych w pojedynczej bazie danych w SQL Server 2008 R2 Express Edition. 

Pierwszy ruch – spróbujmy zwiększyć wielkość pliku do 1MB powyżej owych 10GB: 

USE master;
GO
ALTER DATABASE Test
MODIFY FILE (
  NAME = N'Test',
  SIZE = 10486784KB
);
GO

Odpowiedź serwera: 

Ok, tak się nie da :-) 

Test kolejny – co będzie, jak wstawimy tyle danych, że plik sam będzie zmuszony urosnąć ponad wymienione 10240MB. Tu po prostu wielokrotnie kopiuję moją dużą tabelę dbo.t z testu bufora danych: 

SELECT * INTO dbo.t1 FROM dbo.t; -- tu jedynie zmieniam numerki na kolejne
GO

W końcu serwer odpowiada: 

Z tego wynika, że 10GB jest prawdziwym ograniczeniem rozmiaru danych w pojedynczej bazie danych w SQL Server 2008 R2 Express Edition. 

To jak to w końcu jest?

Jest tak, że jak sami nie sprawdzimy, to może się okazać, że informacje przekazywane drogą poczty pantoflowej mają się nijak do szarej rzeczywistości (i ja przyłożyłem w tym przypadku rękę do powielania, jak się okazuje niekoniecznie prawdziwej, informacji). Jest też tak, że nawet sam Microsoft nie zna ograniczeń swojego produktu, bo ludzie z Redmond proszeni o komentarze do zademonstrowanego tu testu nie bardzo wiedzą, jak sensownie to wytłumaczyć (próbują wymyślać wersje zeznań mówiące, że niby jakieś tam systemowe obiekty i tajemnicze pule systemowe zajmują dodatkowe megabajty w buforze), ale obstają przy wersji, że limit dla bufora nadal wynosi 1GB! Może by nie było o co robić szumu, ale… Ale w edycji Express liczy się każdy megabajt, a tu najpewniej mamy nieścisłość rzędu 40% :-) A to już podstawa do zadania sobie pytania – czy mogę wykorzystać te oddane do dyspozycji “za darmo” 400MB bufora? Skoro w Microsoft o nich nie wiedzą, to pewnie mogę, bo skąd będą wiedzieli, że wykorzystuję coś, o istnieniu czego nie wiedzą ;-) 

Acha, i ciekawostka na koniec – dla SQL Server 2008 R2 Express Edition można śmiało ustawić ‘max server memory (MB)’ na 2048 ;-) Ani SQL Server, ani Management Studio ani pisną, choć taki limit jest bez sensu, bo instancja nie użyje więcej niż 1GB… tfu, wróć… 1,4GB pamięci na bufor danych :-) 

PS. Za chwilę instaluję SQL Server 2008 Express Edition i powtarzam test. Dopiszę obserwacje do tego wpisu. Kto wie, może i na poprzedniej wersji rzeczony limit nie wynosi 1GB ;-) 

[EDYCJA: 2010-08-24]  

Ponieważ Łukasz Grala już zrobił test na SQL Server 2005 Express Edition (patrz jego komentarz do tego wpisu) i wygląda na to, że sytuacja jest analogiczna, nie będę testował, jak to jest na SQL Server 2008 Express Edition. 

[/EDYCJA]