HIgh I/O usage on SQL Server box.

Looking for the syntax in SQL to remove all freespace and truncate your transaction logs.

I have a server that is using large amounts of I/O and SQL processes aren't releasing as they should. I have checked my DB maintenence which is regular with everythig on its own drive and I have rebuilt my indexing. So far this seems to point to a fragmented SQL DB...I suppose I'm looking for the syntax above and thoughts from the brightest minds here.

Thanks all for any input!
Levi MartinSenior Data AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you have high I/O, you should check which files are written / read alot.
by chance, it's not the database files, but the swap file, in which case you only need to ensure you reduce the max memory setting of sql server to ensure it does not take ALL of the RAM, but leave some 200-300 MB for the operating system ( remove even more if you have other applications running on the box).

note that removing freespace and truncating the transaction log will very probably NOT solve the problem, but make it more likely worse!
0
Levi MartinSenior Data AnalystAuthor Commented:
Point taken on the logs. The SQL Server uses very little RAM currently as I cannot get the config values for memory to go to running values. What is I use:

DBCC SHOWCONTIG WITH FAST

...althought this will slow the DB it returns to me how much my DB is fragmented thereby showing me if I should use a defragmetter.

Would it be the next best option to run this command and defrag the DB?
0
Dean ChafeeIT/InfoSec ManagerCommented:
If your SQL server process is using "very little" RAM that could be your problem in itself. SQL loves RAM... as much as you can give it, but don't starve the OS as angel pointed out.  So, you need to get the mem config in running mode.

As far as the fragmentation, that really is simply a performance optimizing routine. It again is probably not your root problem. You need to find out what/who is causing all the I/O and deal with that as well as the memory issue.
Try this:
  in Query Analyzer, run SP_WHO2 ACTIVE  and watch the DiskIO and CPUTime counters. Keep refreshing it and see who/what is hammering the DB.

fs
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Levi MartinSenior Data AnalystAuthor Commented:
How would I defrag a database though...I can defrag the OS but not sure how to tackle the DB. Ideas?

Many thanks!
0
Levi MartinSenior Data AnalystAuthor Commented:
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM tables
      INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO
0
Dean ChafeeIT/InfoSec ManagerCommented:
There is not really a "DB DEFRAG" command as you may expect. However, if you drop and re-create a clustered index on a table per table basis, it will re-organize the data pages in the table resulting in contiguous data. The other option is to bulk copy out the data, drop the table, then re-load which will result in a better optimization, but obviously an off line operation with other possible ramifications.

Your DBCC Indexdefrag SP is about the best online optimization that can be done and it is what I use weekly in my data center.

Wish I had the "defrag syntax" solution you are looking for, but it does not exist AFAIK.
fs
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.