Lock and Connection Management

Using Indexes to Bypass Locks

  • Article

One of the issues you'll face with SQL Server is blocking which is caused by other processes that are holding locks on objects. Until the locks are removed on an object the next process will wait before proceeding. This is a common process that runs within SQL Server to ensure data integrity, but depending on how transactions are run this can cause some issues. Are there ways to get around blocking by using different indexes to cover the queries that may be running?

2008-05-02

3,383 reads

Who's Blocking

  • Script

A quick little standalone script that tells you what process is blocking and what processes the blocking processing actually blocking.When running this script in QA, change your output to "Results in Text" ( CTRL-T ).  Utilizes the blocking info in sp_who2 combined with dbcc inputbuffer and a little cursor to wrap it all up.  Formatting […]

2.33 (3)

2007-04-19 (first published: )

7,419 reads

What's Running

  • Script

spWhatsRunning does just that.  It tells you exactly what is executing on your server.  By combining the output of the sp_who and dbcc inputbuffer, this script will tell you exactly whats being executed.  DBCC INPUTBUFFER will tell you the same thing, but by the time you get the spid, the offending process may be gone.  […]

2007-04-17 (first published: )

2,536 reads

Script to output dbcc inputbuffer adding spid info

  • Script

The following script will allow the user to get information from all spids that have a program name associated with them. That is event info out of dbcc inputbuffer. Additional columns may be added and used in the table through simple modifications of the script. I just found it useful for troubleshooting and setting up […]

2006-10-19 (first published: )

2,139 reads

SQLPing.vbs

  • Script

Troubleshooting intermitten connectivity issues can be difficult. One approach is to repeatedly test connecting to SQL Server outside of an existing application in order to verify if a server-wide intermitten issue is occurring. This script is used to test remote connectivity to a SQL Server. The script loops in one minute intervals (adjust as needed). […]

2006-09-04 (first published: )

1,399 reads

Blogs

Query Performance Tuning with Filtered Statistics

By

Introduction I recently got a call from our DBA telling me that the CPU...

Performance Tuning Steps Query by looking an execution plan and effectiveness of the index Part 2

By

How to read execution plan to improve poorly... [[ This is a content summary only....

VSCode Tips–Remove the Highlighted Line

By

VSCode was making me crazy as I was writing a script. I like a...

Read the latest Blogs

Forums

Growing and Changing

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Growing and Changing

Azure Stretch Database, Part I: Getting Going

By SQLBlimp

Comments posted to this topic are about the item Azure Stretch Database, Part I:...

Minimalistic SELECT

By Thomas Franz

Comments posted to this topic are about the item Minimalistic SELECT

Visit the forum

Ask SSC

SQL Server Q&A from the SQLServerCentral community

Get answers