Tag Archives for " Microsoft SQL Server "

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

Log Connections to SQL Instance

By Ginger Daniel | Helpful Scripts , Miscellaneous , SQL Administration , SQL Server

If you ever have a need to monitor connections to your SQL server, and any related information about the connections such as database, logins, etc., there are some DMVs that can give you tons of information. Previously you might have used the sys.sysprocesses table to derive much of this information, but this is being deprecated […]

Continue reading

Get Index Column Info with Includes for One or Many Tables

By Lori Brown | Helpful Scripts , Missing Indexes , Performance Tuning , Query Tuning , SQL Administration , SQL Development , SQL Server

I was recently working with one of my clients on some low hanging fruit type of query tuning. We had checked the cache for plans with missing index warnings in them and were trying to see if we could tweak and existing index or add a new index to speed things up. If you ever […]

Continue reading

Find Tables That Have Special Features Enabled

By SQLRx Admin | Helpful Scripts , Miscellaneous , SQL Server , Tip of the Month

Find out if any of tables in your database have special features enabled using the queries below.  These features need to be understood and carefully managed. — CDC Enabled Tables select distinct t.name AS CDCTables from sys.tables t where t.is_tracked_by_cdc = 1   — File Tables — SQL 2012 + select distinct t.name AS FileTables from […]

Continue reading

Find Out Who Changed the Database Recovery Model

By Lori Brown | Helpful Scripts , SQL Administration , SQL Maintenance , SQL Server

I ran into a situation where we were working on a migration and had been directed to put all databases into FULL recovery model in anticipation of using log shipping to push databases to the new server. Once we are ready to go live on the new server the plan was to ship the last […]

Continue reading

Links for new DMV’s in SQL 2017

By SQLRx Admin | Miscellaneous , SQL 2017 , SQL Server

Since we at SQLRX have been super busy this week and still need to do something for a blog post, I thought that doing a link round up of new dynamic management views that are going to be available in SQL 2017 would be a quick and good idea. I am really interested in sys.dm_db_log_info […]

Continue reading

Log Shipping Backup Failure

By Ginger Daniel | Backup and Restore , Log Shipping , SQL Administration , SQL Server

For years the SQL Agent service account on my client’s SQL Server instance ran the Maintenance Plans and SQL Agent jobs with no issues. Many of the SQL databases were set to Full recovery model, and Tlog backups executed on regular bases with no problems. The client decided to migrate to new hardware in a […]

Continue reading

List Tables That Contain Special Indexes

By SQLRx Admin | ColumnStore , SQL Administration , SQL Server , Tip of the Month

Find out if any of your tables contain special columnstore or spatial indexes. Columnstore indexes organizes the data in columns instead of rows like traditional indexes and can increase performance on large data sets as found in data warehouses. Spatial indexes are a special type of index on a spatial column such as geometry or […]

Continue reading

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