jdaigle’s gists

jdaigle / gist:2781349

Created May 24, 2012 12:39

SQL Server Service Broker: Short Script to clear empty a named queue

	DECLARE @handle UNIQUEIDENTIFIER;
	WHILE (SELECT COUNT(*) FROM NameOfQueue) > 0
	BEGIN
	RECEIVE TOP (1) @handle = conversation_handle FROM NameOfQueue;
	END CONVERSATION @handle WITH CLEANUP
	END

jdaigle / gist:2781464

Created May 24, 2012 13:09

SQL Server Service Broker: Query to get estimated number of rows in queues

	SELECT q.name, p.rows
	FROM sys.partitions p
	INNER JOIN sys.internal_tables t ON t.object_id = p.object_id
	INNER JOIN sys.service_queues q ON q.object_id = t.parent_object_id
	WHERE p.index_id IN (1, 0) --AND q.name = 'QueueName'

jdaigle / gist:3692168

Created September 10, 2012 17:04

	var name = "John Doe";

	function getFirstName(fullName) {
	var next = fullName[0];
	var i = 0;
	var length = fullName.length;
	while (i < length && next !== " ") {
	console.log(fullName[i]);
	i++;
	next = fullName[i];

jdaigle / gist:4631728

Created January 25, 2013 04:23

playing around with SQL Server XML data type - i might create a lightweight document database derived from this

	--insert into Documents VALUES ('products/1', '<product><name>Foo</name></product>', 'dynamic');
	--insert into Documents VALUES ('products/2', '<product><name>Bar</name></product>', 'dynamic');
	--insert into Documents VALUES ('products/3', '<product><name>Fizz</name></product>', 'dynamic');
	--insert into Documents VALUES ('products/4', '<product><name>Buzz</name></product>', 'dynamic');

	UPDATE Documents SET Data = '<product><name>Foo</name></product>', entitytype='dynamic' WHERE Id = 'products/1';

	select Data.value('(//product/name)[1]','varchar(30)') from Documents where entitytype = 'dynamic2'
	select Data.value('(//product/name)[1]','varchar(30)') from Documents
	select Data.value('(//product/name)[1]','varchar(max)') from Documents

jdaigle / gist:5428198

Created April 21, 2013 02:19

query for single use ad hoc queries in the plan cache

	SELECT [text], cp.size_in_bytes, plan_handle
	FROM sys.dm_exec_cached_plans AS cp
	CROSS APPLY sys.dm_exec_sql_text(plan_handle)
	WHERE
	cp.cacheobjtype = N'Compiled Plan'
	AND cp.objtype = N'Adhoc'
	AND cp.usecounts = 1
	ORDER BY cp.size_in_bytes DESC;

jdaigle / gist:5459155

Created April 25, 2013 11:45

"How To Obtain The Size Of All Tables In A SQL Server Database (see http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx)

jdaigle / gist:5459160

Created April 25, 2013 11:47

Determine SQL Server memory use by database and object (see http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/)

	-- Note: querying sys.dm_os_buffer_descriptors
	-- requires the VIEW_SERVER_STATE permission.

	DECLARE @total_buffer INT;

	SELECT @total_buffer = cntr_value
	FROM sys.dm_os_performance_counters
	WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
	AND counter_name = 'Total Pages';

jdaigle / gist:5459403

Created April 25, 2013 12:40

Show Index Stats based on Usage

	SELECT
	o.name
	, indexname=i.name
	, i.index_id
	, reads=user_seeks + user_scans + user_lookups
	, user_seeks
	, last_user_seek
	, last_system_seek
	, user_scans
	, last_user_scan

jdaigle / gist:5459517

Created April 25, 2013 13:02

query, spaced using in database for each table

jdaigle / gist:5548204

Created May 9, 2013 15:31

Show Service Bus Statistics


	SELECT
	q.name AS Queue_Name
	, s.name AS Service_Name
	, p.rows AS Row_Count
	, (p.rows / 2) AS Estimated_Message_Count -- divide by two because our framework always sends an "END CONVERSATION" message with each real message sent
	, COALESCE(f.Failed_Message_Count,0) AS Failed_Message_Count
	-- other interesting things
	--, q.is_enqueue_enabled
	--, q.is_receive_enabled

Joseph Daigle jdaigle