DB time out issues in production environment

In general, how do I recreate DB time out issue that are happening in production environment to my local development environment?

My front end C# application call the stored proc that get DB time out in production. These DB time out issues are very intermittent. I tried locking the table with Begin Transaction but it does not work because SQL statements have "nolock" next to table name开发者_开发知识库s.

---

Before you try to re-create the error in your dev environment, you will need to do a little investigation to determine where the performance problem is most likely occuring in prod.

When you become aware of the problem occuring in prod, you can utilise the Activity Monitor within SQL Server to get an idea of which process are blocked, and which process they are blocked by. I've used this in the past to track down a problematic query.

---

You will probably be hard pressed to reproduce it on your local dev environment unless it is an exact mirror (number of users, amount of traffic, amount of data, etc.)

What I would recommend is to turn on profiler and see if you can identify what is happening when you get the time out issue on your prod environment. Onve you have identified what happened you might be able to reproduce on your dev environment dependong on what the cause is.

Something to get you started with profiler:

http://msdn.microsoft.com/en-us/library/ms187929.aspx

---

It will probably be pretty hard to get the same timeout in your development enviroment. You would need to use the same kind of hw and the same load as on the production servers.

The time out is most likely caused by: 1) a dead lock 2) a missing index 3) bad query plan

If you know exactly what parameters (their value) are sent in to the stored proc you could take a look at the query plan using SQL Managemnt studio or simular tools. If your running SQL 2008 the query analys will tell you if your missing an index.

If the parameters vary a lot it can be good to force the SQL server to create a new auery plan each time by adding with recompile in the stored procedure.

If the query plan looks good and dandy you need to add a trace on the server and look for locks on tables involved in the stored procedure.