October 24, 2008 at 5:18 am
We have a new production database,day to day the size of the database is increasing and after two months size of the database is too large and performance of the database is degrade. So, what should i do for better performance.
October 24, 2008 at 5:55 am
The database size isn't related to performance directly. Increasing amounts of data in poorly indexed tables may be.
The first thing to realise is there there's no silver bullet to fix performance. There's no 'run queries fast' option or switch.
The first thing you need to do is identify the badly performing queries. SQL Profiler is good for this. Run profiler for an hour or so during a busy time. Capture the RPC:completed and SQL:Batch Completed events and make sure that, at a minimum, you get the textdata, duration, cpu and reads for the events. Once the trace has finished save it to a database table and find the top 5 or so worst performing queries. Take them and see hwat you can do to get them running faster. It may require indexes, it may require rewriting badly written code, it may require both.
Once you've fixed those top 5, use profiler again to get another trace, see what's improved and get another 5 or so queries to tune. Repeat until DB performance is acceptable.
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 24, 2008 at 5:57 am
Not exactly knowing how you manage your database, consider:
- statistics are created and updated
- indexes are analyzed and rebuilded/reorganized
- check for fragmentation (logical/physical/disk)
Also:
- check the location of your data- and logfiles. For better performance, spread them over separate PHYSICAL disks
- The same goes for tempdb. Increase the number of datafiles for the tempdb to the number of CPU cores
- check your memory usage. Maybe it's to less
- Check your databases for missing indexes. If your database grows this will become a bigger problem
My 2cents so far.
Wilfred
The best things in life are the simple things
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply