March 18, 2009 at 3:28 am
I run a website which has a facility for it's members to PM each other, the table that contains these PMs has around 4.5 million rows in it. The problem is that calls to it are starting to timeout, which as you can imagine is highly frustrating for both myself and the members of the site.
I experienced this problem a few months back and to overcome it I ran the sql server profile and then loaded the trace into the database engine tuning advisor, it gave me some suggested indexes which I applied and for a while everything ran okay.
The problem has now returned and once again I have tried to use the tuning advisor, however this time the changes it advises are very small and the esimated improvement is 0%.
The performance issue is not consistent and most of the time the table select calls are very fast, it just seems that for a few minutes at various points throughout the day the table is inaccessible and the connection times out.
If anyone is able to give me some guidance about how to resolve this I'd be most grateful
March 18, 2009 at 4:00 am
Which index you applied few months back?
Is there any other indexes available for the table?
Post the following here.
1)Table structure with all the available indexes.
2) Query
karthik
March 18, 2009 at 4:06 am
If the problem is happening at regular intervals during the day, there may be a job that is scheduled to run that is locking up the tables, check your SQL server jobs to make sure there is nothing in there that could be causing an issue.
Sometimes the advice that the tuning adivsor gives is only slightly useful, quite ofen the decisions on indexs has to be made manualy. If you post your table defs and queries then i am sure someone can help.
March 18, 2009 at 4:11 am
along with what has already been mentioned above , have you updated the statistics?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 18, 2009 at 4:26 am
Okay here goes, this is the table structure and the indexes
-- Table Structure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[msg](
[id] [int] IDENTITY(1,1) NOT NULL,
[c_name] [nvarchar](100) NULL,
[c_mail] [nvarchar](150) NULL,
[c_company] [nvarchar](150) NULL,
[m_subject] [nvarchar](250) NULL,
[c_msg] [ntext] NULL,
[status] [nvarchar](50) NULL,
[m_username] [nvarchar](100) NULL,
[m_email] [nvarchar](100) NULL,
[u_age] [int] NULL,
[m_Created] [datetime] NULL,
[m_read] [char](1) NULL,
[m_dateRead] [datetime] NULL,
[m_replied] [char](1) NULL,
[m_dateReplied] [datetime] NULL,
[m_deleted] [char](1) NULL,
[m_dateDeleted] [datetime] NULL,
[m_forward] [char](1) NULL,
[m_dateForward] [datetime] NULL,
[m_delSent] [nchar](1) NULL,
[m_dateDelSent] [datetime] NULL,
[folder] [nvarchar](100) NULL,
[firstContact] [char](1) NULL,
[importance] [int] NULL,
[richText] [char](1) NULL,
CONSTRAINT [PK_msg] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-- Indexes -------------------------------
CREATE NONCLUSTERED INDEX [_dta_index_msg_8_629577281__K16_K8_K12_K22] ON [dbo].[msg]
(
[m_deleted] ASC,
[m_username] ASC,
[m_read] ASC,
[folder] 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) ON [PRIMARY]
-------------------------------
CREATE NONCLUSTERED INDEX [_dta_index_msg_8_629577281__K2_K20_K8_K1_K12_5_11_13_14_15] ON [dbo].[msg]
(
[c_name] ASC,
[m_delSent] ASC,
[m_username] ASC,
[id] ASC,
[m_read] ASC
)
INCLUDE ( [m_subject],
[m_Created],
[m_dateRead],
[m_replied],
[m_dateReplied]) 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) ON [PRIMARY]
-------------------------------
CREATE NONCLUSTERED INDEX [_dta_index_msg_8_629577281__K22_K8_K16_1] ON [dbo].[msg]
(
[folder] ASC,
[m_username] ASC,
[m_deleted] ASC
)
INCLUDE ( [id]) 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) ON [PRIMARY]
-------------------------------
CREATE NONCLUSTERED INDEX [_dta_index_msg_8_629577281__K8_K23_K2_K1_14_20] ON [dbo].[msg]
(
[m_username] ASC,
[firstContact] ASC,
[c_name] ASC,
[id] ASC
)
INCLUDE ( [m_replied],
[m_delSent]) 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) ON [PRIMARY]
-------------------------------
ALTER TABLE [dbo].[msg] ADD CONSTRAINT [PK_msg] PRIMARY KEY CLUSTERED
(
[id] 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]
March 18, 2009 at 4:31 am
post the query too.
karthik
March 18, 2009 at 4:33 am
Can you post the query?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2009 at 4:38 am
Sure, here's the query:-
WITH myMessages AS (
SELECT msg.id, msg.m_username, msg.c_name, msg.m_Created, msg.m_subject, msg.c_msg, msg.m_read, msg.m_dateRead, msg.m_replied, msg.m_dateReplied, msg.m_forward, msg.importance
,ROW_NUMBER() OVER (ORDER BY msg.m_read asc,
case
when @order=1 then msg.m_Created
end desc,
case
when @order=2 then msg.m_Created
end asc,
case
when @order=3 then msg.m_subject
end desc,
case
when @order=4 then msg.m_subject
end asc,
case
when @order=5 then msg.c_name
end desc,
case
when @order=6 then msg.c_name
end asc
)AS Row
FROM msg WITH (NOLOCK)
WHERE (msg.m_username = @username) AND (msg.m_deleted = 'N') AND (msg.folder = @box)
)
SELECT *
FROM myMessages WITH (NOLOCK)
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
March 18, 2009 at 4:49 am
There's an index that should be usable for that query. Without the second filter (page index and page size), now many rows are returned?
Can you post the execution plan (saved as a sqlplan file, zipped and attached)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2009 at 5:18 am
HI
I've attached the execution plan
March 18, 2009 at 5:24 am
The plan is to be expected to be honest, I'm just trying to work out how to get rid of the Key Look-up as this is where the big cost is however I think we may have a problem because you can't index a text field 🙁
Could some confirm this please?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 18, 2009 at 6:28 am
First, just a word of caution, NOLOCK can lead to bad data. I don't just mean the ubiquitous "dirty read" meaning uncommitted changes, but I mean, extra rows, missing rows... bad data.
As to the query, is the most common access path through the ID or through the user name? If it's not the ID, then I'd restructure the table. Don't make the ID the cluster. Instead, make it this index [_dta_index_msg_8_629577281__K22_K8_K16_1]. Which, by the way, shouldn't need the ID as an include column since the ID is the clustered index, it's automatically included in all nonclustered indexes.
And, just for your own sanity, give the indexes names that are easier to read.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 18, 2009 at 6:36 am
Grant Fritchey (3/18/2009)
First, just a word of caution, NOLOCK can lead to bad data. I don't just mean the ubiquitous "dirty read" meaning uncommitted changes, but I mean, extra rows, missing rows... bad data.
I did this in and act of desperation after reading somewhere it can help performance
Grant Fritchey (3/18/2009)
As to the query, is the most common access path through the ID or through the user name? If it's not the ID, then I'd restructure the table. Don't make the ID the cluster. Instead, make it this index [_dta_index_msg_8_629577281__K22_K8_K16_1]. Which, by the way, shouldn't need the ID as an include column since the ID is the clustered index, it's automatically included in all nonclustered indexes.
I'd say about half and half, when listing PM's in the users inbox it uses the username, when displaying individual PMs it uses the id
Grant Fritchey (3/18/2009)
And, just for your own sanity, give the indexes names that are easier to read.
Sure, I just used that name as it was the one supplied by the tuning wizard
March 18, 2009 at 7:14 am
Matt (3/18/2009)
I'd say about half and half, when listing PM's in the users inbox it uses the username, when displaying individual PMs it uses the id
Then it's going to be tough. The slow part of the query is the key lookup operation. You eliminate the key lookup by changing indexes to use an INCLUDE of the columns needed (in this case, most of them, including a text column, which can't be indexed) or you can modify the clustered index to eliminate the lookup.... What about changing the text column? If you make it a VARCHAR(MAX), which it should be anyway... It might work.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 18, 2009 at 7:26 am
The annoying thing is that at the time of posting on this forum the query runs in less than a second, it's only at certain periods throughout the day when the timeout's happen, usually toward the evening when the site is busiest, but it does also happen when the site is quiet.
Grant Fritchey (3/18/2009)
If you make it a VARCHAR(MAX), which it should be anyway... It might work.
Just so I'm sure, doing this won't have any adverse effects will it? such as truncating the existing data or anything like that?
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply