[Informatica] Tutorial Part 5

Here is the last session of informatica tutorial:

http://www.youtube.com/watch?v=8zdPvAkMv90

[T-SQL] Find current location of data and log file of all database

In general we see that some times we go trough very small queries but it has really a big impact during ongoing scenario.

Here is one of the query by which you can see the location of data and log file of all the databases.

SELECT name, physical_name AS current_file_location
FROM sys.master_files

[T-SQL] Add new column [Not Null] column in a table already having data - Part 2

Previously i wrote an article with a method that how can we include not null column in a table already having data.

But that method has number of scripts to execute. SQL Server handle it in a very good way.

If you execute the script

ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO

Then SQL Server will itself insert the default value 0 for the rows which are already present in database.

[SQLServer] TempDB Recommendations

I was going trough a nice article , so sharing apart from it regarding the recommendations for TempDB.

Source: http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx

Pre-size TempDB appropriately. Leave autogrow on with instant file initialization enabled, but try to configure the database so that it never hits an autogrow event. Make sure the autogrow growth increment is appropriate.

· Follow general IO recommendations for fast IO.

· If your TempDB experiences metadata contention (waitresource = 2:1:1 or 2:1:3), you should split out your data onto multiple files. Generally you will want somewhere between 1/4 and 1 file per physical core. If you don't want to wait to see if any metadata contention occurs you may want to start out with around 1/4 to 1/2 the number of data files as CPUs up to about 8 files. If you think you might need more than 8 files we should do some testing first to see what the impact is. For example, if you have 8 physical CPUs you may want to start with 2-4 data files and monitor for metadata contention.

· All TempDB data files should be of equal size.

· As with any database, your TempDB performance may improve if you spread it out over multiple drives. This only helps if each drive or mount point is truly a separate IO path. Whether each TempDB will have a measurable improvement from using multiple drives depends on the specific system.

· In general you only need one log file. If you need to have multiple log files because you don't have enough disk space on one drive that is fine, but there is no direct benefit from having the log on multiple files or drives.

· On SQL Server 2000 and more rarely on SQL Server 2005 or later you may want to enable trace flag -T1118.

· Avoid shrinking TempDB (or any database) files unless you are very certain you will never need the space again.