Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Growth in tempdb and more RAM Expand / Collapse
Posted Tuesday, January 1, 2013 9:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, December 28, 2013 5:45 PM
Points: 94, Visits: 96
In general, would adding more memory reduce the growth of the tempdb during general operations of SQL Server?
Post #1401648
Posted Tuesday, January 1, 2013 11:56 PM



Group: General Forum Members
Last Login: Tuesday, July 19, 2016 4:34 AM
Points: 2,850, Visits: 4,076
In a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries do not get the minimum required memory for the bitmap. This can affect the query performance and if the sorting process can not fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. To resolve this problem add physical memory or tune the queries to use a different and faster query plan.
see this link Optimizing tempdb Performance

I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1401671
Posted Wednesday, January 2, 2013 2:19 AM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
Maybe. Depends why TempDB's growing.

If it's due to hash and sort spille, adding memory may help (optimising queries may help more). If it's due to temp tables and table variables, more memory won't help the TempDB size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1401703
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse