Settings for sql 2008 instance

Question

Post reply

Settings for sql 2008 instance

Oracle_91

SSChampion

Points: 14548
More actions
February 3, 2010 at 8:25 pm

#210517

Hi,

I have a question on server Level configurations when we are going for 2008 migration from 2000. What all parameters can i set to the 2008 instance to improve the performance compared too sql 2000 Like Memory settings, multiprossor settings so that utilise my cpu's optimally ....

Thanks in Advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Have you tried this in a Development or QA Environment?

I suggest that you try this & run the SQL Server Installation Wizard.

This will check the paramater and a number of other things and advise you on what steps to take.

Please refer to thge following link:

http://msdn.microsoft.com/en-us/library/ms144267.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

It will to a degree depend upon your app, however, you should put the database to 2008 compat mode and make all the set commands to allow use of such things as partitioning and filtered indexes - but it's important to test that your apps work with the database changes. None of the builtin reporting in SSMS works unless your db is in 2008 mode too.

Memory is always good, if you're using x64 be aware the o/s needs a fair bit ( depending on your server ) and in x64 you MUST set your max memory. I'd actually advise always setting memory in any version/edition.

parallelism is much different in 2008 compared to 2000 and the good thing is that you can change these setitngs without a server restart.

here's a script I use to set those options and remove auto created stats.

You should be aware that when you convert a db to sql2008 from sql2000 it creates a schema for every user - I also delete all these schemas.

declare @dbname sysname = db_name();

exec ('ALTER DATABASE '+@dbname+' SET COMPATIBILITY_LEVEL = 100');

exec ('ALTER DATABASE '+@dbname+' SET PAGE_VERIFY CHECKSUM');

exec ('ALTER DATABASE '+@dbname+' SET ANSI_NULL_DEFAULT OFF');

exec ('ALTER DATABASE '+@dbname+' SET ANSI_NULLS ON');

exec ('ALTER DATABASE '+@dbname+' SET ANSI_PADDING ON');

exec ('ALTER DATABASE '+@dbname+' SET ANSI_WARNINGS ON');

exec ('ALTER DATABASE '+@dbname+' SET ARITHABORT ON');

exec ('ALTER DATABASE '+@dbname+' SET CONCAT_NULL_YIELDS_NULL ON');

exec ('ALTER DATABASE '+@dbname+' SET NUMERIC_ROUNDABORT OFF');

exec ('ALTER DATABASE '+@dbname+' SET QUOTED_IDENTIFIER ON');

GO

--

-- clean system stats

--

create table #stats(numkey int identity(1,1),cmd varchar(1000));

insert into #stats(cmd)

select 'drop statistics ['+OBJECT_NAME(object_id)+'].'+name+';' as cmd from sys.stats where name like '_WA_Sys%'

and [object_id]>100;

declare @count int =1;

declare @cmd varchar(1000)='';

while @count<=(select MAX(numkey) from #stats)

begin

select @cmd = cmd from #stats where numkey = @count;

exec(@cmd);

set @count+=1;

end

drop table #stats;

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Welsh Corgi

SSC Guru

Points: 116520

February 4, 2010 at 5:43 am

#1113656

Thanks The GrumpyOldDBA...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Oracle_91 SSChampion Points: 14548 More actions · Answer 4

Oracle_91

SSChampion

Points: 14548

February 4, 2010 at 11:37 pm

#1114198

Hi All,

Am asking about something related to server configurations when going to sql 2008.

I mean like turning on AWE for memory.

Thanks!

Ananda-292708 Ten Centuries Points: 1339 More actions · Answer 5

based on OS version 32 or 64 and the amount of memory you might have to

1)Make necessary changes in Boot.ini -- i mean /PAE /3GB : enough info is available on this

2)Ensure the pagefiles are set to 1.5 times the memory

3)if using AWE ensure lockpages in memory is set to the account under which SQL will run.

4)if required Enable AWE SQL

5)Also define the SQL Max memory . Incase DBserver is shared by SSIS and other application leave enough memory for them .

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

mahesh.vsp (2/3/2010)

Hi,

I have a question on server Level configurations when we are going for 2008 migration from 2000. What all parameters can i set to the 2008 instance to improve the performance compared too sql 2000 Like Memory settings, multiprossor settings so that utilise my cpu's optimally ....

Thanks in Advance.

Maximum worker threads = 255 in 2000.

Change it to 0 in 2008.