question

kumar-9444 avatar image
0 Votes"
kumar-9444 asked Criszhan-msft commented

SQL Server query is running slow

In SQL server 2017, Application team is executing "select" query with datetime in where clause. The query contains multiple nested views. one day query is running normal and on another day its taking more time to complete. The fragmentation is normal and statistics also upto date. What could be reason for this?

sql-server-general
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

one day query is running normal and on another day its taking more time to complete.

May I know the ''day'' means the value of datetime column in where clause, or execute the same query at different times?

0 Votes 0 ·

Select query to fetch data from Nested views with where clause contains "Target Name". Application team is changing TargetName and Datetime parameter only.

Sample query:
Select column1,column2,DateTime from v_m_m_mns_name where TargetName= "XXXXXXXXX" DateTime between 'YYYY-MM-DD 00:00:00' and 'YYYY-MM-DD 23:59:59' GROUP BY DateTime


0 Votes 0 ·

Different targets could have different amounts of data to process.

Are the date intervals always a single day, or could they vary in length.

I can only refer to the answer I posted a few days back. You have not shared any information we can work from.

0 Votes 0 ·

Hi, Fully agree with Erland. Do youcheckl and follow his suggestions and whether there is any update currently.

0 Votes 0 ·

1 Answer

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

There can be many reasons. Here are three that comes to mind directly:

  1. Blocking.

  2. Some days there is more data to process than other days.

  3. Parameter sniffing. That is, the plan is built for a different value that is used right now.

I would recommend using Query Store to track this query, and possibly use Query Store to force a good plan.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.