?
Solved

monitor queries that use too much tempdb log

Posted on 2016-10-19
20
Medium Priority
?
82 Views
Last Modified: 2016-10-26
hi,

recently found a Query that eat too much tempdb log but we knew it just before it is too late.

resource governor from SQL server don't control tempdb, then what is your way to find it out if we define a tempdb log value and let MS SQL server keep detecting it and kill it if the tempdb space is growing too large because of this and then report it out by email.

what tools you all use for that if any .
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 3
20 Comments
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 41849645
There are corporate monitoring tools as SCOM or BMC Patrol.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41849656
use that ? I use idera DM for that and checking with them. but it is more on tempdb contention.

how scom do it ? you tried that before ?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41849660
Those are only monitoring tools. It will send you a warning when the configured threshold is reached.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 1

Author Comment

by:marrowyung
ID: 41849745
but it is not something like detecting what queries keep using Tempdb log and let us specify a size limit and if this query take too much , then kill it ?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41849754
but it is not something like detecting what queries keep using Tempdb log and let us specify a size limit and if this query take too much , then kill it ?
If you need to do something like that is because you're not acting correctly.
Right thing to do is to solve the issue and not killing the query because the query will be run over and over and you'll keep killing it.
Also if your tempdb is growing too much is because is not correctly configured and you should review the size and grow policy for tempdb and reconfigure it to accomplish the extra space needed.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41849759
"Right thing to do is to solve the issue and not killing the query because the query will be run over and over and you'll keep killing it."

yes, you are right, we found an issue with a developer who do open trans but not commit trans, then hold the tempdb log for a long time and it keep growthing, we found that but I need more to control it.

if kill it and report to it, we can stop the SQL serve log from growthing to much/out of control, have the evident then check TOMORROW.

this is what we do with report, handle it later as when the time it happens, we are sleeping.

"Also if your tempdb is growing too much is because is not correctly configured and you should review the size and grow policy for tempdb and reconfigure it to accomplish the extra space needed."

yes we already done that but occasionally we see sth new.

anything in your life to detect people code sth stupid, like open tran and never commit ?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41849762
yes, you are right, we found an issue with a developer who do open trans but not commit trans, then hold the tempdb log for a long time and it keep growthing, we found that but I need more to control it.
Why the developer don't fix his code?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41849766
she has been caught for that yesterday and now I get complain on why I can't find this out but another senior developer accidentally found that out. so I need a one method to do it quickly..

but coding problem can never found by me as I am a production/infrastructure DBA. I have no knowledge on that.

other than whoisactive, someone use this also can't find that out, I am finding out what else i can do to control/monitor tempdb log size.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41849775
I would say this should be threated as an emergency case and developers should have this fixed and a new released should be immediately available in Production but I'm not the manager.
The time you're spending to try to find a solution is more than the time they should spent to fix it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41849792
"I would say this should be threated as an emergency case and developers should have this fixed and a new released should be immediately available in Production but I'm not the manager.

without the email report evident, how can we ask them to fix it.

"The time you're spending to try to find a solution is more than the time they should spent to fix it.

I don't think so as if we have the tools then make use the tools to dig it out.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41849871
without the email report evident, how can we ask them to fix it.
Yourself answered this before:
"yes, you are right, we found an issue with a developer who do open trans but not commit trans, then hold the tempdb log for a long time and it keep growthing, we found that but I need more to control it."

I don't know how is the communication process inside your company but the places I've been we just needed to send an email to the developers stating the facts to have them starting to work on a fix.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41850173
You can't have a monitoring process to detect specific development malpractice. However a very simple but extremely effective tool is Adam Mechanic's sp_whoisactive stored procedure:

http://sqlblog.com/files/folders/beta/entry42453.aspx

It is a free stored procedure that you download and just run the script on the server. It will create the procedure in the master database. After that any time there is something going on you simply open a new query window and run it:

EXEC sp_whoisactive

The result set will show you exactly what happens at the moment of execution, that is processes that are active with the exact query that is exectuted, the waiting type inormation, blocking processes, tempdb allocation, memory, CPU and other usefull info. One column that is returned is called "open_tran_count" and that normally should be 0 unless you have a long running transaction. Be aware that if >0 doesn't necessarily mean that something is wrong, some transactions simply take longer time to execute but with this it is very simple to detect your kind of situation. You can even test it:

1. Open a new query window and run this:
begin tran
select getdate() as dte

Open in new window

2. Open another query window and execute this:
EXEC sp_whoisactive

Open in new window

You will be able to see the process from step 1 with the query and with open_tran_count = 1 and it will not go away from the result of sp_whoisactive proc if you repeatedly execute it unless you add "commit tran" in the first window, select it and execute it or you kill/close that query window.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41851415
Victor:

"I don't know how is the communication process inside your company but the places I've been we just needed to send an email to the developers stating the facts to have them starting to work on a fix."

yes, you are right, but how can we find it and send to them before someone complain by a more meaningful record?

Zberteoc'

"extremely effective tool is Adam Mechanic's sp_whoisactive stored procedure:

http://sqlblog.com/files/folders/beta/entry42453.aspx"

we are using this but this one point out diff things to us, one short come by this tools is, from time to time we can't see the whole statement as it cut the queries by parts.

 we do this:

EXEC dbo.sp_WhoIsActive
     @show_own_spid = 1,
     @get_plans =1 ,
     @get_additional_info =1,
       @get_transaction_info = 1
, @find_block_leaders = 1,
@get_outer_command=1
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41851558
yes, you are right, but how can we find it and send to them before someone complain by a more meaningful record?
With a monitoring tool (I already provided 2 examples above) and DBA pro active work. There's no miracles, man.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41851567
hhaaha, then I still relies on my tools then, just want to know how you handle this kind of case.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41851572
With some manual work. It's DBA work. Can't run from it :)
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1000 total points
ID: 41851995
we are using this but this one point out diff things to us, one short come by this tools is, from time to time we can't see the whole statement as it cut the queries by parts.
How is that a shortcoming? At contrary, it is an advantage! You know exactly what piece of code cases you the problem. I would hate a tool that will only point me to the stored procedure call. And the problem you are describing should had been caught in no time with sp_whoisactive. But if you really want a graphical monitoring tool that is also free I highly recommend you the free version of the Solaris Database Performance Analyzer. I think it is the best there is, it is showing the waiting statistics, which at at the core of performance statistics:

http://www.solarwinds.com/free-tools/database-performance-analyzer-free
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41861173
yes, but developer also said by the query found by whoisactive, they says they can't find where the code is.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41861174
tks alll
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41861208
yes, but developer also said by the query found by whoisactive, they says they can't find where the code is.
Give them the piece of code bellow that will return a list of all the objects in a database, like views, functions and stored procedures, that contain a certain string. You don't have to pass the whole query returned by the sp_whoisactive but at least something from it that will narrow down the search:
declare 
	@str_to_search nvarchar(4000)=''

select distinct
	OBJECT_SCHEMA_NAME(o.[object_id]) as [Schema],
	OBJECT_NAME(o.[object_id]) as ObjectName,
	o.type_desc as ObjectType
from  
	sys.objects o
	inner join sys.syscomments c
		on c.id=o.[object_id]
where
	replace([text],'_','*') like '%'+replace(@str_to_search,'_','*')+'%'

return

-- ##### version with code return here
;with objcode as
(	
	select
		OBJECT_SCHEMA_NAME(o.[object_id]) as [Schema],
		OBJECT_NAME(o.[object_id]) as ObjectName,
		o.type_desc as ObjectType,
		replace(replace(replace(replace(replace(replace(cast(
				(
					select 
						ISNULL([text],'')+'' 
					from  
						sys.syscomments 
					where 
						id=o.[object_id] 
					order by 
						colid for xml path(''), TYPE
				) as nvarchar(max)
			),'&#x0D;',''),'&gt;','>'),'&lt;','<'),'&le;','<='),'&ge;','>='),'&amp;','&') as ObjectCode
	from  
		sys.objects o
) 
select 
	*,
	case when len(ObjectCode)>'65530' then 'Text columns are limitted by MS to 65535 characters' else '' end as Note 
from 
	objcode
where
	replace(ObjectCode,'_','*') like '%'+replace(@str_to_search,'_','*')+'%'
order by 
	ObjectType,
	[Schema],
	ObjectName

Open in new window

0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question