Our Hierarchy of Database Needs training email plan has been a lot of fun. Thousands of SQL Server professionals have signed up to get an email in their in-box every Wednesday for 6 months. It’s like a part-time college course – the one you should have been given when you first got shuffled into this DBA job thingy.
Now, we’ve taken some of the content and turned it into a free 38-page PDF ebook.
It starts at the base of Ozar’s Hierarchy of Database Needs, covering backups, security, and then moves up to capacity planning and performance.
It’s not meant to be doctoral-level – this is just the intro course that we all wish we’d have gotten before management started screaming at us about why the database is so slow. And expensive. And unpredictable.
It’s like a prerequisite of things we want to make sure people know before they move up to our training classes.
Let us know what you think, and enjoy!
4 Comments. Leave new
Hi Brent,
First, thanks for this work. Have been much helpful.
I just wanted to ask something:
On page 23, you say:
“Unfortunately, we don’t have a way of capping how much memory gets used by each database”
Below is the query from Glenn Berry’s Diagnostic Information Queries:
— Get total buffer usage by database for current instance (Query 21) (Total Buffer Usage by Database)
WITH AggregateBufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [CachedSize]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 — system databases
AND database_id 32767 — ResourceDB
GROUP BY DB_NAME(database_id))
SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)],
CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
FROM AggregateBufferPoolUsage
ORDER BY [Buffer Pool Rank] OPTION (RECOMPILE);
— Tells you how much memory (in the buffer pool) is being used by each database on the instance
Is it that I misunderstood or does it need an erratum?
Thanks a lot,
sqlism – Glenn’s query tells you how much is used, yes. Now, what are you going to *do* about it? How are you going to use that information to cap the amount of memory one database uses?
I can tell you how much I weigh too. That doesn’t mean I can change the number at will. 😉
Thanks Brent. Crystal clear.