SQL Maintenance Questions

We use MS SQL Standard, not Enterprise. And we do the reindex on the weekend after Conversion Routine 6430, with all AppServers off and Simple Recovery Model. It takes 35 minutes for a 22gb db.

In a previous engagement on Progress, we rebuilt indexes weekly and Dump&Load yearly during Christmas break.
----- Original Message -----
From: "Clive" <clive.1972@...>
To: <vantage@yahoogroups.com>
Sent: Wednesday, July 24, 2013 7:30 AM
Subject: [Vantage] Re: SQL Maintenance Questions


Be aware that rebuilding indexes will lock tables, so if your rebuild takes a long time (23 hours for me) its highly likely that your TaskAgent AppServer will crash at some point leaving users unable to print etc.

One solution to this is to use MS SQL Enterprise and you can then keep your indexes online during a rebuild (you can't do this in Standard), otherwise you'll want to restart your TaskAgent after the rebuild of your indexes is complete...

--- In vantage@yahoogroups.com, "Chris Robisch" <bluewine@...> wrote:
>
> I'm certainly not an SQL expert but I've found reindexing on a weekly basis to be beneficial. MRP after a reindex was about 1 hour and would creep up to 2 hours through the week until the next reindex. But I feel safer using Epicor's methods rather than MS, mainly so you have more credibility with Epicor Support if something goes wrong. Here's the script I use which is plagarized from Epicor's. Try it out in a test db and if you've never done it before, it may shrink your database considerably due to the overflow of index pointers.
>
> PRINT 'Shrink'; DBCC shrinkdatabase(Epicor905,5)
> USE Epicor905
> DECLARE @TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @TableName
> WHILE @@FETCH_STATUS = 0
> BEGIN
> PRINT 'Reindex ' + @TableName
> DBCC DBREINDEX(@TableName,' ',95) WITH NO_INFOMSGS
> FETCH NEXT FROM TableCursor INTO @TableName
> END; CLOSE TableCursor; DEALLOCATE TableCursor
> PRINT 'Check'; DBCC checkdb(Epicor905)
>
> ----- Original Message -----
> From: "Elizabeth" <gracefulthreads@...>
> To: <vantage@yahoogroups.com>
> Sent: Monday, July 22, 2013 7:35 AM
> Subject: [Vantage] SQL Maintenance Questions
>
>
> E9.04.507A SQL (2008)
>
> My background is as a hardware guy, and so far I've seemed to convince my users that I'm also a software guy. It's a good thing they're gullible.
>
> Over the weekend I upgraded MSSQL from 2005 to 2008, and as others had told me, it went swimmingly and quite easily. The only question I have remaining is on the "Rebuild Indexes" vs "Reset Indexes" vs "Import Indexes" option. I chose Import, since I had no idea how long a Rebuild was going to take, and really didn't want to chance it going into production time on Monday.
>
> So now I've been researching the rebuild, and (unsurprisingly) have even more questions than before.
>
> Epicor 9.04.505 was originally installed using SQL 2005 in late 2010 (I have to assume it was with all default settings on the DB side). I upgraded it to 506A on go-live in January 2011, and then up to the current 507A. No maintenance has ever been done to the SQL database (now about 40G).
>
> 1. There is a script by Ola Hallengren (http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html) that appears to automate the rebuild process. Has anyone used it? What do you think?
>
> 2. In reading the SQL Books Online, it seems to say that the default Fillfactor for an index is 100, which is optimal if all data is added to the END of the table. However, in looking at the actual database itself, it seems as though the Fillfactor is 0. Is that true, or how can I check for sure?
>
> 3. In post #93205, Jarod (k99ja04) states he uses a fillfactor of 80, but that 90 might be better. A couple of other sources I've seen also say 80 is a good place to start.
>
> In all the performance discussions I've seen on this group, I don't remember any that talk about DB maintenance. Is it just not that significant? Am I jousting with windmills here?
>
> Thanks,
>
> Ernie Lowell
> Diba Industries
>
>
>
> ------------------------------------
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2013.0.3349 / Virus Database: 3204/6510 - Release Date: 07/22/13
>
>
> [Non-text portions of this message have been removed]
>




------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links





-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3349 / Virus Database: 3204/6516 - Release Date: 07/24/13


[Non-text portions of this message have been removed]
E9.04.507A SQL (2008)

My background is as a hardware guy, and so far I've seemed to convince my users that I'm also a software guy. It's a good thing they're gullible.

Over the weekend I upgraded MSSQL from 2005 to 2008, and as others had told me, it went swimmingly and quite easily. The only question I have remaining is on the "Rebuild Indexes" vs "Reset Indexes" vs "Import Indexes" option. I chose Import, since I had no idea how long a Rebuild was going to take, and really didn't want to chance it going into production time on Monday.

So now I've been researching the rebuild, and (unsurprisingly) have even more questions than before.

Epicor 9.04.505 was originally installed using SQL 2005 in late 2010 (I have to assume it was with all default settings on the DB side). I upgraded it to 506A on go-live in January 2011, and then up to the current 507A. No maintenance has ever been done to the SQL database (now about 40G).

1. There is a script by Ola Hallengren (http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html) that appears to automate the rebuild process. Has anyone used it? What do you think?

2. In reading the SQL Books Online, it seems to say that the default Fillfactor for an index is 100, which is optimal if all data is added to the END of the table. However, in looking at the actual database itself, it seems as though the Fillfactor is 0. Is that true, or how can I check for sure?

3. In post #93205, Jarod (k99ja04) states he uses a fillfactor of 80, but that 90 might be better. A couple of other sources I've seen also say 80 is a good place to start.

In all the performance discussions I've seen on this group, I don't remember any that talk about DB maintenance. Is it just not that significant? Am I jousting with windmills here?

Thanks,

Ernie Lowell
Diba Industries
I'm certainly not an SQL expert but I've found reindexing on a weekly basis to be beneficial. MRP after a reindex was about 1 hour and would creep up to 2 hours through the week until the next reindex. But I feel safer using Epicor's methods rather than MS, mainly so you have more credibility with Epicor Support if something goes wrong. Here's the script I use which is plagarized from Epicor's. Try it out in a test db and if you've never done it before, it may shrink your database considerably due to the overflow of index pointers.

PRINT 'Shrink'; DBCC shrinkdatabase(Epicor905,5)
USE Epicor905
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindex ' + @TableName
DBCC DBREINDEX(@TableName,' ',95) WITH NO_INFOMSGS
FETCH NEXT FROM TableCursor INTO @TableName
END; CLOSE TableCursor; DEALLOCATE TableCursor
PRINT 'Check'; DBCC checkdb(Epicor905)

----- Original Message -----
From: "Elizabeth" <gracefulthreads@...>
To: <vantage@yahoogroups.com>
Sent: Monday, July 22, 2013 7:35 AM
Subject: [Vantage] SQL Maintenance Questions


E9.04.507A SQL (2008)

My background is as a hardware guy, and so far I've seemed to convince my users that I'm also a software guy. It's a good thing they're gullible.

Over the weekend I upgraded MSSQL from 2005 to 2008, and as others had told me, it went swimmingly and quite easily. The only question I have remaining is on the "Rebuild Indexes" vs "Reset Indexes" vs "Import Indexes" option. I chose Import, since I had no idea how long a Rebuild was going to take, and really didn't want to chance it going into production time on Monday.

So now I've been researching the rebuild, and (unsurprisingly) have even more questions than before.

Epicor 9.04.505 was originally installed using SQL 2005 in late 2010 (I have to assume it was with all default settings on the DB side). I upgraded it to 506A on go-live in January 2011, and then up to the current 507A. No maintenance has ever been done to the SQL database (now about 40G).

1. There is a script by Ola Hallengren (http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html) that appears to automate the rebuild process. Has anyone used it? What do you think?

2. In reading the SQL Books Online, it seems to say that the default Fillfactor for an index is 100, which is optimal if all data is added to the END of the table. However, in looking at the actual database itself, it seems as though the Fillfactor is 0. Is that true, or how can I check for sure?

3. In post #93205, Jarod (k99ja04) states he uses a fillfactor of 80, but that 90 might be better. A couple of other sources I've seen also say 80 is a good place to start.

In all the performance discussions I've seen on this group, I don't remember any that talk about DB maintenance. Is it just not that significant? Am I jousting with windmills here?

Thanks,

Ernie Lowell
Diba Industries



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links





-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3349 / Virus Database: 3204/6510 - Release Date: 07/22/13


[Non-text portions of this message have been removed]
Be aware that rebuilding indexes will lock tables, so if your rebuild takes a long time (23 hours for me) its highly likely that your TaskAgent AppServer will crash at some point leaving users unable to print etc.

One solution to this is to use MS SQL Enterprise and you can then keep your indexes online during a rebuild (you can't do this in Standard), otherwise you'll want to restart your TaskAgent after the rebuild of your indexes is complete...

--- In vantage@yahoogroups.com, "Chris Robisch" <bluewine@...> wrote:
>
> I'm certainly not an SQL expert but I've found reindexing on a weekly basis to be beneficial. MRP after a reindex was about 1 hour and would creep up to 2 hours through the week until the next reindex. But I feel safer using Epicor's methods rather than MS, mainly so you have more credibility with Epicor Support if something goes wrong. Here's the script I use which is plagarized from Epicor's. Try it out in a test db and if you've never done it before, it may shrink your database considerably due to the overflow of index pointers.
>
> PRINT 'Shrink'; DBCC shrinkdatabase(Epicor905,5)
> USE Epicor905
> DECLARE @TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @TableName
> WHILE @@FETCH_STATUS = 0
> BEGIN
> PRINT 'Reindex ' + @TableName
> DBCC DBREINDEX(@TableName,' ',95) WITH NO_INFOMSGS
> FETCH NEXT FROM TableCursor INTO @TableName
> END; CLOSE TableCursor; DEALLOCATE TableCursor
> PRINT 'Check'; DBCC checkdb(Epicor905)
>
> ----- Original Message -----
> From: "Elizabeth" <gracefulthreads@...>
> To: <vantage@yahoogroups.com>
> Sent: Monday, July 22, 2013 7:35 AM
> Subject: [Vantage] SQL Maintenance Questions
>
>
> E9.04.507A SQL (2008)
>
> My background is as a hardware guy, and so far I've seemed to convince my users that I'm also a software guy. It's a good thing they're gullible.
>
> Over the weekend I upgraded MSSQL from 2005 to 2008, and as others had told me, it went swimmingly and quite easily. The only question I have remaining is on the "Rebuild Indexes" vs "Reset Indexes" vs "Import Indexes" option. I chose Import, since I had no idea how long a Rebuild was going to take, and really didn't want to chance it going into production time on Monday.
>
> So now I've been researching the rebuild, and (unsurprisingly) have even more questions than before.
>
> Epicor 9.04.505 was originally installed using SQL 2005 in late 2010 (I have to assume it was with all default settings on the DB side). I upgraded it to 506A on go-live in January 2011, and then up to the current 507A. No maintenance has ever been done to the SQL database (now about 40G).
>
> 1. There is a script by Ola Hallengren (http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html) that appears to automate the rebuild process. Has anyone used it? What do you think?
>
> 2. In reading the SQL Books Online, it seems to say that the default Fillfactor for an index is 100, which is optimal if all data is added to the END of the table. However, in looking at the actual database itself, it seems as though the Fillfactor is 0. Is that true, or how can I check for sure?
>
> 3. In post #93205, Jarod (k99ja04) states he uses a fillfactor of 80, but that 90 might be better. A couple of other sources I've seen also say 80 is a good place to start.
>
> In all the performance discussions I've seen on this group, I don't remember any that talk about DB maintenance. Is it just not that significant? Am I jousting with windmills here?
>
> Thanks,
>
> Ernie Lowell
> Diba Industries
>
>
>
> ------------------------------------
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2013.0.3349 / Virus Database: 3204/6510 - Release Date: 07/22/13
>
>
> [Non-text portions of this message have been removed]
>