I’m excited to announce that I’ll be presenting “Fast and Furious Dynamic SQL” at SQL Saturday #358 in Albuquerque on February 7th, 2015. This will be my first time as a PASS presenter, so please save your best heckling and rotten vegetables for the occasion.
The session will cover various tips and tricks that are generally related to running sp_executesql from within a stored procedure. It will focus on performance, security, and maintainability.
I’m working on a script and wanted to know how SQL was caching the query plan. So I wrote this little snippet. The only real value I’m adding here is getting the XQuery correct to pull out the SQL itself.
DBCC FREEPROCCACHE; -- Run your query here SELECT qp.query_plan, qp.query_plan.value(N'(//@StatementText)[1]', N'NVARCHAR(MAX)') FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp;
Of course, I don’t recommend that DBCC in a production environment. But in a dev environment, it makes things easy.
I coined this a while back. I just came across it again, and thought I’d share it. How many of us know this to be true?
“An otherwise inexplicable bug will become easily apparent to the programmer who caused the bug and has subsequently been searching in vain for the bug only and immediately after he announces to his peers that he cannot find the bug, admits defeat, and asks for help.” – Robinson’s Law
One of the items in my toolbox is this simple query that returns the number of pages used by different tables in the database. The nice thing is that it gives one simple number that includes indexes, lob data – basically the whole table.
SELECT TOP 20
s.name,
o.name,
SUM(ddps.used_page_count)
FROM sys.dm_db_partition_stats AS ddps
INNER JOIN sys.objects AS o
ON ddps.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
GROUP BY s.name, o.name
ORDER BY SUM(ddps.used_page_count) DESC;
I just used this on a database today to find that there are some leftover tables from a migration a while back that are eating up a considerable amount of space. Time to run some DROP TABLE scripts!