SQLServerCentral.com Best Practices Clinic: Part 5

  • Brad McGehee

    SSCertifiable

    Points: 5272

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

    Brad M. McGehee
    DBA

  • Ira L Grollman

    SSC Journeyman

    Points: 78

    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

  • mister boom

    SSChasing Mays

    Points: 603

    CONTEST ENTRY

    This took a bit longer than I thought...

    Queries 1 and 2:

    “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 int

    SELECT @AnalysisLogID=AnalysisLogID

    FROM dbo.AnalysisLog

    WHERE [Data] = @Data

    --AND [Time] = @RoundedTime **still required?**

    IF @AnalysisLogID IS NOT NULL

    UPDATE dbo.AnalysisLog

    SET [Count] = [Count] + 1

    WHERE AnalysisLogID=@AnalysisLogID

    ELSE

    --execute undocumented query to create a new record

    Query 3:

    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 PostID

    FROM Posts

    WHERE IsApproved=1 AND PostLevel>1 AND ApplicationPostType<>16

    AND SectionID=@SectionID AND PostName=@PostName

    ORDER 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, PostID

    FROM Posts

    WHERE IsApproved=1 AND PostLevel>1 AND ApplicationPostType<>16

    AND SectionID=@SectionID AND PostName=@PostName

    ORDER BY PostDate

    And if you can make do without the temp table, simply drop the INSERT line.

    Query 4:

    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_Weblogs

    It 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.SectionID

    WHERE F.ApplicationType=1

    AND F.SettingsID=@SettingsID

  • Brad McGehee

    SSCertifiable

    Points: 5272

    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 rows

    cs_posts: 24,671 rows

    cs_Sections: 97 rows

    cs_weblog_weblogs: 89 rows

    In 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.

    Brad M. McGehee
    DBA

  • b3yond

    Old Hand

    Points: 315

    Contest Entry

    /*----------------------------------------------------------------

    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 before

    BEGIN 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.AnalysisLog

    SET [Count] = COALESCE([Count], 0) + 1 -- Assumes the worst and Count allows NULL, should be defaulted to 0 and the COALSECE removed

    WHERE ArticleID = @ArticleID;

    -- This way the index/table is only searched once for an insert or update whereas before an update required 2 searches

    SET @RowCount = @@ROWCOUNT;

    IF @RowCount = 0

    BEGIN

    INSERT INTO dbo.AnalysisLog (ArticleID, [Count], LastVisited)

    VALUES (@ArticleID, 1, CURRENT_TIMESTAMP);

    END

    COMMIT 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 say

    INSERT INTO #PageIndex (PostID)

    SELECT PostID

    FROM cs_Posts WITH (NOLOCK)

    WHERE SectionID = @SectionID

    AND PostName = @PostName

    AND IsApproved = 1

    AND PostLevel > 1

    AND ApplicationPostType <> 16

    ORDER 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);

    GO

    DECLARE @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 returned

    SELECT 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 LastUserActivity

    FROM cs_Sections F

    INNER JOIN cs_weblog_Weblogs W ON F.SectionID = W.SectionID -- Use JOINS!

    WHERE F.SettingsID = @SettingsID

    AND F.ApplicationType = 1;

    My 2 cents!

  • w.durkin@online.de

    Hall of Fame

    Points: 3943

    /* 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):

    /* Query 1 & 2 */

    DECLARE @roundedtime datetime = '2011-07-17 00:00:00.000',

    @data varchar(50) = 'article_loginclick' ;

    BEGIN TRAN

    MERGE 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

    I would also add the following index to assist the statement:

    CREATE INDEX idx1 ON AnalysisLog ([Time],Data) INCLUDE (Count)

    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:

    SELECT ROW_NUMBER() OVER (ORDER BY P.PostID) AS IndexID,

    P.PostID

    FROM cs_Posts P

    WHERE P.IsApproved = 1

    AND P.PostLevel > 1

    AND P.ApplicationPostType <> 16

    AND P.SectionID = @SectionID

    AND P.PostName = @PostName

    ORDER BY P.PostDate

    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:

    CREATE INDEX idx ON cs_Posts (PostName,SectionId,IsApproved) INCLUDE (PostLevel, ApplicationPostType, PostId)

    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.

    Regards,

    WilliamD

  • Ira L Grollman

    SSC Journeyman

    Points: 78

    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

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

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

  • Brad McGehee

    SSCertifiable

    Points: 5272

    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.

    Brad M. McGehee
    DBA

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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!

  • Brad McGehee

    SSCertifiable

    Points: 5272

    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

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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 > 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 :hehe:)

  • Brad McGehee

    SSCertifiable

    Points: 5272

    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

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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). :hehe:

  • Evil Kraig F

    SSC Guru

    Points: 100851

    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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply