Tag Archives for " Jeff Schwartz "

Nov 02

Rx for Demystifying Index Tuning Decisions – Part 2

By Jeffry Schwartz | Expert , Missing Indexes , Performance Tuning , Query Tuning , SQL Development , SQL Server

Review In Part 1 of this blog series the author discussed various aspects of SQL Server indices such as: Types of indices and their variations, e.g., clustered, nonclustered, filtered, and covering Physical versus logical index access methods, e.g., seeks, scans, key/RID lookups, singleton lookups, and range scans Why index tuning should begin with queries Determining […]

Continue reading
Oct 26

Rx for Demystifying Index Tuning Decisions – Part 1

By Jeffry Schwartz | Expert , Missing Indexes , Performance Tuning , Query Tuning , SQL Administration , SQL Development , SQL Server

Overview Indices are one of the most troubling and mysterious areas for DBAs and developers. Most understand that adding a badly needed index can yield dramatic query performance improvements, but how does one know what is needed? Many believe more indices are better, regardless of the number. Most fear deleting ANY existing indices, even when […]

Continue reading
Aug 10

How Indexing Affects Deletion Queries

By Jeffry Schwartz | Performance Tuning , SQL Development , SQL Server

The Problem Many articles concerning SQL Server discuss how record insertion overhead increases with each additional index. They discuss b-tree manipulations and page splits in addition to leaf and non-leaf levels. However, few discuss the fact that deletion overhead increases as well, especially when large numbers of records are deleted by individual queries. Recently, I […]

Continue reading
Jul 20

Handling Multiple Missing Index Recommendations for the Same Table

By Jeffry Schwartz | Expert , Missing Indexes , Performance Tuning , Query Tuning , SQL Development , SQL Server

The Problem Many articles concerning SQL Server missing index recommendations demonstrate the mechanics for obtaining them and often highlight whether the suggested key columns are used in equality or inequality relationships. Most of these examples emphasize a single index that can be implemented to improve performance. However, real-life situations often involve multiple or many suggested […]

Continue reading
Jun 02

Query Tuning and Missing Index Recommendations

By Jeffry Schwartz | Missing Indexes , Performance Tuning , Query Tuning , SQL Development , SQL Server

Overview Most analysts are familiar with missing index recommendations provided by SQL Server when query plans are displayed within SSMS or reported by various missing index DMVs. Several questions arise concerning these recommendations: What determines whether a column becomes a key or included in the new index? Where does the ordering of the included columns […]

Continue reading
Sep 01

Table-Valued Parameter Performance Using C# Data Tables

By Jeffry Schwartz | Expert , Miscellaneous , Performance Tuning , SQL Development , SQL Server

The following testing and resulting data is a direct result of a recent issue that was discovered at one of our client sites.  After setting up SQL monitoring that does use a SQL trace to capture long running queries, suddenly certain processes would lock up and be unable to complete.  Initially we thought it was […]

Continue reading
>