May 3, 2010 at 8:44 am
You can't rely just on writes, though. Excessive reads could also be causing an issue. Especially if there's a RBAR problem in the query. Bad use of indexing could also cause excessive reads. TempDB, to my knowledge, doesn't record just writes.
May 3, 2010 at 8:47 am
Brandie Tarvin (5/3/2010)
You can't rely just on writes, though. Excessive reads could also be causing an issue. Especially if there's a RBAR problem in the query. Bad use of indexing could also cause excessive reads. TempDB, to my knowledge, doesn't record just writes.
With the article I suggested, it won't matter on whether it is reads or writes. It captures the query that is running that causes the log growth.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2010 at 8:51 am
Sorry, Jason. I'm referring to Gianluca Sartori's post. I should have quoted him.
May 3, 2010 at 8:59 am
I don't see how tempdb reads can grow its data file(s).
At least, I'm not aware of any way this could happen, but I could be flat wrong.
Can you please shed some light?
-- Gianluca Sartori
May 3, 2010 at 9:01 am
Gianluca Sartori (5/3/2010)
I don't see how tempdb reads can grow its data file(s).At least, I'm not aware of any way this could happen, but I could be flat wrong.
Can you please shed some light?
Unless we're talking about the building of hash tables, etc., as part of reads, I'm confused on how reads can increase the size of tempdb too.
"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
May 4, 2010 at 7:16 am
CirquedeSQLeil (4/30/2010)
I suggest to find what is causing it to be that big. Gianluca proposed a solution. I'll propose another option - read this article[/url] and try it's suggestions
Hi guys, I implemented the suggestions in the article provided ... I set the SQLAgent to run it every 5 min. I really want to get to the bottom of this Tempdb growth as I can you you guys do to ...
Will keep you guys posted ;-);-)
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
May 4, 2010 at 8:42 am
Thanks - good luck.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 7, 2010 at 4:15 am
Hi guys on the 5th, we stopped the Solarwinds application, we restarted the SQL service to flush out the Tempdb and started this app which ran every 5 min. I made the Tempdb data file initial file size 1Gb and the log 100Mb and they got Solarwinds back up and running.
@ 02:25 - 02:35 it grew by 3Gb The Tempdb is not @ 35Gb but @ 4.2Gb currently.
In this time 1 Procedure, the SAME Procedure runs for 38 times ... below ==>
CREATE PROCEDURE [dbo].[dbm_TrapMessages_DeleteStale]
@date datetime
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ChunkSize as Int
DECLARE @CatchCount int;
DECLARE @msg VARCHAR(max), @sev INT, @st INT
DECLARE @RowCountVar Int
Set @ChunkSize = 10000
SET @CatchCount = 0;
SET IMPLICIT_TRANSACTIONS OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ROWCOUNT @ChunkSize
NextChunk:
BEGIN TRY
DELETE Traps FROM (SELECT TOP 10000 TrapID FROM Traps WHERE DateTime < @date) AS T1 WHERE Traps.TrapID=T1.TrapID
SELECT @RowCountVar = @@ROWCOUNT
DELETE TrapVarBinds FROM (SELECT TrapVarBinds.TrapID FROM TrapVarBinds LEFT JOIN Traps ON TrapVarBinds.TrapID=Traps.TrapID WHERE Traps.TrapID IS NULL) AS T1 WHERE(TrapVarBinds.TrapID = T1.TrapID)
SET @CatchCount = 0; --Null catch count
END TRY
BEGIN CATCH
SET @CatchCount = @CatchCount + 1; --Increment catch count
IF (@CatchCount = 3) --If catch count reach this value re raise exception
BEGIN
SELECT @msg = ERROR_MESSAGE(), @sev = ERROR_SEVERITY(), @st = ERROR_STATE()
RAISERROR (@msg, @sev, @st)
END
GOTO NextChunk;
END CATCH
IF @RowCountVar = @ChunkSize GOTO NextChunk
SET ROWCOUNT 0
INSERT INTO Events ( EventTime, Acknowledged, Message, EventType, NetworkNode, NetObjectID )
VALUES ( GetDate(), 1, 'All Trap Messages older than ' + convert(varchar(50), @date) + ' were deleted during nightly database maintenance' , 1000 , 0 , 0 )
END
We let it run through till today the 7th ...
this morning this Tempdb went from 3.2Gb to 38.3Gb and again started to grow @ 02:25 but until 03:25. 1 Hour 35Gb in growth.
The CREATE PROCEDURE [dbo].[dbm_TrapMessages_DeleteStale] once again ran numerous times, 311 times +- with some taking well over an hour to execute! Program =(Database Maintenance)
CREATE PROCEDURE [dbo].[NPM_RemoveNodeValue] ran 38 times +- 1sec Program =(.Net SqlClient Data Provider)
CREATE PROCEDURE [dbo].[NPM_SaveNodeValue] ran 105 times +-1sec Program =(.Net SqlClient Data Provider)
There are also numerous TSQL statements and CREATE PROCEDURES which SELECT and even UPDATE temporary tables which reside in the Tempdb. Program =(NetFlowService)
Another thing there are a lot of consecutive CXPACKET waits once the first in a batch of CREATE PROCEDURE [dbo].[dbm_TrapMessages_DeleteStale] is executing!!!
Could these waits cause the Tempdb to grow???
The Degree of Parallelism = 0 and there are 4 CPU's on the box. Do you suggest I change it to 1 or 2? reference ==> http://www.confio.com/English/Tips/Sql_Server_Wait_Type_CXPACKET.php
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
May 7, 2010 at 4:56 am
The number of times a query runs doesn't mean anything. What matters is the number of reads, writes, and recompiles. Also the length of time a query runs.
I'll take a look at the proc you posted, but you need to go re-review your traces and see which queries produced the most reads & writes. Verify it is this one.
May 7, 2010 at 5:00 am
Is this query yours or does it belong to the vendor?
May 7, 2010 at 5:57 am
Hi this is the vendor's query, it takes from 3min to over an hour from start time to insert time!
So you saying the waits are nothing to worry about or should I change the degree of parallelim?
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
May 7, 2010 at 6:07 am
2cams, I'm surprised that you didn't set up any of the suggested monitoring methods and you keep on guessing. I don't think you'll go far this way.
Just my personal humble opinion, anyway.
-- Gianluca Sartori
May 7, 2010 at 6:24 am
I love it. They're running a delete statement with a READ_UNCOMMITTED isolation level... like that'll prevent locking.
You need to get on the vendor about the issue. It's pretty clear there's something up with their code. Take the data you've collected because it's pretty shocking how few vendors will believe you when you call their baby ugly.
"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
May 7, 2010 at 6:26 am
Hi I implemented what the article CirquedeSQLeil suggested. It ran for 2 days and in a previous post is what I found.
I ran the Query MANU-J posted ==> most IO operations
This is my 1st Tempdb issue so I'm grabbing whatever I can implement easily to help with this issue ... Thanks for all the suggestions and tips thus far!!!
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
May 7, 2010 at 6:33 am
This code hits one of my major pet peeves, which probably doesn't contribute much to your problem but certainly doesn't help it either.
Allow me to point out a few lines:
DECLARE @ChunkSize as Int
Set @ChunkSize = 10000
.....
SET ROWCOUNT @ChunkSize
.....
IF @RowCountVar = @ChunkSize GOTO NextChunk
Set @DBASuperRant = ON;
Why, oh why, oh WHY, do developers insist on using variables for HARD CODED VALUES???? It's just as easy to drop the first two lines of code and set RowCount = 10000 and then check if @RowCountVar = 10000, isn't it? It's not like they're resetting @Chunksize anywhere in their code or passing it in from the outside.
Variables take some small amount of memory space in SQL Server, do they not? This could (in a minor way) affect TempDB if TempDB is used to store the variable values.
GAH!
Set @DBASuperRant = OFF;
Sorry, but I just don't get this behavior. And it's not like I have a really valid reason for feeling this way. I just do.
On the other hand, the DELETE statements are kinda weird. I've never seen DELETEs phrased this way and I find myself thinking there has to be a better way for them to have been coded. Don't sub-queries always get evaluated without the use of indexes, almost RBAR style?
EDIT: I am actually looking for someone to correct me or agree with me on the above paragraph. I'm kinda brain dead this morning and want to make sure I'm right or understand why I'm wrong. SO please, chime in on this topic.
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply