August 1, 2011 at 11:49 pm
Comments posted to this topic are about the item SQLServerCentral.com Best Practices Clinic: Part 5
Brad M. McGehee
DBA
August 2, 2011 at 8:04 am
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
August 2, 2011 at 8:39 am
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
August 2, 2011 at 9:19 am
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
August 2, 2011 at 9:31 am
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!
August 2, 2011 at 10:49 am
/* 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
August 2, 2011 at 11:31 am
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
August 2, 2011 at 11:51 am
If we don't want to "win" SM. Can we pick another tool?
August 2, 2011 at 11:58 am
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
August 2, 2011 at 12:04 pm
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!
August 2, 2011 at 12:51 pm
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
August 2, 2011 at 12:59 pm
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:)
August 2, 2011 at 2:18 pm
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
August 2, 2011 at 2:36 pm
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:
August 2, 2011 at 5:10 pm
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.
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 25 total)
You must be logged in to reply to this topic. Login to reply