SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
bharat sethi
bharat sethi
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 84
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
/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87485 Visits: 45272
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search