You have 32GB of memory. If you have small data, EVERYTHING will be sitting in the buffer pool in RAM and drive speed will be virtually irrelevant except for...

1) get rid of functions around columns in exists clauses

2) refactor exists into single exists

3) do not use explicit field for exists (unless NULLable issues are afoot) - use *...

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! ...