June 12, 2013 at 10:10 am
Hi Everyone
On our monitoring tool sqlMonitor we got an alert called 'sql server instance unreachable' which lasted for about 2 minutes. I have checked the Cpu queue length for this period and it spikes at around a value of 17 and the counter 'Machine:Processot time' peaks 100% and stays there for about 1 minute. How can i find out what caused this as having the data is good but i need a way of analyzing the query that may have caused this ?
June 14, 2013 at 2:50 am
I'd suggest going to the moment in time when that occurred and look at the top 10 queries. You may have to bounce around the time a little bit to find the right spot, but you should be able to see which query was causing the problem pretty quickly. You get to the history page by going to the Global Overview tab and then clicking on the clock face that you'll see on the right hand side of the screen near the top. Just go to around the time of the alert or shortly after and you should be able to drill down to the server instance on that same page to see the top queries.
"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
June 14, 2013 at 3:16 am
Thank you Grant I will certainly do that.
I have your boook on query tuning distilled...Can you advise me on a 'REMOTE SCAN' i am seeing on a linked server. I have 2 queries which do the same thing in different tables on the server (updates). The indexes are the same on both tables apart from 1 has a unique clustered index and the other just has a non unique clustered index. They both use the same linked server but one query takes for ever to do the update. I cant find anything in your book regarding the 'Remote scan' and how to avoid it - by the way the query that runs quicky says 'Remote query' in the execution plan.
Any ideas ?
June 14, 2013 at 3:26 am
Remote scan is pretty much what it says, a scan against a remote machine through linked servers. I didn't talk about that in the book. Maybe something I should add.
So, without seeing your queries, I can't tell you specifically what the issue might be, but, in general, linked servers are notorious because, unless you're using OPENQUERY to pass the query and parameters to the linked machine, then all data being accessed has to be moved between the servers, then what ever processing you're doing is done, then all the data that needs to be moved is moved back. In short, it's extremely messy and frequently poorly performing. Very best answer, don't use linked servers. Second best answer, look into OPENQUERY and use that to pass the processing over to the linked machine.
"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
June 14, 2013 at 3:39 am
Grant, the queries are as follows:
-- QUERY 1 TAKES OVER 30 SECONDS AND CAUSES TIME OUTS --
UPDATE [SERVER02].DATABASEA.dbo.TABLEA
SET Comment = Cast(cast(237 AS varchar(10)) AS Ntext)
WHERE UserID = '6FE30BDA-A38B-4DOD-B10F-DC0D324E7883'
--QUERY 2 RUNS IN UNDER 1 SECOND --
UPDATE [SERVER02].DATABASEA.dbo.TABLEB
SET LastActivityDate = '13-jun-2013'
WHERE UserID = '6FE30BDA-A38B-4DOD-B10F-DC0D324E7883'
I have ran both queries on server02 directly and i get the same execution plan on each ie an INDEX SEEK costing 25% and a CLUSTERED INDEX UPDATE Costing 75%.
When i run them from server 4 using the 2 queries listed above i get the first one showing the 'REMOTE SCAN' at cost of 100% and showing 350,000+ rows and query 2 has the 'REMOTE QUERY' with only 1 row.
I am new in this office and i have been told these queries used to work. I dont understand why 1 is quciker than the other ? Does this extra info help you in any further explanation ?
Thanks Grant
June 14, 2013 at 4:23 am
Still pretty much back to where I was. You'll note that one is moving all the data and the other is not. I suspect that the functions on the data in the longer running query may be the cause, although they shouldn't affect it since the same filtering mechanism is used. But it's indicative of issues with using linked servers and why, if I have to use, them, I use OPENQUERY.
"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
June 14, 2013 at 5:08 am
Grant - you've hit the nail on the head !!!!
I changed the 'SET Commnet = Cast(Cast(237 As Varchar(10)) As Ntext)
in the slow performing query to just 'SET Comment = '237' and it ran in less than 1 second !!!!!
The comment field is set to Varchar(50) -
Can you explain to me why the increased overhead because of the CAST statement ?
June 14, 2013 at 5:42 am
Off the top of my head, no.
It sounds like the optimizer, because the function, assumed it needed to bring back all the data in order to filter it down. But that's a little odd. Sorry. I can't always say "why" something occurs.
"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
June 14, 2013 at 6:53 am
Ok Grant no worries.
I thought i had fixed this problem. When we run it inline as the simple update with the removed cast function it runs perfectly, but, when we run it from the altered SP it is still doing the REMOTE SCAN.
The sp on both servers reflects the new change in code !!!
Any ideas as to why when we run it as an inline update it works but not from the SP ?
June 14, 2013 at 9:10 am
It still a linked server in both cases? You should always get the remote scan if it's a linked server, so I'm a little confused by what you're telling me.
"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
June 14, 2013 at 9:21 am
I think he's saying that it runs fine when run locally but slowly when running against a linked server. This makes complete sense due to the fact that linked servers are what they are.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply