May 8, 2013 at 1:39 am
I don't have a problem at the the mo. But was wandering after making a fairly large deployment of multiple sprocs/views.
Is there an easy way of finding out which sproc/view is grinding the system to a halt.
DBCC Opentran (to see the longest running transaction)
Dbcc INPUTBUFFER(SPID)
SELECT * from SYS.sysprocesses where blocked <> 0
(find the top block).
All very clumpy and slow is there a better solution.
Am i missing something.
May 8, 2013 at 1:52 am
Bobby Glover (5/8/2013)
I don't have a problem at the the mo. But was wandering after making a fairly large deployment of multiple sprocs/views.Is there an easy way of finding out which sproc/view is grinding the system to a halt.
DBCC Opentran (to see the longest running transaction)
Dbcc INPUTBUFFER(SPID)
SELECT * from SYS.sysprocesses where blocked <> 0
(find the top block).
All very clumpy and slow is there a better solution.
Am i missing something.
SQL Profiler / Top transactions report. I would prefer to use server side trace to find what is causing slowness in my prod servers.
May 8, 2013 at 2:51 am
Thanks.
Top transaction report doesn’t always report the sproc that is causing the issue.
If the problem sproc in question has increased and is used very often it may not appear but will cause issues if it is holding locks for a longer period of time.
This is a problem that we all face from time to time, just putting it out there if there is a quick way to identify and roll back the sproc quickly.
May 8, 2013 at 3:53 am
I think that first you need to define what you are looking for. For example if your application is slow due to slow I/O, you'll might want to check (among other things) which queries cause the most I/O operation. On the other hand if you have a blocking issue, you'll want to check which queries are at the head of the blocking chain. For each one of those things you'll need a different approach and different queries, so first you'll need to decide what problem you want to solve (High CPU, High I/O operations, memory pressure, blocking etc') and then you have to decide how to get the needed information.
Having said that my favorites tools are – Profiler/server side trace, using sys.dm_os_waiting_tsks and sys.dm_os_wait_stats to see why something is slow, using dm_exec_query_stats in order to find out statistics about query plan that are in the cache.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 8, 2013 at 5:52 am
For an immediate snapshot for what's going on, INPUT_BUFFER is very SQL Server 2000. Switch to the Dynamic Management Objects such as sys.dm_exec_requests and sys.dm_exec_query_stats as the previous poster said. They are a HUGE improvement in functionality especially because you can combine them with sys.dm_exec_query_plan and sys.dm_exec_sql_text to get out the query plan and the query itself.
If you're concerned with tracking the information over time, again, toss Trace & Profiler. Go with Extended Events. There is no GUI in SQL Server 2008 to set up or consume the output, but you have a lot more control over exactly what you capture and how you capture. Further, extended events have a much lower impact on the server than trace.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply