Adsense

Thursday, February 27, 2014

Get All Query Plans & Performance Stats in Cache for a Stored Procedure

Replace "DATABASE" with the database name and "STORED PROCEDURE NAME" with the stored procedure and run.

use DATABASE
go
SELECT s.object_id, s.cached_time, s.sql_handle, s.plan_handle, s.last_execution_time, s.execution_count, s.total_worker_time,
s.min_worker_time, s.max_worker_time, s.total_physical_reads, s.min_physical_reads, s.max_physical_reads, s.total_logical_reads, s.min_logical_reads,
s.max_logical_reads, s.total_logical_writes, s.min_logical_writes, s.max_logical_writes, s.total_elapsed_time, s.min_elapsed_time, s.max_elapsed_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS s ON s.object_id = p.object_id
WHERE p.name = 'STORED PROCEDURE NAME'

and s.database_id = DB_ID()

Wednesday, February 5, 2014

Disable all snapshots on a distributor

When creating replication from script I find that the snapshot agents are enabled and scheduled to run on the hour.  To prevent them from pounding the database and running you need to disable them.  If you have a lot of snapshot agents disabling them all through the GUI can take a long time.  The script below will disable all snapshot agents.


declare @sql nvarchar(1000)

declare curs cursor for
select 'EXEC msdb.dbo.sp_update_job @job_id=N''' + cast(job_id as varchar(100)) + ''', @enabled=0'
from msdb.dbo.sysjobs where date_created > '12/03/2013' and enabled = 1 and category_id = 15 --snapshot'

open curs

fetch next from curs into @sql

while @@FETCH_STATUS = 0
begin
print @sql

exec sp_executesql @sql

fetch next from curs into @sql
end

close curs

deallocate curs

Coin of the month club

I don't normally post personal stuff, but my kids have started up a coin of the month club business.  Subscribe and get a new coin delivered to your house each month.  Check it out.

http://threebrotherscoins.weebly.com/index.html