Parker Software Ltd Homepage
Forum Home Forum Home > WhosOn Live Chat > FAQ's > Advanced FAQ's
  New Posts New Posts RSS Feed - Maintaining A Healthy WhosOn Database
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Maintaining A Healthy WhosOn Database - Event Date: 11 Jan 2007 - 11 Jan 2007

 Post Reply Post Reply
Author
Message
Stephen View Drop Down
Admin Group
Admin Group
Avatar

Joined: 21 Oct 2005
Location: Stoke on Trent
Points: 1454
Post Options Post Options   Thanks (0) Thanks(0)   Quote Stephen Quote  Post ReplyReply Direct Link To This Post Calendar Event: Maintaining A Healthy WhosOn Database
    Posted: 11 Jan 2007 at 11:47am
If you are using a SQL database to store you WhosOn data.. and you have a reasonable amount of traffic (say have a database size more than 1GB), then you should perform regular maintenance on the database to keep the database healthy and maintain optimal performance.

This article shows how to do this for SQL Server 2005 (similar maintenance functions also exist in MySQL).

If you are using SQL Server 2005 full version (not Express), then you should create a Maintenance Plan for the WhosOn DB that executes weekly (say Sunday morning). This maintenance plan should be set to Reorganize each table and the perform a Shrink database. This plan can be run whilst WhosOn is running.

If you are using SQL Server 2005 Express edition then you wont be able to do this because express does not include the Maintenance plan wizard.. however you can do the same by executing some queries manually.

The following queries with regorganize all the main indexes and shrink the database. These can be executed against the WhosOn database in the SQL Server Management studio:

USE [WhosOnV4]
GO
ALTER INDEX [Exceptions_key0] ON [dbo].[Exceptions] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Exceptions_key1] ON [dbo].[Exceptions] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Exceptions_key2] ON [dbo].[Exceptions] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Keywords_key0] ON [dbo].[Keywords] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Keywords_key1] ON [dbo].[Keywords] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Page_key0] ON [dbo].[Page] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Pages_key0] ON [dbo].[Pages] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Pages_key1] ON [dbo].[Pages] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Referrers_key0] ON [dbo].[Referrers] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Referrers_key1] ON [dbo].[Referrers] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visit_key0] ON [dbo].[Visit] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visit_key1] ON [dbo].[Visit] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visit_key2] ON [dbo].[Visit] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visit_key3] ON [dbo].[Visit] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visitors_key0] ON [dbo].[Visitors] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visitors_key1] ON [dbo].[Visitors] REORGANIZE WITH ( LOB_COMPACTION = ON )
USE [WhosOnV4]
GO
DBCC SHRINKDATABASE("WhosOnV4", 10, TRUNCATEONLY)


You can run these whilst WhosOn is running. We recommend you do this once per month at least.

You can also execute the following stored procedure to view the status of the WhosOn DB:

EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"


Steve
Back to Top
fabiozarvos View Drop Down
New User
New User


Joined: 25 Nov 2007
Location: So PauloSP
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote fabiozarvos Quote  Post ReplyReply Direct Link To This Post Posted: 10 Oct 2008 at 10:39am

I did not find the Visit_key2 index on Visit table. Am I missing something?

Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Points: 881
Post Options Post Options   Thanks (0) Thanks(0)   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 15 Oct 2008 at 9:31am
No - this has been removed - the correct code is now:
 

USE [WhosOnV4]
GO
ALTER INDEX [Exceptions_key0] ON [dbo].[Exceptions] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Exceptions_key1] ON [dbo].[Exceptions] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Exceptions_key2] ON [dbo].[Exceptions] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Keywords_key0] ON [dbo].[Keywords] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Keywords_key1] ON [dbo].[Keywords] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Page_key0] ON [dbo].[Page] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Pages_key0] ON [dbo].[Pages] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Pages_key1] ON [dbo].[Pages] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Referrers_key0] ON [dbo].[Referrers] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Referrers_key1] ON [dbo].[Referrers] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visit_key0] ON [dbo].[Visit] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visit_key1] ON [dbo].[Visit] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visitors_key0] ON [dbo].[Visitors] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Visitors_key1] ON [dbo].[Visitors] REORGANIZE WITH ( LOB_COMPACTION = ON )
USE [WhosOnV4]
GO
DBCC SHRINKDATABASE("WhosOnV4", 10, TRUNCATEONLY)
Daniel Tallentire
Support
Parker Software
Back to Top
matrixIII View Drop Down
Professional
Professional


Joined: 21 Jul 2008
Points: 68
Post Options Post Options   Thanks (0) Thanks(0)   Quote matrixIII Quote  Post ReplyReply Direct Link To This Post Posted: 15 Dec 2009 at 4:32pm
Can we get this for V5? V4 script is generating some errors..
Back to Top
Stephen View Drop Down
Admin Group
Admin Group
Avatar

Joined: 21 Oct 2005
Location: Stoke on Trent
Points: 1454
Post Options Post Options   Thanks (0) Thanks(0)   Quote Stephen Quote  Post ReplyReply Direct Link To This Post Posted: 15 Dec 2009 at 5:47pm
Hi,
 
In V5 WhosOn reorganized indexes itself every night during the database maintenance. It first checks the Statistics of the DB and does an re-index for any indexes that are fragmented.
 
The V4 scripts wont work in V5 because the tables/index names are changed.
 
Thanks
Steve
Back to Top
matrixIII View Drop Down
Professional
Professional


Joined: 21 Jul 2008
Points: 68
Post Options Post Options   Thanks (0) Thanks(0)   Quote matrixIII Quote  Post ReplyReply Direct Link To This Post Posted: 18 Dec 2009 at 8:29pm
ahh.. I see.. Didn't know that before. I guess it happens automatically without having us to set up something (dedicated server option)?
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.05
Copyright ©2001-2016 Web Wiz Ltd.

This page was generated in 0.068 seconds.