SQL Server Performance

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

Discussion in 'SQL Server 2005 General DBA Questions' started by bsethi24, Jan 17, 2011.

  1. bsethi24 New Member

    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
    /*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/
  2. satya Moderator

    The performance tuning process is bit different to what you read in books or theoritical knowledge.
    Your approach on the specification is right in terms of process, as SQL SErver 2005 onwards DMV will always come handy to know what and where exactly going wrong, in addition to the third party tools. See Brad's article here http://www.databasejournal.com/feat...formance-Tuning-for-SQL-Server-Developers.htm on how to approach the performance tuning techniques.
  3. bsethi24 New Member

    Dear Satya,
    Thanks for the reply. But, again a complete article with theoretically details. I need PRACTICAL APPROACH, on which Columns of a table & when we need to create Indexes to gain top performance from SQL Server?
    You can take Cursor example mentioned in my post to guide me.
    Thanks & Regards,
    bsethi24
  4. RamJaddu Member

Share This Page