[sims] Spring release + Server 2003 = Slow

[vikpaw]

When will a patch be available I have staff that cannot update the registers and I keep getting faults and timeouts all the time the processing time is 80% and above most of the day. @PhilNeal who is best to speak to so we can get this resolved as I have ofsted coming in and the attendance staff and teachers need to get access to reports quickly and SIMS is just plain not responding.

@whitejames - to EG

It's out now. Ask your Support team - patch 18205.

I got it this morning as a precaution in case i needed it.

[PhilNeal]

If any school still has major performance issues please ensure that you raise a case. We believe that the fixes should have brought CPU rates down to sensible levels.

[mrbios]

Well what a difference a patch makes. I had originally run the patch designed to improve performance from the home screen and that did help a bit but not enough. Today though i ran patch 14265 (the re-indexing patch) because we were having an issue with sims discover data transfers not working, and the CPU load has taken a jump off the cliff. It's the first time i've seen the CPU load hit 0% on that server at any point in a long time!

 

@PhilNeal Could this help anyone else still seeing an issue or is it likely our database was just REALLY in need of reindexing?

[zag]

It says that the re-index patch is an option on the knowledge base article about this

 

If a site is having performance issues with the reporting engine and experiencing timeouts then they can choose to amend the connect.ini file to increase the CommandTimeout parameter as described earlier in this thread.

 

In addition there are generic approaches to improving performance on site

1) run the Truncate Log and re-index Patch (14265)

2) archive the attendance marks for historic years

 

Anyone know how you "archive the attendance marks"?

[Rawns]

It says that the re-index patch is an option on the knowledge base article about this

 

 

 

Anyone know how you "archive the attendance marks"?

 

On SupportNet, Resource 20810 - Monitoring Session and Lesson Attendance handbook. Section 6 - Housekeeping rules, there is an Archive Attendance Marks section documenting it.

 

Roughly, there is a routine you run in SIMS .net to select the years to archive, then you run "archiveattendancemarks.exe" as a scheduled task overnight on the SIMS server to actually action the archive:

 

"D:\Program Files\Microsoft SQL Server\MSSQL10.SIMS2008\MSSQL\Binn\archiveattmarks.exe" /S %COMPUTERNAME%\SIMS2008 /D SIMS /U sa /P ****** /V

 

The results of the archive are written in the Event log on the server.

Edited May 23, 2013 by Rawns

[Ben-BSH]

On SupportNet, Resource 20810 - Monitoring Session and Lesson Attendance handbook. Section 6 - Housekeeping rules, there is an Archive Attendance Marks section documenting it.

 

Roughly, there is a routine you run in SIMS .net to select the years to archive, then you run "archiveattendancemarks.exe" as a scheduled task overnight on the SIMS server to actually action the archive:

 

"D:\Program Files\Microsoft SQL Server\MSSQL10.SIMS2008\MSSQL\Binn\archiveattmarks.exe" /S %COMPUTERNAME%\SIMS2008 /D SIMS /U sa /P ****** /V

 

The results of the archive are written in the Event log on the server.

 

When running that, I get the following :

 

Microsoft Windows [Version 5.2.3790]

© Copyright 1985-2003 Microsoft Corp.

 

C:\Documents and Settings\Administrator>"D:\Program Files\Microsoft SQL Server\MSSQL10.SIMS\MSSQL\Binn\ArchiveAttMarks.exe" /S SIMSSERVERNAME\SIMS2008 /D SIMS /U sa /P ******** /V

 

Server=SIMSSERVERNAME\SIMS2008;Database=SIMS;Uid=sa;Pwd=********;

 

Unhandled Exception: System.ArgumentException: The path is not of a legal form.

at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPath

Length)

at System.IO.DirectoryInfo..ctor(String path)

at ArchiveAttMarks.Program.StartArchiving()

at ArchiveAttMarks.Program.Main(String[] args)

 

C:\Documents and Settings\Administrator>

 

Any ideas? Our performance here is pretty poor, but we have a lot of years to Archive which might make a difference!

 

Thanks

 

Ben.

[Rawns]

When running that, I get the following :

 

Microsoft Windows [Version 5.2.3790]

© Copyright 1985-2003 Microsoft Corp.

 

C:\Documents and Settings\Administrator>"D:\Program Files\Microsoft SQL Server\MSSQL10.SIMS\MSSQL\Binn\ArchiveAttMarks.exe" /S SIMSSERVERNAME\SIMS2008 /D SIMS /U sa /P ******** /V

 

Server=SIMSSERVERNAME\SIMS2008;Database=SIMS;Uid=sa;Pwd=********;

 

Unhandled Exception: System.ArgumentException: The path is not of a legal form.

at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPath

Length)

at System.IO.DirectoryInfo..ctor(String path)

at ArchiveAttMarks.Program.StartArchiving()

at ArchiveAttMarks.Program.Main(String[] args)

 

C:\Documents and Settings\Administrator>

 

Any ideas? Our performance here is pretty poor, but we have a lot of years to Archive which might make a difference!

 

Thanks

 

Ben.

 

Odd. I assume your SIMS server is actually called SIMSSERVENAME, or it's a disguised alternative?

 

In the output, the SA user name contains a space.That could do it?

 

Server=SIMSSERVERNAME\SIMS2008;Database=SIMS;Uid=s a;Pwd=********;

[Rawns]

Found a solution to this error on SupportNet:

 

As per current functionality SIMS user logins do not have access rights to execute the ArchiveAttMarks.exe.

It can be done either using Trusted login or SQL server User(sa) .

In the help file it is mentioned as SQL server user (this does not mean Sims user).

Sql server user is the Sa user which we use to login to the SQL server Management studio.

 

Help file :

Editing the Advanced Properties for the Scheduled Task

1. Ensure that the Task tab is displayed.

2. Amend the text in the Run field to display the following archiving parameters.

If you are accessing SIMS using a User Name and Password:

 

“C:\ProgramwFiles\MicrosoftwSQLwServer\MSSQL.1\MSSQL\Binn\ArchiveAttMarks.exe”w/Sww/Dww/Uww/Pww/V

 

If you are accessing SIMS using Windows® Authentication (a Trusted User):

 

“C:\ProgramwFiles\MicrosoftwSQLwServer\MSSQL.1\MSSQL\Binn\ArchiveAttMarks.exe”w/Sww/Dww/Vw/T

[Ben-BSH]

Microsoft Windows [Version 5.2.3790]

© Copyright 1985-2003 Microsoft Corp.

 

C:\Documents and Settings\Administrator>"D:\Program Files\Microsoft SQL Server\M

SSQL10.SIMS\MSSQL\Binn\ArchiveAttMarks.exe" /S FAKESIMSSERVER\SIMS2008 /D SIMS /V

/T

Server=FAKESIMSSERVER\SIMS2008;Database=SIMS;Trusted_Connection=yes;

 

Unhandled Exception: System.ArgumentException: The path is not of a legal form.

at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPath

Length)

at System.IO.DirectoryInfo..ctor(String path)

at ArchiveAttMarks.Program.StartArchiving()

at ArchiveAttMarks.Program.Main(String[] args)

 

Sorry, that was a typo with the space in sa, while editing out other non-wanted info.

 

So running with windows auth, I get the same message Tried running as sa, sysman and via Administrator on windows auth.

 

Thanks in advance

 

Ben

[splattthecat]

Microsoft Windows [Version 5.2.3790]

© Copyright 1985-2003 Microsoft Corp.

 

C:\Documents and Settings\Administrator>"D:\Program Files\Microsoft SQL Server\M

SSQL10.SIMS\MSSQL\Binn\ArchiveAttMarks.exe" /S FAKESIMSSERVER\SIMS2008 /D SIMS /V

/T

Server=FAKESIMSSERVER\SIMS2008;Database=SIMS;Trusted_Connection=yes;

 

Unhandled Exception: System.ArgumentException: The path is not of a legal form.

at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPath

Length)

at System.IO.DirectoryInfo..ctor(String path)

at ArchiveAttMarks.Program.StartArchiving()

at ArchiveAttMarks.Program.Main(String[] args)

 

Sorry, that was a typo with the space in sa, while editing out other non-wanted info.

 

So running with windows auth, I get the same message Tried running as sa, sysman and via Administrator on windows auth.

 

Thanks in advance

 

Ben

 

Which version of SQL are you using, R2 includes an _50 in the path too.

[Ben-BSH]

Thanks, for some reason it just worked today!

 

- We use 2008 std, but I usually drag and drop exe's into a cmd window anyway to ensure all paths are correct and happy!

 

thanks to all that helped

 

Ben.