<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Indexes question
12 posts, Page 1 of 2
1
2
»»
Indexes question
Rate Topic
Display Mode
Topic Options
Author
Message
sunny.tjk
sunny.tjk
Posted Monday, October 01, 2012 10:26 AM
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:26 PM
Points: 263,
Visits: 866
Hello everyone,
In our datawarehousing environment, every morning indexes are dropped and then data is loaded into the database and finally the indexes are recreated(which were dropped prior to the loads).
So, in an environment like this, can I add as many indexes as possible to enhance the performance ?
Post #1366652
GSquared
GSquared
Posted Monday, October 01, 2012 1:09 PM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442,
Visits: 9,571
You can add as many indexes as you have disk space for, pretty much. But don't count on that improving performance. Add the indexes you need, and no more than that. You can tell what indexes you'll need based on what queries are run most frequently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1366722
Joy Smith San
Joy Smith San
Posted Tuesday, October 02, 2012 11:09 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:35 PM
Points: 1,371,
Visits: 2,301
You can also utilize the index related DMVs to decide on which all index to create/keep or which all to remove.
(based on read/write ratio etc; ). As mentioned above, indexes will degrade performance so badly if not handled properly. Also go through the details of filtered indexes, included columns and indexed views etc; as well. You will get some idea on how to go about it. Further helps you will surely get from the portal like this.
Post #1367188
GilaMonster
GilaMonster
Posted Tuesday, October 02, 2012 11:36 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730,
Visits: 29,996
Joy Smith San (10/2/2012)
As mentioned above, indexes will degrade performance so badly if not handled properly.
Indexes won't degrade a read-only workload. They may not help, but they won't harm.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1367199
GSquared
GSquared
Posted Tuesday, October 02, 2012 11:45 AM
SSCoach
Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442,
Visits: 9,571
Yes. The reason to not add indexes you don't need isn't because of performance degredation. It's because you'll be wasting drive space on storage, and CPU cycles on building/maintaining them (if that's not a one-time thing).
Too many indexes might result in problems on execution plan optimization, but that's unusual, and SQL Server is pretty good at avoiding it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1367204
Joy Smith San
Joy Smith San
Posted Tuesday, October 02, 2012 11:46 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:35 PM
Points: 1,371,
Visits: 2,301
.. Was blind... din't notice "datawarehousing" environment. Thanks Gila.
Post #1367205
Joy Smith San
Joy Smith San
Posted Tuesday, October 02, 2012 10:33 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:35 PM
Points: 1,371,
Visits: 2,301
In our datawarehousing environment, every morning indexes are dropped and then data is loaded into the database and finally the indexes are recreated(which were dropped prior to the loads).
So, in an environment like this, can I add as many indexes as possible to enhance the performance ?
Just a simple question to all...
In the scenario like above, for better performance, Isn't it advisable to DISABLE and REBUILD the indexes instead of dropping and recreating ?
Just want to clarify.
Post #1367395
GilaMonster
GilaMonster
Posted Wednesday, October 03, 2012 1:03 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730,
Visits: 29,996
Not really, the only difference between drop/create and disable/rebuild is that the latter preserves the definition of the index on the system tables. Performance-wise they should be identical as they do mostly the same thing - remove the index b-tree and then later recreate it.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1367420
Antares686
Antares686
Posted Wednesday, October 03, 2012 7:52 AM
SSCrazy Eights
Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357,
Visits: 684
Actually I didn't see anything on how the data warehouse is setup.
Do you have any tables that are partitioned?
How much data are you removing each run?
How much data are you adding each run?
Do you have a clustered index on the table and does it get dropped each day?
How many rows are in the table total?
What version of SQL Server?
There are other things you could consider for improving performance and loading. And yes an index can in fact degrade performance, my most common expeirence with that is composite key indexes where the keys are not in optimal order for usage. But if you are looking to really improve performance try to build non-clustered indexes on a seperate drive array that is on a seperate controller from the data. This improves read times more so that same drive array.
Post #1367688
Miles Neale
Miles Neale
Posted Wednesday, October 03, 2012 9:52 AM
SSCommitted
Group: General Forum Members
Last Login: Monday, May 20, 2013 9:51 AM
Points: 1,891,
Visits: 936
The way I see this is yes you can add as many indexes as possible to enhance the performance. But not all will speed things up. Best case is that you can build some that will greatly speed things up. Worst case is you will build some that the optimizer will not find valuable and will not use. These will suck time, space, and cycles as they are built and will add nothing.
What I read behind the question is can I build indices and hope that they will be used and will make the datawarehouse sail right along? And I would respond that you can build what you want but recreational index building is not the goal, enhancing the speed and accuracy of user access to the data is. Analyze, build, evaluate, and repeat till speed is acceptable.
Not all gray hairs are Dinosaurs!
Post #1367817
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
