Understanding SQL Server Query Optimization – Part 3

A critical part of database design and management is index design. Index design involves balancing space requirements and the resource overhead required to keep indexes up-to-date compared to the performance improvements resulting from the indexes. You can either use dynamic management functions (DMFs) and views (DMVs) or Database Engine Tuning Advisor to identify index needs based representative server activity.

Click here to read full article on SQL-Server-Performance.com

Advertisements

Understanding SQL Server Query Optimization – Part 2

An execution plan is the sequence of operations SQL Server query optimizer performs to run the statements. The SQL Server query optimizer creates an execution plan before it actually runs a query. As per Microsoft Book Online, “Execution plans graphically display the data retrieval methods chosen by the SQL Server query optimizer. Execution plans represent the execution costs for specific statements and queries in SQL Server using icons rather than the tabular representation produced by the SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements”. This graphical approach is very useful that for understanding the performance characteristics of a query, and help you to determine whether the query is written as efficiently as possible. You can use the execution plan information to quickly troubleshoot poorly performing queries.

Click here to read full article on SQL-Server-Performance.com

Understanding SQL Query Optimization – (Part 1)

Most of the activity of the database server is related to queries. Because of this, query optimization is often a major concern. SQL Server tries to optimize query performance itself, but its effectiveness in this may be influenced by several problems. Indexes and poorly designed queries can lead to unacceptable performance, no matter how well you design your database. Key issues related to query optimization include designing indexes, statistics, and index fragmentation. Statistics describe index key values, are maintained for index columns, and are used by SQL Server to decide on the most appropriate index to use when running queries.

Checkout the first of this four part article series here, which gives you an overview of the tools and techniques for SQL Server Query Optimization.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Guide to SQL Server Table Indexes – (Part 4)

Checkout the part-4 of my five part article series on Guide to SQL Server Table Indexes here, in which you will learn about index design, and the suitable candidates for index key and non-key columns. I this article, you will also learn about various SQL Server indexes options, such as pad-index and fill-factor.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Guide to SQL Server Table Indexes (Part 3)

Checkout the part-3 of my five part article series on Guide to SQL Server Table Indexes here, in which you will learn about remaining index types available in SQL Server, and what are these indexes advantages and dis-advantages.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Guide to SQL Server Table Indexes (Part 2)

Checkout the part-2 of my five part article series on Guide to SQL Server Table Indexes here, in which you will learn about the different types of indexes available in SQL Server, and what are some of these indexes advantages and dis-advantages.

This article is published on SQL-SERVER-PERFORMANCE.COM.

Guide to SQL Server Table Indexes (Part 1)

One of the most important tasks for every database administrator (DBA) is to ensure that query times are consistent with service-level agreement (SLAs) or within user expectations. One of the most effective techniques to achieve this objective is to create indexes on tables for your queries. SQL Server uses indexes to sort and organize table data. It creates indexes based on ordering values from one or more selected columns. SQL Server automatically creates indexes when you define a primary key or unique constraint. You can use indexes to manage and track rows in a table or an indexed view.

Indexes improve the performance on most data scans by reducing the overall time query takes to run and the amount of work and resources it consumes. The amount of performance improvement depends on the size of the tables involved, the index design, and the type of query. You can see the role of indexes by observing what happens during queries and data manipulation.

Checkout the part-1 of my five part article series on Guide to SQL Server Table Indexes here, in which you’ll learn about the basics of SQL Server database table’s indexes, the difference between the clustered and non-clustered indexes, and how the leaf nodes, non-leaf nodes, and heaps associated with data storage.

This article is published on SQL-SERVER-PERFORMANCE.COM.