Step 1: Launch SQL Server Management Studio and In the Object Explorer expand the Management folder.

Step 2: Right click on the Maintenance Plans folder and select New Maintenance Plan.

Step 3: Give the meaning full name to maintenance plan.

Step 4: From the Toolbox drag and drop a Check Database Integrity Task, Rebuild Index Task, Update Statistics Task and place them vertically in the same order.

Step 5: Connect the tasks together by dragging the arrow from one box to the other so they are connected as: Check Database Integrity Task – Rebuild Index Task – Update Statistics Task.

Step 6 Right click on the each tasks and select the databases in maintenance plan.

Step 7 Schedule a maintenance plan to run in off business hours.

Step 1: Launch SQL Server Management Studio.

Step 2: In the object explorer, right click on the database and select Reports -> Standard Reports -> Index Physical Statistics.

Step 3: SQL Server Management Studio will generate a report showing information about the Table Names, Index Names, Index Type, Number of Partitions and Operation Recommendations.

Step 4: Repeat the above steps to check the fragmentation of all user databases.

One key value that is provided in the report is the Operation Recommended field. Any value of Rebuild is an indication that the index is fragmented.

By expanding the # Partitions field, you can see the % of fragmentation for a given index.

Report looks like below.

Problem

As a best practice in the industry, a DBA often has two logins that are used to access SQL Server; one is their normal Windows login and the other is an admin level login account which has sysAdmin rights on the SQL Server boxes. In addition most of the time the SQL Server client tools are only installed on the local desktop and not on the SQL Server Production Box. In order to use the different login to connect to SQL Server using SSMS you need to use the “Run as” feature. What do you do in the case of Windows 7 or Windows Vista where you can’t find the Run As Different User option.

Solution

http://www.mssqltips.com/sqlservertip/2617/how-to-use-runas-command-for-ssms-if-option-does-not-exist/