October 27, 2008 at 12:58 am
Dear All,
Sometimes my database goes very slow. I would like to finetune the database.
Can anyone suggest wot are the things to be done please.?
Also I have designed a database for our new ongoing projects.
but I have not taken any precaution against data loss etc,. Plese advice on this.
Also there are many tables, procedures, indexes etc which are not used at all.
Is there any way to find out unused/rarely used objects in the database..??
Thanks in advance,
Santhu.
October 27, 2008 at 1:16 am
nairsanthu1977 (10/27/2008)
Dear All,Sometimes my database goes very slow. I would like to finetune the database.
Can anyone suggest wot are the things to be done please.?
That's a very open question.
Make sure all the queries are written optimally. Make sure there are indexes to support those queries. You can use profiler to find long running queries. You can then run those in management studio, use the execution plan and see where indexes are needed, where code can be tweaked.
Also I have designed a database for our new ongoing projects.
but I have not taken any precaution against data loss etc,. Plese advice on this.
Regular backups? Fulls are essential, log backups also if you're in full or bulk-logged recovery.
Also there are many tables, procedures, indexes etc which are not used at all.
Is there any way to find out unused/rarely used objects in the database..??
No easy way. You can put a query into the procedures so that they log when they were used. You can use profiler to see when things are accessed. The info's not stored anywhere by SQL.
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 27, 2008 at 3:21 am
Thank you.
Infact, wot had happend is, I was not in this project initialy. So developers created tables and procedures left & right and made it completely a mess. It was not even normalised and there was no relationships at all among tables. I have almost rebuild the database now and seems to somewhat ok. Still there are unwanted objects. thats why I asked.
Anyways thanks a lot.
Santhu.
October 27, 2008 at 5:48 am
Run a trace to capture the performance and use of the procedures. That will assist you in determining needed indexes, etc. It's especially important to do this when you're coming along after the fact.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 28, 2008 at 9:02 am
1) An exhaustive (and I mean VERY detailed) review of ALL code is the only way to determine what objects are/may be referenced.
2) If you don't already know where to start to tune your application, I strongly recommend that you hire a performance tuning consultant to review your systems and applications. Things will be improved very quickly and efficiently and you get the benefit of being mentored on how to do this as they go about their job.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 28, 2008 at 7:23 pm
nairsanthu1977 (10/27/2008)
Also there are many tables, procedures, indexes etc which are not used at all.Is there any way to find out unused/rarely used objects in the database..??
I would advise against this. I've tried in the past to get rid of "unused" tables/procedures in the database and it has ALWAYS backfired.
As TheSQLGuru said, the only way to do this is a thorough analysis of ALL code.
However, even with such analysis something could be (and probably will be) missed!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply