Archive for January, 2009

Moving the tempdb database

Posted: January 29, 2009 in Sqlserver

You can move tempdb files by using the ALTER DATABASE statement.

1.    Determine the logical file names for the tempdb database by using sp_helpfile as follows:

   use tempdb

   go

   sp_helpfile

The logical name of the database file can be found in name column.                                                            

Ex: tempdev (Will be the name master database file .mdf) and templog (Will be the name of  Transaction log file .ldf) .

2.    Use the ALTER DATABASE statement, specifying the logical file name as follows:

   use master

   go

  Alter database tempdb modify file (name = tempdev, filename = ‘E:\Mssql\tempdb.mdf’)

  go

  Alter database tempdb modify file (name = templog, filename = ‘E:\Mssql\templog.ldf’)

  go

3.    Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

 

Tip: Check Your Fill Factor

Posted: January 22, 2009 in Sqlserver


Really good article on fill factor from
Tip: Check Your Fill Factor
Say you have a glass completely filled with water, and you try to put more water in that glass. What happens? The water overflows.
It’s like that with SQL Server. Whenever a new row is added to a full index page, SQL Server moves about half the rows to a new page to make room for the new one. This is known as a page split. Page splits make room for new records, but they also take time and are very resource intensive. And they can cause fragmentation, which may adversely affect I/O operations. So how can you avoid them?
To prevent such situations, you must proactively determine the fill-factor value. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder for future growth. For example, configuring a fill-factor value of 60 means that 40 percent of each leaf-level page will be left empty to provide for index expansion as data is added to the underlying table.
The default fill-factor value is always 0, which is OK for the majority of situations. Basically, a fill factor of 0 means that the leaf level is filled almost to capacity, but some space is left for at least one additional index row. (Note that a fill factor of 0 and 100 are similar.)
You can set the fill-factor value for individual indexes during a CREATE INDEX or ALTER INDEX statement, or you can configure this value directly at the server level so that any new indexes created will use the default.
The following example sets the fill-factor value at the server level to 70 percent, meaning you will have 30 percent free space for future expansion. Of course, you must carefully test this option before implementing it in a production environment.

 USE Master; GO SP_Configure 'show advanced options',1; GO SP_Configure 'Fill Factor', 70; GO --You must restart SQL Server Engine for changes to take effect.

What if you want to configure the fill factor at the individual index level? Assume you are building the following table and you would like to create a unique index on the column called Col_A with a fill-factor value of 70. The command would look like this:

 --Create an Item table USE Item_DB; GO CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200)); GO;  --Create a unique index on colum Col_A of Item table with a Fill Factor value of 70 CREATE UNIQUE INDEX AK_Index ON Item (Col_A) WITH (FillFactor = 70); GO
How do you identify the fill factor for each index? You can query sys.Indexes to get the fill-factor value for all the indexes in a database, like so:

 USE Item_DB; GO SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL; GO

  

SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS ColumnName,
dbo.systypes.name AS Datatype, dbo.sysobjects.xtype
FROM
dbo. sysobjects

 

INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns. id  

INNER JOIN dbo.systypes ON dbo.syscolumns.xtype =dbo.systypes. xtype 

 WHERE  (dbo.sysobjects.xtype = ‘U’AND  (dbo.syscolumns.name LIKE‘%EmplID%’)

Query to check Sqlserver version

Posted: January 19, 2009 in Sqlserver
Select SERVERPROPERTY (‘Edition’),SERVERPROPERTY (‘EngineEdition’),SERVERPROPERTY (‘ProductLevel’)