Speeding up query processing

Question

One of our reports is taking average 25 - 30 seconds to generate during peak hours; processing about 2.3 million records. During non peak hours it is taking 6-10 seconds. How can I speed up processing during peak hours. Where do I start from; code review, wait stats; tuning advisor etc. Thanks in advance.

Answer 1

Hi @DM ，
That's a very big question....I can just answer in wide

The common casuings to the slow speed are:

1. There is no index or no index is used (this is the most common problem of slow query)
2. The I/O is small, forming a bottleneck effect.
3. The query is not optimized because the computed column is not created.
4. Insufficient memory
5. Slow network speed
6. The amount of data queried is too large (multiple queries can be used, and other methods can be used to reduce the amount of data)
7. Lock or deadlock (this is also the most common problem of slow query, which is the defect of program design)
   8, sp _ lock, sp _ who, active user view, the reason is read and write competition resources.
8. Unnecessary rows and columns returned10. The query statement is not good and is not optimized.

The solution you can take:

1. Put the data, log and index on different I/O devices to increase the reading speed
2. Divide the table vertically and horizontally to reduce the size of the table (sp _ spaceuse)
3. Upgrade the hardware
4. According to the query conditions, establish the index, optimize the index, optimize the access mode, and limit the data volume of the result set. Take care that the fill factor is appropriate (the default value of 0 is preferred). The index should be as small as possible. Use a column with a small number of bytes to build the index (refer to the creation of the index). Do not build a single index for a field with a limited number of values, such as the gender field.
5. Improve network speed
6. Where clause is used to limit the number of returned rows in the Select statement to avoid table scanning. If unnecessary data is returned, the I/O resources of the server will be wasted, the burden of the network will be increased, and the performance will be reduced. If the table is very large, the table will be locked during the table scan, and other joins will not be allowed to access the table, with serious consequences.
7. Use Profiler to track the query, get the time required by the query, find out the problem of SQL, and use index optimizer to optimize the index.
8. Use the sp _ configure 'query governor cost limit' or SET QUERY _ GOVERNOR _ COST _ LIMIT to limit the resources consumed by a query. When the resources consumed by the evaluation query exceed the limit, the server automatically cancels the query, killing it before the query. SET LOCKTIME Set lock time

My persoanl advice is to use the SQL profiler check what action have the biggest effect on SQL:

This action can also badly affect the performance, I advise you to do this action in some time.

Besides you can check this link: https://www.incworx.com/blog/sql-server-performance#:~:text=Tips%20on%20How%20to%20Improve%20SQL%20Server%20Performance,Fragmentation%20...%207%20Create%20a%20Maintenance%20Plan%20

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"