TRY/CATCH is not a trustworthy construct. All kinds of limitations, gotchas, provisos, etc.

if it REALLY is the insert that is taking so long then either a) you are doing large scans in that SELECT that drives the INSERT or b) your insert...

sqldba_icon (2/9/2011)

---

I created an index, did enough testing and it did improve the performance of the procedure.Now i come back on monday and performance is degraded? The same index is...

1) ORMs are getting better but can still be DEVASTATINGLY BAD for database application performance.

2) one word: SSMSToolsPack!!!! FREE SSMS addin with an incredible array of useful features,...

I am sorry, but I really don't understand what you are looking for here.

Dave Ballantyne (2/9/2011)

---

TheSQLGuru (2/9/2011)

---

Dynamic sql is almost always the most performant way to deal with this type of scenario. Beware SQL Injection as someone else stated and also watch...

sqldba_icon (2/9/2011)

---

TheSQLGuru (2/9/2011)

---

a) parameter sniffing as cause:

1) run your query on both servers with HARD-CODED input values instead of parameters.

2) run query on both...

Dynamic sql is almost always the most performant way to deal with this type of scenario. Beware SQL Injection as someone else stated and also watch out for procedure...

lovelanjunping (2/8/2011)

---

we have a application to sqlserver, app write by java.

there are some performance issue.

 

i monitor it by sqlserver profile, i found some system procedure cost much time.

i don't konw...

It should be EASIER to control what goes on with FEWER people stirring the pot! 🙂

That book will definitely help you long-term (good one Grant!). However, if you want to get things running better NOW, hire a performance tuning professional and have them monitor/fix...

a) parameter sniffing as cause:

1) run your query on both servers with HARD-CODED input values instead of parameters.

2) run query on both servers using...

if you make your update stats a sql agent job, no one will have to remote in and even if they did they certainly wouldn't (SHOULDN'T) leave SSMS running! ...

Oh look, another entity out there who threw a workload (or multiple workloads) at the Database Tuning Advisor and accepted everything!! Outside of using Scalar UDFs that is just...

Better yet, stop using UDFs so you don't get hurt by the MANY ways they can harm you!! 😀