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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Slow Query from App

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

Posted - 2011-05-26 : 17:23:52
Are you updating statistics regularly? Auto-update stats plus UPDATE STATS through a job?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-26 : 18:53:59
Up to date statistics are very important if your data is changing. Now I don't have experience with the data warehousing side, but I assume that statistics are still important there.

You should consider updating the statistics as the last step in your ETL process, that or rebuild the related indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-26 : 19:36:08
You're welcome, glad to help.

You don't need to rebuild the indexes and also update statistics. You get up to date stats when you rebuild indexes. It's just that updating stats is faster than rebuilding indexes and provides much of the same benefit.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 datawarehouses

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -