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 |
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
 Posted - 2007-03-13 : 02:17:00
|
| Dear Experts,I'm a DBA, Working for a Product based company.We are implementing our product for a certain client of huge OLTP.our reports team is facing problem (error: all the reports are timed out).though the queries are written properly, Each query is taking some minutes of time.I've given the command DBCC DROPCLEANBUFFERS.the time immediately dropped to 10 sec.now my question is :please suggest me the DBCC commands or any DBA related commands to improve the performance of the application for my reports team.Thanks in advance.Vinod |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-13 : 07:13:26
|
| Slow queries...reasonsa) underpowered hardware...give details of your setup and we can advise.a1) underpowered network....sometimes it's not your DB/Server that has the problem....but your network returning the data to the client!b) maxed-out database....your database could be massive! Quyick responses are influenced by small datasets, slow repsonses CAN be influenced by LARGE databases. Give details of your database size!c) poorly structured database....no/poor index design can affect performance. Please give DDL details of affected tables + sample queries (+ execution plans)d) lack of use of StoredProcedures...inline code/dynamic SQL won't perform as good.e) use of CURSORS!!!.....aaargh!!!!f) search here also for "parameter sniffing" and solutions/steps to prevent same.Your starting point is too vague for us to easily help you....please provide more details. |
 |
|
|
sunsanvin
Master Smack Fu Yak Hacker
1274 Posts |
Posted - 2007-03-13 : 07:28:49
|
| Thank you Mr.Andrew for responding to me,Actually, which details can I provide for underpowered hardware,network like please give the exact terms I can provide.Vinod |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-13 : 08:30:44
|
| what spec is the PC? what network speed are you operating at...10mb, 100mb, adsl?probably more useful to concentrate on supplying the DDL + sample execution plans/queries improvements in this arena can have the biggest paybacks.What i'm prompting you to think about is the totality of your environment and not just "there must be a simple SQL problem" |
|
|
|
rajyalakshmi
Starting Member
16 Posts |
Posted - 2007-03-13 : 08:48:47
|
| Mr.Andrew , So nice of u.I'll let u know the configurations by tomorrow as I have to ask the client for that database details.but please respond tomorrow.meanwhile I've run sql profiler which gave me----%processor time =60sql compilations=10sql recompilations=5lock requests/sec=109859.009processors=40avg disk sec/read 0.08avg disk sec/write=0.07please give me commands or steps to improve performance based on these counters. |
|
|
|
|
|
|
Help me to improve my Application Performance