All Articles - SQL Authority with Pinal Dave

SQL SERVER – Heaps, Scans and RID Lookup

January 25, 2020

Clustered Index, Execution Plan, SQL Heap, SQL Index, SQL Scripts, SQL Server

The question was do heaps are very bad and always force a table scan? Let us learn about RID Lookup, Heaps and table scan.

A rack of lake water samples at dawn, one cloudy tube marked with a red clip.

Capturing sp_WhoIsActive Snapshots to a Table Every Minute

January 24, 2020

Pinal Dave

SQL Tips and Tricks

DBA, SQL Monitoring, SQL Server, SQL Server Agent

Capture sp_WhoIsActive snapshots every minute, keep seven days, and query past blockers and long requests from a logging table.

Title card: WAIT_ON_SYNC_STATISTICS_REFRESH

SQL SERVER – Resolve WAIT_ON_SYNC_STATISTICS_REFRESH Wait Type

January 23, 2020

Pinal Dave

SQL Performance

SQL Server Management Studio, SQL Statistics, SQL Wait Stats

During Comprehensive Database Performance Health Check I recently faced a brand new wait type WAIT_ON_SYNC_STATISTICS_REFRESH.

Gouache painting of a chest of drawers pulled partly open, each drawer holding a sage ball of yarn and one vermilion ball

Find All Columns With a Specific Name in SQL Server

January 22, 2020

Pinal Dave

SQL Tips and Tricks

SQL Column, SQL Datatype, SQL Scripts, SQL Server

To find all columns with a specific name, query sys.columns and join sys.types for the data type. This post lists the matches with their schema, leaves out system objects, and then uses the same catalog to find names that appear with different data types.

Title card: Provisioning Computer Object Failed With Error 5

SQL SERVER – Always On Listener Failure – Provisioning Computer Object Failed With Error 5

January 21, 2020

Pinal Dave

SQL Tips and Tricks

AlwaysOn, Computer Network, SQL Error Messages, SQL Log, SQL Server, SQL Server Cluster, Windows

I have written a few blogs on issues about the failure of listener creation in the cluster. In this blog, we will explore listener creation failure.

Gouache painting of a freight train of coupled cars along a track, the nearest car vermilion

SqlServerWriter Missing in vssadmin: Causes and Fixes

January 20, 2020

Pinal Dave

SQL Tips and Tricks

SQL Backup and Restore, SQL Command, SQL Error Messages, SQL Scripts

SqlServerWriter missing from the vssadmin output means a backup tool that relies on VSS cannot back up SQL Server. This post checks the three causes: a space at the end of a database name, a login without sysadmin rights, and a stopped writer service. It tests the name check with a demo database.

An unfinished knitted square can be unravelled and worked anew from the same yarn

How to Recompile Stored Procedure? – Interview Question of the Week #260

January 19, 2020

Pinal Dave

SQL Interview Questions and Answers

Parameter Sniffing, SQL Performance, SQL Scripts, SQL Server, SQL Stored Procedure

Question: How to Recompile Stored Procedure?
Answer: The answer to this question is here:

EXEC sp_recompile ‘NameofSP’

Let us read more about it.

Gouache painting of a wooden xylophone with cream bars and one vermilion bar in the middle

Agent Service Missing in Configuration Manager: The Fix

January 18, 2020

Pinal Dave

SQL Tips and Tricks

Registry, SQL Error Messages, SQL Server Agent, SQL Server Services

An Agent service missing in Configuration Manager can come from a wrong program path. This post compares the service lists, reads the Agent path with T-SQL, checks that the file exists, and explains how to repair the path.