Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
willianto
Starting Member
5 Posts |
 Posted - 2011-08-02 : 09:05:38
|
| Hello all,I have an application that approximately every 10 seconds would insert several rows into a database. There are about 90 tables, and the it serve 24/7. In the working hours, the application insert transactions from local store. In night time, it receive bunch of data from five branches then an application would create and send report in e-mail. The database run on MS SQL Server 2005 Standard Edition and has been live for almost a year by now, and the MDF now has grown to 3.5GB while the LDF is currently 39 MB. What I do to maintain it now is I run SQL Profiler once in a while and tune the index with Database Tuning Wizard.I could say that the the application run fine. However report creation, um, well, so-so... There are some reports that need almost a five minutes to complete (one table has more than 3 million rows). And as the time goes by, seems like it needs more and more timeQuestion: Is there any way that I could optimze the database?regards,Willianto |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-08-02 : 10:00:38
|
It sounds like you need to optomize the report.. not the Db. Is the query written well, but performing badly? Are there proper indexes for the query?Corey I Has Returned!! |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-02 : 10:27:12
|
| [url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP |
|
|
|
Jason W
Starting Member
19 Posts |
Posted - 2011-08-02 : 11:08:18
|
| Hi Willianto,Like it was mentioned above, you will need to go through each of the slow reports and find out why they are running slowly. There could be a lot of different issues and you will need to pinpoint the main causes of slowness. Turn your report procedures into stand alone queries that can be run, and view the execution plans on them. That will get you started with tracking down the slowness. Also, if you didn't want to run profiler, you can create a performance trace that loads all of your performance data into a table for easy reporting. This will let you track the slow running reports constantly and over a long period of time. I included some links below to get you started:Execution Plan Review - http://www.sqloptimizationsschool.com/Pages/Basic%20Concepts/Execution%20Plans.aspxPerformance Trace Setup - http://www.sqloptimizationsschool.com/Pages/Tracking%20Performance/Performance%20Tracing.aspxThere are several optimization topics on the site that can be useful, depending on what you see in the execution plan.Jason |
|
|
|
willianto
Starting Member
5 Posts |
Posted - 2011-08-02 : 23:35:14
|
| @seventhnight, @gilamonster, @jason: thank you for a quick response. I appreciate it very much :)Well, I did run the queries generated by my application (capture it on clipboard, and paste it back in MSSQLSMS). Then I run it while having SQL Profiler trace the load. After I got the trace file, I put it on Database Index Tuning Wizard, and run all recommendation from the wizard. These last few times, the recommendation was none. I wonder if there are any further step or procedure I can do to put the database in better shape (btw, I always failed to save the result on the table. The Wizard simply didn't recognized it. So I stayed with tables althought it's troublesome). Thanks for the link. I will learn it follow up what I get from there.Hmmm... anyway, just crossed my mind: Is there anyway that I could find useless indexes? --- that is indexes that is there, but not even used in, say one week. And will that has any effect on the performance?regards,WilliantoNothing runs like a fox... |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-03 : 04:04:53
|
quote:
Originally posted by willianto
After I got the trace file, I put it on Database Index Tuning Wizard, and run all recommendation from the wizard.
That's generally a very bad idea. The Database Tuning advisor is prone to give out lots and lots of recommendations, most of which you don't need. By now you probably have lots of mostly or completely redundant indexes.It's likely that the code needs optimising, not the indexes. Don't suppose there's scope for bringing someone external in?--Gail ShawSQL Server MVP |
|
|
|
willianto
Starting Member
5 Posts |
Posted - 2011-08-03 : 08:49:43
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by willianto
After I got the trace file, I put it on Database Index Tuning Wizard, and run all recommendation from the wizard.
That's generally a very bad idea. The Database Tuning advisor is prone to give out lots and lots of recommendations, most of which you don't need. By now you probably have lots of mostly or completely redundant indexes.
Ah... so, then I must've have tons of redundant indexes by now. I consider my self as a newbie in SQL Server, and mostly I don't understand the recommendations. When I run the wizard for the first time, I took all the recommendation and found that my report went from 3 minutes to 7 seconds. I never thought of redundant index. Do you (or anybody) have any idea how to locate the redundancy and remove it (um.. or remove them to be exact...)?quote:
It's likely that the code needs optimising, not the indexes. Don't suppose there's scope for bringing someone external in?
You mean like having an SQL experts for consultation? Well, I don't know if my boss have budget for that. ;)regards,WilliantoNothing runs like a fox... |
|
|
|
|
|
|
|
|
Is there any rule of thumb to optimze a database?