SQL Server beginner looking for advice on performance tuning and maintenance

Tags:
SQL Server performance
I am particularly new to database management. I would like to know what I should monitor everyday on SQL Server. We have 40 databases – all the users complain to me that it is very slow. The hardware part is OK. Please tell me how I can tune & maintain databases. Where should I start? It is a real tough task for me. My colleague doesn’t want to share knowledge with me.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I love colleagues like that, big help isn’t it?

Are you sure it’s definitely not a hardware / network problem? My first port of call would always be to check those first, try using perfmon or other tools to monitor the system performance and check for any bottlenecks that are unrelated to SQL Server – even a 3rd party application that could be hogging resource. If it’s definitely SQL Server, you really have a million and one things to go for if you haven’t a clue where it could be coming from… you might want to try using perfmon again on some of the SQL Server counters to check for any problems. Also, it might be useful to use profiler to record a good portion of database activity from various points in the day (particularly when it is slow) you can then see what code is being run and will be able to look at it to see if it looks particularly inefficient etc, this will also lead you down the route of checking your indexes etc to make sure they are well used and efficiently put together. Check when the statistics on the database were last updated (if at all) as this might also be worth doing, without them up to date you’re basically navigating across the country with a 1920s road map.

What version of SQL are you running?

Simon

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • WillC
    Simon has made a good point that you have not identifiy your SQL version(s) so only general answer can be provided. Since you call the databases SQL Server I will translate that to Microsoft and suggest you review these two links: How to: Add or Modify Maintenance Plan Subplan Schedules and Database Engine Tuning Advisor Tutorial My experience tells me the first sign of trouble on MS SQL performance comes when the database is in Full recovery model; found by going into the databse container, right clicking the datebase name and selecting properties. In the properties pane select the Options page amd check the recovery model on the right top. If it is in Full, then you want to make sure you have a backup of your transaction logs as part of your maintenance plan. Simon also mentions using profiler and I would add to that that you may want to narrow down the database choice and possible ethe type of transaction first by interviewing the clients reporting the slowness, and acking them to show you what they are doing when they notice the slowness and even show you the same activity when it is at normal speed. This will help to build a better understanding of you cleint expectations and allow you to notice any contributing conditions like too many other open application on the clients desktop, time of day activities etc...
    170 pointsBadges:
    report
  • Denny Cherry
    I'll have to say that profiler is definitely going to be somewhere that you will want to start out. It will show you your long running queries and the queries which are taking a lot of CPU power. You can then look into the execution plans of these queries and see exactly what tables needs an additional index. I'd have to disagree on the database being in full recovery mode slowing down the system. All full recovery mode does is tell the SQL Server not to purge the transaction log until after the log backup has been completed. If anything having it in full recovery mode decreases system load because the log isn't being truncated every 1-2 minutes. Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,190 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following