database running slow after moving to another serv

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)

database running slow after moving to another serv

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-30 : 20:47:12

Hi,

My database was previously running on sql2000 with 2 gigs of RAM and 2 x 2.8ghz XEON processors, and was running pretty decently.

I've now upgraded to SQL2005, 8 gigs of RAM, and 1 Intel 5130 2ghz processor (supposed to be more CPU power than previously)

The problem is its now running very SLOW.

I have run a trace and I'm finding queries that used to take 50,000 reads are now taking 1.4 million reads (25x more) The system runs for a decent amount of time but then SLOWS down massively for awhile. I can't find any cause yet.

What could be causing this ? What steps can I take towards resolving this? I have ran Tara's isp_ALTER_INDEX to try and help, I'm not sure what else to do.

Any suggestions are GREATLY appreciated..

Thanks very much,
mike123

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-01 : 10:19:56

Did you rebuild index or update statistics after moving the db to sql2k5? Is the db in compatibility level 90?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-01 : 14:57:07

I defragged the indexes via Tara's isp_ALTER_INDEX , I do not believe I rebuilt them. Should I? What command should I type to Rebuild the whole DB? Will this knock my DB offline ?

Yes I also set the DB compatibility to 90.

Thanks!
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-01 : 15:08:30

If you ran isp_ALTER_INDEX with the rebuild option, then you are good. If you instead used REORGANIZE, then I'd suggest running it with rebuild so that you can get a complete defrag.

Have you run Performance Monitor to determine if there any hardware bottlenecks?

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-01 : 17:25:09

Hi Tara,

I ran it as seen below. I guess that means I have done all the db optimizations that could have been de-optimized after moving the system? I haven't changed any indexes etc from when it was running great before.

I will look more into hardware bottlenecks, but everything about my system is better, supposedly. I am a bit suspicious of the CPU giving problems, but today I have added another and will watch things.

Memory 2gb -> 8gb
HD RAID 1 -> RAID 0+1
SQL2000 -> SQL 2005
32 bit OS -> 64 bit OS
CPU 2 x 2.8ghz XEON -> 1 x 2.0ghz 5130 intel dual core

(now temporarily running with 2 x 2.0ghz 5130 intel dual core to see if CPU was the problem, I was told it should not be but will try googling some more benchmarks today)


 EXEC isp_ALTER_INDEX 
                            @dbName = 'dbname', 
                            @statsMode = 'DETAILED', 
                            @defragType = 'REBUILD', 
                            @minFragPercent = 10,
                            @maxFragPercent = 100,
                            @minRowCount = 1000

thanks again!
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-01 : 17:55:41

You might want to try @minFragPercent = 1 and @minRowCount = 1 that way you know you've hit everything. Run it during a slow period only such as late at night.

If you aren't familiar with Performance Monitor, then have you at least checked Task Manager to see if the CPU utilization is flat lined high or is it peeks and valleys for the chart?

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-01 : 18:15:42

quote:
Originally posted by tkizer

You might want to try @minFragPercent = 1 and @minRowCount = 1 that way you know you've hit everything. Run it during a slow period only such as late at night.

If you aren't familiar with Performance Monitor, then have you at least checked Task Manager to see if the CPU utilization is flat lined high or is it peeks and valleys for the chart?

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

Hi Tara,

Your follow ups are MUCH appreciated :) Yesterday the CPU was flat lined high as I had one processor, today its running quite a bit better since I added a second as an emergency solution.

The thing is that I believe I should be able to run quite smoothly on just 1 CPU (providing the sales engineer told me correct information on this chips capacity)

I will re run your SPROC tonight with @minFragPercent = 1 and @minRowCount = 1 and see what happens.

Also, I plan to prune the DB of old info that is not needed anymore, or needed the least anyways. What's the best way to ensure that deleting lots of rows doesnt have negative effects on the DB (for instance deleting 1million of 5 million rows in a table) Just updating stats? Will SQLServer be ok on its own?

Thanks again, :)
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-01 : 18:19:56

Here's how I gracefully delete data:
http://weblogs.sqlteam.com/tarad/archive/2003/10/15/305.aspx

EDIT: I should mentioned that it is for SQL Server 2000. In SQL Server 2005, you do it a bit different now.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources