PRACTICAL APPROACH to know On which columns of a table & when we have to apply indexes?

  • Dear All,

    I know that by default Microsoft SQL creates Clustered Indexes on Primary Key fields of a table & if some performance issue is there to extract data from Table then we have to check on which field we can create Non-Clustered index & implement the same to improve the performance.

    Above-mentioned paragraph is completely based on knowledge gained theoretically.

    Now, my doubt is related with Practical working.

    In Practical, how can we know that XYZ Table has performance issue & on this-this columns of XYZ table, we have to create indexes & in index definition which columns we have to define in INCLUDE COLUMN definition.

    Can anyone suggest PRACTICAL Approach to know on which Columns of XYZ Table & when we have to apply Indexes?

    For Example: -

    I have a cursor to check file exists on server or not. If exists then updates IsExists field by TRUE else by FALSE.

    /*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

    Table used in this Cursor is XYZ TABLE & Structure of this Table: -

    Column_name~~Type~~Length~~Nullable

    SchoolCollegeCode~~varchar~~5~~no

    SchoolCollegeName~~varchar~~100~~no

    StntPkey~~int~~4~~no

    Student_No~~varchar~~16~~no

    SchoolCollege_Pkey~~int~~4~~no

    ImageAddress~~varchar~~500~~yes

    IsExists~~varchar~~10~~yes

    I have no Index on this table.

    /*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

    After run the below cursor for DUMMY Values, there is no row exists in any of the below mentioned DMVs used to Identify Missing Indexes: -

    /*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

    sys.dm_db_missing_index_details

    sys.dm_db_missing_index_group_stats

    sys.dm_db_missing_index_groups

    sys.dm_db_missing_index_columns

    /*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

    The code of cursor: -

    /*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

    begin tran

    declare @FileNamewithCompletePath varchar(500)

    declare @StudentPkey int

    declare @SchClgePkey int

    declare @Check int

    set @FileNamewithCompletePath = ''

    set @StudentPkey = 0

    set @SchClgePkey = 0

    set @Check = 0

    declare PhotoExistsPhysicallyorNot cursor for

    select ImageAddress, StntPkey, SchoolCollege_Pkey from XYZ /*where SchoolCollege_Pkey = 1 and StntPkey<=500 */

    order by SchoolCollege_Code, Student_No

    open PhotoExistsPhysicallyorNot

    fetch next from PhotoExistsPhysicallyorNot into @FileNamewithCompletePath, @StudentPkey, @SchClgePkey

    WHILE @@FETCH_STATUS = 0

    BEGIN

    exec master.dbo.xp_fileexist @FileNamewithCompletePath, @Check output

    If (isnull (@Check, 0)=1)

    begin

    Update XYZ set IsExists = 'True' where SchoolCollege_Pkey = @SchClgePkey and StntPkey = @StudentPkey

    set @Check = 0

    end

    else

    begin

    Update XYZ set IsExists = 'False' where SchoolCollege_Pkey = @SchClgePkey and StntPkey = @StudentPkey

    set @Check = 0

    end

    --

    if(@@Error<>0)

    begin

    print 'Error on ' + convert(varchar,@FileNamewithCompletePath)

    end

    fetch next from PhotoExistsPhysicallyorNot into @FileNamewithCompletePath, @StudentPkey, @SchClgePkey

    end

    --select @PPkey

    CLOSE PhotoExistsPhysicallyorNot

    DEALLOCATE PhotoExistsPhysicallyorNot

    rollback

    commit

    /*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

  • Create indexes on columns used in the where clause and on join columns.

    As for practical...

    Run the query, see what the execution plan looks like.

    Add an index.

    Run the query, see what the execution plan looks like, see if it's using your new index.

    Sounds vague, but that's pretty much how it's done.

    These may help.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply