Index Fill Factor in SQL Server

Today once again I felt to write something about Index, soopen up the listof Index articles I have written, find out the topic which are stillmissing for concept of Index and thought to write about fill factor.
Generally, when you create an Index, your data get stored inB-Tree format where you can find “Root Page”, may be “Intermediate Page” and “LEAFPage”. “Leaf Page” would have your actual data sorted in order of Index key andeach “Leaf Page” would be of 8KB.
When you insert/update/delete data to/from table, it triesto insert in the proper data page according to the record you are inserting.For example, you have Index on SSN number you are inserting new row with SSN number,SQL Server tries to insert that record in the appropriate page, suppose yourSSN starts with “2” so it will find what is the last page which has SSN numberstarts with “2”, SQL Server will insert your new row in that page only. If your8 KB page is full and don’t have room to accommodate new row whose SSN startswith “2”, it will split page and the data in that page will be shared betweentwo pages so now you have two pages which are half full so your row will be accommodatedin that page.
If your page would already had space for accommodating newrow, you wouldn’t need to wait for extra I/O overhead and wait until page splitgets finish and space for your row would become available.
This is the time when FillFactor comes into the picture.Fill Factor decides how much your page would be filled up initially. Supposeyou give 10 in FillFactor than your data page will consume only 10% of your 8KBpage size and when you exceed this limit of 10%, it keeps 90% page empty andcreate new page for other records.  Now,when you insert new records, you don’t need to worry about I/O overhead of pagesplit as you would have 90% free space and your record will be accommodate inthat space easily. So, if you have lower number of Fillfactor, you can decreaseI/O over head generated by Page Split which helps you to write your datafaster.
Now, you might be thinking now that why shouldn’t I use lownumber always like 5% or 10% in Fillfactor? Well, it will decrease your pagesplit but it will increase number of data page in your index so every time youscan your index, you have to read more pages and it is again over head whilereading the data and decrease the speed of reading data. Suppose you have 10records in one table and its size is 8KB, you can fit all 10 records in onepage only but if you have fill factor of 50 than those 10 records will bestored in two pages rather than one while creating an index.
Uff, what to do now? if we decrease fillfactor figure, ithelps in writing but creates problem in reading and if we increase fillfactorfigure may be 100% than helps in reading but creates issues while writing indatabase?
You have to be reasonable and have to take decision basedthe load on your database, you have to decide first that you have more writesor more read?
Personally I majority go for figure somewhere between 80 to90 for fillfactor so that you have some space remain for new records anddecrease page split at the same time, we don’t keep too much free space in datapages so number of pages doesn’t get increased. Even, it is not final figure I use for all my databases, if my database is read only or very rare insert/update, I would go even for 100% Fillfactor and if my database is write intensive, I would have even 50% Fillfactor.
BTW, you can define Fill Factor while creating an Indexor rebuildingan Index or you can set default value for fill factor via following T-SQL (bydefault it is 0 in SQL Server).
–turning onadvanced configuration option
Sp_configure ‘show advanced options’,1
GO
RECONFIGURE
GO
–setting upfill factor
sp_configure ‘fill factor’, 90
GO
RECONFIGURE
GO
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Index Fill Factor in SQL Server

Today once again I felt to write something about Index, soopen up the listof Index articles I have written, find out the topic which are stillmissing for concept of Index and thought to write about fill factor.
Generally, when you create an Index, your data get stored inB-Tree format where you can find “Root Page”, may be “Intermediate Page” and “LEAFPage”. “Leaf Page” would have your actual data sorted in order of Index key andeach “Leaf Page” would be of 8KB.
When you insert/update/delete data to/from table, it triesto insert in the proper data page according to the record you are inserting.For example, you have Index on SSN number you are inserting new row with SSN number,SQL Server tries to insert that record in the appropriate page, suppose yourSSN starts with “2” so it will find what is the last page which has SSN numberstarts with “2”, SQL Server will insert your new row in that page only. If your8 KB page is full and don’t have room to accommodate new row whose SSN startswith “2”, it will split page and the data in that page will be shared betweentwo pages so now you have two pages which are half full so your row will be accommodatedin that page.
If your page would already had space for accommodating newrow, you wouldn’t need to wait for extra I/O overhead and wait until page splitgets finish and space for your row would become available.
This is the time when FillFactor comes into the picture.Fill Factor decides how much your page would be filled up initially. Supposeyou give 10 in FillFactor than your data page will consume only 10% of your 8KBpage size and when you exceed this limit of 10%, it keeps 90% page empty andcreate new page for other records.  Now,when you insert new records, you don’t need to worry about I/O overhead of pagesplit as you would have 90% free space and your record will be accommodate inthat space easily. So, if you have lower number of Fillfactor, you can decreaseI/O over head generated by Page Split which helps you to write your datafaster.
Now, you might be thinking now that why shouldn’t I use lownumber always like 5% or 10% in Fillfactor? Well, it will decrease your pagesplit but it will increase number of data page in your index so every time youscan your index, you have to read more pages and it is again over head whilereading the data and decrease the speed of reading data. Suppose you have 10records in one table and its size is 8KB, you can fit all 10 records in onepage only but if you have fill factor of 50 than those 10 records will bestored in two pages rather than one while creating an index.
Uff, what to do now? if we decrease fillfactor figure, ithelps in writing but creates problem in reading and if we increase fillfactorfigure may be 100% than helps in reading but creates issues while writing indatabase?
You have to be reasonable and have to take decision basedthe load on your database, you have to decide first that you have more writesor more read?
Personally I majority go for figure somewhere between 80 to90 for fillfactor so that you have some space remain for new records anddecrease page split at the same time, we don’t keep too much free space in datapages so number of pages doesn’t get increased. Even, it is not final figure I use for all my databases, if my database is read only or very rare insert/update, I would go even for 100% Fillfactor and if my database is write intensive, I would have even 50% Fillfactor.
BTW, you can define Fill Factor while creating an Indexor rebuildingan Index or you can set default value for fill factor via following T-SQL (bydefault it is 0 in SQL Server).
–turning onadvanced configuration option
Sp_configure ‘show advanced options’,1
GO
RECONFIGURE
GO
–setting upfill factor
sp_configure ‘fill factor’, 90
GO
RECONFIGURE
GO
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

ISNULL, COALESCE or CONCAT_NULL_YIELDS_NULL in SQL Server

Before we jump into the core logic, let me explain you whatare the use of this functions / property.
BTW, this is the table to be used in example:
CREATE TABLE EMPTEST
(
FirstName varchar(10)
,MiddleName varchar(10)
,LastName varchar(10)
)
INSERT INTO EMPTEST
select ‘Ritesh’,‘A’,‘Shah’ UNION ALL
select ‘Roger’,NULL,‘Federer’ UNION ALL
select ‘Steffi’,NULL,‘Graf’
GO
Select FirstName,LastName fromEMPTEST
ISNULL: Thisfunction replaces the NULL value with specified value given in function.Suppose we have probability to have Middle Name NULL in Employee table, wecould do something like below given TSQL statement.
Select FirstName, ISNULL(Lastname,) as LastName From EMPTEST
If Lastname will be NULL, it will be replaced with blank inresult set.
COALESCE: BasicallyCOALESCE function will return first not null value from the given list, so wecan use this function in place of ISNULL too, like this:
Select FirstName, COALESCE(Lastname,) as LastName From EMPTEST
NULL is nothing but the absent of value, it doesn’t evenrepresent a blank or space or zero. When you try to concatenate two or morethan two strings and any of the string is NULL, it will return the NULL only. Seefollowing example:
 
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
You will get first record “Ritesh A Shah” as full name and remainingtwo records as NULL as those two records are having NULL value in itsMiddleName field so concatenate results will be NULL only.
You can have solution for that with ISNULL & COALESCE.
Select FirstName + ‘ ‘ + ISNULL(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
Select FirstName + ‘ ‘ + COALESCE(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
In this solution, you have to wrap up all the fields all thefields with either ISNULL or with COALESCE if it has probability of containingNULL value. If you have many fields in one query which needs this wrapping, itis bit tedious for you as a developer. At this time, CONCAT_NULL_YIELDS_NULLproperty comes as a rescue.
CONCAT_NULL_YIELDS_NULL:this property controls concatenation of the string, like what should do if anyof the string is NULL and it is being used in concatenation.
You can set this property at database level by ALTERDATABASE command or you can use this for your current session/connection.
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
As I have already told you that above query returns tworecord with NULL value as FullName, you can do something like below TSQL to setCONCAT_NULL_YIELDS_NULL  for your batch.
SET CONCAT_NULL_YIELDS_NULL OFF;
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
SET CONCAT_NULL_YIELDS_NULL ON;
By default CONCAT_NULL_YIELDS_NULL  set with TRUE (ON) for every database but youcan turn it OFF for your batch or for your database (by ALTER DATABASEcommand). It will save you from writing ISNULL and COALESCE command for manytimes in your query.
If you want to check whether your database is set to TRUE orFALSE for this property or your SESSION has this property TRUE or FALSE, youcan use following TSQL.
SELECT DATABASEPROPERTYEX(‘YourDatabaseName’, ‘IsNullConcat’)
SELECT SESSIONPROPERTY(‘CONCAT_NULL_YIELDS_NULL’)
Personally I prefer to go for either ISNULL or COALESCErather than CONCAT_NULL_YIELDS_NULL . There are few reasons for that.
–> CONCAT_NULL_YIELDS_NULL  is supported in even SQL Server 2008 R2 alongwith previous versions of SQL Server but as per Microsoft, They will remove this feature in new releases, it will be by default ONin new versions and you will not be able to set it OFF
 –> If you havethis option in Stored procedure, it will force your Stored Procedure to recompile every time you execute your SP.
Apart from these, as long as possible, we should avoid tempering with default settingsof SQL Server unless you know what exactly your doing.
 Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

ISNULL, COALESCE or CONCAT_NULL_YIELDS_NULL in SQL Server

Before we jump into the core logic, let me explain you whatare the use of this functions / property.
BTW, this is the table to be used in example:
CREATE TABLE EMPTEST
(
FirstName varchar(10)
,MiddleName varchar(10)
,LastName varchar(10)
)
INSERT INTO EMPTEST
select ‘Ritesh’,‘A’,‘Shah’ UNION ALL
select ‘Roger’,NULL,‘Federer’ UNION ALL
select ‘Steffi’,NULL,‘Graf’
GO
Select FirstName,LastName fromEMPTEST
ISNULL: Thisfunction replaces the NULL value with specified value given in function.Suppose we have probability to have Middle Name NULL in Employee table, wecould do something like below given TSQL statement.
Select FirstName, ISNULL(Lastname,) as LastName From EMPTEST
If Lastname will be NULL, it will be replaced with blank inresult set.
COALESCE: BasicallyCOALESCE function will return first not null value from the given list, so wecan use this function in place of ISNULL too, like this:
Select FirstName, COALESCE(Lastname,) as LastName From EMPTEST
NULL is nothing but the absent of value, it doesn’t evenrepresent a blank or space or zero. When you try to concatenate two or morethan two strings and any of the string is NULL, it will return the NULL only. Seefollowing example:
 
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
You will get first record “Ritesh A Shah” as full name and remainingtwo records as NULL as those two records are having NULL value in itsMiddleName field so concatenate results will be NULL only.
You can have solution for that with ISNULL & COALESCE.
Select FirstName + ‘ ‘ + ISNULL(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
Select FirstName + ‘ ‘ + COALESCE(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
In this solution, you have to wrap up all the fields all thefields with either ISNULL or with COALESCE if it has probability of containingNULL value. If you have many fields in one query which needs this wrapping, itis bit tedious for you as a developer. At this time, CONCAT_NULL_YIELDS_NULLproperty comes as a rescue.
CONCAT_NULL_YIELDS_NULL:this property controls concatenation of the string, like what should do if anyof the string is NULL and it is being used in concatenation.
You can set this property at database level by ALTERDATABASE command or you can use this for your current session/connection.
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
As I have already told you that above query returns tworecord with NULL value as FullName, you can do something like below TSQL to setCONCAT_NULL_YIELDS_NULL  for your batch.
SET CONCAT_NULL_YIELDS_NULL OFF;
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
SET CONCAT_NULL_YIELDS_NULL ON;
By default CONCAT_NULL_YIELDS_NULL  set with TRUE (ON) for every database but youcan turn it OFF for your batch or for your database (by ALTER DATABASEcommand). It will save you from writing ISNULL and COALESCE command for manytimes in your query.
If you want to check whether your database is set to TRUE orFALSE for this property or your SESSION has this property TRUE or FALSE, youcan use following TSQL.
SELECT DATABASEPROPERTYEX(‘YourDatabaseName’, ‘IsNullConcat’)
SELECT SESSIONPROPERTY(‘CONCAT_NULL_YIELDS_NULL’)
Personally I prefer to go for either ISNULL or COALESCErather than CONCAT_NULL_YIELDS_NULL . There are few reasons for that.
–> CONCAT_NULL_YIELDS_NULL  is supported in even SQL Server 2008 R2 alongwith previous versions of SQL Server but as per Microsoft, They will remove this feature in new releases, it will be by default ONin new versions and you will not be able to set it OFF
 –> If you havethis option in Stored procedure, it will force your Stored Procedure to recompile every time you execute your SP.
Apart from these, as long as possible, we should avoid tempering with default settingsof SQL Server unless you know what exactly your doing.
 Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Should Auto_Close database property be ON or OFF in SQL Server?

One of my friends just leased one dedicated SQL Serverhosting server with managed support for his own business purpose. Though it ishosted at hosting company’s data centre with very good hardware configurationand being used for his own (one) database only, it was performing very slowly atcertain point of time though there is no heavy work load.
He asked me if I can help him out in this matter so I hadquick look into it and after sometime I found Auto_Close property a culprit. 
Let me first tell you what this property is and how &where does it comes into the picture.
When we first time access or open the database in SQLServer, our database is assigned some resources to maintain its own stat, somememory for its use, some buffer space and many more. Auto_Close helps you tofree up these resources when last user disconnects from the database.
This is good thing to keep unused resource free but what ifyour last user disconnects and every resource gets free for claim and after fewminutes of freeing up resource another user came to connect to this database? ObviouslySQL Server will assign all resource to the database again and user has to waituntil all resource assigned to the database means, slow performance for thatuser first time.
If you are having only one or may be few databases in yourinstance, I highly recommend keep Auto_Close property to OFF. I have seen thathosting company keep these settings on for their shared hosting server whereyou can find hundreds of databases in one instance.
BTW, As per Microsoft, this feature will be removed from laterversion so this is one more reason not to use this setting in yourdatabase.
Here is the TSQL to check whether Auto_Close is ON or OFFfor your database.
SELECT DATABASEPROPERTYEX(‘YourDatabaseName’, ‘IsAutoCLose’);
Here is the syntax which helps you to set ON or OFF forAuto_Close property in your database.
Alter Database YourDatabaseName SetAuto_Close ON
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance, Line 36 Could not create a capture instance because the capture instance name ‘dbo_ChangeDataCapture’ already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

After reading my previous article on CDC(Change Data Capture), one of the reader had tried to do it in hisdevelopment server but in his server, CDC was already enabled and anotherco-incident was that, the table he has choose for CDC, was already having itscapture instance, may be any of the other team member might have done it and hedoesn’t knew that , so he was greeted with the error like given below.
Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance,Line 36
Could not create a capture instance because the capture instance name’dbo_ChangeDataCapture’ already exists in the current database. Specify anexplicit unique name for the parameter @capture_instance.
Error message is pretty much clear that the table he was usingwas already having capture instance so obviously he should use another tablefor this testing purpose or remove CDCfrom that table and re-create it or while activating CDCfor that table, he has to provide another name for capture instance table by providing@Capture_Instance name explicitly (not recommended). I will provide TSQL neededfrom disable CDClater in this article.
Well, these are some of the solution when you face abovegiven error message but as said by someone that “Prevention is better than cure”, that reader asked me how do I knoweven before activating CDCthat the table is CDC enabled or not. 
It is not a big issue; you can use very small TSQL querieslike below when you want to know it.
–list out thename of databases which are CDC enabled
SELECT [name], database_id
FROM master.sys.databases WHERE  is_cdc_enabled =1    
GO
–list out alltables which are CDC enabled in your database
SELECT [name] AS Table_name
FROM sys.tables  WHERE is_tracked_by_cdc =1
GO
–know indetails like which table is CDC enabled
–which is thecapture instance of that table
EXEC sys.sp_cdc_help_change_data_capture
GO
–disable CDCfrom your table.
EXECUTE sys.sp_cdc_disable_table
    @source_name = N’ChangeDataCapture’,
    @source_schema =N’dbo’,
    @capture_instance =N’dbo_ChangeDataCapture’;
   
–disable CDCfrom your database
EXEC sys.sp_cdc_disable_db
GO
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance, Line 36 Could not create a capture instance because the capture instance name ‘dbo_ChangeDataCapture’ already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

After reading my previous article on CDC(Change Data Capture), one of the reader had tried to do it in hisdevelopment server but in his server, CDC was already enabled and anotherco-incident was that, the table he has choose for CDC, was already having itscapture instance, may be any of the other team member might have done it and hedoesn’t knew that , so he was greeted with the error like given below.
Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance,Line 36
Could not create a capture instance because the capture instance name’dbo_ChangeDataCapture’ already exists in the current database. Specify anexplicit unique name for the parameter @capture_instance.
Error message is pretty much clear that the table he was usingwas already having capture instance so obviously he should use another tablefor this testing purpose or remove CDCfrom that table and re-create it or while activating CDCfor that table, he has to provide another name for capture instance table by providing@Capture_Instance name explicitly (not recommended). I will provide TSQL neededfrom disable CDClater in this article.
Well, these are some of the solution when you face abovegiven error message but as said by someone that “Prevention is better than cure”, that reader asked me how do I knoweven before activating CDCthat the table is CDC enabled or not. 
It is not a big issue; you can use very small TSQL querieslike below when you want to know it.
–list out thename of databases which are CDC enabled
SELECT [name], database_id
FROM master.sys.databases WHERE  is_cdc_enabled =1    
GO
–list out alltables which are CDC enabled in your database
SELECT [name] AS Table_name
FROM sys.tables  WHERE is_tracked_by_cdc =1
GO
–know indetails like which table is CDC enabled
–which is thecapture instance of that table
EXEC sys.sp_cdc_help_change_data_capture
GO
–disable CDCfrom your table.
EXECUTE sys.sp_cdc_disable_table
    @source_name = N’ChangeDataCapture’,
    @source_schema =N’dbo’,
    @capture_instance =N’dbo_ChangeDataCapture’;
   
–disable CDCfrom your database
EXEC sys.sp_cdc_disable_db
GO
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile