Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































SQL Server script to rebuild all indexes for all tables and all databases

MSSQLTips author Greg Robidoux By:   |   Read Comments (62)   |   Related Tips: More > Fragmentation and Index Maintenance
Problem

One of the main functions of a DBA is to maintain database indexes.  There have been several tips written about different commands to use for both index rebuilds and index defrags as well as the differences between index maintenance with SQL Server.  In addition, other tips have been written about using maintenance plans to maintain indexes on all databases.  One of the issues with maintenance plans is that they don't always seem to be as reliable as you would hope and you also sometimes get false feedback on whether the task actually was successful or not.  In this tip we look at a simple script that could be used to rebuild all indexes for all databases.

Solution

The one nice thing about maintenance plans is that it works across multiple databases and therefore you can push out one task to handle the same activity across all of your databases.  The problem that I have seen with maintenance plans is that sometimes they do not work as expected, therefore here is another approach.

The script below allows you to rebuild indexes for all databases and all tables within a database.  This could be further tweaked to handle only indexes that need maintenance as well as doing either index defrags or index rebuilds.

The script uses two cursors one for the databases and another for the tables within the database.  In addition, it uses the INFORMATION_SCHEMA.TABLES view to list all of the tables within a database. 

Because we need to change from database to database we also need to create dynamic SQL code for the queries.  For the DBCC DBREINDEX option we can just pass in the parameters, but for the ALTER INDEX statement we need to build the query dynamically.  Here is the script.

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT

SET
@fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR  
SELECT
name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET
@cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM ['
+ @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

  
-- create table cursor  
  
EXEC (@cmd)  
  
OPEN TableCursor  

  
FETCH NEXT FROM TableCursor INTO @Table  
  
WHILE @@FETCH_STATUS = 0  
  
BEGIN  

       IF
(@@MICROSOFTVERSION / POWER(2, 24) >= 9)
      
BEGIN
          
-- SQL 2005 or higher command
          
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
          
EXEC (@cmd)
      
END
       ELSE
       BEGIN
          
-- SQL 2000 command
          
DBCC DBREINDEX(@Table,' ',@fillfactor)  
      
END

       FETCH
NEXT FROM TableCursor INTO @Table  
  
END  

   CLOSE
TableCursor  
  
DEALLOCATE TableCursor  

  
FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE
DatabaseCursor  
DEALLOCATE DatabaseCursor

The script will work for both SQL 2000 and higher versions.  For SQL 2000 it uses DBREINDEX and for SQL Server 2005 and higher it uses ALTER INDEX.  Thanks go out to LittlePanthers for providing the code snippet to check the version of SQL Server.

Also, I have excluded the system databases, so you can include these or also add other databases to exclude from you index maintenance routines.

Next Steps
  • This is a simple base script that could be modified into a stored procedure and also allow you to pass other parameters such as doing an index rebuild or an index defrag.
  • Make the index rebuild statements more robust with other options.
  • You could also modify this to read from a table that you create to identify which databases and which indexes you want to run this against.  You can look at index fragmentation and only rebuild the indexes that need to be rebuilt.
  • This approach rebuilds all indexes, so be careful if you run this on very large indexes or large databases
  • Take a look at these other index related tips


Last Update: 10/14/2012


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, March 10, 2008 - 7:05:36 AM - ChipLee Read The Tip

There is a little problem in this script. When database name or table name contains space, this script will fail.

The modification is to add '[' and ']' before database name and table name


Friday, April 04, 2008 - 7:41:39 AM - admin Read The Tip

ChipLee,

Agreed and thank you for the post.  In our scenario, we did not have spaces in the database or table names, but from a best practices perspective it is a good idea to address the issue just it case it does occur.

Thank you,
The MSSQLTips.com Team


Monday, June 02, 2008 - 5:03:23 AM - honzas Read The Tip

It would be better to exclude databases in some non-operational states (e.g. offline, readonly etc.)

Just enhance the filtering of the cursor's query e.g. by following:

WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')    -- exlude system datbases
 AND status &  -- exclude statuses not suitable for reindexing
 (   32 -- loadig
  |  64 -- pre recovery
  | 128 -- recovering
  | 256 -- not recovered
  | 512 -- offline
  |1024 -- readonly
  |2048 -- dbo use only
  |4096 -- single user
  |32768 -- emergency mode
  ) = 0
ORDER BY 1 


Friday, June 27, 2008 - 8:49:26 AM - sql_er Read The Tip

When the indexes are being rebuilt, very often the transaction log grows, sometimes to a size you don't want it to grow to, as it might cause problems.  That is why, when I rebuilt indexes (in 1 database only), I usually rebuilt table by table, manually, checking the trans log size (using DBCC LOGSPACE) each time a big table with large indexes was rebuilt.

 If we are to automate it, I would think it might be helpful to add a test, such that after each index/table is rebuilt, check the transaction log space used.  If it is above a certain value, then either backup trans log, or just keep waiting and checking in a loop, until the scheduled trans log backup is executed, and the trans log space used drops below a certain value, and then continue with index rebuilding ...


Monday, November 16, 2009 - 5:58:08 AM - daspeac Read The Tip
Hi there, I believe there is an easier way to do it. try the waiting for sql server to recover databases program, it is a good solution, if you experience any difficulties with dbx files. This application also converts dbf files. Hope it helps

Monday, November 16, 2009 - 1:16:35 PM - admin Read The Tip

Daspeac,

It looks like the tool you mentioned is for recovering data\databases, but this tip is related to rebuilding indexes.  I believe these are two different needs.

Thank you,
The MSSQLTips Team


Friday, November 20, 2009 - 12:41:49 PM - daspeac Read The Tip

thanks for your comment. I think that the recovery implies the rebuilding of entire database, the detailed description of procedure takes a lot of time, it can be read on the website of application developer


Monday, December 21, 2009 - 5:34:48 PM - LittlePanthers Read The Tip

to make it so you don't have to comment/uncomment lines of code, here is a tid bit I put in to run the script for either 2k or 2k5


if (@@microsoftversion / power(2, 24) >= 9)
    begin
       -- SQL 2005 command
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    end
else
    begin
       -- SQL 2000 command
       --DBCC DBREINDEX(@Table,' ',@fillfactor) 
    end
EXEC (@cmd)
    


Monday, December 21, 2009 - 5:35:58 PM - LittlePanthers Read The Tip

here you go, sorry forgot to uncomment out the actual 2k call.

 
if (@@microsoftversion / power(2, 24) >= 9)
    begin
       -- SQL 2005 command
       SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    end
else
    begin
       -- SQL 2000 command
       DBCC DBREINDEX(@Table,' ',@fillfactor) 
    end
EXEC (@cmd)


Monday, December 21, 2009 - 6:41:15 PM - admin Read The Tip

Thanks. 

That is a handy little trick if you have to manage multiple versions of SQL Server.


Friday, April 16, 2010 - 3:28:37 PM - Repriser2010 Read The Tip

Not good enough. You have to query DMV first to find which ones are really in need of a rebuild, not just rebuild everything, because the resource usage will stall your server.


Sunday, September 05, 2010 - 3:38:37 PM - marcio dalago Read The Tip
hi, my sample is:

 

EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'') BEGIN PRINT ''#'';
EXEC #.dbo.sp_msForEachTable ''ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = ''90'')'', @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Index Updated'''''' END',
@replaceChar = '#'




Thursday, June 23, 2011 - 2:38:42 PM - Rickster Read The Tip

I get the following error when running the author's script as shown:

Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'TableCursor' already exists.

Others aren't getting this?

 

 

 

 


Thursday, June 23, 2011 - 11:42:47 PM - marcio dalago Read The Tip

EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'')
BEGIN
    PRINT ''#'';
    EXEC #.dbo.sp_msForEachTable ''ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)'',
    @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Index Updated''''''
END',@replaceChar = '#'

--
EXEC sp_msForEachDb @command1='PRINT ''?'';'  -- DATABASE
EXEC sp_msForEachTable @command1='PRINT ''?''' -- TABLES


Friday, June 24, 2011 - 8:03:47 AM - Greg Robidoux Read The Tip

Thanks Marcio for the alternate approach.


Friday, June 24, 2011 - 8:04:39 AM - Greg Robidoux Read The Tip

Rickster I have not see this issue.  What version of SQL are you using and how many databases are you running this on?


Friday, June 24, 2011 - 10:22:19 AM - Rickster Read The Tip

Greg ... error was produced using Sql Server 2000 QA tool. I found at least one other Google hit on that error msg from someone else who had posted exact same code.

Thanks Marcio.

 


Tuesday, July 12, 2011 - 11:06:42 AM - Much better alternate Read The Tip

Instead of rebuilding ones that don't need it, try a more comprehensive solution, like

Ola Hallengren's: http://ola.hallengren.com

or

SQLFool's: http://sqlfool.com/2011/06/index-defrag-script-v4-1/

 


Monday, July 25, 2011 - 2:51:26 PM - Jay Read The Tip

Hello:

The script is good, i am looking for kind of a same script, which show me fragmentation for some tables but not all. how can i do that.

Any help is greatly appreciated.

Thanks


Monday, July 25, 2011 - 3:26:18 PM - Greg Robidoux Read The Tip

Jay - take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1791


Sunday, August 28, 2011 - 3:22:23 AM - chandramohan Read The Tip

Hi,

    When we have indexes with fillfactor other than 90 then this script will rebuild the indexes with new fill factor which is not wanted. So you need to modify the script so that will take the existing fill factors only.

Thanks


Sunday, August 28, 2011 - 6:23:36 AM - Greg Robidoux Read The Tip

You can change these lines to use the fill factor that is currenlty in effect for the index:

FROM

-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

-- SQL 2000 command
DBCC DBREINDEX(@Table,' ',@fillfactor) 

TO

-- SQL 2005 or higher command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'

-- SQL 2000 command
DBCC DBREINDEX(@Table) 


Monday, January 09, 2012 - 7:01:06 AM - Ramesh Read The Tip

Generally I will use the rebuild index script for all my production database. It runs quite good and helps me alot.

Recently i have ran this script at my another production server database which has very huge data. The size of MDF is 30GB.

I got the below mentioned error, can you tell me what would be reason for that and how should i rectify.

***** Error **************

Database name 'tempdb' ignored, referencing object in tempdb.

Msg 1088, Level 16, State 9, Line 1

Cannot find the object "#TEMP_Location_Scanner_Counts_______________________________________________________________________________________000000000137" because it does not exist or you do not have permissions.

****************** end of error mesage. *****

Thanks in advance

 

 

 


Sunday, February 05, 2012 - 2:31:47 AM - Anil Lobo Read The Tip

Thanks guys, it really helped.


Thursday, May 03, 2012 - 8:16:42 AM - BobC Read The Tip

I second the suggestion to use Ola Hallengren's: http://ola.hallengren.com, or a similar one that will test fragmentation and reogr/rebuild accordingly. 

See also:

http://technet.microsoft.com/en-us/library/ms188917.aspx


Thursday, May 03, 2012 - 9:56:32 AM - Greg Robidoux Read The Tip

BobC - I agree it is better to only maintain the indexes that need maintenance instead of doing all indexes.  This is a good approach for people that want to do something and not have to put to much thought into the process.  Also, this is a good approach for SQLExpress where you cannot setup maintenance plans.

Here is another tip that does selective index maintenance: http://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/


Saturday, May 19, 2012 - 1:32:18 PM - venkat Read The Tip

i have executed the script given in the begining of this page. it has executed succesfully. can you provide me a script that lets me know how the rebuild operation has performed.

 

Thanks

venkat


Tuesday, May 22, 2012 - 7:50:42 AM - Sherbaz Mohamed C P Read The Tip

With the help of some forums and blogs, I have designed a script that will automatically decide and rebuild or reorg all indexes in a database based on fragmentation level. Please have a look and let me know your inputs.

http://www.sherbaz.com/2011/12/automatically-rebuild-or-reorg-index-based-on-fragmentation/


Thursday, August 23, 2012 - 6:47:25 PM - ilir dokle Read The Tip

This work just great thanx a lot.


Tuesday, October 23, 2012 - 7:20:35 AM - Thamaraiselvan Read The Tip

request came for the recreation of the indexes of all EVS tables (DAT_EVS_*) in DATxdb_work schema on URANUS-DAT.

 

Please copy, the existing indexes of EVS tables (DAT_EVS_*) in EARTH-DAT DATXdb TO  URANUS-DAT DATxdb_work EVS tables (DAT_EVS_*)

 

Please do the same for the primary keys as well.

LET ME KNOW THE STEP BY STEP

THANKS.


Sunday, January 27, 2013 - 11:03:31 AM - Brent Ozar Read The Tip

Hi, Greg. One quick tweak to the script - in the below line, switch MASTER to master.  On case-sensitive instances, it'll matter.  Thanks sir!

 

SELECT name FROM MASTER.dbo.sysdatabases   


Sunday, January 27, 2013 - 2:09:22 PM - Greg Robidoux Read The Tip

Hi Brent, thanks for the feedback.  I haven't looked at this tip in quite some time.  That's kind of weird I made master upper case.  Oh well, thanks again for the note and this has been fixed.

Thanks again.


Saturday, February 02, 2013 - 2:33:57 AM - Dhanesh Read The Tip

Hi Greg,

can you plz tell, how to check whether index is rebuidl or not means last index rebuild date


Monday, February 25, 2013 - 9:34:44 PM - xexex Read The Tip

Thanks, the script is great.

I ran it on my SQL Server, but my transcation gowth large.

I'd like to change my database to "simple mode" before re-index, how can I add this to the script?


Tuesday, February 26, 2013 - 7:35:43 AM - Greg Robidoux Read The Tip

@xexex - take a look at these articles on how to set the recovery model to SIMPLE and FULL

http://www.mssqltips.com/sqlservertutorial/4/sql-server-simple-recovery-model/

http://www.mssqltips.com/sqlservertutorial/3/sql-server-full-recovery-model/

http://www.mssqltips.com/sqlservertip/1497/selecting-the-sql-server-database-recovery-model-to-ensure-proper-backups/

You could add these commands inside the Database Cursor loop.

After you revert back to Full you need to make sure you do a full backup again, becuase the log chain will be broken for restoring your last full backup and your transaction log backups.


Wednesday, March 06, 2013 - 5:58:26 PM - brian Read The Tip

 my friend set- up my site and it was hacked. the hacker changed the site completely and moved its location and eased the backups. i rebuilt the site but dont known how to change the server and place the file where it needs to be . any thoughts on how in known its in the pretable but what i am looking greek to me 


Wednesday, March 27, 2013 - 2:05:15 AM - xexex Read The Tip

Thanks @Greg Robidoux

How can I add the script so I don't need to hardcore the DatabaseName inside the Database Cursor loop


Wednesday, March 27, 2013 - 5:34:45 AM - Greg Robidoux Read The Tip

@xerex - the names in the following line only exclude databases, otherwise all databases are included:

WHERE name NOT IN ('master','msdb','tempdb','model','distribution')


Wednesday, March 27, 2013 - 5:56:43 AM - xexex Read The Tip

@Greg Robidoux - I mean I don't need to hardcore the name to change recovery mode, please see the post in February 25, 2013 and February 26, 2013

Thanks for Reply :)

 

 


Wednesday, March 27, 2013 - 2:40:11 PM - Greg Robidoux Read The Tip

@xerex - you can add this code as shown below.  This should work, but I have not tested it.

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('ALTER DATABASE [' + @Database + '] SET RECOVERY SIMPLE')

 

and add this

  CLOSE TableCursor
  DEALLOCATE TableCursor  

  EXEC ('ALTER DATABASE [' + @Database + '] SET RECOVERY FULL')
  FETCH NEXT FROM TableCursor INTO @Table
END


Tuesday, April 02, 2013 - 12:33:40 AM - xexex Read The Tip

@Greg Robidoux It work! Thanks.


Tuesday, April 16, 2013 - 6:40:05 AM - CArl Read The Tip

Can anyone help me?

 

would i just run this query on my SQL and will it re index all my databases?or i need to modify or change some of it before running this query?

thanks for the help..

 

Carl


Tuesday, April 16, 2013 - 10:23:03 AM - Greg Robidoux Read The Tip

@Carl - this will do this for all indexes for all databases.


Tuesday, April 16, 2013 - 8:55:50 PM - Carl Read The Tip

@Greg

Thanks for your prompt reply..

so i will paste this query without editing this?..sorry for the question, i dont know if i should put and edit this query indicating the name of my database(RESCOM)..and if yes i dont know ehere to put it..

 

Regards.


Wednesday, April 17, 2013 - 9:46:59 AM - Greg Robidoux Read The Tip

@Carl - change this part of the code as follows to do just one database

DECLARE DatabaseCursor CURSOR FOR  
SELECT
name FROM master.dbo.sysdatabases  
WHERE name IN ('RESCOM')  
ORDER BY


Thursday, May 09, 2013 - 4:15:42 PM - Paul Read The Tip

I am very new to SQL but I jsut wanted to verify that I can past that code into a new query window for my SQL 2000 server and execute and it will run? 

Also might be a dumb question (I think the answer is yes) but do I have to make sure all users are out of the SQL application before running?


Thursday, May 09, 2013 - 5:31:23 PM - Greg Robidoux Read The Tip

@Paul - yes you can just paste the code from the above into a query window and run this.

If you have small databases this will run quickly, but for larger databases and indexes it will take some time.

When you rebuild an index it will block other users until the index rebuid is complete, unless you are using the ONLINE option which is only available in the Enterprise edition of SQL Server.

Best practice is to rebulid indexes when there is low to no database usage just to eliminate any blocking or heavy I/O that might occur.

You can also change this to limit to one or more databases. 

Let's say you want to do this for only databases Test1 and Test2 change this code:

SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')  
ORDER BY 1  

to

SELECT name FROM master.dbo.sysdatabases  
WHERE name IN ('Test1','Test2')  
ORDER BY 1  


Thursday, May 09, 2013 - 8:14:31 PM - Paul Read The Tip

Perfect, thanks Greg. I appreciate it, this is a big help...


Thursday, May 09, 2013 - 8:19:39 PM - Paul Read The Tip

@Greg -  Anything else I can do to speed up SQL besides reindexing?


Friday, May 10, 2013 - 9:07:35 AM - Greg Robidoux Read The Tip

@Paul - have you run Profiler or a server side trace to find queries that take the longest to run?  You can find these queries and then look at exeuction plans to determine how to improve query performance.

Once you have optimized all of your queries the only other option is to add more or faster hardware such as CPU, memory and disks.

Take a look at these other tips:

http://www.mssqltips.com/sql-server-tip-category/83/profiler-and-trace/


Thursday, May 30, 2013 - 6:02:00 AM - ASHOK KUMAR J Read The Tip

Hi Graig,
Excellent Article

I have one question:

We have a 20 databases, We are used to use maintenance Plan to schedule the Weekly rebuild

index job on Every Saturday night 12pm EST. Last

Two Weeks Weekly Rebuild Index job

Running long over 2days? But acutally it might

run 30 mints.

Which is caused this? How to fix this problem?



Thursday, May 30, 2013 - 11:20:33 AM - Greg Robidoux Read The Tip

@Ashok Kumar 3 - take a look to see if the index rebuilds are being blocked by other proceses.  This is one reason why it may be taking longer to complete.  Also, it maybe helpful to do one database at a time to see if you can pinpoint which database is causing the issues.

 


Monday, August 05, 2013 - 12:11:02 PM - Pat Read The Tip

I have to question if you really wish to rebuild every index on every table in every database.

At the very least, I would think that you would want to check the degree of fragmentation and the number of pages that the index consumes before deciding if the index even needs to be rebuilt in the first place.  Otherwise, you my be wasting a lot of unnecessary CPU cycles.

 


Monday, August 05, 2013 - 1:13:17 PM - Greg Robidoux Read The Tip

@Pat - thanks your your feedback on this tip.  Yes you are correct the better approach is to only maintain indexes that have fragmentation issues.

This is a good alternative for using Maintenance Plans, which rebuilds all indexes, or for small databases where reindexing all indexes is not very time consuming.

I agree a better approach is to selectively rebuild, but maintaining all indexes is better than doing nothing.

Thanks
Greg


Wednesday, October 23, 2013 - 1:20:43 AM - Iyyankann M Read The Tip

Thanks guys, it really helped.


Thursday, October 24, 2013 - 10:45:24 AM - Carlos Read The Tip

I am new to sql scripts good job guys, good tips... I will be stoping by every so often to check for new tips


Tuesday, November 12, 2013 - 6:57:19 AM - Parker Read The Tip

Remember, this article is a bit aged. Rebuilding indexes, especially on a recurring schedule, is no longer important, and usually waste of resources. The only reason you would do this is for index fragementation where the entirety of the index pages are sequentially organized on a single rotating drive. With most data in multi-physical-drive raid environments, and most certainly with Solid State Drives, reducing fragmentation will not have any impact since the data will still be read from multiple discontinguous points, no matter how many times you defrag those pages. The take-away is that hardware technology changes have made it where data and indexes no longer need to be continguous. In most cases the default fill factor will suffice, but your hardware should be considered before throwing on excess processing usage that serves no purpose; save your CPU, log files, and network traffic for your backups, ETL, reporting, etc.


Friday, November 22, 2013 - 3:50:17 PM - Lava Read The Tip

Hi Greg -

Is it possible to modify your script to add a case statement to where the program either rebuild or reorganize based on fragmentation level? I like your way of writing scripts, it is always simple and easy to follow.

 

Thanks,

Lava


Friday, November 22, 2013 - 4:25:50 PM - Greg Robidoux Read The Tip

@Lava - yes this script could be modified, but this doesn't get current fragmentation levels it does every index which is not always the best approach.

Take a look at this tip to see if you could use this or combine the two together:

http://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/


Tuesday, December 10, 2013 - 7:43:09 AM - mo Read The Tip

exec sp_MSforeachdb '

IF ''!'' NOT IN (''master'',''model'',''msdb'',''tempdb'')

BEGIN

USE [!]; 

EXEC sp_MSforeachtable ''ALTER INDEX ALL ON ? REBUILD''

END',@replacechar = '!'


Nice & Easy ;)


Tuesday, February 18, 2014 - 8:59:23 PM - Jay Ganesh Read The Tip

Thanks ,

It Works.

I Need a suggession. i useing sql server 2005 for one small Hospital (free Service organisation).

all table record have a relation with one table. when make query with relate that table , is it affect performance ? (in feature)

can you assit me to how optimize and organize the databse with best performance

 

thanks

 

JAY


Wednesday, February 19, 2014 - 9:12:26 AM - Greg Robidoux Read The Tip

Hi Jay,

Take a look at this tutorial about tuning queries: http://www.mssqltips.com/sqlservertutorial/2250/graphical-query-plan-tutorial/

Also, take a look at these tips to gather information about poor performing queries using Profiler and server side traces: http://www.mssqltips.com/sql-server-tip-category/83/profiler-and-trace/



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.