In general the defaults are pretty good and in most cases shouldn't be changed unless you have a good reason. The exception is the max memory if the server in...

mj obi-wan kenobi (9/5/2008)

---

Can it be because on the first the DB is attached to run in mode 80 i.e. not migrated to a SQL Server 2005 DB and on...

Deepak (9/5/2008)

---

I think it also depends on the number of records present in the table. IF fewer records are there i think the optimzer would prefer a scan rather than...

Wow. 12 sec to compile...

If this server heavily used? Is that proc getting heavily used?

Can you run it again and trace (filtered by your machine name) the events Cache...

Gary Johnson (9/5/2008)

---

Not really.

In query 1, because the expression in the order by is qualified with the table, it's clearly not a...

I'm assuming this table has a clustered index?

You can drop and recreate the clustered index and specify the new location on another filegroup. Then go and shrink the file...

It won't. All the versions of showplan show the same data, just the methods of storing and showing it change.

Look at the seek predicates. If you've got a partial scan,...

Possibly parameter sniffing. When the optimiser compiles the proc, it will optimise the queries based on the current parameter values, and it will optimise all queries in the proc, regardless...

Ok, with 3 columns, populated much the same way as the first. I interleaved the queries to make it as equal as possible

set statistics time on

go

DECLARE @param char(1), @param1 char(1),...

After switching to simple, run a checkpoint. It's the checkpoint that triggers log truncation in simple recovery.

Make very, very sure you take a full backup after switching back to full...

Is there space free within the data file? What does the following return?

exec sp_spaceused

If either the start or the end of the range is sargable, a range...

There is, and it depends on the...

When you say 'can't shrink' do you mean it's giving you an error?

Why are you shrinking the data and log files anyway?

Kenneth Fisher (9/5/2008)

---

As a test I ran DBCC SHOWCONTIG right after the DBREINDEX and got less than 1% fragmentation. Then I ran my SHRINKFILE, then the SHOWCONTIG again. ...