Solved

HIgh I/O usage on SQL Server box.

Posted on 2007-03-20
6
294 Views
Last Modified: 2010-03-20
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!
0
Comment
Question by:Levi Martin
  • 3
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18757292
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
 

Author Comment

by:Levi Martin
ID: 18757862
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
 
LVL 9

Expert Comment

by:FixingStuff
ID: 18758170
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Levi Martin
ID: 18758369
How would I defrag a database though...I can defrag the OS but not sure how to tackle the DB. Ideas?

Many thanks!
0
 

Author Comment

by:Levi Martin
ID: 18758637
/*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
 
LVL 9

Accepted Solution

by:
FixingStuff earned 500 total points
ID: 18759285
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now