Tempdb GROWS

  • 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

  • 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

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I'd agree with Brandie that a good long Profiler session might help you identify what is using space.

  • 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

  • -- 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;

  • 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

  • 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

  • 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

  • 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.


    Regards,

    Joe Burdette
    hanesbrands.com

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • 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