What Are The Best Sql Server Performance Optimization Techniques?
Solution 1:
My approach is to gather commands against the server or database into a table using SQL Server Profiler. Once you have that, you can query based on the max and avg execution times, max and avg cpu times, and (also very important) the number of times that the query was run.
Since I try to put all database access code in stored procedures it's easy for me to break out queries. If you use inline SQL it might be harder, since a change to a value in the query would make it look like a different query. You can try to work around this using the LIKE operator to put the same types of queries into the same buckets for calculating the aggregates (max, avg, count).
Once you have a "top 10" list of potential problems you can start looking at them individually to see if either the query can be reworked, an index might help, or making a minor architecture change is in order. To come up with the top 10, try looking at the data in different ways: avg * count for total cost during the period, max for worst offender, just plain avg, etc.
Finally, be sure to monitor over different time periods if necessary. The database usage might be different in the morning when everyone is getting in and running their daily reports than it is at midday when users are entering new data. You may also decide that even though some nightly process takes longer than any other query it doesn't matter since it's run during off hours.
Good luck!
Solution 2:
"premature optimization is the root of all evil"
In terms of database programming, I think this quote is nonsense. It is extremely expensive to re-write your whole application because your developers don't care to write efficient code the first time. All t-sql code should be thought of in terms of how it will affect database performance second (data integrity is, of course, first). Perfomance should trump everything except data integrity.
Yes, there are optimization things you shouldn't do until you have issues, but some things should be done as a matter of course and not fixed later. It takes no more time to write code that has a better chance of being efficient than code which will not be once you understand how you are affecting efficiency with the bad code. Cervo's discussion of cursor code is one example. Set-based actions are almost always much faster than cursor solutions, so cursors should not ever be written initially when a set-based solution will do. It almost always takes me less time to write a set-based solution that it would to write a cursor, but the only way to get that way is to never write cursors.
And there is no reason to ever use select * instead of specifying your field names. In MSSQL you can drag those names over from the object explorer so you can't tell me it's too hard to do that. But by specyfying only the fields you actually need, you save network resources and database server resources and web server resources. So why should a programmer ever take the lazy option of select * and worry about optimizing later?
The same thing with indexes. You say you do a minimal set of indexes. Depending on how you define minimal, that could be ok, but it is critical to have indexes on all foreign keys and I wouldn't want to push a database that didn't have indexes on a few fields that are most often in the where clauses. If your users are outside clients and not internal, they won't complain about how slow your site is, they will go elsewhere. It only makes busness sense to plan for efficient database access from the start.
One of my main concerns about failing to consider efficiency from the beginning is that the first couple of times that things are too slow companies tend to just throw more equipment at the issue rather than performance tune. By the time people start performacne tuning you have a several gigabyte or more database with many unhappy customers who are getting timeouts more than results. At this point, often almost everything in the database has to be re-written and in the meantime you are losing customers. I remember providing support at one company with a commercial application that it literally took ten minutes for the customer service reps to move from one screen to another while they were trying to help already disgruntled customers on the phone. You can imagine how many customers the company lost due to poorly designed database queries in the commercial product that we could not change.
Solution 3:
SQL Server Execution Plan!!! Go here: http://dbalink.wordpress.com/2008/08/08/dissecting-sql-server-execution-plans-free-ebook/
Solution 4:
After you profile, put the queries you see as troublesome into SQL Query Analyzer and display the execution plan. Identify portions of the queries that are performing costly table scans and re-index those tables to minimize this cost.
Try these references:
Solution 5:
profile your queries, not the obvious ones, but the complex that access different tables, views, etc and/or the ones that return many rows from different tables
That will tell you exactly where you should focus
Post a Comment for "What Are The Best Sql Server Performance Optimization Techniques?"