Solved

Performance consideration of index ms sql

Posted on 2009-07-01
6
148 Views
Last Modified: 2012-05-07
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?




     
 
0
Comment
Question by:angelz4kali
  • 3
  • 2
6 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24756863
better would be:
CREATE INDEX [idx_myFKCol1_myFKCol2] on [myTable]([myFKCol2], [myFKCol1])

because the value of myFKCol2 is more selective than myFKCol1.
0
 

Author Comment

by:angelz4kali
ID: 24756916
myFKCol2  has hundreds of thousands of possible values
myFKCol1 only has 20 to 30 possible values
does this make myFKCol2 more selective???

Will insert, update, delete performance be hurt significantly?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24757015
Yes, that makes col2 more selective.

It probably won't hurt performance TOO much because the index isn't a clustered one...also, since the columns are integers they won't take up much space.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24757053
>myFKCol2  has hundreds of thousands of possible values
>myFKCol1 only has 20 to 30 possible values
>does this make myFKCol2 more selective???

explanation:
say you have 1 Million rows.
for 1 given value of myFKCol1 (of which you have +-25 distinct values), that will potentially bring back 100000/25 = 40000 records.
for 1 given value of myFKCol2 (of which you have +-100000 distinct values), that will potentially bring back 100000/100000 = 10 records.

so, searching (and hence indexing) by myFKCol2 first will be more selective, as immediately much less records have to be considered for further conditions.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24757064
note: if your table does not yet have a clustered index, you should consider to create this one as clustered, or change one of the existing index to clustered.
the decision is usually taken by considering
§ range queries on the indexed columns (pro clustered index)
§ updates of the valued of the indexed columns (con clustered index)
0
 

Author Comment

by:angelz4kali
ID: 24804516
I went ahead and implemented the index and have a gorgeous video watching the CPU drop from avg 99% utilization during peak usage to avg 4% utilization during peak usage.

Thanks for all your help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help with a query 6 67
#deleted rowsc access 2010 backend 4 48
Need a replacement data type in Oracle 6 66
INSERT DATE FROM STRING COLUMN 18 50
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now