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.

Our company currently hosts about 400 databases per SQL Server 2005 instance. 500-600 databases and we see performance start to deteriorate. Database size ranges from 50mb to 40-60gb in the extreme, with probably an average of maybe a few GB.

What are some good metrics to collect and how would we get these to determine our bottleneck. Is it a performance limitation of sql to have too many databases? Is it all of the cached procedure plans for hundreds of stored procedures for hundreds of databases? Is it inefficient queries? A combination.

What is likely to help us increase the number of databases per server the most? All of the schemas are identical and the databases separate each customer's data. Maybe refactoring everything so we can combine multiple customers into one db and filter by customer, or optimizing our queries to the extreme... or maybe its just a sql server limitation... I don't know and value your input.

Thanks!

share|improve this question

3 Answers 3

up vote 7 down vote accepted

It's going to be a challenge to apply a uniform policy across such a varied estate. My first port of call would be to understand the biggest consumers of key resources (CPU, memory, IO) on each instance, with a view to isolating the problematic databases from the others. Glenn Berrys healthcheck DMVs would be a good starting point.

Who's using most of the memory (buffer pool) on each instance?

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

How long are pages staying in the buffer pool?

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager'
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

Who's generating most IO?

SELECT DB_NAME(DB_ID()) AS [Database Name],[file_id], num_of_reads, num_of_writes, 
io_stall_read_ms, io_stall_write_ms,
CAST(100. * io_stall_read_ms/(io_stall_read_ms + io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],
CAST(100. * io_stall_write_ms/(io_stall_write_ms + io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],
(num_of_reads + num_of_writes) AS [Writes + Reads], num_of_bytes_read, num_of_bytes_written,
CAST(100. * num_of_reads/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],
CAST(100. * num_of_writes/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],
CAST(100. * num_of_bytes_read/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],
CAST(100. * num_of_bytes_written/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) OPTION (RECOMPILE);

Which databases are consuming the most CPU and which particular procedures?

SELECT 
    DB_NAME(qt.dbid) AS DatabaseName
  , SUM(qs.total_worker_time) AS [TotalWorkerTime]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    DB_NAME(qt.dbid) IS NOT NULL
GROUP BY
    qt.dbid
ORDER BY
    SUM(qs.total_worker_time) DESC
OPTION
    (RECOMPILE) ;

SELECT TOP (1000)
    DB_NAME(qt.dbid) AS DatabaseName
  , qt.[text] AS [SP Name]
  , qs.total_worker_time AS [TotalWorkerTime]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    DB_NAME(qt.dbid) IS NOT NULL
ORDER BY
    qs.total_worker_time DESC
OPTION
    (RECOMPILE) ;
share|improve this answer

Without doing anything else, add more RAM in the server. RAM is used for caching data and plans. So add more to reduce pressure there. Adding too much RAM is possible but with the amount you've got going on, I doubt it.

Then, run some dmv queries to find things to tune:

Also review your disks:

  • separate volumes for tempdb
  • move your largest databases onto separate volumnes

Finally, are you virtual or physical? You should be physical and dedicated for loaded database servers.

share|improve this answer

It's not a limit in SQL server, you can easily push way beyond 600 databases given enough CPU and disk. It's more likely some inefficiencies are compounding and saturating some resource.

How is performance deteriorating? Slower screen refreshes? Could be a bottle neck at the web server, assuming it's a web front end. Slower report generation? Inefficient queries? or maybe just too much data going down the wire?

Are the workloads spread across all the databases? Or do the larger ones generate 10x the transactions? Depending on the number of servers, you might want to isolate the larger databases from the small ones.

I would start with some perfmon, check disk queue length, disk idle%, and CPU%; and make sure your disk queues aren't consistently high.

Are you maintaining each database, rebuilding indexes, regular transaction log backups, statistics updates? If you're using auto-update on stats, that could cause slowdowns at random intervals.

share|improve this answer

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.