If it's caused by one, or a handful, of procedures I would expect to see a large number for total_physical_reads in sys.dm_exec_query_stats.

select top 50 t.text, s.total_physical_reads

from sys.dm_exec_query_stats s

cross apply sys.dm_exec_sql_text(s.sql_handle)...

How close are you to upgrading to 2012?

AlwaysOn can allow a secondary replica to accept read-only connections.

Aha, yes you've hit the nail on the head. Extended events report GMT.

Living in the UK I only have to account for that during the summer 🙂

There is a bug - see link

http://www.sqlskills.com/blogs/jonathan/incorrect-timestamp-on-events-in-extended-events/[/url]

You don't say if these are all Windows logins or if some/all are SQL logins.

If Windows, create a Windows security group and add them to that group. Then you...

My mistake. I was looking at the Database category (where the auto grow/shrink events are).

The manual shrinks come under the Security Audit category (8), along with all the other DBCC...

Not that I know of.

Auto grows and auto shrinks are recorded in the default trace, but manual operations are not.

If you suspect someone is doing this regularly you could set...

"2008 R2 Ent X64 with Lock pages enabled" so it will use AWE

SELECT SUM(awe_allocated_kb)/1024.

FROM sys.dm_os_memory_clerks

WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL'

And don't compare that with the sum from the buffer descriptors, as that...

At the end of the day you need to look at the query and the execution plan, and use your judgement and experience to work out the best indexes.

This script...

SQL Server 2012 doesn't use local groups anymore (though it does for SSAS).

There's a good explanation here

http://msdn.microsoft.com/en-us/library/ms143504.aspx

Have you looked at the execution plans? That has to be the first step.

I have a couple of scripts that might help.

I've attached the files - had to add .txt to the end to get them to upload.

ScriptLoginDiffs.ps1 is a PowerShell script that...

Just re-read the question - I hadn't noticed it is to be passed to a SP.

My answer only works if the SQL is in the data set. For passing...

If it's called from SSRS, it will substitute the parameter in the SQL.

So the following will work with a multi-value parameter

where ColumnName in (@YourParam)

I haven't come across a TSQL query for this.

I use PowerShell, see below:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$ps = New-Object Microsoft.SqlServer.Management.Smo.Server $YourInstance

$ps.JobServer.Jobs | % {$_.Script()}