Foreign Keyler'deki Eksik Index'leri bulan sp

by nurgun 13. August 2009 23:57

Genelde geliştirme çok az veri ile yapıldığı için geliştirme fazında performans sorunu yaşanmaz.

Performans sorunları genelde stres testinde veya son kullanıcı sisteminde kurulum yapıldıktan sonra ortayı çıkar. 

Alttaki sp ile hızlı şekilde eksik indeksleriniz bulabileceksiniz. Indeks yaratırken gerçekten o indekse ihtiyaç var mı ? 

Sorusunu da cevaplamaya çalışın. Küçük tablolara "Table Scan" yapılması daha performanslı olabiliyor !

Indekslerin IDX1_ ile başlamasını sağladım, bu şekilde sonradan yaratılan indekslerin listesini hemen bulabileceksiniz.

Mutlaka indeks olmadan ve indekslerin yaratılmasından sonra performans karşılaştırması yapınız. 

Kolay gelsin,

 

create proc us_create_fk_index
as
DECLARE @sqlStmt varchar(max) 
SELECT @sqlStmt = IsNull(@sqlStmt + ';' + char(13) , '') 
      +  'CREATE INDEX [IDX1_' 
      + tablename + '_' + columnname +'] ON [dbo].[' 
      + tablename + '] ( [' + columnname + '] ASC)'
FROM 
(
      SELECT
        o.name AS tablename, c.name AS columnName FROM sys.foreign_key_columns fc
        inner join sys.objects o on fc.parent_object_id = o.object_id
        inner join sys.columns c on c.object_id = o.object_id 
                        and fc.parent_column_id = c.column_id
    EXCEPT 
        SELECT o.name, c.name  FROM sys.index_columns ic
            inner join sys.objects o on ic.object_Id = o.object_id
            inner join sys.columns c on c.object_id = o.object_id 
                        and ic.column_id = c.column_id
) T
ORDER BY 
    tableName, columnName
SELECT @sqlStmt

--THE END

Bu sp'yi çalıştırdıktan sonra sonucun son satırına dikkat ediniz. Eğer çok fazla eksik indeksleriniz var ise

son satır yarım gelebilir. Indeksleri yarattıktan sonra sp'yi tekrar çalıştırınız.


 


 

 

Tags: , , ,

MS SQL Performans

Comments

4/23/2010 4:43:21 AM #

shower invitation

I personally have embraced the new technologies and the CMS platforms, I think the new tools only make the web designs better. I am glad that new technologies are coming out in web design that make things easier, improved, and better looking for design.

shower invitation United States |

4/25/2010 10:52:36 AM #

doctors coats

This is one of the most incredible blogs Ive read in a very long time.  The amount of information in here is stunning, like you practically wrote the book on the subject.  Your blog is great for anyone who wants to understand this subject more.  Great stuff; please keep it up!

doctors coats United States |

4/27/2010 11:46:57 PM #

Rapidshare

I\'m happy I found this blog, I couldnt discover any info on this subject matter prior to. I also run a site and if you want to ever serious in a little bit of guest writing for me if possible feel free to let me know, i\'m always look for people to check out my site. Please stop by and leave a comment sometime!

Rapidshare United States |

4/27/2010 11:50:20 PM #

answer spots

This is a smart blog.  I mean it.  You have so much knowledge about this issue, and so much passion.  You also know how to make people rally behind it, obviously from the responses.  Youve got a design here thats not too flashy, but makes a statement as big as what youre saying.  Great job, indeed.

answer spots United States |

4/30/2010 11:43:41 PM #

Toywatch

Great stuff from you, man.  Ive read your stuff before and youre just too awesome.  I love what youve got here, love what youre saying and the way you say it.  You make it entertaining and you still manage to keep it smart.  I cant wait to read more from you.  This is really a great blog.

Toywatch United States |

5/2/2010 8:50:36 PM #

buy cigarettes

This is one of the most incredible blogs Ive read in a very long time.  The amount of information in here is stunning, like you practically wrote the book on the subject.  Your blog is great for anyone who wants to understand this subject more.  Great stuff; please keep it up!

buy cigarettes United States |

5/2/2010 9:38:30 PM #

pearl wedding anniversary gifts

What youre saying is completely true.  I know that everybody must say the same thing, but I just think that you put it in a way that everyone can understand.  I also love the images you put in here.  They fit so well with what youre trying to say.  Im sure youll reach so many people with what youve got to say.

pearl wedding anniversary gifts United States |

5/17/2010 5:35:23 AM #

pingback

Pingback from topsy.com

Twitter Trackbacks for
        
        MSSQL ve Oracle Blog, MSSQL, Oracle, Telekom | Foreign Keyler'deki Eksik Index'leri bulan sp
        [nurgun.net]
        on Topsy.com

topsy.com |

5/28/2010 9:21:30 PM #

low voltage landscape lighting

The next time I read a blog, I hope that it doesnt disappoint me as much as this one.  I mean, I know it was my choice to read, but I actually thought youd have something interesting to say.  All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention.

low voltage landscape lighting United States |

5/29/2010 9:35:49 PM #

Hi Tech Gadgets

Your blog is outrageous!  I mean, Ive never been so entertained by anything in my life!  Your vids are perfect for this.  I mean, how did you manage to find something that matches your style of writing so well?  Im really happy I started reading this today.  Youve got a follower in me for sure!

Hi Tech Gadgets United States |

5/31/2010 5:59:03 PM #

casino en ligne francais

Salut Vey im Nice intéressant blog de l'Ecluse J'ai trouvé ceci sur google je suis dans la location de grues affaires de l'entreprise, j'ai trouvé ce blog très intéressant bonne chance avec elle, je reviendrai sur ce blog prochainement. C'est certainement mon arrêt initial par et j'aime vraiment ce que je vois. Votre blog est tellement agréable à regarder par-dessus, tout à fait convaincant, ainsi que d'information. Je vais sans aucun doute le recommander à mes amis. Néanmoins, j'ai eu un problème avec le commentaire. Il me donnait un problème quand je clique sur publier un commentaire. Je l'espère, qui peut être fixée. Merci beaucoup

casino en ligne francais France |

5/31/2010 11:09:19 PM #

motorcycle insurance washington

For what its worth, the layout is definitely amazing.  You know how to balance writing and images/videos.  However, I cant get over how little you actually bring to light here.  I think that everyones said the same thing that youve said over and over again.  Dont you think its time for something more?

motorcycle insurance washington United States |

6/1/2010 12:20:41 AM #

profit instruments

Cool place of duty.

profit instruments United States |

7/17/2010 7:31:24 PM #

pingback

Pingback from assoutcash.com

Todd Baker | Making money the affiliate way

assoutcash.com |

9/12/2010 10:33:00 PM #

pingback

Pingback from robfortner82.wordpress.com

The week in Android News « Rob Fortner's Blog

robfortner82.wordpress.com |

Nurgün Özgür

Facebook

 

Nurgün Özgür Photo

1993'ten bu yana profesyonel olarak yazılım ve sistem yönetimi konularında çeşitli yerli ve yabanci kuruluşların farklı kademelerinde aktif olarak yer aldım. 2011 yılında emekliliğe ayrıldım.

 

Valid XHTML 1.0 Transitional


Tag cloud


Facebook ile giriş