ACCESS FEED: [MS_AccessPros] Re: Access 2003 on Windows2008 R2 64bit performance issue.

Selasa, 27 November 2012

[MS_AccessPros] Re: Access 2003 on Windows2008 R2 64bit performance issue.

Ozair

Are you running scheduled maintenance on your SQL Server database? Is the database properly indexed? Have you tried re-indexing all the tables? SQL Server 2000 is famous for doing a poor job re-indexing through the maintenance wizard.

Here is a procedure for re-indexing all tables:
/*
Reindex an entire database.
Work-around for maint wizard failure.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
902388 (http://support.microsoft.com/kb/902388/) Event ID: 208 may be logged
*/

SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON

USE MyDatabase -- CHANGE THE DATABASE NAME
go
DECLARE @tabname sysname
DECLARE @dbstring varchar(300)
DECLARE @exec_string varchar(300)

DECLARE tabDBCC CURSOR FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tabDBCC
FETCH NEXT FROM tabDBCC INTO @tabname

SELECT @dbstring = DB_NAME()
PRINT 'Starting DBCC DBREINDEX for database ' + upper(@dbstring)

WHILE (@@fetch_status = 0)
BEGIN
PRINT 'Reindexing table ' + upper(@tabname)
SELECT @exec_string = 'dbcc dbreindex ([' + @tabname + '])'
EXEC(@exec_string)
FETCH NEXT FROM tabDBCC INTO @tabname
END
CLOSE tabDBCC
DEALLOCATE tabDBCC

PRINT 'Finished DBCC DBREINDEX for database ' + upper(@dbstring)

/****End Code****/

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com

--- In MS_Access_Professionals@yahoogroups.com, "Ozair" <ozairkhalid@...> wrote:
>
>
> Hello,
>
> We have just moved to new server.
>
> Detail...
> Machine : Intel Xeon 2.33Ghz ( 2 processors ) 16 GB RAM
> Windows : Windows 2008 Server R2 (64 Bit) Service Pack 1
> Back End : SQL Server 2000 Service Pack 4
> Front End: MS Access 2003 Service Pack 3 ( with Post SP3 Hotfix )
>
> We expected improved performance and better speed instead inverse has happened to us.
>
> For example, there is a "process" behind a command button that adds 100 ( just one hundred ) records. In the old machine it used to take just a couple of seconds to do it but in the new machine it took 2 minutes to complete the "process".
>
> ALSO
>
> We are frequently seeing
> a - "Running query..." in the bottom of the form.(for too longer time)
> b - "Not Responding" or white screen.
>
> Can someone help?
>
> Ozair.
>