Sql-server – Aggressive index tuning

clustered-indexindexindex-tuningnonclustered-indexsql server

Background info – I have a table that stores Job information with a unique JobID (PK) and a ClientID (one client can have many jobs), as well as bunch of columns that are heaped data.

I have ran sp_BlitzIndex and it returned this:

dbo.Job.PK_Job_1 (1): Row lock waits: 399; total duration: 21 minutes;
avg duration: 3 seconds; Page lock waits: 171; total duration: 2
minutes; avg duration: 1 seconds; Lock escalation attempts: 125,805;
Actual Escalations: 0.

This table only has 35K rows in it, but it is heavily used and updated every few seconds. The current index is on the PK which is a unique value. I do not know much about index tuning, but if I remove this index and create a nonclustered index for the JobID + ClientID, am I on the right track to reducing the aggressiveness of the index? Or am I making it worse?

Best Answer

We don't have the full picture about the table or the queries that access the table or the load in the database/server but from what we know:

Background info - I have a table that stores Job information with a unique JobID (PK) and a ClientID (one client can have many jobs), as well as bunch of columns that are heaped data.

  • OK, so the table has a JobID and a PRIMARY KEY constraint (and therefore an index on it).
  • There is a ClientID which is an (explicit or implicit) foreign key to Client.
  • We don't know if there is a FOREIGN KEY constraint defined. Is a FOREIGN KEY defined?
  • We don't know the other columns of the table and their sizes. What are the columns, types and sizes?
  • We don't know if the index on (JobID) is clustered or not (the "heaped data" suggests not but we can't be sure). Is the table clustered or a heap?
  • It would better to provide the whole CREATE TABLE statement with all the indexes.

This table only has 35K rows in it, but it is heavily used and updated every few seconds.

  • OK, but "heavily" is not a number. 100 queries per second or 10K queries per second?
  • A row insert/update/delete every few seconds or all rows updated every few seconds?
  • The load on the table and database matters:
    • What queries are run?
    • Is the load OLTP or OLAP?
    • What is the version and edition of SQL Server?
    • What are the server specs (RAM, CPUs, HDD/SSD)?

The current index is on the PK which is a unique value. I do not know much about index tuning, but if I remove this index and ...?

  • No, do not go and blindly remove indexes, especially when the table has so few (i.e. just one).
  • You could add indexes on columns used in joins and WHERE conditions and expect (usually) no problems and likely improvements.

... and create a nonclustered index for the JobID + ClientID, ...?

  • The specific suggested index on (JobID, ClientID) is likely useless, considering there is already a unique index on (JobID).
  • Adding an index on (ClientID) on the other hand will likely help some queries (that have joins to Client). Since ClientID is an FK, it is also likely that you have such queries.
    Still, adding an index blindly may give you no performance gain (and even make things worse), so test, test, test, before and after the index.

.. am I on the right track to reducing the aggressiveness of the index? Or am I making it worse?

  • I'm not sure what "index aggresiveness" means but see the points above about potential actions.
  • If you really want to see significant improvements in the efficiency of queries, you'll need to do some proper analysis of the structure of the table(s) and the queries that run most often and then testing of different indexing strategies.
Related Question