SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Index Related Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sonu619
Posting Yak Master

195 Posts

Posted - 12/05/2012 :  14:06:53  Show Profile  Reply with Quote
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

USA
37134 Posts

Posted - 12/05/2012 :  14:48:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 12/05/2012 :  15:10:16  Show Profile  Reply with Quote
Thanks tkizer for prompt reply...


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?

Please guide me Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 12/05/2012 :  15:59:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
25 million rows is a small table. I don't think I'd even bother with a filtered index here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 12/05/2012 :  17:25:18  Show Profile  Reply with Quote
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...

Please advise..
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/05/2012 :  17:33:13  Show Profile  Reply with Quote
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?
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 12/05/2012 :  17:35:09  Show Profile  Reply with Quote
76%...

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/05/2012 :  17:47:19  Show Profile  Reply with Quote
Can you please what Query you have on file? and Indexes also so folks over here can guide you through.
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 12/05/2012 :  17:54:21  Show Profile  Reply with Quote
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...
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/05/2012 :  20:14:35  Show Profile  Reply with Quote
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
Go to Top of Page

Sonu619
Posting Yak Master

195 Posts

Posted - 12/05/2012 :  23:00:44  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000