Skip to main content

Reindexing the entire sql server database

How To: Re index Entire database

Applies to : SQL Server 2000/ 2005

Recently i moved some database to new server and wanted to reindex entire Sql Server database, as people were complaining about slow response for our .NET WinForms application.

I thought it might save some body's hours of time who is searching for the solution.
Problem :
You want to increase the performance of your database so you move Sql Server from one machine to another machine which has higher capacity than the older one. But after migration users start complaining that their application response is slow.

Soultion :
This is what i done to solve the above problem. I read somewhere that re indexing and reorganizing will help to improve some performance. So here is how to do it.

System Stored Provedure : sp_MSforeachtable

This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set.

EXEC [sp_MSforeachtable] @command1="DBCC DBREINDEX('?')"

To see progress messages while it is running, try this version:

EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')".

You can Re index the Live Databases as well.
Each table will be locked up for the duration of the reindexing. Users will be locked out if they are hitting that table.

If you know any other solution to such problems then please share it here so that it helps people to solve such problems.

Comments

Popular posts from this blog

How to Install Hadoop (2.7.3) on Ubuntu (16.04 LTS)

As I am planning to learn Hadoop, I wanted to install Hadoop (2.7.3) on my Ubuntu (16.04 LTS) and I followed the steps mentioned in the documentation on the Apache Hadoop website. I encountered few problems which are mentioned below, spent some time finding solution to them.

Below are the steps I followed and the description of the error is at the end of this post and also I have mentioned what I missed and what caused these errors.


Step 1. Download Hadoop installation file. For Hadoop 2.7.3 version I used the following link 

http://www.apache.org/dyn/closer.cgi/hadoop/common/hadoop-2.7.3/hadoop-2.7.3-src.tar.gzStep 2. (Assuming you have downloaded the file into /home/<username>/Downloads folder

$ tar zxf hadoop-2.7.3-src.tar.gz
This will extract the files into a folder  "hadoop-2.7.3" .

Step 3. I moved the file to /home/<username> folder (many suggest to move it into /usr/local but I prefer to keep it here, may be once I learn more about linux I might get into tha…

Javascript KeyCode Reference table for Event Handling

The post explains Keyboard event handling using javascript.Javascript events are used to capture user keystrokes. Below is a table of key codes for the keys on a multimedia keyboard. If this table is inconsistent with your own findings, please let me know.

Java Script Code to Find Key code

<script language="JavaScript">
document.onkeydown = checkKeycode
function checkKeycode(e) {
var keycode;
if (window.event) keycode = window.event.keyCode;
else if (e) keycode = e.which;
alert("keycode: " + keycode);
}
</script>


Key Code Reference Table
Key PressedJavascript Key Codebackspace8tab9enter13shift16ctrl17alt18pause/break19caps lock20escape27page up33page down34end35home36left arrow37up arrow38right arrow39down arrow40insert45delete46048149250351452553654755856957a65b66c67d68

Replacing OpenJDK with Oracle JDK in Ubuntu

You can completely remove the OpenJDK and fresh Install Oracle Java JDK by the following steps:
Remove OpenJDK completely by this command:
sudo apt-get purge openjdk-\*Download the Oracle Java JDKhere.
Note: download appropriate file, for example if your system is x64 Ubuntu (i.e, Debian) the download file is named like this: jdk-8u51-linux-x64.tar.gz
To find which version is your OS, check hereCreate a folder named java in /usr/local/by this command:
sudo mkdir -p /usr/local/javaCopy the Downloaded file in the directory /usr/local/java. To do this, cd into directory where downloaded file is located and use this command for copying that file to /usr/local/java/:
sudo cp -r jdk-8u51-linux-x64.tar.gz /usr/local/java/CD into /usr/local/java/ directory and extract that copied file by using this command:
sudo tar xvzf jdk-8u51-linux-x64.tar.gzAfter extraction you must see a folder named jdk1.8.0_51.Update PATH file by opening /etc/profile file by the command sudo nano /etc/profile and past…