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.
1. Yes it can be. Ascending is the default and is usually what's used. If your query requires the data in descending order, descending order of the index might be a little faster. I'd probably still use ascending though. 2. You can drop/create it.
I have table Preety big close to 25m Records. I don't want to do table partion however 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 table and i want to filter my index by WHERE Year(Servicedate) = '2005'
Should i use filtered on all existing Indexes or one is enough?
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 idea if i follow all recommendation...
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?
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 seen i 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 impossible to provide you all that information.
If someone go through this process he should know what i am taking about...
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
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
Posted - 12/05/2012 : 14:06:53
