performance tuning in sql

Question

Hi,

how to do a performance tuning in large set of sql data ... please help me with the steps and not link

how cluster and not cluster index works in terms of disk space and memory

Answer 1

Hi @Vineet S,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

We would like to inform you that, To perform performance tuning on a large set of SQL data, you can follow these steps:

1. Analyse Execution Plans: Review execution plans for slow queries to identify bottlenecks like table scans, key lookups, or expensive operations. Focus on reducing logical reads, rewiring bad joins, and optimizing query operators.
2. Optimize Indexing Strategy: Use clustered indexes on columns frequently used in range queries or sorting. Add non-clustered indexes for specific queries, focusing on columns used in WHERE, JOIN, or ORDER BY clauses. Create covering indexes by including all columns in a query to avoid lookups.
3. Partition Large Tables: Use table partitioning to divide large tables into smaller, manageable chunks based on ranges of values. Ensure queries leverage partition elimination.

We would like to inform you that, Indexes can reduce disk I/O operations and consume fewer system resources. Therefore, these indexes improve query performance. Indexes can be helpful for various queries that contain SELECT, UPDATE, DELETE, or MERGE statements.

Clustered:

• Clustered indexes sort and store the data rows in the table or view based on their key values. These key values are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
• The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered:

• Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
• The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
• You can add nonkey columns to the leaf level of the nonclustered index to bypass existing index key limits, and execute fully covered queries.

Please refer to the below mentioned link for more information.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-center-for-sql-server-database-engine-and-azure-sql-database?view=sql-server-ver16&source=recommendations

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver16

https://learn.microsoft.com/en-us/training/modules/explore-query-performance-optimization/2-understand-query-plans?source=recommendations

I hope, This response will address your query and helped you to overcome on your challenges.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.