SQL Server: List all marked startup SPs

Select StartupProc = name from sys.sysobjects where xtype = 'p'
and OBJECTPROPERTY(id, 'ExecIsStartup') = 1;

SQL Server: Memory pressure

Back to "A DMV a Day"

It turns out that there are several known issues with ad-hoc plan caching in SQL Server 2005 RTM and SP1 that have been corrected in hot fixes that are rolled up in SQL Server 2005 SP2. This blog post discusses the issue in more detail.

Recently, I detected that a 32GB database server that I use was using between 12-13GB of memory in the plan cache. This was causing internal memory pressure, and causing Page Life Expectancy (PLE) to go down into the sub 200 range (which is bad).

These DMV queries will help you detect this.

-- Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS 'Plan Cache Size(GB)'
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP'
OR type = 'CACHESTORE_OBJCP'

-- Top 10 consumers of memory from Buffer Pool
SELECT TOP 10 type, sum(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

-- UseCounts and # of plans for Adhoc plans
SELECT usecounts, count(*) as no_of_plans
FROM sys.dm_Exec_Cached_plans
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc'
GROUP BY usecounts
ORDER BY usecounts

Use this DMV query to find the offenders:

-- Find the ad-hoc queries that are bloating the plan cache
SELECT TOP(100) *
FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
--AND size_in_bytes < 200000
ORDER BY size_in_bytes DESC

Microsoft has made changes in SP2 that will help prevent this plan cache bloating problem. In the meantime, if you have lots of ad-hoc plans with a usecount of one, you can use a little trick to keep them from going into the plan cache. Just append OPTION (RECOMPILE) to the end of your ad-hoc SQL statement, and that will tell SQL Server to not put it into the cache. Then, you have to run DBCC FREEPROCCACHE to clear out the contents of the cache server-wide. You can also use a more surgical approach by using DBCC FLUSHPROCINDB.

--Determine the id of your database
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'yourdatabasename')

--Flush the procedure cache for your database
DBCC FLUSHPROCINDB (@intDBID)

SQL Server; Most expensive query record

--DBCC FREESYSTEMCACHE
--DBCC FREEPROCCACHE

SELECT TOP 100 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) Expensive_QUERY,
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time/1000000 total_worker_time_in_S,
qs.last_worker_time/1000000 last_worker_time_in_S,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.max_elapsed_time/1000000 MAX_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- total logical reads
-- ORDER BY qs.total_logical_writes DESC -- total logical writes
-- ORDER BY qs.total_worker_time DESC -- total CPU time
--ORDER BY qs.last_worker_time DESC -- most recent CPU time
ORDER BY qs.max_elapsed_time DESC, qs.total_elapsed_time DESC, execution_count ASC-- max execution time
--ORDER by qs.last_elapsed_time DESC -- most recent exection time

SQL Server: Rebuild index

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
Declare @SQL Varchar(8000);


DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN



SET @SQL = 'USE ['+@Database+']; '
-- Ensure a USE statement has been executed first.
SET @SQL = @SQL + ' SET NOCOUNT ON; '
SET @SQL = @SQL + ' DECLARE @objectid int; '
SET @SQL = @SQL + ' DECLARE @indexid int; '
SET @SQL = @SQL + ' DECLARE @partitioncount bigint; '
SET @SQL = @SQL + ' DECLARE @schemaname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @objectname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @indexname nvarchar(130); '
SET @SQL = @SQL + ' DECLARE @partitionnum bigint; '
SET @SQL = @SQL + ' DECLARE @partitions bigint; '
SET @SQL = @SQL + ' DECLARE @frag float; '
SET @SQL = @SQL + ' DECLARE @command nvarchar(4000); '
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @SQL = @SQL + ' SELECT '
SET @SQL = @SQL + ' object_id AS objectid, '
SET @SQL = @SQL + ' index_id AS indexid, '
SET @SQL = @SQL + ' partition_number AS partitionnum, '
SET @SQL = @SQL + ' avg_fragmentation_in_percent AS frag '
SET @SQL = @SQL + ' INTO #work_to_do '
SET @SQL = @SQL + ' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') '
SET @SQL = @SQL + ' WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; '
-- Declare the cursor for the list of partitions to be processed.
SET @SQL = @SQL + ' DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; '
-- Open the cursor.
SET @SQL = @SQL + ' OPEN partitions; '
-- Loop through the partitions.
SET @SQL = @SQL + ' WHILE (1=1) '
SET @SQL = @SQL + ' BEGIN; '
SET @SQL = @SQL + ' FETCH NEXT '
SET @SQL = @SQL + ' FROM partitions '
SET @SQL = @SQL + ' INTO @objectid, @indexid, @partitionnum, @frag; '
SET @SQL = @SQL + ' IF @@FETCH_STATUS < 0 BREAK; '
SET @SQL = @SQL + ' SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) '
SET @SQL = @SQL + ' FROM sys.objects AS o '
SET @SQL = @SQL + ' JOIN sys.schemas as s ON s.schema_id = o.schema_id '
SET @SQL = @SQL + ' WHERE o.object_id = @objectid; '
SET @SQL = @SQL + ' SELECT @indexname = QUOTENAME(name) '
SET @SQL = @SQL + ' FROM sys.indexes '
SET @SQL = @SQL + ' WHERE object_id = @objectid AND index_id = @indexid; '
SET @SQL = @SQL + ' SELECT @partitioncount = count (*) '
SET @SQL = @SQL + ' FROM sys.partitions '
SET @SQL = @SQL + ' WHERE object_id = @objectid AND index_id = @indexid; '
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
SET @SQL = @SQL + ' IF @frag < 30.0 '
SET @SQL = @SQL + ' SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE''; '
SET @SQL = @SQL + ' IF @frag >= 30.0 '
SET @SQL = @SQL + ' SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD''; '
SET @SQL = @SQL + ' IF @partitioncount > 1 '
SET @SQL = @SQL + ' SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10)); '
SET @SQL = @SQL + ' EXEC (@command); '
--Print (@command);
SET @SQL = @SQL + ' PRINT N''Executed: '' + @command; '
SET @SQL = @SQL + ' END; '
-- Close and deallocate the cursor.
SET @SQL = @SQL + ' CLOSE partitions; '
SET @SQL = @SQL + ' DEALLOCATE partitions; '
-- Drop the temporary table.
SET @SQL = @SQL + ' DROP TABLE #work_to_do; '
Print @SQL
EXECUTE(@SQL)
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

SQL Server: Change replication batch size

sp_configure 'max text repl size', 131072
RECONFIGURE
go

SQL Server: Steps to set up replication using backup files instead of snapshots

/*
Steps to set up replication using backup files instead of snapshots

1. Run the Create Publication wizard on the publisher. Make sure NOT to create a snapshot.

2. Once the wizard has finished, right-click the new publication, choose Properties
and make sure that the "Allow initialization from backup files" is set to True

3. Disable the "Distribution clean up: distribution" job to make sure that no
commands are deleted from MSrepl_commands before the entire backup/restore operation
is complete

4. Create a full database backup to disk and make sure that the file is available for the
publisher until the entire replication setup is finished.

5. Transfer the backup file to the subscriber server and restore it there. If you do
regular backups to disk make sure to use one that was taken *after* the publication
was created. Also restore trans log backups taken after the full backup. The last backup
should be restored using WITH RECOVERY, and keep in mind that a more recent backup saves
you time in the syncing process when the replication is being initialized.

6. Disable all triggers on the subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

7. Disable all constraints on subscriber database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

8. Run the following script ON THE PUBLISHING SERVER IN THE PUBLISHING DATABASE to enable
the replication:
EXEC sp_addsubscription
@publication ='myPublication', --> Name of the publication
@subscriber = 'myserver.myDomain.com', --> Fully qualified name or IP of subscriber server
@destination_db ='MySubscriberDB', --> Name of the database you just restored (doesn't have to be the same as the publisher)
@sync_type = 'initialize with backup', --> no need to change this
@backupdevicetype = 'disk', --> no need to change this
@backupdevicename = 'F:\backupfile.bak' --> Pointer to the last backupfile that was restored, but from the folder on the on the
publishing server. If you restored trans logs also the last translog file is what you
need to put here

9. Enable the "Distribution clean up: distribution" job again
*/

SQL Server: Query problematic replication trasnactions

--Run following query in distribution databases:
sp_browsereplcmds @xact_seqno_start = '0x0003FDBA0006519B001400000000,', @xact_seqno_end = '0x0003FDBA0006519B001400000000';
--OR
select * from dbo.MSarticles
where article_id in
(
select article_id from MSrepl_commands where xact_seqno = '0x0003FDBA0006519B001400000000'
)