aiDeeva.com

Author: Jugal Shah

  • How to check the Index Fragmentation in SQL Server?

    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.

  • How to connect the SQL Server running on the different TCP/IP port?

    In case you have configured SQL Instance to use the static TCP/IP port number. You can connect SQL Server as below using SSMS.

  • How to check waits in SQL Server 2000?

    Today I got a comment, how to check the wait statistics in SQL Server 2000. You can query sysprocesses table and use the DBCC SQLPERF to get the wait statistics in SQL Server 2000.

    select top 5* from sysprocesses
    dbcc sqlperf(‘waitstats’)

    Wait Statistics Image

  • How to use RunAs command for SSMS if option does not exist?

    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/

  • Looking for Job Change?