Recommendation when using SQL (Express or licensed Server)

AyaNova 1.x - 6.x AyaNova 3.x to 6.x
1

Quote from Microsoft:

The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time (including importing, etc) these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. Remedy this by reorganizing the index.

Issues that you may encounter is slow response, timeout errors displaying grids, timeout errors displaying detailed reports, etc

Microsoft recommends that you run maintenance on a regular basis.

If using licensed SQL Server, you can just easily use the SQL Management Studio for the full AyaNova which includes the Maintenance Wizard that you can set up to reorganize and reindex and schedule to re-occur on a regular basis so that you do not manually have to do it every time.

If using SQL Express, refer to this very useful topic by Jasper Smith at http://www.sqldbatips.com/showarticle.asp?ID=27and http://www.sqldbatips.com/showarticle.asp?ID=29

Basic steps are - refer to the links above for details:

Download the Expressmaint utility from http://www.sqldbatips.com/showarticle.asp?ID=27
Extract to root of the C:\ of the SQL Express server
Download the expressmaint code from http://www.sqldbatips.com/showarticle.asp?ID=29
Rename expressmaint.sql
Create a folder called C:\Reports

From a DOS command at the C:, type in the following :

sqlcmd -S .\SQLExpress -i c:\expressmaint.sql

when it returns to the DOS command type in at C:\ DOS prompt:

expressmaint -S SQLExpress -D AyaNova -T REINDEX -R c:\reports -RU DAYS -RV 1

This attaches to the server SQLExpress, on the database AyaNova, runs the REINDEX code, and puts output into the report file located in C:\Reports.

2

Wanted to post here for the benefit of all:

A company using AyaNova with SQL Express 2008 R2 would experience a timeout error when print previewing a Detailed type report on more than 4500 records (if 4000, no error. If 4500 or higher, timeout error).

After checking server logs and sql logs, confirming SQL optimization and reindex of the database it was determined that the issue was due to the RAID virtualization setting Write Through. The Write Policy was changed to Write Back as shown in the image below, and the timeout errors no longer occur.

3

This post is not a specific recommendation to use, but is provided only as additional knowledge for any that wish to review. Use or misuse of the information provided is your responsiblity.

Check out http://ola.hallengren.com/ for SQL Server Maintenance solution

4

Apparently the Expressmaint utility is not compatible with SQL Express 2008 R2 or SQL Express 2012

For re-indexing a SQL Express database, you can also use the following:

[ul]
[li]Open SQL Management Studio
[/li][li]Log in using SQL Authentication and your sa password
[/li][li]Expand Databases on the left so you can see your sql databases
[/li][li]Right-click the AyaNova database, and select New Query
[/li][li]Copy the sql query below and paste into the New Query in your SQL Management Studio for your AyaNova database
[/li][li]Click on the Execute menu option at the top
[/li][li]Will take less than a minute and will show " Command(s) completed successfully." when completed.
[/li][/ul]

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+’.’+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ’ + @TableName + ’ REBUILD WITH (FILLFACTOR = ’ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO