Find out who are the owners of the SQL agent jobs by running the script below.
SELECT J.name AS [Job Name] ,L.name AS [Job Owner] FROM msdb.dbo.sysjobs_view J INNER JOIN master.dbo.syslogins L ON J.owner_sid = L.sid GO
No comments
Tag: performance monitoring
SQL Server – Script to Check Plan Cache Bloat
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
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)
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
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
SQL SERVER – LCK_M_XXX – Wait Type
Locking is a mechanism used by the SQL Server Database Engine to synchronize access by multiple users to the same piece of data, at the same time. In simpler words, it maintains the integrity of data by protecting (or preventing) access to the database object.
LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock.
LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock.
LCK_M_IU
Occurs when a task is waiting to acquire an Intent Update (IU) lock.
LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.
LCK_M_S
Occurs when a task is waiting to acquire a Shared lock.
LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock.
LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock.
LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock.
LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.
LCK_M_U
Occurs when a task is waiting to acquire an Update lock.
LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.
LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock.