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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » PRACTICAL APPROACH to know On which columns...

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

Rate Topic

Display Mode

Topic Options

Author

Message

bharat sethi

Posted Monday, January 17, 2011 7:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 5:33 AM
Points: 44, Visits: 62

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
/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/

Post #1048741

GilaMonster

Posted Monday, January 17, 2011 7:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:31 PM
Points: 38,091, Visits: 30,386

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 2008, MVP
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

Post #1048745

« Prev Topic | Next Topic »

Permissions