March 18, 2009 at 7:46 am
Matt (3/18/2009)
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.
While the query can be tuned, it sounds like you might be hitting blocking, not so much a direct query performance problem. You might want to get one of the blocking monitor scripts in place and see what you get.
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?
It shouldn't, not at all, but don't take anyone's word on these forums for granted. ALWAYS test the solution prior to implementing it on your Production system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 18, 2009 at 7:50 am
Grant Fritchey (3/18/2009)
While the query can be tuned, it sounds like you might be hitting blocking, not so much a direct query performance problem.
I wondered about that but wouldn't it affect access to all tables?
March 18, 2009 at 8:13 am
Nah, blocking can be very isolated. It might be a particular page on the table that's slowing stuff down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 18, 2009 at 8:16 am
Matt (3/18/2009)
I wondered about that but wouldn't it affect access to all tables?
No. If there's a lock on the messages table it will just affect queries on the messages table. If there's a lock on a portion of the messages table, then it will just affect queries trying to read that portion of the messages table.
I agree with Grant, check the blocking. Though, with nolock there shouldn't be any blocking, unless it's waiting on a latch or similar.
Matt, can you monitor for a couple days, when the query's slow check what the wait_resource and wait_time are. (sys.dm_exec_requests)
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 8:25 am
GilaMonster (3/18/2009)
No. If there's a lock on the messages table it will just affect queries on the messages table. If there's a lock on a portion of the messages table, then it will just affect queries trying to read that portion of the messages table.
I thought that calling the query with NOLOCK as I have done would stop this from happening?
March 18, 2009 at 11:06 am
Matt (3/18/2009)
GilaMonster (3/18/2009)
No. If there's a lock on the messages table it will just affect queries on the messages table. If there's a lock on a portion of the messages table, then it will just affect queries trying to read that portion of the messages table.I thought that calling the query with NOLOCK as I have done would stop this from happening?
As Gail already pointed out, because of the NOLOCK, it's unlikely that it's blocking (but not impossible). The best thing to do is determine what is causing the slow down, as well as work on tuning the query. To answer the slow down, I'd suggest reading up on this article: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc. It's about identifying bottlenecks by looking at what things are waiting on. That will tell you precisely where the problem is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 18, 2009 at 12:27 pm
I've found the perfect tool to use on the server to help me out, it's called a sledge hammer, it won't fix the problem but it will make me feel better 😉
Seriously, thanks for the link, I'll read through the document this evening and see if I can apply it
March 18, 2009 at 12:43 pm
Right there with you. I finally developed enough arm & shoulder strength to graduate to the 10lb sledge. Makes a HUGE difference. As you say, it'll fix or replace anything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 18, 2009 at 1:27 pm
GilaMonster (3/18/2009)
Matt (3/18/2009)
Matt, can you monitor for a couple days, when the query's slow check what the wait_resource and wait_time are. (sys.dm_exec_requests)
Okay, I'll do that and post the times up here
March 18, 2009 at 1:46 pm
Matt (3/18/2009)
I've found the perfect tool to use on the server to help me out, it's called a sledge hammer
😀 There's a reason I have a hammer in my computer tool kit.
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 21, 2009 at 10:19 am
I've taken the trouble to go through a number of indexes that were created by the tuning wizard and made some altercations here and there and there have been some good improvements.
The main issues seems to be when viewing PMs that have been sent which involves the folling query: -
WITH myMessages AS (
select [id], [m_username], [c_name], [m_Created], [m_subject], [c_msg], [m_read], [m_dateRead], [m_replied], [m_dateReplied]
,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.m_username
end desc,
case
when @order=6 then msg.m_username
end asc
)AS Row
from msg where c_name=@username
)
SELECT [id], [m_username], [c_name], [m_Created], [m_subject], [c_msg], [m_read], [m_dateRead], [m_replied], [m_dateReplied] ,[row]
FROM myMessages
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
So when you log in and view the PMs that you have sent the page takes an age to load and it may or may not time out, but heres the annoying part, if the page doesn't time out then you go to the page again it is almost instantaneous, so it's only the first time it is run that the trouble arises.
So if I log out of the site and open a new web browser and visit the PM sent page then once again it takes forever, then subsequent visits after that are very fast.
Anyone able to shed some light on this?
March 21, 2009 at 4:13 pm
Yeppp... couple/three things... first, it sounds like you may have a little "parameter sniffing" going on. Google'ing that will produce a pot wad of information on what it is and how it can be fixed.
Second, the fact that the page comes back a second time in an instant is just a matter of stuff being in cache.
Third... you mentioned you added indexes and it was better for a while. I apologize for the obvious question but didn't see it in this rather long thread... are you doing any maintenance on the indexes to keep them up to snuff?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2009 at 4:29 pm
Jeff Moden (3/21/2009)
Yeppp... couple/three things... first, it sounds like you may have a little "parameter sniffing" going on. Google'ing that will produce a pot wad of information on what it is and how it can be fixed.
Sure, I will definitely check that out
Jeff Moden (3/21/2009)
Second, the fact that the page comes back a second time in an instant is just a matter of stuff being in cache.
When you say stuff being in cache do you mean in the browser, if so that is not the case as the content changes when viewing the page after subsequent views
Jeff Moden (3/21/2009)
Third... you mentioned you added indexes and it was better for a while. I apologize for the obvious question but didn't see it in this rather long thread... are you doing any maintenance on the indexes to keep them up to snuff?
No, I'm not doing any maintenance on the indexes, I was under the impression that once they were created you didn't need to do any more as long as the table structure didn't change or the way in which the tables were referenced were changed, am I wrong in thinking that?
March 21, 2009 at 4:54 pm
No... the cache I'm talking about is in the memory of the SQL Server.
And, no... indexes don't take care of themselves. Now that you've identified that you're not running any regular maintenance on your tables, I'll bet that if you did, you'd think you'd bought a new super server.
You don't need to redevelop the wheel to do the index maintenance... look up DBCC ShowContig in Books Online... once there, find the example with the following label and decide what to set the @MaxFrag variable to. The example has 30 (percent). Personally, I use 10 or 15 depending on the database.
[font="Arial Black"]E. Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database[/font]
If you look at the code in that example (which you can implement as is), you should find a snippet that allows you to fun INDEXDEFRAG on a whole table. Do that as an experiment for the IM table you're currently having performance difficulty with and see what happens.
Like I said, start doing some regular maintenance on the databases (this is just ONE part of what regular maintenance should be) and be amazed at how much faster your server runs.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2009 at 5:32 pm
Alrighty, I've put in code to sop the parameter sniffing and that alone seems to have had a positive effect, I've also scheduled in a job to run in the early hours of the morning to peform the index defrag so I'll know what the effect of that will be tomorrow morning, got my fingers crossed 🙂
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply