Shamshad Ali (5/27/2009)
We found that the bottlneck is SQL Server all the time.
Nope... it's not SQL Server. It's the code that's running against SQL Server. You say you've optimized the app. Does that mean that you've converted all of the app embedded SQL to stored procs? Probably not. Have you analyzed the slower queries to see which indexes are being used or not? Probably not. Are you sure of what the slower queries actually are and where they're coming from? Probably not. Do you have any Cursors, While Loops, Recursive CTE's, UDF's that reference tables (except maybe for a Tally table), formulas around columns in a FROM clause, non-equality correlated sub-queries, joined UPDATEs where the target table isn't in the FROM clause, views-of-views, more than 2 levels of stored procedures, UDF's of UDF's, or a pot wad of places that have SELECT's with variables on the left side of the equals sign in the Select List, tables with a lot of repeating data or NULLs, queries/objects that return more data than they need to, or multi-purpose highly "flexible" procs that will take any criteria or sort order that you can possible throw at it (just to name a few)? I'm betting you do.
SQL Server frequently gets the blame because people stop looking when they find "slowness in the server". It's probably not the server... it's the RBAR and other bad practices that have been built into the code. Your performance problems are in the code.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs