where can I start with performance Improvement for SQL 2008

Question

where can I start with performance Improvement for SQL 2008

mpalaparthi

Say Hey Kid

Points: 688
More actions
September 20, 2010 at 7:01 am

#229421

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 1

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 2

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

Tim Parker SSCommitted Points: 1644 More actions · Answer 3

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.

😀

mpalaparthi Say Hey Kid Points: 688 More actions · Answer 4

Thank you all for your valuable replies.

Great Regards

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

These may be of interest

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass