SQL Server 2000 I/O Basics

SQL Server 2000 I/O Basics
SQL Server I/O Basics Chapter 2 (I/O and SQL Server 2005)

Utilities
Microsoft provides a few utilities for testing the general properties of your system.
SQLIOStress.exe
SQLIOStress.exe simulates various patterns of SQL Server 2000 I/O behavior to ensure rudimentary I/O safety.
The SQLIOStress utility can be downloaded from the Microsoft Web site. See the following article.
•
How to Use the SQLIOStress Utility to Stress a Disk Subsystem such as SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;231619
Important The download contains a complete white paper with extended details about the utility.
SQLIO.exe
SQLIO.exe is a SQL Server 2000 I/O utility used to establish basic benchmark testing results.
The SQLIO utility can be downloaded from the Microsoft Web site. See the following:
•
SQLIO Performance Testing Tool (SQL Development) – Customer Available
http://download.microsoft.com/download/f/3/f/f3f92f8b-b24e-4c2e-9e86-d66df1f6f83b/SQLIO.msi


For detailed information about the use of caching controllers and SQL Server, see the following articles on the Microsoft Support Web site.
• SQL Server and Caching Disk Controllers
http://support.microsoft.com/default.aspx?scid=kb;en-us;86903
• Using Disk Drive Caching with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;234656
• Using Hard Disk Controller Caching with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;46091


Note: You can also read more about I/O and SQL Server 2005 in SQL Server I/O Basics Chapter 2.

Monitoring Disk Space and Sending Alerts with TSQL

Monitoring Disk Space and Sending Alerts with TSQL

Monitoring disk space is one important task for SQL Server DBAs. To proactively monitor disk space, we want to be notified when disk space is below certain level. We also want to collect database file size information over time for trend analysis, for which Gregory Larsen has an excellent article "Avoiding the red zone". In addition, I also found it is helpful to collect free disk space data over time, since many SQL Servers are used for other purposes as well. Therefore, analyzing available disk space over time can give us a better idea of disk usage.
Normally, using WSH (Windows Scripting Host) and WMI (Windows Management Instrumentation) is a better way of gathering disk information. However, it is still helpful to do this in T-SQL, especially for DBAs who don't have access to the Windows server itself. This sounds strange, but is actually pretty common. Many DBAs's only way of managing SQL Server is through Enterprise Manager and Query Analyzer. Of course, one could use master..xp_cmdshell or a CmdExec job to bypass this limitation, but it is still handy to know the T-SQL way of doing things.
In this article, I will address 2 disk related issues:
how to use a stored procedure to send an email alert when disk free space is below a given level;
how to use a stored procedure to collect disk available space data and store that information in a table. Both stored procedures use one SQL Server extended stored procedure, master..xp_fixeddrives
Stored procedure to send an email alert when disk free space is below a certain level
The following is a stored procedure I wrote to alert DBAs when disk space is below a given limit. Depending on your file growth rate, you can schedule a job to run this stored procedure weekly, daily, or hourly. In my case, running this job daily served my purpose.
Note that I separated the C Drive from the other disk drives, as the OS is usually installed on C. In my code, if C drive has less than 1 GB(1024 MB), then an alert email will be sent. For the other drives, the default benchmark value is 2 GB. You can change these values to suit your specific needs.
Note that temp tables and cursors are used in this stored procedure. Temp table, as opposed to table variable, is necessary because you cannot insert results from an extended stored procedure into a table variable. Also, the cursor is defined as FAST_FORWARD, because it is read-only and direction is not important to us. The rest of the code should be self-explanatory. If you have SQL Mail configured properly, just replace the @recipients value and this procedure should work.
CREATE PROCEDURE usp_DiskFreeSpaceAlert
@DriveCBenchmark int = 1024,
@OtherDataDriveBenchmark int = 2048
AS
--By: Haidong "Alex" Ji This procedure sends out an alert message when hard disk space is below a predefined value. This procedure can be scheduled to run daily so that DBA can act quickly to address this issue.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL,
FreeMB INTEGER NOT NULL)
DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)
/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
EXEC master..xp_fixeddrives
SELECT @DiskFreeSpace = FreeMB FROM #disk_free_space where DriveLetter = 'C'
IF @DiskFreeSpace < @DriveCBenchmark
Begin
SET @MailSubject = 'Drive C free space is low on ' + @@SERVERNAME
SET @AlertMessage = 'Drive C on ' + @@SERVERNAME + ' has only ' + CAST(@DiskFreeSpace AS VARCHAR) + ' MB left. Please free up space on this drive. C drive usually has OS installed on it. Lower space on C could slow down performance of the server'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End
DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from #disk_free_space where DriveLetter not in ('C')
open DriveSpace
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
WHILE (@@FETCH_STATUS = 0)
Begin
if @DiskFreeSpace < @OtherDataDriveBenchmark
Begin
set @MailSubject = 'Drive ' + @DriveLetter + ' free space is low on ' + @@SERVERNAME
set @AlertMessage = @DriveLetter + ' has only ' + cast(@DiskFreeSpace as varchar) + ' MB left. Please increase free space for this drive immediately to avoid production issues'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
End
close DriveSpace
deallocate DriveSpace
DROP TABLE #disk_free_space
GO
Stored procedure to collect disk available space data and store that information in a table
As mentioned earlier, for capacity planning and trend analysis purpose, sometime it is not enough just to collect database file growth data overtime. It is beneficial to also have disk usage data overtime. To achieve that, I wrote the following stored procedure to collect disk available space data and store that into a table. Again, you can schedule a job that runs weekly or daily, depending upon your specific needs, to collect this data over time for trend analysis.
For DBA administrative purposes, I always create a database called DBA to store database admin-related data. In this case, I create a table to store available disk space information. This table has 4 columns: identity column, Drive Letter column, Available MB column, and a time stamp column with a default value of GetDate(). See the following DDL (Data Definition Language) for this table.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DiskAvailableSpace]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DiskAvailableSpace]
GO
CREATE TABLE [dbo].[DiskAvailableSpace] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[DriveLetter] [char] (1),
[FreeMB] [int] NOT NULL ,
[TimeCollected] [smalldatetime] NOT NULL
)
GO
ALTER TABLE [dbo].[DiskAvailableSpace] WITH NOCHECK ADD
CONSTRAINT [DF_DiskAvailableSpace_TimeCollected] DEFAULT (getdate()) FOR [TimeCollected]
GOThe following is the code for this stored procedure. After this stored procedure is executed, the results will be saved in the DiskAvailableSpace table. If you schedule this procedure to run every week, after a few weeks, you will be able to draw a chart of disk usage. This can be pretty valuable for trend analysis. CREATE PROCEDURE usp_TrackDiskAvailableSpace AS
/*
Author: Haidong Ji Date: 1/21/2003
Purpose: Trace and record xp_fixeddrives results into the DiskAvailableSpace table. The results will be invaluable
for trend analysis.
*/
SET NOCOUNT ON
/* Create a temp table to hold disk space information */
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
DriveLetter CHAR(1) NOT NULL
,FreeMB INTEGER NOT NULL
)
/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
EXEC master..xp_fixeddrives
/* Populate DiskAvailableSpace with free space data. This table will have an identity field and time stamp */
INSERT INTO DiskAvailableSpace
(DriveLetter, FreeMB)
SELECT DriveLetter, FreeMB FROM #disk_free_space
/* Clean up. Drop the temp table */
DROP TABLE #disk_free_space
GO
ConclusionThe above 2 stored procedures enabled me to proactively monitor disk usage information. I scheduled the disk space alert stored procedure daily. I also scheduled the TrackDiskSpaceAvailable stored procedure weekly. Those 2 stored procedure really server me well so far. You can increase the job frequency as needed in your environment. Hopefully they can help you as well.

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] )
[ ; ]


Creating a RANGE LEFT partition function on an int column
The following partition function will partition a table or index into four partitions.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);



Creating a RANGE RIGHT partition function on a datetime column
The following partition function partitions a table or index into 12 partitions, one for each month of a year's worth of values in a datetime column.
CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('20030201', '20030301', '20030401',
'20030501', '20030601', '20030701', '20030801',
'20030901', '20031001', '20031101', '20031201');

Planning Guidelines for Partitioned Tables and Indexes

Planning Guidelines for Partitioned Tables and Indexes


Designing Partitions to Manage Subsets of Data
Creating Partitioned Tables and Indexes




Concepts
Designing Partitioned Tables and Indexes
Special Guidelines for Partitioned Indexes
Implementing Partitioned Tables and Indexes
Partitioning in the AdventureWorks Sample Database

Implementing Partitioned Tables and Indexes

Implementing Partitioned Tables and Indexes


Creating Partitioned Tables and Indexes
Explains how to create partition functions and partition schemes, and how to apply them to a table or index.
Modifying Partitioned Tables and Indexes
Explains how to modify partition functions and partition schemes, how to convert a nonpartitioned table to a partitioned table and vice versa, and how to move partitions between tables.
Querying Data and Metadata from Partitioned Tables and Indexes
Explains how to query data from a partitioned table, how to use the $PARTITION scheme to obtain information about individual partitions, and how to obtain metadata about partitioned tables through the catalog views.

Tips, Tricks, and Advice from the SQL Server Query Processing Team : Index Build strategy in SQL Server

Tips, Tricks, and Advice from the SQL Server Query Processing Team : Index Build strategy in SQL Server - Introduction (II)

Partitioned Tables and Indexes in SQL Server 2005

Partitioned Tables and Indexes in SQL Server 2005

Sql Server 2005 Partitioning

Sql Server 2005 Partitioning

SQLskills.com

SQLskills.com Immerse Yourself In SQL Server

Partitioning in SQL Server 2005 Beta II

Partitioning in SQL Server 2005 Beta II

Exploring Table and Index Partitioning in SQL Server 2005

Exploring Table and Index Partitioning in SQL Server 2005

SQL Server 2005 - Full-Text Search (MSDN)

Full-Text Search Developer InfoCenter


SQL Server 2005 Books Online
Full-Text Search Developer InfoCenter
To help the Full-Text Search developer be as effective as possible, the following lists provide links to relevant topics.
Concepts
Introduction to Full-Text Search
Using Full-Text Search
Getting Started with Full-Text Search
Comparing Full-Text Functions and Full-Text Predicates
Inquiring About the Full-Text Key Column
Integrating Full-Text Search and Transact-SQL Predicates
Obtaining Full-Text Property Values Using Transact-SQL Functions
Searching for Specific Word or Phrase (Simple Term)
Performing Prefix Searches
Searching for the Inflectional Form of a Specific Word (Generation Term)
Searching for Words or Phrases Close to Another Word or Phrase (Proximity Term)
Searching for Words or Phrases Using Weighted Values (Weighted Term)