Tempdb is a factor that we could not forget when troubleshooting performance issue on SQL Server. Here is some my notes:
Allow for tempdb files to automatically grow as required.
Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value.
Preallocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment.
Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.
Put the tempdb database on disks that differ from those that are used by user databases.
Refer: http://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
Ở 1 link khác, http://technet.microsoft.com/library/Cc966534 - Storage top 10 Best Practice, để tuning tempdb còn có những kiến thức sau:
- Chia 1 tempdb tương ứng ra nhiều file, mỗi file tương ứng 1 CPU, ví dụ server có 2 CPU thì chia ra 2 file.
- Tempdb hoạt động tốt nhất nếu dùng Raid 1+0.
Tham khảo thêm ở đây: http://www.confio.com/logicalread/sql-server-tempdb-best-practices-initial-sizing-w01/#.Ul9bKlCNm-g
Ở 1 link khác, http://technet.microsoft.com/library/Cc966534 - Storage top 10 Best Practice, để tuning tempdb còn có những kiến thức sau:
- Chia 1 tempdb tương ứng ra nhiều file, mỗi file tương ứng 1 CPU, ví dụ server có 2 CPU thì chia ra 2 file.
- Tempdb hoạt động tốt nhất nếu dùng Raid 1+0.
Tham khảo thêm ở đây: http://www.confio.com/logicalread/sql-server-tempdb-best-practices-initial-sizing-w01/#.Ul9bKlCNm-g
0 comments:
Post a Comment