Tech Article

Freebies: Stored procedures and functions

You may already be working with the usual suspects for performance tuning tools, but are you using all the features available in each tool? The following is one of several must-have free tools, or go to the complete list of freebies.

  6. Stored procedures and functions Return to Complete List

What would SQL Server be without the system stored procedures and functions that operate in the rawest form to monitor SQL Server and gather the necessary performance statistics? Here are some of my favorites.

Object Name Purpose
sp_who2 Listing of the current system processes in SQL Server with application name, last batch, blocking, etc.
DBCC INPUTBUFFER The first 255 characters of the last statement sent to SQL Server by a single system process identifier (i.e. SPID)
sysprocesses System table with system processes
sp_lock Current locks by spid with the type of lock
sp_configure Listing of the SQL Server system configurations
fn_virtualfilestats I/O statistics for each database file
DBCC SHOWCONTIG When executed per table outlines the data and index fragmentation
Trace flags Logs additional information for SQL Server events that are typically not captured

Click for the next featured freebie: SQL Server performance tuning dashboard


About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.

This was first published in July 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: