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 |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2010-08-30 : 01:56:07
|
| We have the production databases Causing huge transactions therefore LDF files were gradually increasing.This is normal phenomenon. However, there are other issues like the Server getting slowdown; when accessing data for reports time taken is more;To establish what was causing to get delayed and solve them what are the SQL Views to get the information?Thanks |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-08-30 : 17:56:00
|
| Thanks Russel. I have 2005 Environment will these DMVs Applicable to them and how? |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-30 : 22:53:05
|
| yes those DMVs are for SQL 2005.You need to identify the poorly performing queries and why. Look at the # of reads 1st. Look at execution plans -- specifically looking for table scans and index scans.Since it's reporting queries, it may be that there is no avoiding the scans. But perhaps you can pre-aggregate the data for the reports.If you're reporting from an OLTP system, you're bound to have issues eventually. Check for blocking (sp_who_2, or sysprocesses) Might be it's time for another server if you're trying to report directly from your OLTP system, where you can copy off data to it (via SSIS or replication) and report from that. Maybe you can have nightly jobs that aggregate the reporting data.I can't tell you specifically without seeing the system, but those DMVs, profiler and perfmon are the place to start.Are you rebuilding indexes regularly? Is auto update statistics on?Regarding tran log growth, what recovery model are you using? If not simple, are you taking regular log backups? How often? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-08-31 : 18:11:41
|
Thanks very much. its great start and to finish on triumphing note. Still a Learning process for me. Great going PATRONS OF THIS FORUM.Keep It up. |
|
|
|
|
|
|
Performance Queries