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 |
|
lotek
Starting Member
42 Posts |
 Posted - 2008-02-15 : 13:26:20
|
| I have a 40 gig database (SQL 2005 Standard)(5 million rows in some tables) and need to run several hundred queries against it every night. These queries are for reporting purposes and need to run within certain time constraints. I have added indexes to the tables, but the queries could potentially include filter criteria for any number of 100 or so columns. At this point, i am at a loss of how to optimize a database of this size properly to faciliate these requirements. Any thoughts?Thanks,Matt |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-02-15 : 13:44:38
|
| Firstly, make sure that the queries are properly spaced out. Make sure your statistics are up to date. Without actually seeing any execution plans, tables etc, I would run the jobs one night with Profiler On . Focus on the slowest running queries and try to optimise the slowest one.Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
 |
|
|
lotek
Starting Member
42 Posts |
Posted - 2008-02-15 : 16:52:47
|
| How do you mean by properly spaced?I tried to update the statistics with:UPDATE STATISTICS table1 WITH SAMPLE 25 PERCENT, ALL |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-02-16 : 06:34:11
|
| By spaced , I mean there aren't jobs running at the same time which may have a performance impact. Also, have you checked your fragementation levels. On BOL there is a script, check DBCC SHOWCONTIG and you'll find a script which checks the level of logical fragmentationJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-02-16 : 08:01:34
|
check the query plans to see if there are any table scans on your large tables or other similar bad stuff.
elsasoft.org |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2008-02-16 : 12:35:57
|
| Are your queries being exectuted by a "Job"? Are they stored procedures? Do you use dynamic SQL? Give us a few more specifics. Feel free to contact me by e-mail. |
|
|
|
Bhavan
Starting Member
8 Posts |
Posted - 2008-02-22 : 17:18:54
|
| you can try :1. using named caches & binding the object to named cache2. parallel processing3. table partitioninghowever, if u want best results , you may want to post ur query + show plan + statistics of the table + lock scheme of the table + indexes details of the table to begin with.. |
|
|
|
|
|
|
|
|
Performance Help