Sql-server – Query execution time is random, need to identify the root cause

performancequeryquery-performancesql server

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.

Best Answer

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

Related Question