SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Brad M. McGehee / SQLServerCentral.com Best Practices Clinic: Part 5 / Latest Posts

RE: SQLServerCentral.com Best Practices Clinic: Part 5

bradmcgehee@hotmail.com — Tue, 23 Aug 2011 12:10:38 GMT

Sorry for the delay in announcing the winner of this contest, but I was out last week traveling. After reviewing the entries, and talking about them to several other DBAs, we selected WilliamD's response as the winning entry. We all thought his answer seemed the most complete. Thanks to everyone who participated in this contest.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

pradeep-408312 — Thu, 04 Aug 2011 23:34:39 GMT

Contest Entry--------------------------------------------------------------Recommendation For Query#1 and @Query#2--------------------------------------------------------------Understanding: It seems that AnalysisLog table used to keep count of clicks on each artical(data) date wise.Logical Change Recommended : first execute Update query to increase count (query#1). if not row affected by Update Query then execute insert query to make first record. This will eliminate execution of query#2Solution:UPDATE dbo.AnalysisLog SET [Count] = [Count] + 1 WHERE [Time] = @RoundedTime AND [Data] = @Data[b]IF @@ROWCOUNT = 0[/b] **Insert query**--------------------------------------------------------------Recommendation For Query#3--------------------------------------------------------------Recommendation#1. use nolock with first select query, which is used to get ThreadID and PostIDRecommendation#2. create index on [cs_Posts].[PostDate], this saves sorting time. Because in execution plan of Insert into #PageIndex query sort cost is 36%.Solution:CREATE NONCLUSTERED INDEX [] ON [dbo].[cs_Posts] ([PostDate])GOSelect @ThreadID = p.ThreadID, @PostID = p.PostIDFrom cs_Posts p [b]with (nolock)[/b]Where p.SectionID = @SectionID and p.PostName = @PostNameGO--------------------------------------------------------------Recommendation For Query #4--------------------------------------------------------------1. We can use inner join between cs_Sections F, cs_weblog_Weblogs W on F.SectionID = W.SectionID2. We can use nolock with both tables3. Make non-cluster index on F.SectionID and W.SectionIDSolution:CREATE NONCLUSTERED INDEX [] ON [dbo].[cs_Sections] ([SectionID])GOCREATE NONCLUSTERED INDEX [] ON [dbo].[cs_weblog_Weblogs] ([SectionID])GOSELECT F.[SectionID], F.[SettingsID], F.[IsActive], F.[ParentID], F.[GroupID], F.[Name], F.[NewsgroupName], F.[Description], F.[DateCreated], F.[Url], F.[IsModerated], F.[DaysToView], F.[SortOrder], F.[TotalPosts], F.[TotalThreads], F.[DisplayMask], F.[EnablePostStatistics], F.[EnablePostPoints], F.[EnableAutoDelete], F.[EnableAnonymousPosting], F.[AutoDeleteThreshold], F.[MostRecentThreadID], F.[MostRecentThreadReplies], F.[PostsToModerate], F.[ForumType], F.[IsSearchable], F.[ApplicationType], F.[ApplicationKey], F.PropertyNames as SectionPropertyNames, F.PropertyValues as SectionPropertyValues, F.[DefaultLanguage], F.[DiskUsage], W.*, null as LastUserActivityFROM [b]cs_Sections F with (nolock) inner join cs_weblog_Weblogs W with (nolock) on F.SectionID = W.SectionID[/b]WHERE F.SettingsID = @SettingsID AND ApplicationType = 1 GO--------------------------------------------------------------Pradeep Chaurasiacpradeep@pisoftek.comP. I. Softek LtdSec-62, Noida, UP

RE: SQLServerCentral.com Best Practices Clinic: Part 5

bradmcgehee@hotmail.com — Thu, 04 Aug 2011 12:49:09 GMT

[quote][b]Craig Farrell (8/4/2011)[/b][hr][quote][b]Ninja's_RGR'us (8/3/2011)[/b][hr][quote][b]Ninja's_RGR'us (8/2/2011)[/b][hr]UPDATE dbo.AnalysisLog SET @Count = Count = Count + 1 WHERE ...[/quote]Nevermind that part, I had missed the fact that there's 1 row per date where there's action on the page.[/quote]We both did. I was inspired to believe it was a single row first generated when the first click of the page was performed, primarily because of this quote from the article:[quote] 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;[/quote]Brad, can you clarify please?[/quote]Based on a Profiler trace I took, here is what appears to happen. Query 1 runs first, and if it doesn't find a record for page that is viewed, then an INSERT is performed into the table so that its views can be tracked. This INSERT query is not in the 10 ten, so I did not list it. If Query 1 does find a record, then Query 2 runs, updating the record for the page that is viewed. The developer of this code was a contractor and did not document eactly what happens or why, so I have had to infer what happens based on what I see in the trace.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Evil Kraig F — Thu, 04 Aug 2011 11:46:11 GMT

[quote][b]Ninja's_RGR'us (8/3/2011)[/b][hr][quote][b]Ninja's_RGR'us (8/2/2011)[/b][hr]UPDATE dbo.AnalysisLog SET @Count = Count = Count + 1 WHERE ...[/quote]Nevermind that part, I had missed the fact that there's 1 row per date where there's action on the page.[/quote]We both did. I was inspired to believe it was a single row first generated when the first click of the page was performed, primarily because of this quote from the article:[quote] 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;[/quote]Brad, can you clarify please?

RE: SQLServerCentral.com Best Practices Clinic: Part 5

bradmcgehee@hotmail.com — Thu, 04 Aug 2011 08:57:03 GMT

Thanks for everyone who has contributed to the contest so far. The goal of the contest was to try an interactive article format with reader feedback on “SQLServerCentral.com Best Practices Clinic: Part 5”, and have a little fun trying to solve a “puzzle of sorts” with the information provided. I feel that there is enough information in the article to provide a “good enough” answer. Sure, with more information you might be able to provide a “better” answer, but this "puzzle of sorts" was designed this way not to place too much impact on our reader’s time. Please keep the answers coming!Thanks!

RE: SQLServerCentral.com Best Practices Clinic: Part 5

gryphonsclaw — Wed, 03 Aug 2011 10:02:23 GMT

Contest Entry:For query 1I would put an index on Time and Data for the analysis log table. For query 2The query 1 item would take care of this query as well.For query 1 and 2. The interesting thing would be to cache the days counts off in server memory or another temporary table then do a nightly or hourly job to write all that down to the databaase or copy it to the analysis log. One other arc change I would make is down use a varchar(50) if possible, can these data events be a key of an int or bigint. That would far improve the indexing. Maybe provide a lookup table to find the description.For query 3 remove the order by if possible, the sort might be better selecting out of the temp table later if needed. So, add a PostDate column there. Alternately, add an index on cs_Posts with a ThreadID,IsApproved,PostDate,PostID,PostLevel,ApplicationPostType (though this is wide).Query 4:Looks like an index is needed on cs_weblog_weblogs on sectionID, but selectivity might just be poor. I would alias applicationtype in the where clause, also is W.* really needed? Expand the W.* items out. Write the , out as an ANSI join style just for best practice.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Wed, 03 Aug 2011 07:42:25 GMT

[quote][b]Ninja's_RGR'us (8/2/2011)[/b][hr]UPDATE dbo.AnalysisLog SET @Count = Count = Count + 1 WHERE ...[/quote]Nevermind that part, I had missed the fact that there's 1 row per date where there's action on the page.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Tue, 02 Aug 2011 20:10:32 GMT

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.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Tue, 02 Aug 2011 20:03:38 GMT

[quote]--Windows 2008 (64-bit)--SQL Server 2008 (64-bit)[/quote]What editions? AND SP for that matter.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Tue, 02 Aug 2011 19:56:36 GMT

[quote][b]bradmcgehee@hotmail.com (8/2/2011)[/b][hr][quote][b]Ninja's_RGR'us (8/2/2011)[/b][hr]If we don't want to "win" SM. Can we pick another tool?[/quote]Don't know this answer, but I will ask.[/quote]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 :Whistling:

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Tue, 02 Aug 2011 19:54:40 GMT

SET Twisted mind onI'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?

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Evil Kraig F — Tue, 02 Aug 2011 17:10:22 GMT

[quote][b]bradmcgehee@hotmail.com (8/2/2011)[/b][hr]I am providing most of the information requested, except the XML execution plans. You will have to work with the graphical execution plans.[/quote]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:[quote]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.[/quote]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:[quote]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?[/quote]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:[quote]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.[/quote]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.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Tue, 02 Aug 2011 14:36:40 GMT

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[url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]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). :hehe:

RE: SQLServerCentral.com Best Practices Clinic: Part 5

bradmcgehee@hotmail.com — Tue, 02 Aug 2011 14:18:59 GMT

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.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Tue, 02 Aug 2011 12:59:06 GMT

So I get credit for all further answers, how nice :-P.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 > [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]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 :hehe:)

RE: SQLServerCentral.com Best Practices Clinic: Part 5

bradmcgehee@hotmail.com — Tue, 02 Aug 2011 12:51:29 GMT

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.[code="sql"]--Schema for AnalysisLogUSE [SQLServerCentral]GO/****** Object: Table [dbo].[AnalysisLog] Script Date: 08/02/2011 12:21:48 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[AnalysisLog] ADD CONSTRAINT [DF_AnalysisImageLog_Data] DEFAULT ('') FOR [Data]GOUSE [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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOALTER TABLE [dbo].[cs_Posts] WITH CHECK ADD CONSTRAINT [FK_cs_Posts_cs_Threads] FOREIGN KEY([ThreadID])REFERENCES [dbo].[cs_Threads] ([ThreadID])GOALTER TABLE [dbo].[cs_Posts] CHECK CONSTRAINT [FK_cs_Posts_cs_Threads]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_Username] DEFAULT ('') FOR [PostAuthor]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_Posts_SectionID] DEFAULT (1) FOR [SectionID]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_Posts_PostDate] DEFAULT (getdate()) FOR [PostDate]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_Posts_Approved] DEFAULT (1) FOR [IsApproved]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_IsLocked] DEFAULT (0) FOR [IsLocked]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_IsIndexed] DEFAULT (0) FOR [IsIndexed]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_Posts_Views] DEFAULT (0) FOR [TotalViews]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF__Posts__Body2__0B27A5C0] DEFAULT ('') FOR [Body]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_IPAddress] DEFAULT (N'000.000.000.000') FOR [IPAddress]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF__posts__PostType__290D0E62] DEFAULT (0) FOR [PostType]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_EmoticonID] DEFAULT (0) FOR [EmoticonID]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF__forums_Po__AggVi__78D3EB5B] DEFAULT (0) FOR [AggViews]GOALTER TABLE [dbo].[cs_Posts] ADD DEFAULT (0) FOR [PostConfiguration]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [cs_Post_UserTimeDefaultValue] DEFAULT (getdate()) FOR [UserTime]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [cs_Post_ApplicationTypeDefaultValue] DEFAULT ((1)) FOR [ApplicationPostType]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_Points] DEFAULT ((0)) FOR [Points]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_RatingSum] DEFAULT ((0)) FOR [RatingSum]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_TotalRatings] DEFAULT ((0)) FOR [TotalRatings]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_PointsUpdated] DEFAULT (getdate()) FOR [PointsUpdated]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [DF_cs_Posts_PostMedia] DEFAULT ((0)) FOR [PostMedia]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [cs_Posts_DefaultSpamScore] DEFAULT ((0)) FOR [SpamScore]GOALTER TABLE [dbo].[cs_Posts] ADD CONSTRAINT [cs_Posts_DefaultPostStatus] DEFAULT ((0)) FOR [PostStatus]GOUSE [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]GOUSE [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]GOUSE [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]GOUSE [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]GOUSE [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]GOUSE [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]GOUSE [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]GOUSE [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 ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_SettingsID] DEFAULT (0) FOR [SettingsID]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_Active] DEFAULT (1) FOR [IsActive]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF__Forums__ParentID__01342732] DEFAULT (0) FOR [ParentID]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_NewsgroupName] DEFAULT ('') FOR [NewsgroupName]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_DateCreated] DEFAULT (getdate()) FOR [DateCreated]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_Url] DEFAULT ('') FOR [Url]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_Moderated] DEFAULT (0) FOR [IsModerated]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_DaysToView] DEFAULT (7) FOR [DaysToView]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_SortOrder] DEFAULT (0) FOR [SortOrder]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_TotalPosts] DEFAULT (0) FOR [TotalPosts]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_TotalThreads] DEFAULT (0) FOR [TotalThreads]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF__forums__DisplayM__004002F9] DEFAULT (0) FOR [DisplayMask]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_EnablePostStatistics] DEFAULT (1) FOR [EnablePostStatistics]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_EnableAutoDelete] DEFAULT (0) FOR [EnableAutoDelete]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_EnableAnonymousPosting] DEFAULT (0) FOR [EnableAnonymousPosting]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_AutoDeleteThreshold] DEFAULT (90) FOR [AutoDeleteThreshold]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_Forums_MostRecentPostID] DEFAULT (0) FOR [MostRecentPostID]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentThreadID] DEFAULT (0) FOR [MostRecentThreadID]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentThreadReplies] DEFAULT (0) FOR [MostRecentThreadReplies]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentPostSubject] DEFAULT ('') FOR [MostRecentPostSubject]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentPostAuthor] DEFAULT ('') FOR [MostRecentPostAuthor]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentPostAuthorID] DEFAULT (0) FOR [MostRecentPostAuthorID]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_MostRecentPostDate] DEFAULT ('1/1/1797') FOR [MostRecentPostDate]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_forums_cs_PostsToModerate] DEFAULT (0) FOR [PostsToModerate]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_ForumType] DEFAULT (0) FOR [ForumType]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_IsSearchable] DEFAULT (1) FOR [IsSearchable]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_ApplicationType] DEFAULT (0) FOR [ApplicationType]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF__forums_For__Path__75F77EB0] DEFAULT ('') FOR [Path]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_EnablePostPoints] DEFAULT ((1)) FOR [EnablePostPoints]GOALTER TABLE [dbo].[cs_Sections] ADD CONSTRAINT [DF_cs_Sections_DiskUsage] DEFAULT ((0)) FOR [DiskUsage]GOUSE [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]GOUSE [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]GOUSE [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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostID] DEFAULT (0) FOR [MostRecentPostID]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostDate] DEFAULT (getdate()) FOR [MostRecentPostDate]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostAuthorID] DEFAULT (0) FOR [MostRecentPostAuthorID]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostAuthor] DEFAULT ('') FOR [MostRecentPostAuthor]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentPostSubject] DEFAULT ('') FOR [MostRecentPostSubject]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleID] DEFAULT (0) FOR [MostRecentArticleID]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleDate] DEFAULT (getdate()) FOR [MostRecentArticleDate]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleAuthorID] DEFAULT (0) FOR [MostRecentArticleAuthorID]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleAuthor] DEFAULT ('') FOR [MostRecentArticleAuthor]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_MostRecentArticleSubject] DEFAULT ('') FOR [MostRecentArticleSubject]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [cs_weblog_Weblogs_PC] DEFAULT (0) FOR [PostCount]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [DF_cs_weblog_Weblogs_AC] DEFAULT (0) FOR [ArticleCount]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [cs_weblog_Weblogs_CC] DEFAULT (0) FOR [CommentCount]GOALTER TABLE [dbo].[cs_weblog_Weblogs] ADD CONSTRAINT [cs_weblog_Weblogs_TC] DEFAULT (0) FOR [TrackbackCount]GOUSE [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[/code]

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Tue, 02 Aug 2011 12:04:01 GMT

When do you plan on posting the DDL of the tables for the top 4 queries along with keys indexes and sample data. You already gave the rowcount, but did you do it for all tables?The contest is pretty much useless without that. We can't post tested code bassed on your need so at this point this is just a guess!

RE: SQLServerCentral.com Best Practices Clinic: Part 5

bradmcgehee@hotmail.com — Tue, 02 Aug 2011 11:58:29 GMT

[quote][b]Ninja's_RGR'us (8/2/2011)[/b][hr]If we don't want to "win" SM. Can we pick another tool?[/quote]Don't know this answer, but I will ask.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ninja's_RGR'us — Tue, 02 Aug 2011 11:51:02 GMT

If we don't want to "win" SM. Can we pick another tool?

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ira L Grollman — Tue, 02 Aug 2011 11:31:18 GMT

Thanks for the information Brad. I usually model and evaluate full configurations before suggesting what would be optimal. Two of the other replies are on similar tracks to what I was considering, so, they are ahead :-) in terms of submitting an answer. The budget question was just about where you wanted to spend time/money in terms of people versus hardware. A complete redesign would hold things like the lookups for existence in a SSD, then increment counts or create and increment in a copy of the data in a RAID set based filegroup and asynchronously flush daily to another filegroup on one of the RAID arrays and rebuild the SSD based copy. Given your configuration, I would monitor for hot spots and also optimize using MERGE as suggested by another respondent. There's a few other possibilities including caching the top x% of hits (newly published article links, etc.) in the web front end that should get this configuration to average about 100 transactions per second. Without a load distribution, I presumed 30% of the time, peak load is occurring and the system is running at ~ 80% capacity.Regards, Ira

RE: SQLServerCentral.com Best Practices Clinic: Part 5

WilliamD- — Tue, 02 Aug 2011 10:49:28 GMT

/* Contest Entry */You said that this was a 2008 machine, so for Query 1 and 2 (and the 3rd non top 10 query to do the insert) you would be fine with a MERGE statement (killing three birds with one stone):[code="sql"]/* Query 1 & 2 */DECLARE @roundedtime datetime = '2011-07-17 00:00:00.000', @data varchar(50) = 'article_loginclick' ; BEGIN TRANMERGE dbo.AnalysisLog trg USING (SELECT @roundedtime AS [Time], @data AS [Data]) src ON src.Data = trg.DATA AND src.[Time] = trg.[Time] WHEN NOT MATCHED THEN INSERT ([Count],[Time],Data) VALUES (1,src.[Time], src.Data) WHEN MATCHED THEN UPDATE SET trg.[Count]=trg.[Count]+1 ;COMMIT TRAN[/code]I would also add the following index to assist the statement: [code="sql"]CREATE INDEX idx1 ON AnalysisLog ([Time],Data) INCLUDE (Count)[/code]On Query 3 I see a you are not getting any data returned. If this is typical activity, I would want to know if there has maybe been a logic change elsewhere in the system that is possibly rendering this query obsolete (in it's current state). If not you could replace the statement with the following:[code="sql"]SELECT ROW_NUMBER() OVER (ORDER BY P.PostID) AS IndexID, P.PostIDFROM cs_Posts PWHERE P.IsApproved = 1 AND P.PostLevel > 1 AND P.ApplicationPostType <> 16 AND P.SectionID = @SectionID AND P.PostName = @PostNameORDER BY P.PostDate [/code]You could take into consideration if the temp table is necessary at all, removing that if possible. It may also be possible to perform some index consolidation, changing the index that provides a seek on SecntionID and PostName to become a covering index for this query. Maybe something like:[code="sql"]CREATE INDEX idx ON cs_Posts (PostName,SectionId,IsApproved) INCLUDE (PostLevel, ApplicationPostType, PostId)[/code]This is going on the premise that the three key columns will be highly selective (if not unique) and the rest being added as includes to cover the query. Not knowing the table structure and meaning of the columns restricts this to only a possibility though.For Query 4, it may be an idea to reduce the columns to an absolute necessity. Then I would look into the LOB reads - what is this actually achieving (if anything). The join syntax works, but is deprecated and should be changed to an INNER JOIN.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

b3yond — Tue, 02 Aug 2011 09:31:58 GMT

Contest Entry[code="sql"]/*----------------------------------------------------------------Query #1 & #2----------------------------------------------------------------*/-- Assuming you have a unique ID for each article.....CREATE UNIQUE NONCLUSTERED INDEX idx_AnalysisLog_ArticleID ON dbo.AnalysisLog (ArticleID) INCLUDE ([Count]);GO-- Wrap in a transaction to ensure multiple inserts don't fire a the same time if an article has never been viewed beforeBEGIN TRANSACTION;-- Create or update based on the ArticleID, numbers make for faster searching.-- It doesn't mention that you want to know how many clicks per day, just how many clicks, assuming that....DECLARE @ArticleID int, @RowCount int;SET @ArticleID = 7;UPDATE dbo.AnalysisLogSET [Count] = COALESCE([Count], 0) + 1 -- Assumes the worst and Count allows NULL, should be defaulted to 0 and the COALSECE removedWHERE ArticleID = @ArticleID;-- This way the index/table is only searched once for an insert or update whereas before an update required 2 searchesSET @RowCount = @@ROWCOUNT;IF @RowCount = 0BEGIN INSERT INTO dbo.AnalysisLog (ArticleID, [Count], LastVisited) VALUES (@ArticleID, 1, CURRENT_TIMESTAMP);ENDCOMMIT TRANSACTION;/*----------------------------------------------------------------Query #3----------------------------------------------------------------*/CREATE TABLE #PageIndex ( IndexID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, PostID int);-- Saves doing 2 searches, you may need to add a new index and re-order the columns in the where clause to put the most selective first-- Again, you would be better passing in a Blog ID of some sort and filter on this rather than filter on characters (PostName)-- It would be better if a temp table were never created, without seeing more information on how that table is used it's difficult to sayINSERT INTO #PageIndex (PostID)SELECT PostIDFROM cs_Posts WITH (NOLOCK)WHERE SectionID = @SectionID AND PostName = @PostName AND IsApproved = 1 AND PostLevel > 1 AND ApplicationPostType <> 16ORDER BY PostDate;/*----------------------------------------------------------------Query #4----------------------------------------------------------------*/-- Create this index to improve performance on this table, depending on the number of existing indexes and size of the table (it could be 3 rows for all I know!)CREATE NONCLUSTERED INDEX idx_cs_weblog_Weblogs_SectionID ON dbo.cs_weblog_Weblogs (SectionID);GODECLARE @SettingsID int;SET @SettingsID = 1000;-- If this query is used to give information about blog authors it appears to have a vast amount of irrelevant data returnedSELECT F.[SectionID], F.[SettingsID], F.[IsActive], F.[ParentID], F.[GroupID], F.[Name], F.[NewsgroupName], F.[Description], F.[DateCreated], F.[Url], F.[IsModerated], F.[DaysToView], F.[SortOrder], F.[TotalPosts], F.[TotalThreads], F.[DisplayMask], F.[EnablePostStatistics], F.[EnablePostPoints], F.[EnableAutoDelete], F.[EnableAnonymousPosting], F.[AutoDeleteThreshold], F.[MostRecentThreadID], F.[MostRecentThreadReplies], F.[PostsToModerate], F.[ForumType], F.[IsSearchable], F.[ApplicationType], F.[ApplicationKey], F.PropertyNames AS SectionPropertyNames, F.PropertyValues AS SectionPropertyValues, F.[DefaultLanguage], F.[DiskUsage], W.*, -- Should specify the exact columns required NULL AS LastUserActivityFROM cs_Sections F INNER JOIN cs_weblog_Weblogs W ON F.SectionID = W.SectionID -- Use JOINS!WHERE F.SettingsID = @SettingsID AND F.ApplicationType = 1;[/code]My 2 cents!

RE: SQLServerCentral.com Best Practices Clinic: Part 5

bradmcgehee@hotmail.com — Tue, 02 Aug 2011 09:19:29 GMT

I am not able to answer all of your question, but here is some information that might be useful.Each node of the cluster has identical hardware and software:--Two, 2.26Hz Quad Core Intel CPUs (a total of eight cores, no hyperthreading)--24GB RAM--Two, locally-attached RAID 1 arrays (array 1 holds the system files and SQL Server binaries, and array 2 is used to make local backups, before they are moved off the server)--Two, RAID 10 SAN arrays (array 1 holds the MDF files, and array 2 holds the LDF files)--One, RAID 1 SAN array (used exclusively for tempdb)--Windows 2008 (64-bit)--SQL Server 2008 (64-bit)In regard to the process that invokes the first two queries, essentially it is any page view on the SQLServerCentral.com website. I am not exactly sure what invokes the last two queries, but I will ask our web developer (who did not write this code) to see if he can figure it out.In regards to server load, the webserver get about 120,000 to 150,000 page views during a typical business day, with most of them occuring during the day.Here are some stats on the number of rows in some of the key tables:AnalysisLog: 1,651,589 rowscs_posts: 24,671 rowscs_Sections: 97 rowscs_weblog_weblogs: 89 rowsIn regard to a "budget" or timeframe for making improvements, I am not going to disclose this, other to say that we do have a full-time developer for the website (who has a lot of work already on his hands), and that adding to the existing hardware would be difficult (except RAM), as the equipment is under a lease. For the assumptions of the contest, assume the hardware will remain the same, although we might be able to get some developement time allocated.

RE: SQLServerCentral.com Best Practices Clinic: Part 5

mister boom — Tue, 02 Aug 2011 08:39:33 GMT

CONTEST ENTRYThis took a bit longer than I thought...[b]Queries 1 and 2:[/b]“Query #2 runs first, looking to see if that particular article has ever been viewed before or not.” If you simply want to know whether the article has “ever” been run before, why do you need to check the [Time] column? If there is a record in the table, wouldn’t that be enough to tell you if it has ever been viewed? If this is the case, I would remove the “[Time]=@RoundedTime” criteria.If the [Time] criteria is necessary, I would apply the first listed nonclustered index recommendation:CREATE NONCLUSTERED INDEX [IX_AnalysisLog_TimeData] ON [dbo].[AnalysisLog] ([Time],[Data]) You don’t show the structure of the AnalysisLog table. I assume that there is no identity column. If you were to add an identity column to the AnalysisLog table, eg. called AnalysisLogID, you could do this:DECLARE @AnalysisLogID intSELECT @AnalysisLogID=AnalysisLogIDFROM dbo.AnalysisLog WHERE [Data] = @Data --AND [Time] = @RoundedTime **still required?** IF @AnalysisLogID IS NOT NULL UPDATE dbo.AnalysisLog SET [Count] = [Count] + 1 WHERE AnalysisLogID=@AnalysisLogIDELSE --execute undocumented query to create a new record[b]Query 3:[/b]With this one, it appears that you are passing @SectionID and @PostName parameters, using them to lookup @ThreadID and @PostID, but only use the @ThreadID in the WHERE clause of the INSERT query. I don’t know if you need the @PostID at all, or if @ThreadID is sufficient. This should be more efficient: INSERT INTO #PageIndex (PostID)SELECT PostIDFROM PostsWHERE IsApproved=1 AND PostLevel>1 AND ApplicationPostType<>16AND SectionID=@SectionID AND PostName=@PostNameORDER BY PostDate You also don’t show how you use the #PageIndex temp table after you create it. If you just run a simple SELECT statement afterwards, I would change the code to not use a temp table at all, especially as about 27% (31% of 91%) of the execution time is taken up by inserting into the temp table. If you need to use the temp table, rather than create a clustered index, why not use: INSERT INTO #PageIndex(IndexID, PostID)SELECT ROW_NUMBER() OVER (ORDER BY PostID) as IndexID, PostIDFROM PostsWHERE IsApproved=1 AND PostLevel>1 AND ApplicationPostType<>16AND SectionID=@SectionID AND PostName=@PostNameORDER BY PostDate And if you can make do without the temp table, simply drop the INSERT line.[b]Query 4: [/b]I can see that there is a clustered index on both tables, but we don’t know which columns they are on. We can also see a large number of lob logical reads, but don’t know which columns are causing this as we don’t know the table definition. I can’t see anything obvious to improve on, but I would:Consider adding an index on the SectionID column of the cs_weblog_Weblogs table.Consider adding a covering index on cs_weblog_WeblogsIt won’t improve efficiency, but I would revise the query to replace W.* with the actual column names and for consistency with the other queries, change the way that the tables are joined: FROM cs_Sections F INNER JOIN cs_weblog_Weblogs W ON F.SectionID=W.SectionIDWHERE F.ApplicationType=1AND F.SettingsID=@SettingsID

RE: SQLServerCentral.com Best Practices Clinic: Part 5

Ira L Grollman — Tue, 02 Aug 2011 08:04:03 GMT

Could you describe the overall process that invokes these queries and some approximate statistics on the volume and relative distributions of information being stored? Also, some details on the physical computing environment's capacity and performance of the storage and processing elements would be helpful. Similarly, what's the "budget" and timeframe for making improvements?Regards, Ira

SQLServerCentral.com Best Practices Clinic: Part 5

bradmcgehee@hotmail.com — Mon, 01 Aug 2011 23:49:47 GMT

Comments posted to this topic are about the item [B]SQLServerCentral.com Best Practices Clinic: Part 5[/B]