Have you looked to see what that (and other) query(s) is/are waiting on? Check the WAIT type or most common wait types, may give you an idea where the issue...

Thanks for the comments.

2 mins (roughly) downtime is acceptable, we currently have no High Availability, and with our current set up (web edition) im limited to logshipping (better than nothing...

If your workload is more OLAP than OLTP you may want more parallelism on your machine. If you have a lot of transactions / processes waiting on CXPACKET etc you...

Try this simple query:

SELECT

[TABLE_CATALOG],

[TABLE_SCHEMA],

[COLUMN_NAME],

[COLUMN_DEFAULT],

[DATA_TYPE],

[IS_NULLABLE]

FROM INFORMATION_SCHEMA.COLUMNS

WHERE [TABLE_NAME] = 'TableName'

AND [IS_NULLABLE] = 'YES'

Edited: Changed IS_NULLABLE = 'Yes'

This link provides further details[/quote]

This is a duplicate post.

Have you considered using [sp_help_revlogin]?

http://support.microsoft.com/kb/246133

Check this out

http://www.henrycordes.nl/post/2008/11/Saving-changes-is-not-permitted-(SQL-Server-2008).aspx

I can't see anything too worrying in that exec plan, apart from maybe your Key Lookup and Ordering. Is this definately the plan for that SP? I was perhaps expecting...

No, you dont need to enable that on 64bit

You say SQL is consuming a lot of memory, but is that causing performance issues on that box. Remember, sql will keep taking memory (up to what you allow in...

Have you checked the execution plans of the 8sec queries? Does that index get used in scan or seek operation (if at all used)? What are the fragmentation levels of...

Gail, thanks for the reply.

I'll change the traceflag to 1222 as suggested. The deadlock frequency has gone from around 1 a week, to about 1 a day - depending on...

Agreed. I've used sp_help_revlogin many times to transfer logins from a primary to secondary which is in read only standby mode for reporting purposes.

http://support.microsoft.com/kb/246133

Thats what I find confusing. He's saying it runs slower the second time after a reboot. You'd expect maybe to run slower the first time due to compilations and empty...