Tuesday, November 07, 2006

SQL Server 2005 Script Repository

Found this on the TechNet site; sample scripts and stored procedures for managing and working with SQL Server 2005. However on the TechNet site the code is spread ove several pages. I decided to make a user-friendly list instead

Buffer Cache
Sample scripts and stored procedures for managing and monitoring the SQL Server buffer cache.

Retrieve Buffer Counts by Object and Index

CPU and Optimization
Sample scripts and stored procedures for optimizing CPU performance in SQL Server.

Determine CPU Resources Required for Optimization
Retrieve Parallel Statements With the Highest Worker Time
Retrieve Statements with the Highest Plan Re-Use Counts
Retrieve Statements with the Lowest Plan Re-Use Counts

Indexes and Indexing
Sample scripts and stored procedures for working with SQL Server indexes.

Analyze Index Statistics
Create/Truncate an Indexstats Table
Determine Index Cost Benefits
Identify Missing Indexes
List Indexes With the Most Contention
Retrieve Index Statistics
Retrieve Index Usage Statistics
Retrieve Indexes Not Used Since the Last Recycle Time
Retrieve Object and Index Fragmentation Information
Retrieve Tables, Indexes, Files, and File Groups Information

Input/Output
Sample scripts and stored procedures for managing and monitoring input and output in SQL Server.

Calculate Average Stalls
List Queries That Could Benefit From an Index
List Rarely-Used Indexes
List Statements By Input/Output Usage

Performance (General)
Sample scripts and stored procedures for managing and monitoring general performance issue in SQL Server.

List Cached Plans Where Worker Time Exceeds Elapsed Time
List Currently-Executing Parallel Plans
List Recompiled Statements
List Runnable Queues
List Statements With the Highest Average CPU Time
List Statements with the Highest Execution Counts
List Top Wait Types for a Workload

Processor Cache
Sample scripts and stored procedures for managing the SQL Server processor cache.

Compare Single-Use and Re-Used Plans
List Statements By Plan Re-Use Count

SQL Text
Sample scripts and stored procedures for working with SQL text.

Retrieve a SQL Statement with a Specified .SQL_Handle
Retrieve SQL Text and XML Plans

SQLOS
Sample scripts and stored procedures for working with the new SQL Server Operating System Layer.

Compare Signal Waits and Resource Waits
List Currently-Executing Statements
List Scheduler Wait List Information
List Schedulers, Workers, and Runnable Queues
List Session and Scheduler ID Information
List SQLOS Execution Model Information
List Statements from a Specified Waiter List

Tempdb
Sample scripts and stored procedures for working with the tempdb system database.

List Real Time Tempdb Task Usage
List Real-Time Tempdb Statements

Transactions and Logging
Sample scripts for working with SQL Server transactions and locks.

Compare Locking and Repeatable Reads
Configure a Block Condition
Handle a Block Condition
List Lock and Serializable Information
List Locks and Reads
List Locks and Repeatable Reads
List Real-Time Blocker and Waiter Statements
Report Blocker and Waiter SQL Statements


Waitstats
Sample scripts and stored procedures for working with SQL Server waitstats.

Retrieve Waiter List Information
Retrieve Waitstat Snapshots

2 comments:

Mladen said...

Awsome.
Thanx for that list.

SQL said...

np, it sure is handy ;-)

I wrote about two of these dynamic managment views on my own (so far)

sys.dm_db_index_usage_stats
and
sys.dm_exec_sessions
and hopefully will cover them all before katmai comes out