1

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.
Improve this question
7
  • 1
    Please provide a bit more details. 1) What indexes you have on this table? 2) Columns use Integer datatype but in the query, you provide characters, why? 3) Are statistics on this table updated? 4) Is there a possibility that your query is blocked by some data changes or data load? 5) What is the transaction isolation level used in this DB?
    – Marek Masko
    Commented Sep 12, 2018 at 8:19
  • ...and also is the query executed as is or using parameters? Is it an ad-hoc query or a part of a stored proc?
    – Denis Rubashkin
    Commented Sep 12, 2018 at 9:40
  • 1
    A great place to start from dba.stackexchange.com/questions/204565/…
    – Denis Rubashkin
    Commented Sep 12, 2018 at 9:53
  • @MarekMasko updated the question
    – pranjal thakur
    Commented Sep 12, 2018 at 9:55
  • 1
    If your query isn't using any index, that means it must be scanning the entire table, then it doesn't matter if your update script is updating one row or all of them, it's still going to block your query from accessing any of the rows it is updating (and your query wants all of them and yes, even with read uncommitted, you still need locks, so for example you might be vulnerable to schema change locks). Add an index to support your query and/or tune your update script to reduce blocking duration.
    – Aaron Bertrand
    Commented Sep 12, 2018 at 11:58

2 Answers 2

Reset to default
1

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

Improve this answer
0

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 )
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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