Keep having to reindex sql tables

Question

I have a database on SQL Server 2012 and am having some problems with a few tables that become slow after a while and the thing that helps is rebuilding the indexes. I was wondering if anyone has advice on what could be wrong in any of them, I will post their structure and indexes below. I have not built this structure myself but have full access to modify.

Table1

• ID (int, not null)
• Type (tinyint, not null)
• Name (PK, nvarchar(255), not null)
• fkID (PK, int, not null)
• UID (int, not null)

Indexes:

• I_UID (Unique, Non-Clustered) [UID]
• I_Name (Non-Unique, Non-Clustered) [Type,Name]
• pk_Name (Clustered) [Name, fkID]

Table2

• ID (PK, bigint, not null)
• Name (nvarchar(50), not null)
• ShortValue (nvarchar(250), null)
• StringValue (nvarchar(max), null)
• IntValue (int, null)
• FloatValue (float, null)
• DateTimeValue (datetime, null)
• BoolValue (bit, null)
• fkPID (FK, int, null)
• fkAID (FK, int, null)
• fkAGID (FK, int, null)
• fkVID (FK, int, null)
• fkCID (FK, int, null)
• fkL (FK, int, not null)
• fkIMID (FK, not null)
• fkPRID (FK, int, null)
• fkNID (int, null)

Indexes:

• I_AG (Non-Unique, Non-Clustered) [fkAGID]
• I_IM (Non-Unique, Non-Clustered) [fkIMID]
• I_R (Non-Unique, Non-Clustered) [fkPRID]
• PK_D (Clustered) 5447370
• I_PDL (Non-Unique, Non-Clustered) [fkL]

Table3

• ID (PK, int, not null)
• fkPID (FK, int, not null)
• fkAID (FK, int, not null)
• Sort (int, not null)
• Group (nvarchar(50), null)
• Size (int, null)
• FMB (nvarchar(50), null)

Indexes:

• PK_D (Clustered) 5447370
• I_PAA (Non-Unique, Non-Clustered) [fkAID]
• I_PAP (Non-Unique, Non-Clustered) [fkPID]
• I_PAPID (Non-Unique, Non-Clustered) [fkPID,fkAID]

Answer 1

A great tool that will analyze and, if necessary, rebuild indexes and update statistics is Ola Hallengren's Index and Statistics Maintenance tool/script. We run this thing nightly, along with the Integrity Check, to keep our databases healthy.

http://ola.hallengren.com/

Answer 2

One column that sticks out to me is this one:

pk_Name (Clustered) [Name, fkID]

Clustered keys determine the physical order of records in the database table. If Name is a string and values are inserted in "random" order (i.e. not always at the end of the table alphabetically) there could be performance problems as the database is always having to "insert" rows into the physical tables. This could cause the table data to become fragmented, which could reduce performance as well.

Re-building a clustered index also re-organizes the physical data which is likely why you're seeing improved performance afterwards. Recomputing statistics could also be a factor, but a primary key that results in non-consecutive inserts is usually a red flag.

Also your definition doesn't specify the columns that make up the clustered indices on tables 2 and 3, but based on the name I'm assuming they're indexed by ID.

Answer 3

I came across a similar problem. When you add data to your table the statistics gets updated only if u pass a specific change threshold as described here:

http://blog.sqlauthority.com/2010/04/21/sql-server-when-are-statistics-updated-what-triggers-statistics-to-update/

I have not found any good way to overcome this problem except for major changes to the way we work with our DB. In the meanwhile you can run

UPDATE STATISTICS <Table Name>

or

alter INDEX <Index Name> ON <Table Name> rebuild

Every night