help tuning new sql2005 server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

help tuning new sql2005 server

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-18 : 16:45:00

Hi,

I just upgraded my sql2000 server (3gb Ram,2 x2.4ghz p4xeon processors) to sql2005 64 bit (4gb ram,1 x dual core 2.0 xeon processor) The single processor is supposed to be faster than the previous dual processors combined.

The problem is now I am having major performance problems, the queries are lagging pretty badly intermittently.

What steps should I take to resolve this? I am planning on upgrading to 8gb of RAM tonight or tommorow as a 'throwing hardware at this problem without really knowing what I'm doing approach.'

I am not very good with perfmon, but if you want any stats from there please let me know. I'm googling now what I should be checking for.

I can't figure out why its actually running worse, these problems got worse after I set a maintenance plan to run last night which did backups, reindex all the stuff etc... It failed after 2 hours and I woke up to horrible performance this morning.

Any help much appreciated!

Thanks,
mike123

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-18 : 16:47:26

As soon as you upgrade the DB, the first thing you should do is to reindex all the tables and run update stats on all the tables. The problems with bad query plans will only continue/worsen if the tables are not reindexed.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-18 : 17:01:23

Hi DinaKar,

I would love to try this.

What command should I use in sql2005 ?

much appreciated
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-18 : 17:04:17

Also, is this going to pretty much render the database unusable while this is happening? I guess I should have done this before putting it live =[

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-18 : 17:07:33

If you already have your CREATE INDEX scripts, you can add the clause DROP_EXISTING and run the job.
to update the stats you can do

UPDATE STATISTICS <table> WITH FULLSCAN

You can do this for all tables as:

SELECT 'UPDATE STATISTICS ' + [name] + ' WITH FULLSCAN' + CHAR(10)+ 'GO'
from sysobjects where type = 'u'

get the script as text and compile it.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-18 : 17:08:33

quote:
Originally posted by mike123

Also, is this going to pretty much render the database unusable while this is happening? I guess I should have done this before putting it live =[

Yes sir.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-18 : 17:10:24

dropping and recreating the indexes? I have some tables with 10 million rows, I am not sure but I think this will be a very slow operation ? This DB is live and I have a feeling this will halt it

Isnt there some great new command to sql2005 for online re-indexing? I haven't used it yet but thought I heard about it. I'm only a few days into sql2005 experience =

thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 17:13:31

Check out my new isp_ALTER_INDEX stored procedure in my blog. You will want to pass rebuild into it and probably do a detailed report. This should run during the slow periods, typically at night.

Here are all of my database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tara Kizer
http://weblogs.sqlteam.com/tarad/

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-18 : 17:16:49

awesome tara, I think I will even try this right now as my DB server is sittin on 100% cpu and just doing horrible.

I'll keep you posted

any other suggestions I'm all ears, and desperate! :)

thanks,
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-18 : 17:48:07

Hey Tara,

I rebooted sql, and ran your SPROC. So far things have been running decent after the reboot. I can't say for sure what the problem was as of right now. I created your UPDATE_STATISTICS sproc and an going to run that too.

I'm confused on what the "SAMPLE" value that is supposed to be passed is?

Thanks very much,
mike123

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-18 : 17:51:41

Since you just upgraded, I would recommend running it with FULLSCAN instead of the default sample size. For any future update stats, you can use the default sampling size. SQL Server, by default, takes 10% of the data, to determine the stats. Sometimes it worksout good. sometimes it may not.
Check out Books On Line regarding sampling sizes for UPDATE STATISTICS.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-18 : 18:03:57

Check out sys.dm_db_index_physical_stats in BOL for details on SAMPLE, DETAILED.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-04-19 : 04:07:08

Is your database in SQL-2000 compatibility mode? I think this robs some performance on SQL 2005. (Restoring a SQL2-2000 database on SQL-2005 leaves it in SQL-2000 compatibility)

Note that there are subtle differences, so your ought to do a QA test as part of changing to SQL-2005 compatibility mode.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources