Azure SQL Database large tables performance Issue

Arundhati Sen 151 Reputation points
2024-03-19T11:58:45.5833333+00:00

In my Azure SQL Database, there exists a considerably large table, let's call it 'tableA'. In my application, I have several distinct queries (approximately 10) that involve different sets of columns from this table. For example, 'query1' utilizes columns 'col1', 'col2', and 'col3', while 'query2' uses columns 'col2', 'col3', and 'col4', then , while 'query3' uses columns 'col7', 'col8', and 'col9', and so forth.

And there are almost 50 such tables where same issue in present as 'tableA'.

Without applying indexing to these individual queries, I observe a significant performance drop in terms of output. However, on the flip side, when I create separate indices for each of these SELECT queries, the insertion speed in my table drastically declines and is noticeably poor.

Having said that, I am struggling to strike an optimum balance between read and write performance. Could anyone advise the best industry practices to manage such a scenario? What could potentially be the ideal indexing strategy to boost the overall efficiency in this case without excessively compromising on the insertion speed in the table? Any guidance to maneuver through this situation would be greatly appreciated.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,825 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Amira Bedhiafi 24,636 Reputation points
    2024-03-19T16:15:37.31+00:00

    I think that your situation is compounded by the fact that you have multiple distinct queries accessing different column sets across a large number of tables.

    So instead of creating separate indexes for each query, consider creating indexes that cover multiple queries. This means identifying columns that are frequently accessed together and indexing those. For instance, if col2 and col3 are commonly queried together, a single index on both could serve multiple queries.

    For queries that target a specific subset of data, filtered indexes can be extremely efficient. These indexes are smaller and less expensive to maintain because they only include rows that meet certain criteria. If your queries have where clauses that filter rows on the same or similar conditions, filtered indexes might be a good choice.

    If your workload involves a lot of aggregate functions or scanning large numbers of rows, columnstore indexes can significantly improve performance. These indexes are designed to be highly efficient for read-heavy operations, particularly for analytics workloads. However, they might not be suitable for all scenarios, especially where transactional updates and deletes are frequent.

    Having many indexes can lead to increased overhead during data modification operations. Implement a robust index maintenance strategy that includes regular reviews of index usage and fragmentation. Dropping unused or less frequently used indexes and reorganizing or rebuilding fragmented indexes can help in maintaining optimal performance.

    For tables with very high transaction rates, consider using In-Memory OLTP features. Tables can be moved into memory-optimized tables, and you can also use natively compiled stored procedures for accessing these tables. This can significantly reduce latency for both reads and writes, although there are some limitations and considerations for using in-memory OLTP.

    Table partitioning can help manage large tables by splitting them into smaller, more manageable pieces, while still treating them as a single logical entity. This can improve performance for certain types of queries and also make maintenance tasks like indexing more efficient.


  2. hossein jalilian 7,205 Reputation points
    2024-03-19T22:36:31.16+00:00

    Thanks for posting your question in the Microsoft Q&A forum.

    I have several recommendations regarding this matter:

    1. Partitioning: Divide large tables into smaller, manageable partitions based on specific data ranges to improve query performance and reduce data scanning.
    2. Stored Procedures (SP): Use stored procedures to pre-compile and cache execution plans, enhancing query performance, code reusability, and security.
    3. Smart Indexing: Implement targeted indexing strategies tailored to query patterns and frequently accessed columns to optimize query execution.
    4. Caching: Introduce caching mechanisms, such as a helper service or in-memory cache, to reduce database accesses and minimize latency, enhancing overall system performance.
    5. Command Query Responsibility Segregation (CQRS): Separate command and query operations into distinct databases or data models to optimize each operation independently, promoting scalability, performance, and flexibility.

    Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

    0 comments No comments

  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

  4. Erland Sommarskog 111.6K Reputation points MVP
    2024-03-27T22:45:29.02+00:00

    What is the pattern of INSERT and SELECT? Are you inserting constantly, or are you running nightly loads? In the latter case, you could disable the indexes before the load, and rebuild them after the load.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.