performance problem in production database

Question

performance problem in production database

d.srikanth50

Say Hey Kid

Points: 693
More actions
April 13, 2010 at 3:00 pm

#213797

friends one production database performance is slow.we have kept that recover model is in simple.if we can modify to full log file reaching its limit.
please guide me how to improve performance of this database.
thanks in advances friends

Viewing 6 posts - 1 through 5 (of 5 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Well, this is a loaded question. The subject of performane monitoring and tuning is vast and detailed. I can give you some guidence as to where to start, but keep in mind that there are people that have full-time jobs just doing this kind of work so there may not a quick fix to your issues.

To fix the problem, you must first identify why things are slow. You say that the system is slow, can you be more descriptive? What does slow mean to you? When is it 'slow', always? Did it just recently become 'slow'?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

LutzM SSC Guru Points: 107049 More actions · Answer 2

Please be a little more specific what you mean by "database performance is slow". AFAIK, a database itself cannot be slow: if there is no activity at all (neither insert nor update nor select) on that database, is it still slow? I guess not...

So, it seems like an issue of some bad performing code or inappropriate hardware (bad coding itself (aka RBAR), blocking, missing index, hardware issues,... almost anything).

You need to narrow down the problem.

One way would be to find out the worst performing queries using sys.dm_exec_query_stats or to run a profiler trace to see what's going on "in general". Hard to tell...

Once some bad performing code is identified, try to tune it. If assistance is needed, please follow the advice given in this article[/url].

Or, answering your question as general as it has been asked (sorry for being a little bit sarcastic): Get someone in to help (maybe a consultant) figuring out the details.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Are you simply referring to the IO performance related to constant log growth or the log file filling up?

If you switch to full recovery, you must enable transaction log backups on a regular schedule.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ps. One Orange Chip Points: 29422 More actions · Answer 4

d.srikanth50 (4/13/2010)
friends one production database performance is slow.we have kept that recover model is in simple.if we can modify to full log file reaching its limit.
please guide me how to improve performance of this database.
thanks in advances friends

performance of a database is not always linked to log file's growth, though database can be slow momentarily when file grows physically.

I suggest u go through Gail's articles to find out which queries are running slow.

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/

Pradeep Singh

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Another article suggestion - this is to find what is causing the log to grow.

http://www.sqlservercentral.com/articles/Log+growth/69476/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events