large database - tempdb
-
Also I suggest to monitor the initial size of the files that form tempdb and compare them with their actual size as it will give you good information on the need of adjusting them or not.
The following query will help you get that information.
SELECT
alt.filename
,alt.name
,alt.size * 8.0 / 1024.0 AS originalsize_MB
,files.size * 8.0 / 1024.0 AS currentsize_MB
FROM
master.dbo.sysaltfiles alt INNER JOIN tempdb.dbo.sysfiles files ON
alt.fileid = files.fileid
WHERE
dbid = db_id('tempdb')
AND alt.size <> files.size
Ernesto Fernandez
LA -
I would have to agree. Stop shinking the tempdb. You only increase the overhead on the machine by shrinking it. Each time you do it, it consumes more disk IOs each time it grows. Sperating in into mulitple files on the same disk area (raid5) would be good.
Growth of the tempdb and the bad performance are not usually related, but but corralated. If you have the code the application was written with, start profiling the queries. Analyze the explain plans. Also, if you use the table option to gather the data, you can right queries against the profiled information to see how many times the queries are being ran. Start with high volume queries. Little gains in performance and show up big on a system if they are ran hundres or 1000's a time a hour. We had one application that was running a query more than 1000 times a hour. We were able to put a index hint in the query and reduced the time from 12 sec 1.2 seconds. OR, if you see a process or query taking a long time, hit that one.
Perfomance tuning, you will gain 10% at the DB and 90% tuning query/processes.
Joseph
-
Thanks to all! I really appreciate the help!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply