SQL server performance Issue

Question

When one user access a report which call a SP in sql server 2008, it causes other users hang. SP select data froma a view which has join and left join with few tables(10). SP takes 10-15 seconds to access one year data. if we execute the same sp again, it returns in data immediatly within one or two seconds. Please advise how to solve this issue. What type of SQL server and other configuration I can suggest for this issue?

---

Developer

Answer 1

I assume from the description that you are running into two issues.

First, if the reporting query is blocking other users, it is probably running at an isolation level that is causing it to acquire locks, and those locks are blocking other users until released. You might want to review the information on locking to see if there is another isolation level that you could use that would reduce the potential for locking: http://msdn.microsoft.com/en-us/library/ms187101.aspx.

Second, if you are pulling a year's data, I assume that is a lot of rows from the underlying tables, much of which is relatively old and no longer frequently accessed. In that case, the reason the first query runs a relatively long time is that a lot of the rows have to be read from disk into memory, and disk I/O's are relatively long operations in computer terms. The subsequent queries run faster because the bulk of the rows are already in the DB Engine's buffer pool in memory.

---

Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.