| Author |
Topic |
|
funketekun
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-04-15 : 01:28:52
|
| A vendor's application is performing slow. Vendor tested it in QA and it's slow. End-users run it in PRD and it's slow. The application calls SP1, and SP1 calls SP2. Inside SP1 has a cursor. I believe as the db gets larger. The application is going to be even slower. What can I suggest to the vendor in order to fix it? Tell them to re-write the application code? Eliminate cursor?Thanks |
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-04-15 : 04:02:03
|
| Removing Cursor is a good idea but there are many other factors too hwich affect the performancePrakash.PThe secret to creativity is knowing how to hide your sources! |
 |
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-04-15 : 12:41:44
|
| I know. But listen the vendor tested the application1 using the same database in his company's pc and it's working OK for him. That means it's not the application1.We have lots of SP and applications running in our QA and PRD and they seem to be OK besides this evil application1. what could be wrong? Only experts (5+ years) pls. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-15 : 13:11:43
|
| First have a look at performance of SP1 in query analyser/mgmnt studio. Analyse its execution plan. if its really the bottleneck think of rewriting the logic by removing cursor. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-04-15 : 13:19:10
|
oh i just love this part:> Only experts (5+ years) pls. the solution to your problem depends on what the cursor is doing. simple as that. tells us what it does and then we can help you._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-15 : 13:37:51
|
| Just because the vendor tested the application on the same database doesn't mean that the problem isn't with the application. There's not much that we do to help except the normal performance troubleshooting steps:1. Run SQL Profiler/SQL Trace to collect slow running queries, optimize as needed2. Analyze execution plans and see if any indexes need to be added3. Run Performance Monitor to determine if there is a hardware bottleneck4. Redesign the database schema5. Eliminate all bad codeTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-04-15 : 14:27:55
|
| hey listen, I removed all the nonclustered indexes as they do not have 95% selectivity. Not sure if this improved the speed. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-15 : 14:30:13
|
| I'm sure it decreased the speed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-15 : 14:37:46
|
| It isn't wrong, but it also is making an assumption that 95% is correct. You should view the execution plans to determine what gets used. I believe that there is a report in the performance dashboard which shows if an index ever gets used or not. You can also use that to determine what to drop.I would highly recommend not dropping all indexes just because the selectivity is not over 95%. That's only one thing to consider when determining what indexes to create/drop.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
app slow.