Wednesday, April 27, 2011

Suggested Max Memory Settings for SQL Server 2005/2008

It is important to make sure you set the Max server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the host OS that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.  This setting controls how much memory can be used by the SQL Server Buffer Pool.  If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems.

This is for x64, on a dedicated DB server.

Physical RAM (GB)

MaxMem Setting

2 1500
4 3200
6 4800
8 6700
12 10600
16 14500
24 22400
32 30000
48 45000
64 59000

You can set this value with Transact-SQL like this (it may take 1-3 minutes to complete)

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
-- See what the current value is for 'max server memory (MB)'
EXEC sp_configure
-- Set max memory = 45000MB 
EXEC sp_configure 'max server memory (MB)', 45000

Monday, April 25, 2011

Use sys.dm_os_performance_counters to get your Buffer cache hit ratio and Page life expectancy counters

Buffer cache hit ratio

This means what is the percentage that SQL Server had the data in cache and did not have to read the data from disk. Ideally you want this number to be as close to 100 as possible.

In order to calculate the Buffer cache hit ratio we need to query the sys.dm_os_performance_counters dynamic management view. There are 2 counters we need in order to do our calculation, one counter is Buffer cache hit ratio and the other counter is Buffer cache hit ratio base. We divide Buffer cache hit ratio base by Buffer cache hit ratio and it will give us the Buffer cache hit ratio.
Here is the query that will do that, this query will only work on SQL Server 2005 and up.

   1: 1.SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 AS BufferCacheHitRatio

   2: 2.FROM sys.dm_os_performance_counters  a

   3: 3.JOIN  (SELECT cntr_value,OBJECT_NAME 

   4: 4.    FROM sys.dm_os_performance_counters  

   5: 5.    WHERE counter_name = 'Buffer cache hit ratio base'

   6: 6.        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME

   7: 7.WHERE a.counter_name = 'Buffer cache hit ratio'

   8: 8.AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

Page life expectancy

Now let's look at Page life expectancy.

Page life expectancy is the number of seconds a page will stay in the buffer pool, ideally it should be above 300 seconds. If it is less than 300 seconds this could indicate memory pressure, a cache flush or missing indexes.

Here is how to get the Page life expectancy

   1: 1.SELECT *

   2: 2.FROM sys.dm_os_performance_counters  

   3: 3.WHERE counter_name = 'Page life expectancy'

   4: 4.AND OBJECT_NAME = 'SQLServer:Buffer Manager'

Wednesday, April 20, 2011

script – percentage of identity values being used

Here is a script which list out all the identity columns and percentage used


SELECT	SCHEMA_NAME(t.schema_id) + '.' + AS TableName, AS ColumnName,
CASE idc.system_type_id
	WHEN 127 THEN 'bigint'
	WHEN 56 THEN 'int'
	WHEN 52 THEN 'smallint'
	WHEN 48 THEN 'tinyint'
END AS 'DataType',
idc.last_value AS CurrentIdentityValue,idc.seed_value,increment_value,
CASE idc.system_type_id
	WHEN 127 THEN ((CONVERT(INT, idc.last_value) - CONVERT(INT, idc.seed_value) )+1 )* 100. / 9223372036854775806/CONVERT(INT,increment_value)
	WHEN 56 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 )* 100.  / 2147483646/CONVERT(INT,increment_value)
	WHEN 52 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 )* 100.  / 32766/CONVERT(INT,increment_value)
	WHEN 48 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 ) * 100. / 256/CONVERT(INT,increment_value)
END AS 'PercentageUsed' 
FROM	sys.tables AS t
INNER JOIN sys.identity_columns idc ON idc.object_id = t.object_id
WHERE	idc.is_identity = 1
ORDER BY PercentageUsed DESC

Thursday, April 14, 2011

Index Fragmentation on Partitioned tables

Partitioning can be effectively used to break up a very large table into multiple partitions based on a column or partitioning key. However, there can be significant management overhead in maintaining partitioned tables. The features of partitioned tables that give you the ease of management can also conspire against you to make your maintenance a nightmare!

Thinking of each partition as its own table will help you understand how best to approach your maintenance. Index rebuilds can be quicker because you can rebuild just those partitions that you identify as being fragmented. Conversely, full partitioned Index rebuilds will take longer as each partition is its own b-tree.

Note that old style DBCC SHOWCONTIG command does not work for partitioned indexes. You need to familiarize yourself with the more powerful DMV sys.dm_db_index_physical_stats. This DMV returns fragmentation information at a partition level and provides you the necessary information to make a decision on which partitions require rebuilding or reorganizing.

-- to return fragmentation information on partitioned indexesSELECT
 object_name(a.object_id) AS object_name,
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') a
JOIN sys.indexes b on a.object_id = b.object_id and a.index_id = b.index_id
order by object_name(a.object_id), a.index_id,, b.type_desc, a.partition_number 

Once fragmentation has been identified we can rebuild the index statement for just that single partition:

--Rebuild only partition 10.
ALTER INDEX IX_alert_events_timestamp
ON dbo.alert_events
REBUILD Partition = 10;

If you're partitioning your data on a date field and you have a sliding window and you are only ever adding data to the right most partitions then you can save unnecessary overhead by not checking for fragmentation of older partitions where you already know they are not fragmented. sys.dm_db_index_physical_stats DMV allows you to specify a partition number. This greatly improves IO over full index scans and has the potential to shorten maintenance job durations.

Wednesday, April 13, 2011

Find the Size of Database

I encountered the situation recently where I needed to find the size of the database.

--size of database 
SELECT DB_NAME(database_id) AS DatabaseName,SUM((size*8)/1024) SizeInMB
FROM sys.master_files
GROUP BY DB_NAME(database_id) 
-- size of individual db files
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeInMB
FROM sys.master_files