October 1, 2012 at 10:26 am
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 ?
October 1, 2012 at 1:09 pm
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
October 2, 2012 at 11:09 am
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.
October 2, 2012 at 11:36 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2012 at 11:45 am
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
October 2, 2012 at 11:46 am
.. Was blind... din't notice "datawarehousing" environment. Thanks Gila.
October 2, 2012 at 10:33 pm
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.
October 3, 2012 at 1:03 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2012 at 7:52 am
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.
October 3, 2012 at 9:52 am
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!
October 3, 2012 at 2:56 pm
Miles Neale (10/3/2012)
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.
Aside from the usual "Increased maintenance, increased overhead, increased disk space, increased backup size, increased rebuild/recreate time", I'd say the actual worst case is that the optimizer will have a rare case where the best plan for a huge query is very close to the last plan the optimizer is willing to spend the time to investigate, and then when you add a bunch of other indexes, that plan is still the best... but now the optimizer no longer considers it because it spend some of its time budget considering some alternate case that wasn't as good.
This should be extremely rare, but I have seen queries where the optimizer picks a very suboptimal index.
October 4, 2012 at 12:12 am
In this senario we can create multiple indexes . Creating multiple index with diffrent combination of columns is nothing but providing more option to the optimizer to plan better and better excution plan.
With the help of following query we can know is our indexes are helping optimizer or not.
--select * from HumanResources.Department
/*It will usefull when you directly run it with the production database
Note : it will give zero result if your database is just started and no any script run, because
when database is start all old index information will delete by msss engine.
*/
/***********Procedure to analysis the indexes in specified database****************************/
--exec index_analysis 'info_testTbl','AdventureWorks'
--exec index_analysis
Create procedure Index_analysis(@Table_name varchar(200)=null,@Database_name varchar(100)=null )
As
Begin
if @Database_name is null
set @Database_name=(select DB_NAME())
If @Table_name is null
begin
SELECT
OBJECT_NAME(s.object_id) AS 'Table Name'
,idx.name as 'Index name'
,idx.type_desc as 'Index type'
,s.last_user_update
,s.last_system_update
,s.user_seeks
,s.user_scans
,s.user_lookups
,s.user_updates
,s.system_seeks
,s.system_scans
,s.system_lookups
,s.system_updates
,(s.user_seeks +s.user_scans+s.user_lookups+s.system_seeks+s.system_scans+s.system_lookups) as 'Total Use'
,(s.system_updates+s.user_updates) as 'Total Update'
FROM sys.dm_db_index_usage_stats s
inner join sys.indexes idx
on idx.object_id=s.object_id and idx.index_id= s.index_id
WHERE database_id = DB_ID( @Database_name) order by s.last_user_update desc
End
Else
begin
SELECT
OBJECT_NAME(s.object_id) AS 'Table Name'
,idx.name as 'Index name'
,idx.type_desc as 'Index type'
,s.last_user_update
,s.last_system_update
,s.user_seeks
,s.user_scans
,s.user_lookups
,s.user_updates
,s.system_seeks
,s.system_scans
,s.system_lookups
,s.system_updates
,(s.user_seeks +s.user_scans+s.user_lookups+s.system_seeks+s.system_scans+s.system_lookups) as 'Total Use'
,(s.system_updates+s.user_updates) as 'Total Update'
FROM sys.dm_db_index_usage_stats s
inner join sys.indexes idx
on idx.object_id=s.object_id and idx.index_id= s.index_id
WHERE database_id = DB_ID( @Database_name) and s.object_id=object_id(@table_name)
End
End
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply