large database - tempdb
-
I have a large database, about 300GB and it will continue to grow over the next few months. It is causing my tempdb to increase drastically reducing performance. I have a job scheduled to 'shrinkfile's in the tempdb but it fails some of the time. I need to plan for now and the future! HELP!
-
Need to find out why tempdb is growing so much. What is happening in your code that is causing tempdb to jump.
-
What is the best way to find out what is happening to the code? There is a siebel app that pulls user queries from the database.
-
You can run Profiler and watch for queries when the tempdb is growing. If this is a function of the data increasing (larger results in queries) then there's not much you can do.
Any query that uses an ORDER BY , GROUP BY, will use tempdb to store intermediate results. How big is your tempdb when you start the server? What's a drastic increase? You might need to just size a larger tempdb at startup.
-
I guess I'm looking for tips to increase performance on this large database that will probably be increasing in size on a regular basis. Right now the tempdb is at 500MB and running fine. Earlier it was up to 2000 and has even gone up to 1GB! I've read about moving the tempdb to another drive... which it is. Right now I have to restart the server at least once a week to "refresh" the tempdb. I also have a scheduled job to run every 10 minutes to 'shrinkfile'. Is it safe to keep shrinking the database like that.
-
Wow....a 300GB database with TempDB up to 1 GB. My 250+ GB database and my TempDB frequently grows to 4 GB or more.
I say put the TempDB on another drive with more space, if it's really a problem where it is now.
-SQLBill
-
Is it possible to link servers and place the tempdb on the other server?
Thanks a bunch for all your help. -
1GB isn't big, especially with a 300GB db. Why are you worried?
Moving tempdb to another drive will help it's performance, but if the server is slow, you might need a bigger server.
-
The database/server slows down to a horrible crawl. Today it was like that for over an hour. It'll freeze trying to get into Enterprise manager. We are a bank and can't have lag time. It took one manager 15 minutes to run a query that normally runs for 2 minutes. This is a regular occurrance with this server. Periodically (about once a week) we get horrible drag and I usually boot everyone off and reboot the server. I personally think it's bad queries. But the server should be big enough to handle it. I don't understand.
-
Seems you're looking into wrong direction.
Check what your SQL Server is doing. It must be some bad development: missing indexes, full table update instead of several rows update, cursors combined with table locks, etc.
If it's really tempdb you probably need to check the hard drive for bad clusters.
_____________
Code for TallyGenerator -
"I also have a scheduled job to run every 10 minutes to 'shrinkfile'."
Well, that could be one of the reasons of bad performance. Imagine that your DB needs a tempdb sized around 2 GB for certain operations. You start with a small tempdb. Every time it increases its size, it uses resources. Then you start to shrink it again, also using resources, and soon it has to grow again... and again. Stop the shrink job, let the tempdb grow and remain large. The size of TEMPDB, at least as long as it is a few GB, does not affect performance adversely, but the grow/shrink process does. As an example, our production DB is around 200 GB, while TEMPDB has grown to 15 GB. Most of the time, it consists of a few MB of data and lots of empty space - but if the space is needed, it can be accessed quickly. We never shrink TEMPDB... simply put it on a separate disk, that will be reserved for tempdb only, and you won't have to bother about the size.
Once you have sorted this out, I suggest you take a look at fragmentation of indexes, missing indexes etc.
-
Thanks.
-
Your tempdb only grows because SQL needs the space. Stop shrinking it! Plan to have a tempdb of whatever size is needed to service your application (be it 1GB or 50 GB...)
Also look at the KB article about high concurrency in tempdb. You may want to put tempdb into multiple fixed size files.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
I certainly appreciate all the help
-
If space permits, I would suggest creating multiple Tempdb files, say 4x4GB , on a separate disk, of an equal size and disable autogrowth for datafile...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply