By setting the DB to readonly no Shared-Lock is requested anymore so there 's a performance benefit in doing that.

and the syntax is so easy

ALTER DATABASE [DB] SET ...

SQLSACT (9/17/2012)

---

pooyan_pdm (9/17/2012)

---

You should use the actual counter values from the sys.dm_os_performance counters for the three mentioned counters and for checking wheather the cache is bloating or not you...

You should use the actual counter values from the sys.dm_os_performance counters for the three mentioned counters and for checking wheather the cache is bloating or not you can check...

Initial Compilation='SQL Compilations/sec'-'SQL ReCompilations/sec'

plan reuse= ( 'Batch Requests/sec' - 'Initial Compilation')/'Batch Requests/sec'

this one is more accurate

Thanx for your reply but I it's not the case it's well less than the threshold.

I don't know what's inside the proc can you send the actual execution plan ?

try updating the statistics of the tables involved in the query.and also try executing the query...

If the Mirror server is dedicated to the mirror db and no other IO intensive process is running on it , then your guess logicaly make scence. But in larger...

It depends on several factors.First of all to the Principal database transaction load, if this value is almost the same at the principal server it's totally normal.Then to the disk...

You can have user_seeks and range_scans on a non unique index at the same time when you're looking for a column of an index , I guess the reason there's...

Lynn Pettis (9/6/2012)

---

pooyan_pdm (9/6/2012)

---

when the index key is non-unique even when you're looking for a single value(a user_seek) sql server performs a range_sacan because there might be more than one...

when the index key is non-unique even when you're looking for a single value(a user_seek) sql server performs a range_sacan because there might be more than one records with that...

Thanx for your answer anyway. but it's a general question nothing to do with the database details.what I asked is how can we have singleton_lookups on an non-unique index while...

this index also has some column included in it's leaf level.

Both col1,col2 are non-unique so even if the engine is looking for a single combination of them it should go...

any suggestion?;-)

Use the sys.dm_exec_requests DMV to see what process is blocking this session (blocking_session-id) and using the sql_handle column of the blocking session you can see the Body of that session