Archive | May 2014

Fill Factor

The never ending debate — what should your fill factors be set to?  There is no one set answer, but there are some things you can do to at least get yourself a little more performance depending on how the environment is set up for you.

I came into a position and the reporting setup had a *ton* of indexes already set to a fill factor of 90.  The person who decided to go the 90 route no longer was employed and the reporting team just continued the tradition because they didn’t know any better.

A quick query to run on the databases in question — this will restrict it to clustered indexes that are not set to be full:

SELECT
sys.tables.name as TableName
, sys.indexes.name as IndexName
, fill_factor
, i.rows
FROM sys.indexes
INNER JOIN sys.tables ON sys.tables.object_id = sys.indexes.object_id
INNER JOIN sys.partitions AS i ON sys.tables.object_id = i.object_id
AND i.index_id = 1
where fill_factor between 1 and 99 and sys.indexes.type_desc = ‘clustered’
ORDER BY
sys.tables.name,
sys.indexes.name

In my case, I found a couple 20 million row tables [on development] to experiment with — just wanted to confirm that changing it would improve performance.  First, you have to find out how the procedures that are hitting that table run to before you make a change.  Using this query, I found a couple stored procedures and tested their performance:

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text] LIKE ‘%InsertNameOfTableHere%’
AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1
GROUP BY OBJECT_NAME(id)

In my case — I like to turn stats on so I can get some real values:

SET STATISTICS IO ON
GO

SET STATISTICS TIME ON
GO

exec ProcedureNameHere

SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO

Make a note of the cpu/elapsed times as well as the reads for the table in question.  In my case
SQL Server Execution Times:
CPU time = 2871 ms, elapsed time = 61580 ms.

SQL Server Execution Times:
CPU time = 3385 ms, elapsed time = 62395 ms.

Table ‘tblNamehere’. Scan count 1237, logical reads 144961, physical reads 538, read-ahead reads 63937, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

I altered the fill factor
ALTER INDEX [PK_tblNameHere_Schedule] ON [dbo].[tblNameHere] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)

After about thirty seconds [it depends upon the size of the table], it was finished.

I then run the stored procedure again and get:
SQL Server Execution Times:
CPU time = 2855 ms, elapsed time = 45136 ms.

SQL Server Execution Times:
CPU time = 3323 ms, elapsed time = 45718 ms.

Table ‘tblNameHere’. Scan count 1237, logical reads 126408, physical reads 459, read-ahead reads 63760, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

That is a pretty significant difference for a very minor change.  We are not done, though  — you do want to do some diligence with your nightly ReOrg process.  Hopefully you are using Ola Hallengren’s solution.  Make sure you turn on the LogToTable=’Y’ parameter, this way when reorg’s happen, you have a log of it.  You can turn this off after a while, but it is pretty nice to have so that you can prove out your work.

After you have changed some fill factors, wait for the nightly Reorg to happen a couple of times.  Then run this query [change date as necessary]:
select databasename, objectname, indexname, count(1)
from commandlog
where commandtype <> ‘update_statistics’
and starttime > ‘2014-05-09 20:00:01.240’
group by databasename, objectname, indexname
having count(1) > 1
order by count(1) desc

This will give you a listing of all of the indexes that were rebuilt more than once — if the ones you changed continue showing up, lower the fill factor by ~2 or so until you get to the point it isn’t having to reorganize it nightly.

You should also try to keep an eye on page splits although it can be difficult to track them down.  Jonathan Kehayias has a great posting on using Extended Events to track them here

Questions? Comments?  Post!

LINQ

One thing that has been tough for me as a DBA is the change in development styles from  using stored procedures to developers using LINQ instead.

In case you are not familiar with LINQ, essentially the programming language of choice creates the queries for you on the fly based upon your object model — which was created based upon the database schema.

There are some things that are good to look at as far as optimization.  This is where we can assist the developers in getting the best performance out of something we have very little control over!

SELECT DISTINCT
theText.[text] as QueryText
, stats.execution_count AS ExecutionCount
, stats.max_elapsed_time AS MaxElapsedTime
, ISNULL(stats.total_elapsed_time / stats.execution_count, 0) AS AvgElapsedTime
, stats.creation_time AS LogCreatedOn
, ISNULL(stats.execution_count / DATEDIFF(s, stats.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_sql_text( stats.sql_handle ) theText
WHERE execution_count > 50
AND theText.[text] like ‘%@p[_][_]linq[_][_]0%’
ORDER BY stats.max_elapsed_time DESC

If you run this query [and you have the space on your server for query plans to be saved] – you will get a listing of the cached query plans that were called by LINQ.  The most common issue I have seen is where LINQ is sending over nvarchar but the database is set to a varchar field.  Make your developers aware and they can adjust their code appropriately so that it sends the correct data typing.  Keep an eye on the AvgElapsedTimes, obviously the lower the better!  Feel free to lower the execution_count value if you want more results.  As always, if possible, run on development servers so the code can be fixed there first.

Questions? Comments?  Post below!