April 28, 2010 at 4:35 am
lol thanks, I'm monitoring it currently, tempdb files sizes and checking queries as the day goes on.
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 28, 2010 at 5:14 am
And you need to identify queries that using a lot of I/O to see what they're doing. You can track down the causes and address them.
"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
April 28, 2010 at 5:21 am
Your best bet is to run a day long session of Profiler and PerfMon to identify your issues.
Or have both run sample traces every X number of minutes. But that's less likely to get you the info you need even if it doesn't hurt performance as much as the day-long trace.
April 28, 2010 at 6:52 am
I'd agree with Brandie that a good long Profiler session might help you identify what is using space.
April 28, 2010 at 6:57 am
Although, again, I'd suggest a quick query against sys.dm_exec_query_stats. Order by physical/logical reads or physical/logical writes and you'll see the queries currently in cache that are hitting the system hard in that area. It's quick, and in most cases, as likely to be accurate as gathering data with a server side trace (don't use the Profiler gui).
That said, a good day of trace data, as Steve & Brandie said, will tell you what you need to know.
"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
April 28, 2010 at 1:35 pm
-- Which queries are causing the most IO operations (can take a few seconds)
SELECT TOP (20) total_logical_reads/execution_count AS [avg_logical_reads],
total_logical_writes/execution_count AS [avg_logical_writes],
total_worker_time/execution_count AS [avg_cpu_cost], execution_count,
total_worker_time, total_logical_reads, total_logical_writes,
(SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
(SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
ELSE statement_end_offset
END - statement_start_offset
) / 2)
FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
last_logical_reads, min_logical_reads, max_logical_reads,
last_logical_writes, min_logical_writes, max_logical_writes,
total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
(total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND (total_worker_time/execution_count) > 100
ORDER BY io_weighting DESC;
April 29, 2010 at 2:27 am
Hi Guys an update ...
The Tempdb is staying at the 35Gig mark. We are happy to leave it at that size as it is not growing to fill the drive but will monitor it.
There was warnings that SQL was running low on memory, PAE switch enabled, AWE was enabled and now with lock pages in memory is the latsest change. Server has 6Gig of RAm ... SQL min = 1Gig and Max = 4Gig Memory
We will be monitoring it further memory issues
Thanks
Feeg
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 29, 2010 at 5:22 am
2cams (4/29/2010)
Hi Guys an update ...The Tempdb is staying at the 35Gig mark. We are happy to leave it at that size as it is not growing to fill the drive but will monitor it.
There was warnings that SQL was running low on memory, PAE switch enabled, AWE was enabled and now with lock pages in memory is the latsest change. Server has 6Gig of RAm ... SQL min = 1Gig and Max = 4Gig Memory
We will be monitoring it further memory issues
Thanks
Feeg
Based on this and the questions around tempdb, I really hope you're gathering statistics on the queries being run and are looking at the code because I think you guys have some issues there that need to be addressed. Otherwise, as your system grows I think you're going to run into more, and bigger, problems.
"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
April 30, 2010 at 3:30 am
Hi Grant, Solarwinds is a server performance monitoring application which is 3rd party. We logged a call with them enquiring about the tempdb issues we haing.
We will monitor the temodb growth as like you said as the user DB's grow it is likely the tempdb will do the same. Thanks once again to all for the help, queries and comments ;-);-)
MCITP: Database Administrator 2005
MCTS SQL Server 2008
MCP SQL 2012/2014
MCSA SQL Server 2012/2014
MCSE Data Management and Analytics
April 30, 2010 at 12:43 pm
Best practice guidelines for tempdb (both 2005 & 2008) clearly states that tempdb files should be of fixed size, one per cpu (max of 8, diminishing returns with more than 8), both log and data files should be a fixed EQUAL size. So on a 8 CPU server, you would have 8 data files (all the same size, no autogrow), and one log file (same size as the datafiles, and no autogrow). A good starting point for the size of tempdb would be some percentage of the amount of memory you have planned to allocate to the SQL Server.
For this case, I’d start by adjusting the tempdb to match Microsoft best practice guidelines. Since you only have a 40 GB device to work with, you’re probably going to want to make the total size of all the tempdb physical files to total up to around 35 GB to start with.
Joe Burdette
hanesbrands.com
April 30, 2010 at 2:07 pm
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
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
April 30, 2010 at 11:33 pm
2cams (4/30/2010)
Hi Grant, Solarwinds is a server performance monitoring application which is 3rd party. We logged a call with them enquiring about the tempdb issues we haing.We will monitor the temodb growth as like you said as the user DB's grow it is likely the tempdb will do the same. Thanks once again to all for the help, queries and comments ;-);-)
I'd like to know why you think it's the SolarWinds app that's causing the problem. You haven't been able to isolate the query that causes the growth so what makes you believe it's the SolarWinds app?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2010 at 4:46 am
I'd like to know the answer to Jeff's question myself. Please don't take this the wrong way, because we're just trying to help. But you seem to be making an assumption that could cause you problems in the future.
May 3, 2010 at 6:17 am
I see that this thread is still alive, so I'm throwing some more 0.02 € in.
Adam Machanic wrote a great monitoring procedure that could help you tracking down this issue. His sp_WhoIsActive returns two columns that you could find helpful in your scenario:
* tempdb_writes
* tempdb_current
You could schedule this procedure to run every 5 minutes and output the results to a table for those queries that exceed a predefined threshold on tempdb_writes and tempdb_current. I'm sure that a query that generates 40 Gb of tempdb activity won't last less than 5 minutes, so you'll be quite sure to catch it in your monitoring.
-- Gianluca Sartori
May 3, 2010 at 8:40 am
Gianluca Sartori (5/3/2010)
I see that this thread is still alive, so I'm throwing some more 0.02 € in.Adam Machanic wrote a great monitoring procedure that could help you tracking down this issue. His sp_WhoIsActive returns two columns that you could find helpful in your scenario:
* tempdb_writes
* tempdb_current
You could schedule this procedure to run every 5 minutes and output the results to a table for those queries that exceed a predefined threshold on tempdb_writes and tempdb_current. I'm sure that a query that generates 40 Gb of tempdb activity won't last less than 5 minutes, so you'll be quite sure to catch it in your monitoring.
Similar method in the article that I suggested.
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
Viewing 15 posts - 16 through 30 (of 67 total)
You must be logged in to reply to this topic. Login to reply