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 |
|
maksuda
Starting Member
15 Posts |
 Posted - 2011-03-23 : 12:07:03
|
| Hi:I have a web reporting application that imports data every hour from remote database. In my database I have created stored procs to arrange the imported data in monthly, qtrly and yearly tables for monthly, qtrly and yearly reports. I scheduled job to run the stored procs every hour. Hourly data volume is pretty high. At the very begining performance was ok. From last 3 months performance reduced very much. In my stored procs I'm using table variables to handle large volume of data and depending on the availability of the data I have to insert or update table records.Now performace issue becomes such a big issue that some jobs sometime fails and cann't update tables hourly.Any idea please what I can do to improve the prformance.FIY:database SQL 2005, OS- windows 2003, RAM - 8GB, processor - 4DH- more than 3 tera.Thanks in advance...Maksuda |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-03-23 : 13:05:55
|
| Table variables should not be used for large volume of data. This is a big no no. Table variables should only be used for small data sets, such as 1000 or less rows. You'll need to figure out what the culprit of the performance issues is. Are you having a hardware bottleneck? Run PerfMon to verify. Are there long running queries? Run SQL Profiler to verify. Are there missing indexes? Run the missing indexes report. Are you experiencing bad execution plans? Check the plans to verify. The list could go on and on and on.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
skybvi
Posting Yak Master
193 Posts |
Posted - 2011-03-23 : 13:43:26
|
| Are you running regularly updating stats, rebuilding indexes ?It can have other reasons like h/w bottleneck, You should see the execution plans too.Regards,SushantDBAWest Indies |
|
|
|
|
|
|
Database performance very slow