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 |
|
IslandHopper
Starting Member
3 Posts |
 Posted - 2011-05-26 : 17:12:26
|
| Hello,We have a reporting web app that runs a SQL query and produces an ActiveReport report. The report has not been traditionally slow or big. Recently, the report has been timing out (SQL timeout). I ran the query that report uses directly in SQL Server Management Studio and the result comes up instantly.I'm a developer, not a DB admin, but I've learned that at times when query gets slower to execute over time, rebuilding indexes often would resolve the issue. So I did that and the report was not timing out anymore. That was yesterday.Today, it is happening again. Again, I rebuilt the indexes and the issue appears to be resolved, at least for now. My question is what other options should I look into to resolve this issue for the long-term?Thanks in advance for any input. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
IslandHopper
Starting Member
3 Posts |
Posted - 2011-05-26 : 18:40:58
|
| Not that I'm aware of, I have not specifically update any stats or created jobs that update them. I'm not sure if there are any inherent stats that get created when the tables were created.Some additional notes:-- The database uses a star schema-- Data gets repopulated each day using an SSIS ETL process.-- There are 2 fact tables (star tables), one with 900K records, the other with 400K records. These are the tables that I rebuilt indexes on to resolve the timeout issue.-- The first fact table has 6 surrogate key columns to 6 dimension tables. The other one has 5.Thank you. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
IslandHopper
Starting Member
3 Posts |
Posted - 2011-05-26 : 19:33:34
|
| I am not experienced with statistics, so I'll look into that. Good idea also on adding the rebuilding indexes as last step of the ETL process. Thanks very much! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-05-27 : 02:18:04
|
| The advanatge of explicitly doing an UPDATE STATISTICS as opposed to relying on Auto Update Statistics , is that depending on the size of the table the sample rate can be low. Very relevant to datawarehousesJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Slow Query from App