0

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?

  • @a_horse_with_no_name Sorry! SQL Server 2012 – P Corn Jan 9 '17 at 15:48
  • Potentially making it worse. Turning the table into a heap isn't likely to help performance. Which index(es) you should create depends completely on the queries that run. Without knowing that, it's all just shots in the dark. – Erik Darling Jan 9 '17 at 15:53
  • @sp_BlitzErik Thanks for your comment. 100% of the queries - selects and inserts, are done using the JobID. I just wondered whether creating a non-clustered index on JobID and ClientID would benefit as these are the most commonly filtered columns? Again, probably a shot in the dark. – P Corn Jan 9 '17 at 16:03
  • Yeah, without some more information, most guesses would be pretty bad. – Erik Darling Jan 9 '17 at 16:19
  • Broadly and generically speaking: An index on ClientID (and, possibly, status information, so that completed jobs could be excluded) would probably speed things up for common usage patterns. If you look up information by ClientID frequently, then this would almost always speed up processing. However, there is a HUGE gap between "almost always" and "always", so be aware that your performance might not be affected. You might want to search for a Missing Index query to see what is recommended but do not accept all recommendations blindly. – Laughing Vergil Jan 9 '17 at 18:16
1

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.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.