Web Wiz Support and Community Forums : WebWizForums 8 DB is not optimized

Web Wiz Support and Community Forums : WebWizForums 8 DB is not optimized https://forums.webwiz.net/ Copyright (c) 2006-2013 Web Wiz Forums - All Rights Reserved. Mon, 06 Apr 2026 19:09:24 +0000 Mon, 17 Sep 2007 10:07:47 +0000 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 12.08 360 https://forums.webwiz.net/RSS_post_feed.asp?TID=24408 <![CDATA[Web Wiz Support and Community Forums]]> https://forums.webwiz.net/forum_images/web_wiz_forums.png https://forums.webwiz.net/ <![CDATA[WebWizForums 8 DB is not optimized : moti wrote: I think SQL...]]> https://forums.webwiz.net/webwizforums-8-db-is-not-optimized_topic24408_post127678.html#127678 Author: WebWiz-Bruce
Subject: 24408
Posted: 17 September 2007 at 10:07am

moti wrote:

I think SQL 2005 doesn't say crap.

You've got to be joking!

If you believe that it will help then give it ago, if it doesn't work make a note of the changes and just remove them at a later stage.

Edited by -boRg- - 17 September 2007 at 10:07am]]> Mon, 17 Sep 2007 10:07:47 +0000 https://forums.webwiz.net/webwizforums-8-db-is-not-optimized_topic24408_post127678.html#127678 <![CDATA[WebWizForums 8 DB is not optimized : Thanks for the reply but the...]]> https://forums.webwiz.net/webwizforums-8-db-is-not-optimized_topic24408_post127635.html#127635 Author: moti
Subject: 24408
Posted: 15 September 2007 at 9:15am

Thanks for the reply

but the main problem is that I test forum in a special situation , I test forum application with one of the best Web Applcation Stress test, with 800 users online for 10 min , each of these users click on a link in forum every 5 sec , and on the server I ran SQL Profiler and record all of the queries within this 10 min test, then saved them to a sql file , and load that in Database Engine Tuning Advisor , then this programm told me above results (in my first post) , I think SQL 2005 doesn't say crap.

]]> Sat, 15 Sep 2007 09:15:33 +0000 https://forums.webwiz.net/webwizforums-8-db-is-not-optimized_topic24408_post127635.html#127635 <![CDATA[WebWizForums 8 DB is not optimized : Web Wiz Forums already has indexes...]]> https://forums.webwiz.net/webwizforums-8-db-is-not-optimized_topic24408_post127603.html#127603 Author: WebWiz-Bruce
Subject: 24408
Posted: 14 September 2007 at 9:34am

Web Wiz Forums already has indexes that it needs and is optimised for the most common quiries, creating to many indexes can be worse than not having any at all. Running the optimiser for 2 minutes won't give you a clear picture as forums are quite complex with many different types of queries run on the database, optimising it for all the queries run would in turn slow down the overall performance and increase CPU usage.

I can't see how the CPU usage could be getting to 63% as we have over 200 Web Wiz Forums running on our database server and many with over 100 active users at all times and have never seen the CPU usage go above 5%, and thats running 200 forums!!
]]> Fri, 14 Sep 2007 09:34:54 +0000 https://forums.webwiz.net/webwizforums-8-db-is-not-optimized_topic24408_post127603.html#127603 <![CDATA[WebWizForums 8 DB is not optimized : Yesterday , the hosting company...]]> https://forums.webwiz.net/webwizforums-8-db-is-not-optimized_topic24408_post127595.html#127595 Author: moti
Subject: 24408
Posted: 14 September 2007 at 2:09am

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]]> Fri, 14 Sep 2007 02:09:28 +0000 https://forums.webwiz.net/webwizforums-8-db-is-not-optimized_topic24408_post127595.html#127595