Friday, October 29, 2010

Which Fillfactor should I use to improve performance?

The performance of your SQL Server database will depend on multiple factors. One of these factors is index fragmentation. Fragmentation of indexes will slow down performance because more page should be read. This blog post will talk about how some index fragmentation can be avoided by using the fill factor option in the index definition. By default all index information is stored in pages. During the creation of the index the storage engine will make the index leaf level pages 100% full. In other words leaving no space. This means that a random insert, or a record that increases in size, will result in a page splitt. Now fragmentation of your index is started. Random inserts will occur if the first column of an index will have random values for instance an GUID. The more page splitts occur on your index, the more fragmentation you have on this index. Page splitts can be avoided if there is enough space in the page to insert the new index records. By using the fill factor option in the configuration of your index, the storage engine will leave some free space in the leaf level pages during index build or rebuild operations. A fill factor of 90 means that 90 % of the index pages will be used and 10% is free index space.

The fill factor to use depends on the percentage of random inserts, or records that increase in size in the periode between 2 index defragmentations periods. For instance if you defrag your indexes every week, you need to know the percentage of index insert during a week.

First of all I will use a script to show the index fragmentation. I will create a table and a stored procedure to insert records and I will create 4 indexes with a GUID as the first column to generate random inserts.

-- Create a test table
   (Division INT,
    HID INT Identity(1,1) NOT NULL,
    TestGuid [uniqueidentifier] ROWGUIDCOL NOT NULL,
    TestChar CHAR(760) NULL)
CREATE CLUSTERED INDEX PK_FillfactorTest ON FillfactorTest(Division, HID)

Now we have a test table: FillFactorTest which need to get data. I will use a stored procedure for this. This stored procedure can be used to add data in batches.

CREATE PROC [dbo].[FillFactorinsert](@Division INT, @NumberInserts INT) AS
  SET @Division = 100
  SET @TestINT = 2
  INSERT INTO FillFactorTest (Division,TestINT,TestGuid,TestChar)
    Values (@Division,@TestINT,NEWID(),'Pipo')
  WHILE @COUNTER < @NumberInserts
     INSERT INTO FillFactorTest (Division,TestINT,TestGuid,TestChar)
        Values (@Division,@TestINT,NEWID(),'Pipo')
  SET @TestINT = @TestINT +1

-- Now it is time to insert records
-- Insert first set of 10000 records for Division 100
EXEC FillFactorinsert 100,10000
-- Insert first set of 2500 records for Division 200
EXEC FillFactorinsert 200,2500
-- Insert first set of 7000 records for Division 300
EXEC FillFactorinsert 300,7500

-- Now we have added 20.000 records to the table FillFactorTest

-- Now it is time to add indexes. We will add 4 indexes on which only the fillfactor is different.
-- Create 4 times the same index on only guid only fillfactor is different
CREATE INDEX TestEntryGUID ON FillfactorTest(Testguid)
CREATE INDEX TestEntryGUIDFILL98 ON FillfactorTest(Testguid) WITH (FillFactor = 98)
CREATE INDEX TestEntryGUIDFILL95 ON FillfactorTest(Testguid) WITH (FillFactor = 95)
CREATE INDEX TestEntryGUIDFILL90 ON FillfactorTest(Testguid) WITH (FillFactor = 90)

-- Use next query to retrieve the fragmentation of these indexes.
-- Defragmentation percentage of indexes in a table
SELECT index_name AS IndexName,MAX(Fill_factor) AS Fill_factor
     ,SUM(avg_fragmentation_in_percent) AS Fragmentation_Percent
      SI.[name] AS index_name
      , SDDIPS.[avg_fragmentation_in_percent]
      , NULL as Fill_factor
    FROM sys.[dm_db_index_physical_stats](DB_ID(),OBJECT_ID('FillfactorTest') , NULL, NULL, 'Detailed') SDDIPS
    INNER JOIN sys.[indexes] SI
      ON SDDIPS.[object_id] = SI.[object_id] AND SDDIPS.[index_id] = SI.[index_id]
    WHERE SDDIPS.[index_id] > 1
    SELECT COALESCE(, 'N/A') as index_name
      ,NULL AS avg_fragmentation_in_percent,i.fill_factor AS Fill_Factor
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius
       ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = db_id()
   WHERE t.object_id = OBJECT_ID('FillfactorTest') AND i.type = 2
) XX
GROUP BY index_name

You wil see that all indexes do not have any fragmentation.
Now it is time to insert additional records. Let's see when fragmentation occurs. We will do it in steps of 1 %.

Step 1:
-- Insert 1% new records.
EXEC FillFactorinsert 100,100
EXEC FillFactorinsert 200,25
EXEC FillFactorinsert 300,75
Step 2:
Re run the defragmentation script. You will see that the index with the default fill factor is full fragmented. (More than 95%)

Step3: repeat Step 1 and Step 2 for 4 times.
During these steps you will see when fragmentation start occuring on the different indexes. The results of these inserts are reflected in next graph.

Conclusion: Fragmentation (> 20%) will occur if 60% of the free space created by the fillfactor is used. This means that if you insert 5% new data you need a fill factor of at least (5/60)*100 = 8,3 Rounded to 10

Attention point: Using a fill factor will improve performance because it will avoid fragmentation. However you need to configure a defragmentation job to avoid fragmentation in the future. This means that if you insert 5 % new records per month, the defragmentation job should be scheduled once per month.

Thursday, October 21, 2010

Exact System Information improvement report extended with filtered index suggestions.

As of today the Exact System Information improvement report is extended with the possibility to implement filtered indexes for Globe databases on a server with SQL Server 2008 or SQL 2008 R2. See next screen shot from an improvement report.

As of SQL Server 2008 filtered indexes are introduced. A filtered index allows us to create an index with a filter on a subset of rows within a table. A filtered index will:
  1. Improve query performance. Statistics are more accurate which can result in better query plans.
  2. Reduce index maintenance costs. An index is only maintained when the data in the index is changed.
  3. Reduce index storage costs.
Filtered indexes can be very useful on columns which contains mostly NULL values and where the queries retreive only the rows where the data is NOT NULL. In Exact Globe we have a lot of indexes on columns which contains mostly NULL values. See Microsoft website link for more information about filtered indexes.

After implementing filtered indexes on some customer databases, we have seen reduction of the index size between 25% to 30%. This will results in an overall database size reduction of around 10 %. The actual reduction of the index size in your database depends on the number of NULL values in your database. Please DO NOT shrink your database after implementing the filtered indexes. Shrinking databases and explanding database can result in defragmentation of the database files on NTFS level. SQL Server will first use the free space in the database before it will growth.

Performance tests indicated no noticeable performance increase or decrease with read actions, however write performance is gained due to the fact less index information needs to be written. After implementing filtered indexes, the fragmentation of your indexes will be lower. This is good for the overall performance of your Exact solution.

To begin you need to start the Exact System Information tool and request an improvement report. The user who start the Exact System Information tool should have a SQL System Administrator role (SA). If you can implement filtered indexes, it will be mentioned in the improvement report. In the improvement report you will find a link to the script to implement filtered indexes.

We strongly advise you to run the Exact System Information on a regular basis. For instance once per 3 months. We are adding on a regular basis new suggestions to the improvement report to improve your Exact solution. If you have any feedback, please let us know. You can comment on this blog post or send an email to

This blog is also published on the Exact Product Blog.

Wednesday, October 6, 2010

Combine SQL Profiler with Performance monitor logs

To analyze the performance of your applications which is running on SQL Server you need to make use of 2 standard tools:
  1. SQL Profiler
  2. Windows Performance Monitor. (Perfmon)
Both tools are useful to understand what happens at which moment. In SQL Profiler you load a profile trace however you can also load the performance monitor log file in the same loaded SQL Profiler trace file. See next example:

What do you need to do:
  1. Make a SQL Profile trace of your application.
  2. Make a performance log file with Performance monitor (Perfmon) of your application via a data collector set. Save the result to a file.
  3. Execute some load in your application.
  4. Stop the SQL profiler trace file and store it as a trace file.
  5. Load the trace file in SQL Profiler.
  6. In the menu of SQL Profiler Select File, Import Performance Data and select your performance log file.
  7. Select the counters you want to see in the SQL profiler.
Now you have both trace files combined in one application. When you scroll through the profile entries you will see the red vertical bar moving. A perfect way to analyze the performance of your application.

Enjoy using the performance monitor with SQL Profiler.

Monday, October 4, 2010

My filtered index is not used: The impact of forced parameterization.

As of SQL Server 2008 filtered indexes can be used. This can save you a lot of index space and will improve performance during index updates and will result in less index defragmentation. In case your database is configured with forced parameterization, it can happen that your filtered index is not used. For instance you have an filtered index like:
CREATE INDEX ZipcodeFiltered on Zipcodes(Zipcode) WHERE Zipcode = '3000'

Next query is executed
SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = '3000'
Looking to the query plan the ZipcodeFiltered index is not used. This happens if your database is configured for Forced Parameterization.

To explain this behaviour I will create a repro sample:

USE [FilterTest]
    (ID INT,
     Zipcode NVARCHAR(20),
     City NVARCHAR (100),
     Extra NVARCHAR (100))


INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3000','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3001','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3002','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3003','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3004','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3005','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3006','Rotterdam',NULL)
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3000','Rotterdam','TestFilter')
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3001','Rotterdam','TestFilter')
INSERT INTO Zipcodes (ID,Zipcode,City,Extra) VALUES (1,'3002','Rotterdam','TestFilter')

-- Create regular indexes and filtered version
CREATE CLUSTERED INDEX Clus_zipcodes ON Zipcodes(ID)
CREATE INDEX Zipcode ON Zipcodes(Zipcode)
-- Create same index but now with filter
CREATE INDEX ZipcodeFiltered ON Zipcodes(Zipcode)
                WHERE Zipcode = '3000'
CREATE INDEX Extra ON Zipcodes(Extra)
-- Create same index but now with filter
CREATE INDEX ExtraFiltered ON Zipcodes(Extra)
                WHERE EXTRA IS NOT NULL

-- Database Not using forced parameterization

SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = '3000'
-- Look in the queryplan: Index ZipcodeFiltered is used

SELECT Extra,ID FROM Zipcodes WHERE EXTRA = 'TestFilter'
-- Look in the queryplan: Index ExtraFiltered is used


SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = '3000'
-- Look in the queryplan: Index Zipcode is used. So not the ZipcodeFiltered

SELECT Extra,ID FROM Zipcodes WHERE EXTRA = 'TestFilter'
-- Look in the queryplan: Index ExtraFiltered is still used. Even with Forced Parameterization


Why is the filtered index not used with forced parameterization?

With forced parameterization the SQL Server engine will auto parameterize your query. This means that next query:
SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = '3000'
will be stored in the procedure cache as:
SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = @0

The query plan for this query: SELECT ID,Zipcode FROM Zipcodes WHERE Zipcode = @0
will be using the 'regular' index without the filter because for the value @0 the query optimizer does not know if it is '3000' or another value.
As you have seen in the example this behaviour does not apply for filtered indexes with an WHERE COLUMN_X IS NOT NULL.

You can use filtered indexes with Forced Parameterization if your filtered indexes are using a WHERE Clause like COLUMN_X IS NOT NULL.

Special thanks to my colleague Ad van der Hoeven for bringing this to my attention.

Enjoy the use of filtered indexes

Sunday, October 3, 2010

SQL Profiler is on my second screen, but my second screen is not connected.

I use a lot of time multiple screens on my laptop. With SQL Profiler this can give some challenges. I will explain in which situation.
  • Connect an extra monitor to your laptop.
  • Extend this screen as the second screen.
  • Start SQL Profiler and move it to the second screen.
  • Close SQL profiler on the second screen.
  • Disconnect the second screen.
  • Start SQL Profiler.
In the taskbar you can see that SQL profiler is active however you do not see SQL Profiler. It is still open on your not connected screen. This is a bug in SQL Profiler which does not reset the window position. What should you do to make use of the SQL Profiler if you do not have a second screen available.

  • Click on the SQL Profiler window in the taskbar.
  • Press ALT ENTER to get the propertie window of the SQL Profiler.
  • Select MOVE.
  • Use you arrow keys to move the invisible window back to your screen.
Enjoy the use of SQL Profiler.