Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQLServerCentral.com Best Practices Clinic: Part 5 Expand / Collapse
Author
Message
Posted Tuesday, August 2, 2011 12:51 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:06 AM
Points: 176, Visits: 728
Part of the challenge is asking the right questions. Below is the schema for the four tables used in the four queries (along with their index structure) to help you along.


--Schema for AnalysisLog

USE [SQLServerCentral]
GO

/****** Object: Table [dbo].[AnalysisLog] Script Date: 08/02/2011 12:21:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AnalysisLog](
[AnalysisLogID] [int] IDENTITY(1,1) NOT NULL,
[Time] [datetime] NOT NULL,
[Data] [varchar](50) NOT NULL,
[Count] [int] NOT NULL,
CONSTRAINT [PK__AnalysisImageLog__1CBC4616] PRIMARY KEY CLUSTERED
(
[AnalysisLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[AnalysisLog] ADD CONSTRAINT [DF_AnalysisImageLog_Data] DEFAULT ('') FOR [Data]
GO


USE [SQLServerCentral]
GO

/****** Object: Index [PK__AnalysisImageLog__1CBC4616] Script Date: 08/02/2011 12:22:03 ******/
ALTER TABLE [dbo].[AnalysisLog] ADD CONSTRAINT [PK__AnalysisImageLog__1CBC4616] PRIMARY KEY CLUSTERED
(
[AnalysisLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO




--Schema for cs_posts:

USE [CommunityServer]
GO

/****** Object: Table [dbo].[cs_Posts] Script Date: 08/02/2011 12:24:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[cs_Posts](
[PostID] [int] IDENTITY(1,1) NOT NULL,
[ThreadID] [int] NOT NULL,
[ParentID] [int] NOT NULL,
[PostAuthor] [nvarchar](64) NULL,
[UserID] [int] NOT NULL,
[SectionID] [int] NOT NULL,
[PostLevel] [int] NOT NULL,
[SortOrder] [int] NOT NULL,
[Subject] [nvarchar](256) NULL,
[PostDate] [datetime] NOT NULL,
[IsApproved] [bit] NOT NULL,
[IsLocked] [bit] NOT NULL,
[IsIndexed] [bit] NOT NULL,
[TotalViews] [int] NOT NULL,
[Body] [ntext] NULL,
[FormattedBody] [ntext] NULL,
[IPAddress] [nvarchar](32) NULL,
[PostType] [int] NOT NULL,
[EmoticonID] [int] NOT NULL,
[PropertyNames] [ntext] NULL,
[PropertyValues] [ntext] NULL,
[SettingsID] [int] NULL,
[AggViews] [int] NOT NULL,
[PostConfiguration] [int] NOT NULL,
[PostName] [nvarchar](256) NULL,
[UserTime] [datetime] NULL,
[ApplicationPostType] [int] NOT NULL,
[Points] [int] NOT NULL,
[RatingSum] [int] NOT NULL,
[TotalRatings] [int] NOT NULL,
[PointsUpdated] [datetime] NOT NULL,
[IndexDate] [smalldatetime] NULL,
[PostMedia] [int] NOT NULL,
[SpamScore] [int] NOT NULL,
[PostStatus] [int] NOT NULL,
CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[cs_Posts] WITH CHECK ADD CONSTRAINT [FK_cs_Posts_cs_Threads] FOREIGN KEY([ThreadID])
REFERENCES [dbo].[cs_Threads] ([ThreadID])
GO

ALTER TABLE [dbo].[cs_Posts] CHECK CONSTRAINT [FK_cs_Posts_cs_Threads]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_Username] DEFAULT ('') FOR [PostAuthor]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_Posts_SectionID] DEFAULT (1) FOR [SectionID]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_Posts_PostDate] DEFAULT (getdate()) FOR [PostDate]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_Posts_Approved] DEFAULT (1) FOR [IsApproved]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_IsLocked] DEFAULT (0) FOR [IsLocked]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_IsIndexed] DEFAULT (0) FOR [IsIndexed]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_Posts_Views] DEFAULT (0) FOR [TotalViews]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF__Posts__Body2__0B27A5C0] DEFAULT ('') FOR [Body]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_IPAddress] DEFAULT (N'000.000.000.000') FOR [IPAddress]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF__posts__PostType__290D0E62] DEFAULT (0) FOR [PostType]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_EmoticonID] DEFAULT (0) FOR [EmoticonID]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF__forums_Po__AggVi__78D3EB5B] DEFAULT (0) FOR [AggViews]
GO

ALTER TABLE [dbo].[cs_Posts] ADD DEFAULT (0) FOR [PostConfiguration]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [cs_Post_UserTimeDefaultValue] DEFAULT (getdate()) FOR [UserTime]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [cs_Post_ApplicationTypeDefaultValue] DEFAULT ((1)) FOR [ApplicationPostType]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_Points] DEFAULT ((0)) FOR [Points]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_RatingSum] DEFAULT ((0)) FOR [RatingSum]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_TotalRatings] DEFAULT ((0)) FOR [TotalRatings]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_PointsUpdated] DEFAULT (getdate()) FOR [PointsUpdated]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_PostMedia] DEFAULT ((0)) FOR [PostMedia]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [cs_Posts_DefaultSpamScore] DEFAULT ((0)) FOR [SpamScore]
GO

ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [cs_Posts_DefaultPostStatus] DEFAULT ((0)) FOR [PostStatus]
GO


USE [CommunityServer]
GO

/****** Object: Index [PK_Posts] Script Date: 08/02/2011 12:25:22 ******/
ALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO

USE [CommunityServer]
GO

/****** Object: Index [IX_Posts_ThreadID] Script Date: 08/02/2011 12:25:41 ******/
CREATE NONCLUSTERED INDEX [IX_Posts_ThreadID] ON [dbo].[cs_Posts]
(
[ThreadID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO

USE [CommunityServer]
GO

/****** Object: Index [IX_Posts_PostLevel] Script Date: 08/02/2011 12:38:28 ******/
CREATE NONCLUSTERED INDEX [IX_Posts_PostLevel] ON [dbo].[cs_Posts]
(
[PostLevel] ASC,
[IsApproved] ASC
)
INCLUDE ( [SectionID],
[PostName],
[UserTime],
[PostID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO

USE [CommunityServer]
GO

/****** Object: Index [IX_Posts_PostDate] Script Date: 08/02/2011 12:38:42 ******/
CREATE NONCLUSTERED INDEX [IX_Posts_PostDate] ON [dbo].[cs_Posts]
(
[UserID] ASC,
[PostDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO


USE [CommunityServer]
GO

/****** Object: Index [IX_cs_Posts_Thread_Sort] Script Date: 08/02/2011 12:38:59 ******/
CREATE NONCLUSTERED INDEX [IX_cs_Posts_Thread_Sort] ON [dbo].[cs_Posts]
(
[ThreadID] ASC,
[SortOrder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO


USE [CommunityServer]
GO

/****** Object: Index [IX_cs_Posts_Post_Parent] Script Date: 08/02/2011 12:39:21 ******/
CREATE NONCLUSTERED INDEX [IX_cs_Posts_Post_Parent] ON [dbo].[cs_Posts]
(
[ParentID] ASC,
[PostDate] ASC,
[PostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO


USE [CommunityServer]
GO

/****** Object: Index [_dta_index_cs_Posts_6_850102069__K6_K25_1_2] Script Date: 08/02/2011 12:42:57 ******/
CREATE NONCLUSTERED INDEX [_dta_index_cs_Posts_6_850102069__K6_K25_1_2] ON [dbo].[cs_Posts]
(
[SectionID] ASC,
[PostName] ASC
)
INCLUDE ( [PostID],
[ThreadID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO


USE [CommunityServer]
GO

/****** Object: Index [_dta_index_cs_Posts_6_850102069__K22_K11] Script Date: 08/02/2011 12:43:11 ******/
CREATE NONCLUSTERED INDEX [_dta_index_cs_Posts_6_850102069__K22_K11] ON [dbo].[cs_Posts]
(
[SettingsID] ASC,
[IsApproved] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO





--Schema for cs_sections:

USE [CommunityServer]
GO

/****** Object: Table [dbo].[cs_Sections] Script Date: 08/02/2011 12:39:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[cs_Sections](
[SectionID] [int] IDENTITY(1,1) NOT NULL,
[SettingsID] [int] NOT NULL,
[IsActive] [smallint] NOT NULL,
[ParentID] [int] NOT NULL,
[GroupID] [int] NOT NULL,
[Name] [nvarchar](256) NULL,
[NewsgroupName] [nvarchar](256) NULL,
[Description] [nvarchar](1000) NULL,
[DateCreated] [datetime] NOT NULL,
[Url] [nvarchar](512) NULL,
[IsModerated] [smallint] NOT NULL,
[DaysToView] [int] NOT NULL,
[SortOrder] [int] NOT NULL,
[TotalPosts] [int] NOT NULL,
[TotalThreads] [int] NOT NULL,
[DisplayMask] [binary](512) NOT NULL,
[EnablePostStatistics] [smallint] NOT NULL,
[EnableAutoDelete] [smallint] NOT NULL,
[EnableAnonymousPosting] [smallint] NOT NULL,
[AutoDeleteThreshold] [int] NOT NULL,
[MostRecentPostID] [int] NOT NULL,
[MostRecentThreadID] [int] NOT NULL,
[MostRecentThreadReplies] [int] NOT NULL,
[MostRecentPostSubject] [nvarchar](64) NULL,
[MostRecentPostAuthor] [nvarchar](64) NULL,
[MostRecentPostAuthorID] [int] NOT NULL,
[MostRecentPostDate] [datetime] NOT NULL,
[PostsToModerate] [int] NOT NULL,
[ForumType] [int] NOT NULL,
[IsSearchable] [smallint] NOT NULL,
[ApplicationType] [smallint] NOT NULL,
[ApplicationKey] [nvarchar](256) NULL,
[PropertyNames] [ntext] NULL,
[PropertyValues] [ntext] NULL,
[Path] [varchar](255) NOT NULL,
[EnablePostPoints] [smallint] NOT NULL,
[DiskUsage] [bigint] NOT NULL,
[DefaultLanguage] [nvarchar](32) NULL,
CONSTRAINT [PK_cs_Sections] PRIMARY KEY CLUSTERED
(
[SettingsID] ASC,
[SectionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY],
CONSTRAINT [IX_cs_Sections_Validation] UNIQUE NONCLUSTERED
(
[SettingsID] ASC,
[ApplicationKey] ASC,
[ApplicationType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_SettingsID] DEFAULT (0) FOR [SettingsID]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_Active] DEFAULT (1) FOR [IsActive]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF__Forums__ParentID__01342732] DEFAULT (0) FOR [ParentID]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_NewsgroupName] DEFAULT ('') FOR [NewsgroupName]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_Url] DEFAULT ('') FOR [Url]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_Moderated] DEFAULT (0) FOR [IsModerated]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_DaysToView] DEFAULT (7) FOR [DaysToView]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_SortOrder] DEFAULT (0) FOR [SortOrder]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_TotalPosts] DEFAULT (0) FOR [TotalPosts]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_TotalThreads] DEFAULT (0) FOR [TotalThreads]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF__forums__DisplayM__004002F9] DEFAULT (0) FOR [DisplayMask]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_EnablePostStatistics] DEFAULT (1) FOR [EnablePostStatistics]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_EnableAutoDelete] DEFAULT (0) FOR [EnableAutoDelete]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_EnableAnonymousPosting] DEFAULT (0) FOR [EnableAnonymousPosting]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_AutoDeleteThreshold] DEFAULT (90) FOR [AutoDeleteThreshold]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_MostRecentPostID] DEFAULT (0) FOR [MostRecentPostID]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentThreadID] DEFAULT (0) FOR [MostRecentThreadID]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentThreadReplies] DEFAULT (0) FOR [MostRecentThreadReplies]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentPostSubject] DEFAULT ('') FOR [MostRecentPostSubject]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentPostAuthor] DEFAULT ('') FOR [MostRecentPostAuthor]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentPostAuthorID] DEFAULT (0) FOR [MostRecentPostAuthorID]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentPostDate] DEFAULT ('1/1/1797') FOR [MostRecentPostDate]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_forums_cs_PostsToModerate] DEFAULT (0) FOR [PostsToModerate]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_ForumType] DEFAULT (0) FOR [ForumType]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_IsSearchable] DEFAULT (1) FOR [IsSearchable]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_ApplicationType] DEFAULT (0) FOR [ApplicationType]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF__forums_For__Path__75F77EB0] DEFAULT ('') FOR [Path]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_EnablePostPoints] DEFAULT ((1)) FOR [EnablePostPoints]
GO

ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_DiskUsage] DEFAULT ((0)) FOR [DiskUsage]
GO


USE [CommunityServer]
GO

/****** Object: Index [PK_cs_Sections] Script Date: 08/02/2011 12:40:07 ******/
ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [PK_cs_Sections] PRIMARY KEY CLUSTERED
(
[SettingsID] ASC,
[SectionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO

USE [CommunityServer]
GO

/****** Object: Index [IX_cs_Sections_Validation] Script Date: 08/02/2011 12:40:20 ******/
ALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [IX_cs_Sections_Validation] UNIQUE NONCLUSTERED
(
[SettingsID] ASC,
[ApplicationKey] ASC,
[ApplicationType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO


USE [CommunityServer]
GO

/****** Object: Index [_dta_index_cs_Sections_6_1634104862__K1_K2_K31_32] Script Date: 08/02/2011 12:40:43 ******/
CREATE NONCLUSTERED INDEX [_dta_index_cs_Sections_6_1634104862__K1_K2_K31_32] ON [dbo].[cs_Sections]
(
[SectionID] ASC,
[SettingsID] ASC,
[ApplicationType] ASC
)
INCLUDE ( [ApplicationKey]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO





--Schema for cs_weblog_weblogs:

USE [CommunityServer]
GO

/****** Object: Table [dbo].[cs_weblog_Weblogs] Script Date: 08/02/2011 12:41:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[cs_weblog_Weblogs](
[SectionID] [int] NOT NULL,
[MostRecentPostID] [int] NOT NULL,
[MostRecentPostDate] [datetime] NOT NULL,
[MostRecentPostName] [nvarchar](256) NULL,
[MostRecentPostAuthorID] [int] NOT NULL,
[MostRecentPostAuthor] [nvarchar](256) NULL,
[MostRecentPostSubject] [nvarchar](256) NULL,
[MostRecentArticleID] [int] NOT NULL,
[MostRecentArticleDate] [datetime] NOT NULL,
[MostRecentArticleName] [nvarchar](256) NULL,
[MostRecentArticleAuthorID] [int] NOT NULL,
[MostRecentArticleAuthor] [nvarchar](256) NULL,
[MostRecentArticleSubject] [nvarchar](256) NULL,
[PostCount] [int] NOT NULL,
[ArticleCount] [int] NOT NULL,
[CommentCount] [int] NOT NULL,
[TrackbackCount] [int] NOT NULL,
[SettingsID] [int] NULL,
CONSTRAINT [PK_cs_weblog_Weblogs] PRIMARY KEY CLUSTERED
(
[SectionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostID] DEFAULT (0) FOR [MostRecentPostID]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostDate] DEFAULT (getdate()) FOR [MostRecentPostDate]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostAuthorID] DEFAULT (0) FOR [MostRecentPostAuthorID]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostAuthor] DEFAULT ('') FOR [MostRecentPostAuthor]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostSubject] DEFAULT ('') FOR [MostRecentPostSubject]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleID] DEFAULT (0) FOR [MostRecentArticleID]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleDate] DEFAULT (getdate()) FOR [MostRecentArticleDate]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleAuthorID] DEFAULT (0) FOR [MostRecentArticleAuthorID]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleAuthor] DEFAULT ('') FOR [MostRecentArticleAuthor]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleSubject] DEFAULT ('') FOR [MostRecentArticleSubject]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [cs_weblog_Weblogs_PC] DEFAULT (0) FOR [PostCount]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_AC] DEFAULT (0) FOR [ArticleCount]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [cs_weblog_Weblogs_CC] DEFAULT (0) FOR [CommentCount]
GO

ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [cs_weblog_Weblogs_TC] DEFAULT (0) FOR [TrackbackCount]
GO


USE [CommunityServer]
GO

/****** Object: Index [PK_cs_weblog_Weblogs] Script Date: 08/02/2011 12:41:29 ******/
ALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [PK_cs_weblog_Weblogs] PRIMARY KEY CLUSTERED
(
[SectionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
GO




Brad M. McGehee
DBA
Post #1152916
Posted Tuesday, August 2, 2011 12:59 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
So I get credit for all further answers, how nice .



I also asked for sample data > (insert statements).


I'll go 1 up and ask for the actual execution plans for all top 4 queries > http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


Can you [re]post the rowcount for all tables, current fill factor and space used? I just want to have the correct info to rebuild test tables (unless you don't mind sending a backup over here, which I wouldn't really expect to get at this point )
Post #1152926
Posted Tuesday, August 2, 2011 2:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:06 AM
Points: 176, Visits: 728
Hopefully you will find the following information useful. I am providing most of the information requested, except the XML execution plans. You will have to work with the graphical execution plans.
Below are the number of rows in each table, fill factor setting, actual percent page full numbers, and space used (data and indexes):

AnalysisLog: 1,651,589 rows, fill factor: 100 (actual 98% full), Space Used 118,400 KB (data), 496 KB (indexes)

cs_posts: 24,671 rows, fill factor: 100 (actual 92% full), Space Used 221,696 KB (data), 17,744 KB (indexes)

cs_Sections: 97 rows, fill factor: 100 (actual 45% full), Space Used 408 KB (data), 48K (indexes)

cs_weblog_weblogs: 89 rows, fill factor: 100 (actual 30% full, Space Used 80 KB (data), 16K (indexes)

Be default, all the tables use the default fill factor of 100. The last two tables are so small that they never get rebuilt during daily index maintenance, as there is a rule built in not to rebuild tables less than 100 pages in size, and that is why the actual full percentage amount is small for these two tables. The first two tables are rebuilt each night, and that is why their percent full is near 100.

I have attached the first 10 rows of each table to this post for sample data.


Brad M. McGehee
DBA


  Post Attachments 
sample_data.zip (5 views, 11.39 KB)
Post #1153041
Posted Tuesday, August 2, 2011 2:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
So you want us to guess at the plan and where it goes wrong not seeing missing index, wrong estimations, actual operator costs?

Sorry dude you're better than that and it won't fly with me.


Either give full access to the prod server or post the plans.

Plan C would be to post all the stats using the scripting option with statstream but that's a beast to run on a prod server with larg-ish tables. You might be better off just uploading the actual execution plans


Also it's better netiquette to post the data in this format

http://www.sqlservercentral.com/articles/Best+Practices/61537/


TIA.


P.S. I'll be fetching the page-split / sec and other counters from the SM demo site to site if this idea has any merits (see how I snagged that idea from everybody else too).
Post #1153061
Posted Tuesday, August 2, 2011 5:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 5,401, Visits: 7,513
bradmcgehee@hotmail.com (8/2/2011)

I am providing most of the information requested, except the XML execution plans. You will have to work with the graphical execution plans.


Why not? Not that I can't recreate a reasonable facsimile of your data, I can't mimic your data distribution with a random data loader. Why would you purposely tie the hands of those asking?

Query 1 and 2:
Here’s a little background on what they do. Both queries are used to help track the number of clicks articles receive on SQLServerCentral.com. When a web page is accessed, Query #2 runs first, looking to see if that particular article has ever been viewed before or not. If not, then another query (which is not in the top 10 list) is run to create a new record where clicks are tracked in a table; but if the article has been clicked on before, then query #1 runs, which increments a counter to track the number of clicks for that page. As you can see from figure 2, both of these queries have a high number of executions, high duration, high CPU usage, and high logical reads over the 24 hour period being analyzed.


That's all lovely, but why isn't this information attached to the articles table (unlisted but assumed to exist) where the Article ID (obviously available, as it's in the HTTP header) is easily seekable and can increment simply there, on an existing clustered ID for the article? This would also get it out of this logging table which is apparently a mismatch and thus has a wonky data-distribution. Also, the article should always exist in the articles table, which means half of this check can be thrown out the window. This is an attribute being applied in the wrong place, from this initial discussion's perspective.

This seems like an odd design choice to me that requires further analysis. In the worst case scenario remove the 'check' and simply include a row into the table when the article is first generated.

Query3:

I found all of this information from a Profiler trace so that I could better understand how this particular query works, and to ensure that the data I used for the parameters was typical.

Take a few minutes to review the actual code I ran, the IO STATISTICS results, and the execution plans. Based on this information, what, if anything, would you do to try to optimize this query?


Without knowing the wrapper and intent of the outlying query, it is hard to know if this is just cost of doing business or not. What is the purpose here of using a second proc by the calling proc and forcing a temptable involved? Is this a cross linked server query? Is this the coder's way of using 'modular' code?

The query itself also seems odd out of context. It's doing a double pull from the source, selecting PostId twice but never using the variable. P.ThreadID = @ThreadID could be swapped to the two parameters in the WHERE clause in the first select, and thus completely remove the reasoning behind the double call. Can we confirm this is a correct translation of the original code and/or see the originating proc with a sample parameter? This doesn't make sense at first glance.

Query 4:

The last of the queries we are examining in this article is the longest of the four, and is used by the SQLServerCentral.com blog to display information about blog authors.


The indexing is off in cs_sections. Nowhere will it leading edge with SectionID with a second column of ApplicationType, it's all ApplicationKey first. Unless there's other queries using ApplicationKey, swap those around in the Unique index IX_cs_Sections_Validation and that may help on selectivity, can't know without seeing heuristics. 97 rows should NOT take up that much firepower though.

After that, I'd like to know the heuristics on selectivity of SectionID in cs_weblog_WebLogs. 89 rows isn't that expensive to scan though, so it might be a moot point.

This query being an issue is something of a puzzle. I really want to see why 97 rows by 89 rows (even crossjoined) is taking 18ms to produce, unless it's time to deliver the data that's really the issue and it's asynch_IO waits, which are a whole different story. That's really something that I think you'd have to be on-server for. It's relatively tight other than the flip on the index, and that w.* pretty much kills any covering index trims.


Other notes:
I personally get some different top 10s in the 15 minute totals overview, and in the 5 minute average. I don't have a 24 hour option available from the free review.

Ninja, thanks for asking about the DDL. The first thing that struck me as I went through the process was "Where's the schema?... must be attached at the end or something."

Brad, you know the drill. If you want intelligent answers we need to see the execution plans. Otherwise we're swinging an axe in the dark. No heuristics, no visibility to blocks and locks at time of execution, no ability to run traces on things, we're already limited. No reason to tie the other hand up too.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1153199
Posted Tuesday, August 2, 2011 7:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
SET Twisted mind on


I've had another inspiration about the execution plan. I think this is delibirately left out simply because some of the greatest DBAs all had a wack at it. Let's assume at least Brad and Steve if not 5-6 more of the same caliber. My best guess at this point is that this info is <considered> completely useless or they simply want us to consider other options because that path's been beaten to death already. Missing indexes are fine but there's no way in h*ll they have not considered that before. So while this seems like a viable option, I'm pretty sure they expect something completely different than that.

With that in mind >> Do you guys really need to track those clicks real time? Are you opened to creating either a logging table with identity bigint, article_key and just inserting to that table with a single update every 15 minutes to the base table? Or god forbid, track that in aspx in the session variables and flush to the db once a minute (or whatever makes sense for your needs)

AnalysisLog.Data is varchar(50). This is a hell of a long key for something that gets that much activity. Any opened-ess to change that key to identity as int (4 billions articles should get you a long way). I have no idea of the real cost of doing this as I don't know how much code change this would entail nor the actual cost of adding FKs to point to that and possibly new indexes to support that operation.


Same thing for PostName nvarchar(256).


Are you guys using compression, if so page or row? I checked out on SM but I couldn't find the sql version info anywhere (sorry if I missed it and also if it's not available). The "standard" is that you get more CPU and less disk activity. You seem to have a lot of room on the cpus, so maybe that could be a nice hit to take there and save on disks / ram (even if cache hit ratio is 100%). All in all maybe a 0 to 5% gain, but still something worth trying.


Do you have a job that runs every hour around H:20-23? You get a 5X spike in page splits around that time. I would guess something like the top poster and other leaderboads being updated but I can't tell for sure. Log shipping, replication?


Do you do anything special arount 11:00 to 17:00 on sqlservercentral db? It gets a hell of a spike on log flushes and transactions... or is it just your "normal" business hours.


Any way we can get a trace for replay so we can prove our ideas make a real impact or are we supposed to guess all the way to the end of the contest?
Post #1153221
Posted Tuesday, August 2, 2011 7:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
bradmcgehee@hotmail.com (8/2/2011)
Ninja's_RGR'us (8/2/2011)
If we don't want to "win" SM. Can we pick another tool?


Don't know this answer, but I will ask.


P.S. I'd like a tool-belt upgrade. If that's too much I can accept just sql prompt upgrade. Support is nice but considering all the new suggestions I make with the products I use I think you should at least take that out of the "value" of the gift...

not to mention all the times I recommend your stuff over these forums. Must have been 100+ times over the last few months
Post #1153222
Posted Tuesday, August 2, 2011 8:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
--Windows 2008 (64-bit)
--SQL Server 2008 (64-bit)


What editions? AND SP for that matter.
Post #1153226
Posted Tuesday, August 2, 2011 8:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Re merge :


My first thought was nice idea. But then I realized that you do "all that" extra work for an event that happens less than 0.01% of the lifetime of the article.

Same thing for ISNULL(). Didn't have time to test and prove this but I think that separating those is a better path to take.


Also 3 part update exists (or output). So no need for update + select there. I know all articles page display the viewed n times in the title. So maybe you can remove an extra select or join (excluded from the top 4) by doing something like this >\


UPDATE dbo.AnalysisLog SET @Count = Count = Count + 1 WHERE ...


maybe add the "missing" index... depends on the datatype option.
Post #1153227
Posted Wednesday, August 3, 2011 7:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Ninja's_RGR'us (8/2/2011)
UPDATE dbo.AnalysisLog SET @Count = Count = Count + 1 WHERE ...


Nevermind that part, I had missed the fact that there's 1 row per date where there's action on the page.
Post #1153530
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse