Database tuning to improve memory and scalability?
Q: Our’s is a microsoft shop. Our products need database and we work with Sql server 2000 database.The prodcut is shipped to clients and clients need to have sql server database. Our products are data and memory intensive products. We do analysis on millions of records, with mutiple application processing engines. When multiple engines are running, they load data from database_A and process and then dump the data into database_B and run reports fron database_A and database_B. This process depending on data takes any where from 1hr to 15 days.During retrieving huge data sets from database, one or more analysis engines fails and we loose lot of time. Based on where the analysis is, we cannot recover the work and have to redo. Lot of time is spent on client performance and scalabilty issues. Generally database_A can grow upto 100G and database_B can grow upto 700G. All the databases are well normalized. How can we improve scalability and memory usage with Sql server?
Kevin Kline says: You haven’t given a full description of your application or environment, but the fact that you have even one report that can take 15 days to run on a 100GB database means that there are some starting performance problems in your application which, I would bet money, are due to poorly performing SQL statements and possibly poor database design.
Here are some broad tuning tips:
1. You mention that the database is well normalized. Normalization is especially important for OLTP applications with lots of inserts, updates, and deletes happening. If your database is used for lots of reporting and aggregations, then DENORMALIZE. Do this by pre-performing any summations or aggregations before running the reports. If your normalized table has a AMOUNT column, and you frequently pull AVG and SUM from that column, calculate and save those numbers in the table ahead of time, rather than in the reporting phase.
2. You are almost certainly getting a lot of thrash on TEMPDB, and possibly in your user databases. Be sure that they are each on their own disk subsystem and that transaction logs and database files are not sharing the same physical drive or RAID array.
3. Avoid cursors! They consume lots of additional memory and locking resources on SQL Server.
4. When using procedural code like Transact-SQL, include SET NOCOUNT ON into all of your procedures, functions and triggers. You’ll get at least a 30% boost in performance speed.
5. You don’t describe how your “analysis engine” interfaces with SQL Server. If possible, encapsulate as much of the analysis engine’s logic into stored procedures so that you get the advantage of caching.
After considering these tips, spend a few hours looking around http://www.SQL-Server-Performance.com. There are many, many more tips there.
Technorati Tags:
normalization, denormalization, T-SQL, cursors, SQL Performance, database tuning