?
Solved

HIgh I/O usage on SQL Server box.

Posted on 2007-03-20
6
Medium Priority
?
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 143

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

764 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