InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 15:28:15 GMT20http://www.sqlservercentral.com/Forums/Topic1048741-360-1.aspxCreate 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.[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]Mon, 17 Jan 2011 07:07:44 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1048741-360-1.aspxDear 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.[b]Can anyone suggest PRACTICAL Approach to know on which Columns of XYZ Table & when we have to apply Indexes?[/b]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~~NullableSchoolCollegeCode~~varchar~~5~~noSchoolCollegeName~~varchar~~100~~noStntPkey~~int~~4~~noStudent_No~~varchar~~16~~noSchoolCollege_Pkey~~int~~4~~noImageAddress~~varchar~~500~~yesIsExists~~varchar~~10~~yesI 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 trandeclare @FileNamewithCompletePath varchar(500)declare @StudentPkey intdeclare @SchClgePkey intdeclare @Check intset @FileNamewithCompletePath = ''set @StudentPkey = 0set @SchClgePkey = 0set @Check = 0declare PhotoExistsPhysicallyorNot cursor forselect ImageAddress, StntPkey, SchoolCollege_Pkey from XYZ /*where SchoolCollege_Pkey = 1 and StntPkey<=500 */order by SchoolCollege_Code, Student_Noopen PhotoExistsPhysicallyorNotfetch next from PhotoExistsPhysicallyorNot into @FileNamewithCompletePath, @StudentPkey, @SchClgePkeyWHILE @@FETCH_STATUS = 0BEGINexec master.dbo.xp_fileexist @FileNamewithCompletePath, @Check outputIf (isnull (@Check, 0)=1) begin Update XYZ set IsExists = 'True' where SchoolCollege_Pkey = @SchClgePkey and StntPkey = @StudentPkey set @Check = 0 endelse begin Update XYZ set IsExists = 'False' where SchoolCollege_Pkey = @SchClgePkey and StntPkey = @StudentPkey set @Check = 0 end--if(@@Error<>0)beginprint 'Error on ' + convert(varchar,@FileNamewithCompletePath)endfetch next from PhotoExistsPhysicallyorNot into @FileNamewithCompletePath, @StudentPkey, @SchClgePkeyend--select @PPkeyCLOSE PhotoExistsPhysicallyorNotDEALLOCATE PhotoExistsPhysicallyorNotrollbackcommit/*--<><>----<><>----<><>----<><>----<><>----<><>----<><>----<><>--*/Mon, 17 Jan 2011 07:01:25 GMTbharat sethi