Another way to skin the cat? (results are for the million-row table provided in the article)

(apologies if someone already posted this method)

--SET STATISTICS IO ON

--SET STATISTICS TIME ON

SELECT CustomerID

FROM #Purchase

WHERE...

Ah yes, good point Jeff!

I've seen plenty of DISTINCT code that had some dodgy JOINs, or bad assumptions about the underlying data relationships.

I was only hazarding a guess with the...

This is a really simple and effective script!

One question I had is how would the function know the difference between 'Mon' and 'MON' if the DB collation is case-insensitive?

When I...

A set-based, non-loopy way of doing a similar thing:

SELECT

DB_NAME(d.database_id)'DB',

ISNULL

('Backed up '+CAST(DATEDIFF(day,b.backup_start_date,GETDATE()) as varchar(10))+' days ago.',

'Never been backed up!'

)'Backup Info'

FROM sys.databases d

LEFT JOIN

(SELECT database_name,MAX(backup_start_date)'backup_start_date'

FROM...

Hi Vinay,

the trick is to right-click on the results and select "Copy with Headers".

That copies the column names too.

Very nice tricks!

I think I'll use your version in the future,

makes it easy to run all the returned statements at once 😀

This script is amazing! Thank you Theo!

I found it didn't run on SQL 2000,

so I half-ported it and added a few performance/aesthetic tweaks

and an option to only show times where...

I think each page = 8KB, so you could add a calculation to the SELECT clause:

SELECT

COUNT(*)AS cached_pages_count,

COUNT(*)/128.0 AS Cached_MB

It might be that your stored procedures aren't cleaning up the temp tables they use properly.

It's a good idea to use

IF object_id('tempdb..#yourTempTableName') IS NOT NULL DROP TABLE #yourTempTableName

at the beginning...

Luckily, I'm getting a bunch of zero counts with this script.

Might be nicer to filter out queries that don't have any pages allocated/deallocated?

SELECT

er.session_id,

er.request_id,

er.sql_handle,

er.statement_start_offset,

er.statement_end_offset,

er.plan_handle,

counts.task_alloc,

...