Optimizing performance on SQL Server 2005 Express 1

[bubarooni1]

Hi,

I just 'upgraded' a Great Plains application from MSDE to 2005 Express. The performance is absolutely awful!

I know that MSDE could handle 2 processors and 2 GB's of RAM and I went in knowing about the 1 processor and 1 GB RAM limitation of 2005 Express, but as per a microsoft article on upgrading:

'The new SQL Server 2005 Express ceiling of 1 GB coupled with today's hight performance processors provides more than adequate capacity for a large number of concurrent users.'

Either I need to 'optimize' the Express install or that line is bull. The machine has a dual core AMD 5000+ processor and 4 GB's of RAM. I switched everyone's workstations to using ODBC SQL Native Client.

Any insights would be greatly appreciated.

Thanks In Advance

 

[mrdenny]

Have you updated the statistics since you did the upgrade? SQL Server 2005 can't use the SQL Server 2000 statistics and performance will be horrible until you manually update the statistics.

I also like to have people rebuild all the indexes after the upgrade as well (personal preference).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bubarooni1]

ummm... no i had not.

and that did seem to help, though i'll have to wait until monday and see what the users say.

should i also do a reindex? if so, does it matter whether i updatestats first?

also, i had on error on one db when i tried to updatestats.

Msg 1505, Level 16, State 1, Line 14
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.WDC51101' and index name 'PKWDC51101'. The duplicate key value is (33, 10, 535).

this is on the great plains DYNAMICS db. i tried to follow the directions on this microsoft kb to no avail:

http://support.microsoft.com/kb/139444

am i heading the wrong direction on that one?

 

[mrdenny]

That KB will be what you want to do in order to remove the duplicate record.

I recommend indexing. It shouldn't matter which you do first.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bubarooni1]

You Sir, are a genius!

Users are all happy. They've even removed the rope from the tree behind the bldg.

Nice blog too!

 

[mrdenny]

No problem.

Happy to help, and thanks.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bubarooni1]

Oddly enough, shortly after I declared victory here in this forum, the whole thing went to heck again. I found out that I had inadvertently installed the original SQL Server 2005 Express and updated it to SP 2 over the weekend hoping that would help, to no avail.

While rummaging across the internet trying to find similar problems, I found a discussion on the Auto Close property. Setting this property from the default value of True to False has instantly tripled the performance of Great Plains.

I'm not naive enough to believe that anything in life is that easy. Am I taking some awful risk by changing that value?

 

[mrdenny]

No, you are not placing yourself at risk by changing that setting. All that does is tell SQL to unload the database when there are no users using the system. If users are logging in and out very often through out the data the database will spend a lot of time opening and closing the file.

It could easily be that easy.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bubarooni1]

I believe the opening and closing is exactly what was causing the problem. I think I read that the value was 300 milliseconds of no activity. During a query or write operation could the inactivity factor pass and the db close, the query or write op start to do something and have to reopen the db again many times? That would kinda explain it to me.

Thanks for the explanation on what the Auto-Close does. I helps allay my fears. I'm just not used to things working out that easily...

 

[mrdenny]

No, SQL doesn't close the database while users are still connected. If a single user runs a query, then disconnects and then reconnects and runs another query then disconnects the database will have opened and closed two times.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bubarooni1]

ok, thanks again for the info!

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog