I have a query like below
select * from table1 where column2=123 and column3='B'
Now the issue faced by us is that this query is executing very slowing sometimes as seen in our performance tracking tool. Also from the knowledge of our application, we know that it gets executed 100s of time in a day, but we only see it being reported as slow 2-3 times a day.
When I run it in SQL Server Management Studio, it gets executed in almost 200ms consistently.
What I need to know is, what is the cause of such random slowness and what could be few strategies to identify/fix the root cause?
Also, note the table has only 3 columns.
- Column1 – Primary Key, (Integer)
- Column2 – Foreign Key, (Integer)
- Column3 – Value, varchar(10)
Other important information as asked in comments.
Indexes on table – No index
Are statistics on this table updated? – No, how to do that?
- Is there a possibility that query is blocked by
some data changes or data load? – There are chances that an edit
script might be running at the same time this query is running, but
the update script would not be updating the same row.
- What is the transaction isolation level used in this DB? – Read Uncommitted.
In general, A primary key constraint creates a clustered index in the table. It’s important has at least one index in the table to avoid fullscans.
As you said the table doesn’t have any kind of index, nevertheless you’re searching in the table with a predicate which has two columns. In my experience, I’ll create a non-clustered index to support this query. There’s another essential thing, you should include all columns that you’re using in the statement select. Include columns are not key columns in the index, soo be careful.
CREATE INDEX idx ON MyTable (column2 ,column3) INCLUDE (Name, Address)
I’ve already read a lot of pages of this book and I recommend it for you.
Expert Performance Indexing in SQL Server
This another one here is good too.
SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query
The first thing you should do is to create a new index. This index will be used for this particular query and help to support foreign key on the table.
create index IDX_table1_column2_column3 on table1 ( column2, column3 )