Enrique | SQL Conjuror

SQL Server – Script to Check Plan Cache Bloat

By Enrique 21/04/2017 29/04/2020 SQL SERVER, Database Administration

SQL Server will look at each query and check if it has a plan cache already compiled for. If it has, then it will reuse the plan in the cache. If it does not have, then an execution plan is compiled and added to the plan cache.

When identical queries ran over many times, its plan will be reused. This will save resources from having to compile repeatedly.

Now what happens when you have hundreds or thousands of different queries that are only executed once? In this case there are no execution plan to be reused. This is what we call PLAN CACHE BLOAT.

Plan cache bloat is bad because it steals memory from the buffer pool that are better used for database pages. When you execute the script below, and if you see “Adhoc” Cache type to be on top, then that means that there are a lot of queries that are executed once and are causing plan cache bloat.

SELECT 
    objtype AS [CacheType]
    , count_big(*) AS [Total Plans]
    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
    , avg(usecounts) AS [Avg Use Count]
    , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM 
    sys.dm_exec_cached_plans
GROUP BY 
    objtype
ORDER BY 
    [Total MBs - USE Count 1] DESC
GO

Plan cache bloat can be prevented by enabling “Optimize for Adhoc Workloads” by only caching the full execution plan of queries that are known to be used more than once.

To enable the optimize for ad hoc workloads setting, use sp_configure, as shown below.

EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'optimize for ad hoc workloads',1
RECONFIGURE

Facebook

Google

Twitter

No comments

SQL Server – Check if “Enforce Password Policy” is On Using TSQL

By Enrique 06/04/2017 29/04/2020 SQL SERVER, Database Administration, T-SQL

The T-SQL query below will check if “Enforce Password Policy” is On. A value of 1 under is_policy_checked column indicates that it is turned on. Otherwise, it is disabled.

select name, is_policy_checked 
from sys.sql_logins

SQL Server – The EXECUTE permission was denied on the object ‘xp_instance_regread’, database ‘mssqlsystemresource’,schema ‘sys’ (Error 229)

By Enrique 28/03/2017 29/04/2020 SQL SERVER, Troubleshooting

One of our users encountered the error below.

I was able to fix the problem by granting the user direct access to the procedure.

USE master
GO

GRANT EXECUTE ON [sys].[xp_instance_regread] TO [DOMAIN\USER];

--To veriy that the user has been granted the privilege
EXECUTE AS USER = 'DOMAIN\USER';Select * from fn_my_permissions('xp_instance_regread','Object')

SQL Server – View FileNames and Paths Using TSQL

By Enrique 27/03/2017 30/04/2020 SQL SERVER, Database Administration, T-SQL

The T-SQL script below can be used to check the Filenames and paths of your data and log files.

SELECT 
    DB_NAME ([database_id]) AS [Database Name]
    ,name
    ,physical_name
    ,type_desc
    ,state_desc
    ,CONVERT(bigint, size/128.0) AS [Total Size in MB]
    ,CONVERT(decimal(18,2), size/131072.0) AS [Total Size in GB]
FROM
    sys.master_files WITH(NOLOCK)
WHERE
    [database_id] > 4
    AND [database_id] <> 32767
    OR [database_id] = 2
ORDER BY DB_NAME ([database_id]) OPTION(RECOMPILE)

Output

SQL Server – Check Installation Date of SQL Server

By Enrique 23/03/2017 30/04/2020 SQL SERVER, Database Administration

Below is a T-SQL script to check when your SQL Server was installed. It is a good idea to know how old your instance is.

SELECT 
    @@SERVERNAME AS [Server Name] 
    ,createdate AS [SQL Server Install Date]
FROM
    sys.syslogins
WHERE 
[sid] = 0x010100000000000512000000

SQL Server – Get VLF Counts for All Databases in an Instance

By Enrique 21/03/2017 30/04/2020 SQL SERVER, Database Administration

Below is a script to get the VLF count for all databases on an instance.

CREATE TABLE #VLFInfo
(
    RecoveryUnitID INT
    ,FileID INT
    ,FileSize BIGINT
    ,StartOffset BIGINT
    ,FSeqNo BIGINT
    ,Status BIGINT
    ,Parity BIGINT
    ,CreateLSN NUMERIC(38)
);

CREATE TABLE #VLFCountResults
(
    DatabaseName sysname
    ,VLFCount INT
)
    
EXEC sp_MSforeachdb N'Use [?];

        INSERT INTO #VLFInfo
        EXEC sp_executesql N''DBCC LOGINFO([?])'';

        INSERT INTO #VLFCountResults
        SELECT DB_NAME(), COUNT(*)
        FROM #VLFInfo;

        TRUNCATE TABLE #VLFInfo;'

SELECT 
    DatabaseName
    ,VLFCount
FROM 
    #VLFCountResults
ORDER BY 
    VLFCount DESC

DROP TABLE #VLFInfo
DROP TABLE #VLFCountResults

-- High VLF counts can affect write performance
-- and they can make database restored and recovery take much longer

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication

By Enrique 17/03/2017 30/04/2020 SQL SERVER, Troubleshooting

I encountered the error below during Transaction Replication setup between SQL Server 2008 R2 (Publication) and SQL Server 2012 (Subscriber). When you hit this error, what you need to do is to set up the Subscriber from SQL Server 2012 Management studio in the ‘Subscriber’ database server.

SQL Server – Check Latest Transaction Log Backup

By Enrique 13/03/2017 30/04/2020 SQL SERVER, Database Administration

To know what the last transaction log backup of your database was, execute the T-SQL script below.

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS ServerName,

msdb.dbo.backupset.database_name AS DatabaseName,

MAX(msdb.dbo.backupset.backup_finish_date) AS Last_TLog_Backup_Date

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'L'

GROUP BY

msdb.dbo.backupset.database_name

ORDER BY

msdb.dbo.backupset.database_name

SQL Server – Check SQL Server Error Logs via T-SQL

By Enrique 13/03/2017 30/04/2020 SQL SERVER, Database Administration, T-SQL, Uncategorized

Below is a script to check SQL Server Error logs.

declare @Time_Start datetime;

declare @Time_End datetime;

set @Time_Start=getdate()-2;

set @Time_End=getdate();

-- Create the temporary table

CREATE TABLE #ErrorLog (logdate datetime

, processinfo varchar(255)

, Message varchar(MAX))

-- Populate the temporary table

INSERT #ErrorLog (logdate, processinfo, Message)

EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';

-- Filter the temporary table

SELECT LogDate, Message FROM #ErrorLog

WHERE (Message LIKE '%error%' OR Message LIKE '%failed%') AND processinfo NOT LIKE 'logon'

ORDER BY logdate DESC

-- Drop the temporary table

DROP TABLE #ErrorLog

SQL Server – TSQL Script to Check Job Run Status

By Enrique 11/03/2017 30/04/2020 SQL SERVER, Database Administration

Below is a T-SQL script to check SQL job run status.

SET NOCOUNT ON

--Checking for SQL Server verion

IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8

BEGIN

---This is for SQL 2k5 and SQL2k8 servers

SET NOCOUNT ON

SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,

j.name AS job_name,

CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,

CASE jh.run_status WHEN 0 THEN 'Error Failed'

WHEN 1 THEN 'Succeeded'

WHEN 2 THEN 'Retry'

WHEN 3 THEN 'Cancelled'

WHEN 4 THEN 'In Progress' ELSE

'Status Unknown' END AS 'last_run_status',

ja.run_requested_date as last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,

ja.next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message) AS step_description

FROM

(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status

END

ELSE

BEGIN

--This is for SQL2k servers

SET NOCOUNT ON

DECLARE @SQL VARCHAR(5000)

--Getting information from sp_help_job to a temp table

SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,

CASE last_run_outcome WHEN 0 THEN ''Error Failed''

WHEN 1 THEN ''Succeeded''

WHEN 2 THEN ''Retry''

WHEN 3 THEN ''Cancelled''

WHEN 4 THEN ''In Progress'' ELSE

''Status Unknown'' END AS last_run_status,

CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,

CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,

CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,

CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,

last_run_date AS lrd, last_run_time AS lrt

INTO ##jobdetails

FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'

exec (@SQL)

--Merging run date & time format, adding run duration and adding step description

select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status,

CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,

CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message) AS step_description

from (##jobdetails jd LEFT JOIN msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null

order by jd.job_name,jd.job_status

--dropping the temp table

drop table ###jobdetails

END

Facebook

Google

Twitter

Author: Enrique

SQL Server – Script to Check Plan Cache Bloat

SQL Server – Check if “Enforce Password Policy” is On Using TSQL

SQL Server – The EXECUTE permission was denied on the object ‘xp_instance_regread’, database ‘mssqlsystemresource’,schema ‘sys’ (Error 229)

SQL Server – View FileNames and Paths Using TSQL

SQL Server – Check Installation Date of SQL Server

SQL Server – Get VLF Counts for All Databases in an Instance

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication

SQL Server – Check Latest Transaction Log Backup

SQL Server – Check SQL Server Error Logs via T-SQL

SQL Server – TSQL Script to Check Job Run Status

Recent Posts

Archives

Categories

Recent Posts

Archives

Categories

Tag Cloud