Performance consideration of index ms sql
Posted on 2009-07-01
I have a query which is executed against one of our databases 100+ per minute
The queries bombarding this table look like this:
SELECT mt.myPKCol, mt.myDataCol1, mt.myDataCol2, mt.myDataCol3, ot.myDataCol1
FROM myTable mt
INNER JOIN myOtherTable ot ON (mt.myFK1Col=ot.myPKCol)
WHERE mt.myFK1Col=x AND mt.myFK2Col=y
myTable looks like this:
CREATE TABLE [myTable] (
[myPKCol] [int] IDENTITY(1,1) NOT NULL,
[myFK1Col] [int] NOT NULL,
[myFK2Col] [int] NOT NULL,
[myDataCol1] [nvarchar](255) NULL,
[myDataCol2] [datetime] NULL,
[myDataCol3] [int] NULL,
constraint [PK_myPKCol] primary key ([myPKCol])
There are 25 or so possible values for FKCol1 and hundreds of thousands of values for FKCol2.
There are currently over 4 million rows in mytable and Profiler shows each instance of this problem query with an avg CPU time of over 300ms and there are hundreds per minute. CPU runs 90% + during peak hours.
I cannot affect the query because it is coming from third party software
Here is my question:
I would like to add the following index to improve performance of above SELECT query:
CREATE INDEX [idx_myFKCol1_myFKCol2] on [myTable]([myFKCol1],[myFKCol2)
In a test environment I have added the index and the select queries perform MUCH better
What is the impact on inserts, updates, deletes?
This table is constantly inserted to, updated and ocasionally but less frequently deleted from.
20-30 very active concurrent users of third application
Although once a row is inserted its myFK1Col and myFK2Col columns are NEVER updated, only myDataCol1, myDataCol2, and myDataCol3 are updated
Will I solve the problem with the select queries by creating the index only to create a new problem with insert, update, and delete queries on table?