SQL doesn't randomly set databases to single user mode, the command is coming from somewhere. Either an application, a job or another user. Check the error log, consider running traces...

Then either there's an application that's sending that command to SQL, or someone's running it manually.

How many rows in the table, how many rows does the query return? If it's most of the table, then the table scan could be the most efficient way.

btw, lack...

What is the value of ZoneSearch?

He said it's out next week. Most bloggers write blog posts ahead of time and schedule them, that one's scheduled for the 13th May

Tell the vendor to fix their garbage (politely). They probably don't have a clue about good indexes.

If you can't drop or change indexes, how do you expect to tune the...

Firstly, you have multiple redundant indexes on that table.

Let's see...

CREATE INDEX [IX_tbl_extr_items_Collection_Surr_Id_3E6F809B-F53A-48FC-81A1-E6F7504DF641] ON [dbo].[tbl_extr_items] ( [Collection_Surr_ID] ) INCLUDE ( [active]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?);

CREATE INDEX [Ind_collsurr_prostatus_item_computer_3E6F809B-F53A-48FC-81A1-E6F7504DF641] ON [dbo].[tbl_extr_items] ( [Collection_Surr_ID],...

Page file isn't something in SQL Server. It's a file the OS uses for 'extra' memory (so it can move apps from memory into the page file if there's memory...

There's a maintenance plans folder in Enterprise manager, you look through that. Check to see if any of your maintenance plans have the check database integrity task. If any do,...

Lynn, could you please pm me your current email address?

I'm not talking about your backup job.

Look at your maintenance plans. Do you have any which have the database integrity task? If there are, does that task have 'automatically repair...

Go look at your database integrity job. Does it have the option "automatically fix minor problems" checked? If so, uncheck it. Then do a full DBCC CheckDB and see how...

Please note: 10 year old thread.

The majority of those are harmless waits, to be ignored. You need to filter out the meaningless waits. Also, taking a single view doesn't help much, you need to take...

In that case, unless you have some custom auditing in place, you don't find out. The default trace (which populates that report) tracks DDL changes, but SQL only keeps 5...