September 20, 2010 at 7:01 am
Hello SQL Server Experts,
I am SQL Server DBA. I have a typical situation here. I have a database size of 7GB and the total number of objects are around 1000 including tables, stored procedures, functions and triggers. My application is still under testing stage, it's not rolled into production yet. My task is to optimize the sql server performance. Right now there is not much data in the tables (max data rows are about 2000). All it has is legacy data
I started with Index optimization first and when I checked initial fragmentation, it was 100% and when I ran the rebuild index command for all the indexes it was reduced to 80%, it is not going below that level. I know there are duplicate Indexes (i.e non clustered index on a primary key etc..) and there are some un-used indexes also. My question is can I remove those unsed and duplicate Indexes, bacause this application is not rolled in to production, so I am not sure if these Indexes are useful in future. I am new to this job and I am at basic level as a sql server dba. Also I read an articule that some time the Index fragmentation will be high if the data in the database is low and once the data is start adding then the fragmentation will be reduced.
My questions are:
1) Does it sounds good for a DB size of 7GB there are 1000 objects. This is not a critical application. how do I know If there any unsed objects, is there any script for that.
2) Where can I start my work of optimizing the DB. I don't think at this momet I cant change the dataabse design because the it was already developed and If I made any modifications in the database design I have to made modification at lot of other places (like in application code etc..)
Kindly give all your feedback. I really appreciate your comments and suggestions
Thank you so much
September 20, 2010 at 8:05 am
mpalaparthi (9/20/2010)
Hello SQL Server Experts,My questions are:
1) Does it sounds good for a DB size of 7GB there are 1000 objects. This is not a critical application. how do I know If there any unsed objects, is there any script for that.
2) Where can I start my work of optimizing the DB. I don't think at this momet I cant change the dataabse design because the it was already developed and If I made any modifications in the database design I have to made modification at lot of other places (like in application code etc..)
Kindly give all your feedback. I really appreciate your comments and suggestions
Thank you so much
1. This like most thing in SQL all depends...
2. You are correct on the fragmentation....if the table is small then rebuilding the index is not going to help on the Frag, part.....you should be looking at tables with more then 100 pages....the best number is over 1000.... but on a small db start with 100 as the DB begins to grow this will give you a place to focus...
3. yes drop any index that is a duplicate....as this is going to cause more inserts and will hurt performance
September 20, 2010 at 8:21 am
after you get it up and running run this script, this will tell you the insert vs the seeks/scans.... only keep and index if you have more seeks then inserts....some may be around 50/50 then you will need to make a judgemnt if the index is worth keeping...this will also give you the drop index statement if choose to you it or not
SELECT o.name AS _name,c.name as col,i.object_id, i.name AS index_name
, i.type_desc, u.user_seeks, u.user_scans, u.user_lookups
, u.user_updates, u.last_user_seek, u.last_user_scan
, 'Drop index ' + i.name + ' on ' + o.name as DropIndexStatement
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u ON i.object_id = u.object_id
AND i.index_id = u.index_id
join sys.index_columns ic on ic.object_id = i.object_id
and ic. index_id = i.index_id
join sys.columns c on c.object_id = ic.object_id
and c.column_id = ic.column_id
AND u.database_id = DB_ID()
WHERE o.type <> 'S'
and isnull(u.user_updates,0) > 0
--and i.type_desc <> 'HEAP'
and i.type_desc = 'NONCLUSTERED'
--and o.name = 'CMSBrowse_Tree'
ORDER BY u.user_seeks, o.name, (convert(decimal(19,4),ISNULL(u.user_seeks, 0))
+ ISNULL(u.user_scans, 0)
+ ISNULL(u.user_lookups, 0))/ISNULL(u.user_updates, 0) asc
, user_updates desc, i.name
September 20, 2010 at 8:36 am
Well you have already started with the first thing that needs to be addressed when improving performance in a database. In many cases, regardless of memory or the disk subsystem on your server, proper indexes can be the single biggest gain in performance you can make, so good job there.
As for duplicate indexes, I aggree you should reduce this as much as possible. When you have a clustered index in place, along with non-clustered indexes, you will increase your updates to the table exponentially. Since non-clustered indexes have pointers to the clustered index, each non-clustered index must be subsequently updated each time a row is added or deleted in the clustered index.
As a new SQL DBA, become familiar with using SQL Server profiler to establish performance baselines in your database. As well as establising these baselines, you can also utilize the tuning advisor to help gain insight into what changes can improve performance.
😀
September 20, 2010 at 8:39 am
Thank you all for your valuable replies.
Great Regards
September 21, 2010 at 7:29 am
Tim Parker (9/20/2010)
Well you have already started with the first thing that needs to be addressed when improving performance in a database. In many cases, regardless of memory or the disk subsystem on your server, proper indexes can be the single biggest gain in performance you can make, so good job there.
Let's hope the op wrote "sargable" code where the indexes can actually be used effectively.
--Jeff Moden
September 21, 2010 at 7:34 am
456789psw (9/20/2010)
after you get it up and running run this script, this will tell you the insert vs the seeks/scans.... only keep and index if you have more seeks then inserts....some may be around 50/50 then you will need to make a judgemnt if the index is worth keeping...
I would strongly not suggest doing that without further analysis. That DMV only keeps data since the last server start. If there are indexes that are critical to a monthly process and the DB's been up for 3 weeks, that DMV may show that they are not in use.
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
September 21, 2010 at 7:36 am
These may be of interest
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply