Post reply

How do we determine if Index fill factor is causing query to run slow?

  • September 23, 2019 at 4:28 pm

    #3682625

    Hi,

    I'm troubleshooting a slow running stored procedure and want to know how do we determine if Index fill factor is causing query to run slow?

  • September 23, 2019 at 5:25 pm

    #3682644

    First, check to see if the index is even being used, by checking the query plan.  Next, if the query is being used, you can use Event Sessions to see if the query is doing an abnormal number of reads.

    While the query is running, check the wait_type in sys.dm_exec_sessions.  This may give you an idea on where to start looking.

  • September 24, 2019 at 10:29 am

    #3682891

    Fill factor determines the amount of free space in a page. Generally, this alone, isn't determining query performance. You may be seeing additional I/O because you have a very low fill factor and therefore more pages have to be read on disk and in memory. However, this is usually only an issue if you're performing lots of scans.

    For query tuning, there are probably 20-30 other items to look at before we start talking about changing query performance by adjusting fill factor.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply