January 17, 2011 at 7:01 am
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
/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/
January 17, 2011 at 7:07 am
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.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply