MSSQLWIKI

Karthick P.K on SQL Server

Archive for May, 2012

What is Target Server Memory (KB)?

Posted by Karthick P.K on May 27, 2012

What is Target Server Memory (KB)?

 

To  super simplify  in conventional memory model  SQL Server calculates something like target1 and target2 pages using below formula

 

               

Target1 = Current committed pages of SQL Server + ( Available Physical Memory – min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2))

ullAvailPageFile: The maximum amount of memory the current process can commit, in bytes. This value is equal to or smaller than the system-wide available commit value. To calculate the system-wide available commit value, call GetPerformanceInfo and subtract the value of CommitTotal from the value of CommitLimit.

 

If (Max Server Memory < ullAvailPageFile)

{

Target2= Max Server Memory

}

Else

{

Target2=Total Physical Memory

}

 

Target Server Memory (KB) =Minimum (Target1,Target2)

 

So if AvailablePhysicalMemory is very high (or) when MaxServermemory is low then Target Server Memory (KB) would give you the MaxServerMemory  else value derived from above formula.

 

 

 

Thanks

Karthick P.K

Posted in Performance, SQL Server Engine, SQL Server memory | Tagged: , , , | 2 Comments »

SQL Server performance degraded in 32-Bit SQL Server after adding additional RAM.

Posted by Karthick P.K on May 18, 2012

 

Do you know that adding additional RAM can affect the performance of SQL Server Sometimes?

I am not going to write how Optimizer can some times choose suboptimal plans when we have large amount of memory on the system but We will see how the memory which can be used by other memory clerks (aks: stolen memory) can shrink when we have large physical memory and AWE enabled.

 

If you notice  performance of 32-Bit SQL Server degraded after you added additional RAM or if you see SQL Server memory errors like ones below after adding RAM then it could be because of Large BUF structures which reduced the size of Bpool.

 

Errors:

SQL Server 2005/2008

 

Buffer Pool errors:

    BPool::Map: no remappable address found.

 

Either BPool or MemToLeave errors:

    Error: 17803 “Insufficient memory available..”

    Buffer Distribution:  Stolen=7901 Free=0 Procedures=1 Inram=201842 Dirty=0 Kept=572…

 

Extract from SQL Server memory design

{

SQL Server "User address space" is broken into two regions: MemToLeave and Buffer Pool

 

Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during start up as below.

 

MTL (Memory to Leave)= (Stack size * max worker threads) + Additional space to load Dll’s.

 

Stack size =512 KB per thread for 32 Bit SQL Server (904K under WOW)

 

I.e. = (256 *512 KB) + 256MB =384MB

 

Additional space to load Dll’s= 256 MB from SQLServer2000. This space is used to store COM objects, Extended stored procedure, Linked server in SQL Server process

 

Note: Additional space to load Dll’s can be modified using -g startup parameter.

 

on any machine with less than 4 processors the Maximum worker Thread’s is always 256 by default (unless we change the value using SP_configure)

 

SQL Server Buffer Pool is minimum of “Physical RAM “ or “user mode memory(2GB or 3GB) – MTL-  BUF structures”

 

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

 

}

 

 

When AWE is enabled in 32-Bit SQL Server M_pbuf (part of BUF structures) which is mentioned earlier is calculated and allocated for entire physical memory on the system . Regardless of “MAX Server Memory”   This is to adjust Max server memory without restarting SQL Server.

 

SQL Server requires 8MB to create M_pbuf for every 1GB of RAM available on the server.

 

Machine with 64 GB RAM can consume 64 (RAM) *8MB (M_pbuf for each GB) =512 MB just for the BUF array alone.

 

So the amount of BPOOL available for SQL Server is adversely affected.

 

Going back to the previous formula for BPOOL. Size of Bpool for 32-Bit SQL Server with AWE enabled and 64 GB of RAM would be.

 

BPool = Minimum (Physical memory, User address space – MTL) – BUF structures

 

BPool= Minimum (64GB, (2GB-384MB)) – BUF structures (512+ MB)

 

Bpool would approximately become 1GB.  Since size BPOOL become very small we might end up with memory errors.

 

Note:  In 32-Bit SQL Server Only data pages an index pages can be placed in AWE memory. So the memory available for other SQL Server memory objects is still limited to BPOOL and MTL.  

 

How to resolve this issue?

Remove few GB of RAM from server J if you can convince your management that removing RAM will improve performance.

(Or)

There is a startup trace flag TF 836 which you can use to indicate that BUF’s need to be allocated only for the configured max server memory setting. Enable this Trace Flag (836) and Reduce the “MAX Server Memory” of SQL Server.

(Or)

Enable /3GB. This will increase the Size of SQL Server BPOOL by 1GB providing relief to SQL Server BPOOL pressure.

Note: When the physical RAM in the system exceeds 16 GB and the /3GB switch is used, the operating system will ignore the additional RAM until the /3GB switch is removed.

 

 

 

Related posts

Troubleshooting steps for all SQL Server Memory errors

Trouble shooting working set trim “A significant part of SQL Server process memory has been paged out”

SQL Server lock pages in memory should I use it?

SQL Server memory leak

What is new in SQL Server 2012 Memory

How to set max server memory and min server memory

 

If you liked this post do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group MSSQLWIKI

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Memory, Performance, SQL General, SQL Server Engine | Tagged: , , , , , , , | 6 Comments »

Transaction log for the database is growing and system SPID is holding open transaction

Posted by Karthick P.K on May 18, 2012

Transaction log for the database is growing and system SPID is holding open transaction

Select log_reuse_wait_desc From Sys.databases where name=’%’ will return

‘ACTIVE_TRANSACTION’

DBCC Opentran says there is a Opentran held by system thread (Similar to SPID (server process ID): 7s and 6s in below example)

Oldest active transaction:

SPID (server process ID): 7s

UID (user ID) : -1

Name : user_transaction

LSN : (543263:28204:1)

Start time : Dec 8 2011 11:02:19:483PM

SID : 0x01

Replicated Transaction Information:

Oldest distributed LSN : (544101:227459:27)

Oldest non-distributed LSN : (543263:28204:1)

Oldest active transaction:

SPID (server process ID): 6s

UID (user ID) : -1

Name : tran_sp_MScreate_peer_tables

LSN : (958510:111529:1)

Start time : May 6 2012 5:33:37:240AM

SID : 0x01

Replicated Transaction Information:

Oldest distributed LSN : (962272:93878:5)

Oldest non-distributed LSN : (0:0:0)

If we see Name: tran_sp_MScreate_peer_tables in DBCC opentran

Follow http://support.microsoft.com/kb/2509302 ie. Restart SQL-Server and wait for Script upgrade to complete and then start the SQL Server Agent.

If you see Name : user_transaction then script upgrade for database has failed leaving an open transaction. Verify the SQL Server error log to identify why the script upgrade has failed.

We can enable Trace flag -T3601 which causes the first 512 characters of each batch being executed to be printed to the error log while doing script upgrade . Identify the batch which is failing and troubleshoot the batch.

For more information Follow : https://mssqlwiki.com/2010/11/17/sqlserver2008-script-level-upgrade-for-database-master-failed-because-upgrade-step-sqlagent100_msdb_upgrade-sql-encountered-error-574-state-0-severity-16/

Thanks

Karthick P.K

 

Posted in Space management, SQL Server Engine, Startup failures | Tagged: , , , , , | 6 Comments »

Copy database wizard or replication setup might fail due to broken dependency

Posted by Karthick P.K on May 4, 2012

Copy database wizard would fail with below error while creating views or user defined functions if the dependency lists of objects is  broken.

Error:

failed with the following error: “Invalid object name ‘dbo. .”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()

at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()

at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()

Replication setup would also fail while applying the scripts if the dependency lists of objects is broken.

Error:

The schema script ‘XXX_4.sch’ could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

Get help: http://help/MSSQL_REPL-2147201001

Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164)

Get help: http://help/MSSQL_REPL20164

Invalid object name ‘. (Source: MSSQLServer, Error number: 208)

Get help: http://help/208

--Below script will fix the broken dependencies on all the objects
----------------------------------------------------------------------------
--List of objects for which referenced objects are missing.
--ex: View created on table XYZ and table XYZ is dropped
----------------------------------------------------------------------------
SELECT OBJECT_NAME (referencing_id),referenced_database_name, referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name not in (select name from sysobjects)

create table #t_excluded_modules (module_name sysname)
go

create table #t_modules_refreshed_in_end (module_name sysname)

go

------------------------------------------------------------------------------
--
-- get the list of modules whose dependencies have to be refreshed
--
-- Comment:
-- in the list we're not considering procedures or triggers because
-- because they can be created in any order, which means they can be refreshed
-- in any order
--
------------------------------------------------------------------------------
create table #t_user_views_or_tables (module_id int)
insert into #t_user_views_or_tables(module_id)
		select object_id from sys.objects where
			type in ('V', 'FN', 'IF', 'TF')
			and name not like 'MSMerge%'
			and is_ms_shipped <> 1
			and name not in (select * from #t_modules_refreshed_in_end)
			and name not in (select * from #t_excluded_modules)

insert into #t_user_views_or_tables(module_id)
		select object_id from sys.objects where
			name in (select * from #t_modules_refreshed_in_end)

----------------------------------------
--
-- get the dependency table
-- |---------------------------------|
-- |  referencing_id | referenced_id |
-- | ----------------|---------------|
-- |      XXX        |     XXX       |
-- | ----------------|---------------|
--
----------------------------------------
Declare @module int, @message varchar(1000), @str nvarchar(1000)
create table #t_dependency_table (referencing_id int, referenced_id int)
DECLARE modules_cursor CURSOR FOR SELECT module_id FROM #t_user_views_or_tables
open modules_cursor
fetch next from modules_cursor into @module

IF @@FETCH_STATUS <> 0
	PRINT '            <<None>>	No module to refresh'

while @@FETCH_STATUS = 0
	begin
		select @str = quotename(schema_name(objectproperty(@module, 'schemaid'))) + '.' + quotename(object_name(@module))
		select @message = '            trying to refresh ' + @str
		print @message
		exec sys.sp_refreshsqlmodule @str
		select @message = '            ' + @str + ' was refreshed'
		print @message

		insert into #t_dependency_table (referencing_id, referenced_id)
			select distinct object_id as referencing_id, referenced_major_id as referenced_id
				from sys.sql_dependencies
				where object_id <> referenced_major_id      -- to avoid self recursion for functions
					  and object_id = @module

		fetch next from modules_cursor into @module
	end

close modules_cursor
deallocate modules_cursor

-------------------------------------------------------------------------------------------
--
-- get the the bottom of the dependency list i.e. independent modules
-- i.e.
-- get the list of referenced_ids in the dependency table which
-- don't occur in the referencing_ids column
--
-- Comment:
-- if there are circular dependencies then the few modules which form a circular dependency
-- would be ignored in the independent modules list
--
-------------------------------------------------------------------------------------------
create table #t_independent_modules (modules int)

insert into #t_independent_modules (modules)
	select #t_dependency_table.referenced_id from
			#t_dependency_table left outer join #t_dependency_table t2
			on #t_dependency_table.referenced_id = t2.referencing_id
	where
			t2.referencing_id is NULL

-------------------------------------------------------------------------------------------
--
-- build the ordered list of dependencies starting with the independent modules
-- in the beginning first few rows, ones dependent on it in the following rows and so on...
--
-- there can be tricky cases of dependencies such as
-- V1 -> V2 -> V3
--  |           ^
--  +-----------+
--
-- in the above example the refresh order would be V3, V1, V2, V1.
-- note that V1 is being refreshed twice, the last refresh of V1 after V2 is important.
--
-------------------------------------------------------------------------------------------
create table #t_final_dependency_list (id_num int IDENTITY(1,1), modules int)

while exists (select * from #t_independent_modules)
	begin
		-- append the set of independent modules into a list
		insert into #t_final_dependency_list select * from #t_independent_modules

		-- get the set of dependent modules
		select distinct #t_dependency_table.referencing_id into #temp_table
			from #t_dependency_table
			where #t_dependency_table.referenced_id in (select * from #t_independent_modules)

		-- clear up the list of independent modules
		truncate table #t_independent_modules

		-- the dependent modules now become the independent modules
		insert into #t_independent_modules select * from #temp_table

		-- delete the dependent modules list
		drop table #temp_table
	end

-----------------------------------------------------------
--
-- refresh the modules once more but in the right order now
--
-----------------------------------------------------------
declare modules_cursor_final cursor for
	select modules from #t_final_dependency_list order by #t_final_dependency_list.id_num

open modules_cursor_final
fetch next from modules_cursor_final into @module

IF @@FETCH_STATUS <> 0
	PRINT '            <<None>>	No module to refresh'

while @@FETCH_STATUS = 0
	begin
		if (select type from sys.objects where object_id = @module) in ('V', 'FN', 'IF', 'TF')
			and (select is_schema_bound from sys.sql_modules where object_id = @module) = 0
			begin
				select @str = quotename(schema_name(objectproperty(@module, 'schemaid'))) + '.' + quotename(object_name(@module))
				select @message = '            trying to finally, once more, refresh ' + @str
				print @message
				exec sys.sp_refreshsqlmodule @str
				select @message = '            ' + @str + ' was finally refreshed once again'
				print @message
			end

		fetch next from modules_cursor_final into @module
	end

close modules_cursor_final
DEALLOCATE modules_cursor_final

-----------
--
-- cleanup
--
-----------
drop table #t_excluded_modules
drop table #t_modules_refreshed_in_end
drop table #t_user_views_or_tables
drop table #t_dependency_table
drop table #t_independent_modules
drop table #t_final_dependency_list

Posted in Copy database wizard, Replication, SQL General | Tagged: , , , , | 5 Comments »

 
%d bloggers like this: