Hi Gail,

I originally was trying to find out if CTE's use TEMPDB? I was always told to think of them as you said.

"CTEs are nothing more than...

If anyone is curious like most things it depends..

If the CTE is using recursion then its uses a spool operator therefore tempdb is used....therefore not much performance benefit vs just...

The optimizer will create shell queries that do not contain the full execution plan but only a pointer to the full plan (not actual execution plans).Run the following qry and...

you probably already have these but just in case, here a couple of scripts if it get hung again

also be aware if this is 2005 it does not use sql...

sometimes the index gets hung up, especially if multiple FT index are rebuilt at the same time.I found that I needed to separate them by like 30 min and...

I agree with Grants Option 2, if you understand it, you dont need it.

I would add option 3 if you dont understand it dont touch it! you will more then...

if you have not got this figured out yet. save you trace file. then move it the server you want to run it on

Then go the profiler on the test...

FTS is going to need the data to be on one table as well. you would have to have two searches then compare the rankings. That might work.

I have...

running 64 bit 2008 windows so switch is not an issue.

quick geuss you have packet loss, check your nic card, here's a couple of threads they might help

http://connect.microsoft.com/SQLServer/feedback/details/518158/-packet-error-a-fatal-error-occurred-while-reading-the-input-stream-from-the-network

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=145733

correct sorry its actually 300 sec..is the standard

try this

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/12/02/missing-perfmon-counters-using-64-bit-windows-mmc-32-perfmon-msc.aspx

in order to determine if its a Disk or Ram issue check, Page life expectancy, min requirements is 500. This tells you that the pages are staying in the buffer...

you might try something like this, you add as needed....

USE [master]

GO

/****** Object: StoredProcedure [dbo].[BackupDbWithTs] Script Date: 07/05/2011 16:23:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[BackupDbWithTs]

@db_name...

you can create a non clustered index to replace the IX_SessionVars. At this point is doing what its suppose to which is 24 seeks, against the table. you can make...