Category Archives for "Expert"

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

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

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

Common Criteria Compliance and LCK_M_SCH_M

By Lori Brown | Expert , Performance Tuning , Security , SQL Administration , SQL Server

If you have ever wondered what happens when you mistakenly enable Common Criteria compliance in SQL, well wonder no more!! We have a client who has no idea how or when Common Criteria was enabled on their production system. All they know is that performance has been slowly degrading. After collecting performance data, we found […]

Continue reading

SQL 2016 Automatic Seeding of Availability Groups

By Lori Brown | Expert , SQL 2016 , SQL Administration , SQL Server , Trace Flags

I came across something today that I did not know existed in AG’s, Automatic Seeding. This allows SQL to automatically create the secondary replicas for all databases in an AG. Pretty cool!! So if you set things up correctly when you set up your AG’s, you don’t have to worry about backing up databases and […]

Continue reading

SQL 2016 – Partitioning An Existing Table With A Columnstore Index

By Lori Brown | ColumnStore , Expert , Partitions , SQL Administration , SQL Server

— by Lori Brown @SQLSupahStah I recently ran across a data warehouse that did have the largest table partitioned. We were migrating up to SQL 2016 and we wanted to make sure that the partitions were still working and in place. Since I felt I needed to refresh my table partitioning skills, I decided to […]

Continue reading

SSISDB – Package Execution Time in Minutes and Average Execution Time

By Lori Brown | Expert , SQL 2016 , SQL Development , SQL Server , SSIS Catalog

–By Lori Brown   @SQLSupahStah I have a client who has a lot of SSIS packages in the SSIS catalog that we are migrating to a new server. Of course there is lots of testing going on. So the question came up about how to more easily see package runtimes and comparing to the overall average […]

Continue reading

Large MSDB From Hidden sysxmitqueue Table

By Lori Brown | Expert , SQL Administration , SQL Maintenance , SQL Server

— by Lori Brown @SQLSupahStah Recently, I was checking on some of the servers that I manage and found that one had an 8GB msdb database. While that is not too bad it is kind of large so I figured that there must be something that needed to be cleaned up. I ran the Disk […]

Continue reading

SQL Server Statistics and Trace Flag 2371

By Lori Brown | Expert , Miscellaneous , SQL Administration , SQL Server

— by Lori Brown @SQLSupahStah Statistics are pretty important to SQL Server. Statistics are small objects that hold information of the distribution of data in a table. The SQL optimizer uses this information to create efficient ways to get data from the table. So, if statistics are not taken care of SQL may have a […]

Continue reading