Link to home
Start Free TrialLog in
Avatar of Levi Martin
Levi MartinFlag for United States of America

asked on

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!
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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!
Avatar of Levi Martin

ASKER

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?
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
How would I defrag a database though...I can defrag the OS but not sure how to tackle the DB. Ideas?

Many thanks!
/*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
ASKER CERTIFIED SOLUTION
Avatar of Dean Chafee
Dean Chafee
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial