March 6, 2016 at 2:22 am
Please Help me Query/Performance Tuning Steps with out help of profiler
March 6, 2016 at 3:04 am
March 6, 2016 at 6:20 am
Performance tuning is a pretty big topic. To boil it down to a set number of steps, I guess they would be something like this:
1. Measure performance.
2. Make an adjustment and see if if you improved performance.
3. Test, test, test.
4. Repeat until done.
Look at your problem code and go through it section by section and line by line. Find your pain points. It could be that you only have one calculation in a large procedure that's giving you trouble. Or it could be the whole thing. Get each part to perform as well as it can, but keep in mind that the performance of the whole is what you're after.
Generally speaking, look for things like RBAR, implicit casts and non-SARGable predicates, scalar functions and MTVFs. If you have intrinsic functions that perform poorly (example: FORMAT) consider replacing them with something that's more performant.
March 6, 2016 at 10:23 am
1) Why can't you use Profiler? Aggregate profiler analysis is my single-most important tool as a performance tuning consultant!!!
2) You can capture workload with extended events.
3) sp_whoisactive is great for showing you what is hurting right now. It also has ability to do do a differential analysis for some seconds to see what is burning a lot of resources.
4) Glenn Berry's SQL Server Diagnostic Scripts have lots of DMV-based queries to find TOP N Worst Plans by different metrics (reads, duration, CPU, etc).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2016 at 4:12 pm
Eirikur Eiriksson (3/6/2016)
ganapathy.arvindan (3/6/2016)
Please Help me Query/Performance Tuning Steps with out help of profilerQuick suggestion, get Grant's book on execution plans[/url]
😎
+1 Million to that. Profiler can help but not like analyzing actual execution plans.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2016 at 1:56 am
You shouldn't be using Profiler in SQL 2014 anyway. Use Extended Events, Lighter-weight, far more powerful and you can convert any profiler-based analysis to extended events trivially.
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 7, 2016 at 6:06 am
You shouldn't be using Profiler anyway. Instead, use extended events.
The other posters largely have it right. I'm just piling on.
First, you have to identify what is causing stuff to run slow, which queries are the problem, either because of frequency of call, length of run time, resources used, or all three.
After you identify the query you want to make run faster, you need to understand if there are opportunities to improve performance. Usually this is done by improving the code. Sometimes it's adding an index. You have to go through the process of understanding your code in order to arrive at the right solutions for fixing it. This is a huge topic. I'd strongly suggest getting a copy of my book on query tuning. It's linked below.
"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 7, 2016 at 8:05 am
Grant Fritchey (3/7/2016)
You shouldn't be using Profiler anyway. Instead, use extended events.
Heh... I strongly agree... to disagree. 😛 Maybe it's just the bad taste in my mouth left by the mess on the original MS attempt at getting it right but I really don't care for Extended Events.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2016 at 8:12 am
Jeff Moden (3/7/2016)
Heh... I strongly agree... to disagree. 😛
You're welcome to disagree, but Profiler's deprecated and Extended Events are far better, lighter weight, more configurable and new events aren't getting added to server-side traces (eg no Availability group related profiler events, no columnstore related profiler events, no Hekaton related profiler events)
Plus there's a whole bunch of things that can be done with XE that couldn't be done with trace (eg catch all queries which waited for more than 250ms on PageLatch_EX)
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 7, 2016 at 8:23 am
Jeff Moden (3/7/2016)
Grant Fritchey (3/7/2016)
You shouldn't be using Profiler anyway. Instead, use extended events.Heh... I strongly agree... to disagree. 😛 Maybe it's just the bad taste in my mouth left by the mess on the original MS attempt at getting it right but I really don't care for Extended Events.
The main thing is that there are no additional trace events being added. There haven't been any for the last several versions. For core monitoring like rpc_complete & sql_batch_complete you can keep using trace, but if you want to monitor other stuff, AG, in-memory, columnstore, query store, you have no choice, you have to move to extended events. Yeah, they fumbled some of the initial set up, but that's all fixed now. It's the right tool for the job in 2012 or greater.
"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 7, 2016 at 11:50 am
1) In my experience as a performance tuning consultant at lots of different clients of varying sizes, 95%+ of performance problems can be found with profiler with it's existing set of events and capabilities. Most of the rest can be found with a few "TOP N bad queries by YYY" DMV queries. Yes, there are Okapi-style tuning issues that can only be found with XEs.
2) Show me an XE-consuming anything that can do what my copy of the free and awesome Qure Analyzer can do and I will CONSIDER giving up profiler tracing to disk as my primary means of finding what needs tuning at a client.
3) I don't need to learn XML-anything to continue to use profiler or traces.
4) Deprecated it may be, but I will repeat that I will still be using traces at least 10 years from now, if not 15. I still have clients on SQL 2000, and quite a few still on 2005. 15 and 10 years, respectively. And I have come across VERY few instances of SQL Server 2014 so far. So even if profiler doesn't make it into the next build after 2016, I will still be good for a long time.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 7, 2016 at 12:20 pm
Grant Fritchey (3/7/2016)
The main thing is that there are no additional trace events being added.
BWAAA-HAAAA-HAAA!!!! That means that it's finally stable! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2016 at 6:09 am
Jeff Moden (3/7/2016)
Grant Fritchey (3/7/2016)
The main thing is that there are no additional trace events being added.BWAAA-HAAAA-HAAA!!!! That means that it's finally stable! 😀
Ha! Right. Like SQL Server 2000 is stable.
"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 12, 2016 at 9:23 am
GilaMonster (3/7/2016)
Jeff Moden (3/7/2016)
Heh... I strongly agree... to disagree. 😛You're welcome to disagree, but Profiler's deprecated and Extended Events are far better, lighter weight, more configurable and new events aren't getting added to server-side traces (eg no Availability group related profiler events, no columnstore related profiler events, no Hekaton related profiler events)
Plus there's a whole bunch of things that can be done with XE that couldn't be done with trace (eg catch all queries which waited for more than 250ms on PageLatch_EX)
Yes, I know that Profiler's deprecated. So have a lot of other good tools. It's also a matter of opinion as to whether or not Extended Events are actually "better". Yes, I agree that they handle the new stuff and allow for other functionality that some may find useful, but I don't agree that they're "better" for the things that I currently use profiler for.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2016 at 4:27 pm
No to use profiler is a good idea...
Try this queries to find where your resources are beeing consumed
LONG RUNNING PROCESS (Processess running "now")
Use master;
IF OBJECT_ID('tempdb..#paso') IS NOT NULL
DROP TABLE #paso
IF OBJECT_ID('tempdb..#B') IS NOT NULL
DROP TABLE #B
SELECT requests.session_id,
requests.status,
requests.command,
requests.total_elapsed_time,
CAST('' as Varchar(4000))as SQLCommand
INTO #paso
FROM sys.dm_exec_requests requests
WHERE requests.session_id > 50
And requests.session_id != @@spid
declare @sid smallint;
declare cur cursor for
select session_id from #paso;
CREATE TABLE #B(eventtype nvarchar(30), parameters int, eventinfo nvarchar(max))
open cur;
fetch cur into @sid;
while @@fetch_status = 0
Begin
INSERT INTO #B(EventType, Parameters, EventInfo)
EXEC ('dbcc inputbuffer (' + @sid + ') with no_infomsgs')
Update #paso set SQLCommand = EventInfo
FROM #B
WHERE #paso.session_id = @sid
delete #B
fetch cur into @sid
End
close cur;
deallocate cur;
SELECT *
FROM #paso
ORDER BY total_elapsed_time DESC;
drop table #paso;
drop table #B;
STATISTICS TO FIND THE QUERIES THAT CONSUME YOUR RESOURCES
Use master;
DECLARE @orden varchar(30)
SET @orden = '#Orden#' --PUT HERE THE ORDER BY YOU WANT : 'AVERAGE_TIME', 'FRECUENCY', 'TOTAL_TIME', 'CPU', 'PHISICAL_READS', 'LOGIAL_READS'
SELECT top 30
creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
,execution_count
,total_worker_time
,total_elapsed_time
,total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY CASE WHEN @orden = 'AVERAGE_TIME' THEN total_elapsed_time / execution_count ELSE NULL END DESC,
CASE WHEN @orden = 'FRECUENCY' THEN execution_count ELSE NULL END DESC,
CASE WHEN @orden = 'TOTAL_TIME' THEN total_elapsed_time ELSE NULL END DESC,
CASE WHEN @orden = 'CPU' THEN total_worker_time ELSE NULL END DESC,
CASE WHEN @orden = 'PHISICAL_READS' THEN total_physical_reads ELSE NULL END DESC,
CASE WHEN @orden = 'LOGIAL_READS' THEN total_logical_reads ELSE NULL END DESC
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply