WebWizForums 8 DB is not optimized

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic


   Yesterday , the hosting company which I hosted my forum on their servers , called me that your site is using too much CPU processes, I didn't beleive that , but they sent me screen shot of task manager and my user process was 62% ,
They told me you have to Optimize your database, my db size is 230 MB , 
One of my friends told me maybe its because of Incorrect Indexing , he told me you have to tune your Indexes,
I just test that with SQL 2005 by using Database Engine Tuning Advisor   after configuring the program after about 2 min it shows me that after tuning and creating indexes which recommended by the software it will be 63% improved in performance , I didn't create new indexes on the hosting server yet but I just create them on my own computer database it seems its ok
 
here is the indexes scripts which is created by SQL 2005 Database Engine Tuning Advisor
 
---
 
 
[code]
use [WebWizDB]
go
CREATE CLUSTERED INDEX [_dta_index_tblPermissions_c_5_677577452__K15_K3_K1_K2] ON [dbo].[tblPermissions] 
(
 [View_Forum] ASC,
 [Forum_ID] ASC,
 [Group_ID] ASC,
 [Author_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_tblPermissions_5_677577452__K14_K1_K2] ON [dbo].[tblPermissions] 
(
 [Moderate] ASC,
 [Group_ID] ASC,
 [Author_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_tblPermissions_5_677577452__K1_K15_K3] ON [dbo].[tblPermissions] 
(
 [Group_ID] ASC,
 [View_Forum] ASC,
 [Forum_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_677577452_3_1] ON [dbo].[tblPermissions]([Forum_ID], [Group_ID])
go
CREATE STATISTICS [_dta_stat_677577452_3_2] ON [dbo].[tblPermissions]([Forum_ID], [Author_ID])
go
CREATE STATISTICS [_dta_stat_677577452_1_14] ON [dbo].[tblPermissions]([Group_ID], [Moderate])
go
CREATE STATISTICS [_dta_stat_677577452_15_3] ON [dbo].[tblPermissions]([View_Forum], [Forum_ID])
go
CREATE STATISTICS [_dta_stat_677577452_2_1_3] ON [dbo].[tblPermissions]([Author_ID], [Group_ID], [Forum_ID])
go
CREATE STATISTICS [_dta_stat_677577452_2_1_14] ON [dbo].[tblPermissions]([Author_ID], [Group_ID], [Moderate])
go
CREATE STATISTICS [_dta_stat_677577452_2_1_15_3] ON [dbo].[tblPermissions]([Author_ID], [Group_ID], [View_Forum], [Forum_ID])
go
CREATE NONCLUSTERED INDEX [_dta_index_tblAuthor_5_149575571__K2_K3_1_21_22_23_26] ON [dbo].[tblAuthor] 
(
 [Group_ID] ASC,
 [Username] ASC
)
INCLUDE ( [Author_ID],
[No_of_posts],
[Join_date],
[Active],
[Last_visit]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_tblAuthor_5_149575571__K1_3_18] ON [dbo].[tblAuthor] 
(
 [Author_ID] ASC
)
INCLUDE ( [Username],
[DOB]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_149575571_1_2] ON [dbo].[tblAuthor]([Author_ID], [Group_ID])
go
CREATE STATISTICS [_dta_stat_149575571_5_1] ON [dbo].[tblAuthor]([User_code], [Author_ID])
go
CREATE STATISTICS [_dta_stat_149575571_5_33_1] ON [dbo].[tblAuthor]([User_code], [skin], [Author_ID])
go
CREATE NONCLUSTERED INDEX [_dta_index_tblTopic_5_2121058592__K16_K2_K12_1_5] ON [dbo].[tblTopic] 
(
 [Event_date] ASC,
 [Forum_ID] ASC,
 [Start_Thread_ID] ASC
)
INCLUDE ( [Topic_ID],
[Subject]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED INDEX [_dta_index_tblTopic_5_2121058592__K13_K1_K2_K15_5] ON [dbo].[tblTopic] 
(
 [Last_Thread_ID] ASC,
 [Topic_ID] ASC,
 [Forum_ID] ASC,
 [Hide] ASC
)
INCLUDE ( [Subject]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_2121058592_9] ON [dbo].[tblTopic]([Locked])
go
CREATE STATISTICS [_dta_stat_2121058592_2_10] ON [dbo].[tblTopic]([Forum_ID], [Priority])
go
CREATE STATISTICS [_dta_stat_2121058592_2_16] ON [dbo].[tblTopic]([Forum_ID], [Event_date])
go
CREATE STATISTICS [_dta_stat_2121058592_15_13] ON [dbo].[tblTopic]([Hide], [Last_Thread_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_1_2] ON [dbo].[tblTopic]([Topic_ID], [Forum_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_15_2_1] ON [dbo].[tblTopic]([Hide], [Forum_ID], [Topic_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_12_1_2] ON [dbo].[tblTopic]([Start_Thread_ID], [Topic_ID], [Forum_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_2_12_16] ON [dbo].[tblTopic]([Forum_ID], [Start_Thread_ID], [Event_date])
go
CREATE STATISTICS [_dta_stat_2121058592_13_12_15] ON [dbo].[tblTopic]([Last_Thread_ID], [Start_Thread_ID], [Hide])
go
CREATE STATISTICS [_dta_stat_2121058592_12_13_4] ON [dbo].[tblTopic]([Start_Thread_ID], [Last_Thread_ID], [Moved_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_10_13_4_2] ON [dbo].[tblTopic]([Priority], [Last_Thread_ID], [Moved_ID], [Forum_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_13_12_1_15] ON [dbo].[tblTopic]([Last_Thread_ID], [Start_Thread_ID], [Topic_ID], [Hide])
go
CREATE STATISTICS [_dta_stat_2121058592_15_2_13_12] ON [dbo].[tblTopic]([Hide], [Forum_ID], [Last_Thread_ID], [Start_Thread_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_12_13_10_4] ON [dbo].[tblTopic]([Start_Thread_ID], [Last_Thread_ID], [Priority], [Moved_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_10_4_2_12_13] ON [dbo].[tblTopic]([Priority], [Moved_ID], [Forum_ID], [Start_Thread_ID], [Last_Thread_ID])
go
CREATE STATISTICS [_dta_stat_2121058592_2_13_12_1_15] ON [dbo].[tblTopic]([Forum_ID], [Last_Thread_ID], [Start_Thread_ID], [Topic_ID], [Hide])
go
CREATE NONCLUSTERED INDEX [_dta_index_tblThanks_5_1310627712__K2] ON [dbo].[tblThanks] 
(
 [Thread_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_1310627712_3_2] ON [dbo].[tblThanks]([From_ID], [Thread_ID])
go
CREATE NONCLUSTERED INDEX [_dta_index_tblThread_5_85575343__K3_K1_K2] ON [dbo].[tblThread] 
(
 [Author_ID] ASC,
 [Thread_ID] ASC,
 [Topic_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE STATISTICS [_dta_stat_85575343_3_2] ON [dbo].[tblThread]([Author_ID], [Topic_ID])
go
CREATE STATISTICS [_dta_stat_85575343_5_2] ON [dbo].[tblThread]([Message_date], [Topic_ID])
go
CREATE STATISTICS [_dta_stat_85575343_5_1] ON [dbo].[tblThread]([Message_date], [Thread_ID])
go
CREATE STATISTICS [_dta_stat_85575343_3_5] ON [dbo].[tblThread]([Author_ID], [Message_date])
go
CREATE STATISTICS [_dta_stat_85575343_2_3_5] ON [dbo].[tblThread]([Topic_ID], [Author_ID], [Message_date])
go