| Author |
Topic |
|
Sonu619
Posting Yak Master
202 Posts |
 Posted - 2012-12-05 : 14:06:53
|
| Hi Guys, I am working on task where i am optomizing SP. I have few specific questions. 1) In Index window when we add Column to create index on, Is Sord Order is Important? it was Assending, if i change it to Descending what do u think i can see any improvement.2) I can use Filter Option on existing Index?Thank you if some one reply back ASAP.Once again Thanks in advance.. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-12-05 : 15:10:16
|
| Thanks tkizer for prompt reply...I have table Preety big close to 25m Records. I don't want to do table partionhowever i would like to do Index partition. I have data in this table from last 8 years. User ver rear chance go and check the data last 8 years so i want to filter index.I have 1 clustered and 6 non cluster index on this tableand i want to filter my index by WHERE Year(Servicedate) = '2005'Should i use filtered on all existing Indexes or one is enough?Please guide me Thanks. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-12-05 : 17:25:18
|
| I agree its not a big table. For feature reference could you please answer my question on last post?One other Guidence. I Create a file on SQL Profiler that i am going to use on "Database Engine Tuning Advisor" after some analyze they create a report with some recommendation and as you know most of recommendations are create Statistics or Create Non Clustered Index. My question is for you what do you think its a good ideaif i follow all recommendation... Please advise.. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-05 : 17:33:13
|
| You shouldn't rely completely on DTA. Best way is to analyze the query and execution plans and then apply them so it is effective. What percentage improvement does it suggest? |
|
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-12-05 : 17:35:09
|
| 76%... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-05 : 17:47:19
|
| Can you please what Query you have on file? and Indexes also so folks over here can guide you through. |
|
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-12-05 : 17:54:21
|
| I went to front end and do some random test. In the test they ran lot of store procedure and function and other thing.Behind the seeni am saving all steps in SQL Server Profiler, once i done after 5m i stop profile and save file. Then i use this "Tuning" file in Database Engine Tuning Advisor. Thats all information i have. If you wanna know each step and store procedure. I hope you understand its impossibleto provide you all that information.If someone go through this process he should know what i am taking about... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-05 : 20:14:35
|
| If the performance improvement is 76% then i would analyze with missing index report in performance dashboard and DMVs and Intensive query and then apply indexes for which i know it will be helpful |
|
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2012-12-05 : 23:00:44
|
| Sodeep if i understand right, In simple wording "IF Database Engine Tuning Adviser" are Recommending Missing Indexes and statistics that will improve 76% Performance don't close your eyes and create those Missing Indexes and statistics" am i right? |
|
|
|
|
Index Related Question