Category: SQL SERVER

No comments

SQL Server – Top Queries Consuming CPU

  By ,

Below is a script that captures that top SQL queries consuming CPU.

SELECT TOP 50
[Avg. MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000 / qs.execution_count,
[Total MultiCore/CPU time(sec)] = qs.total_worker_time / 1000000,
[Avg. Elapsed Time(sec)] = qs.total_elapsed_time / 1000000 / qs.execution_count,
[Total Elapsed Time(sec)] = qs.total_elapsed_time / 1000000,
qs.execution_count,
[Avg. I/O] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total I/O] = total_logical_reads + total_logical_writes,
Query = 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
),
Batch = qt.[text],
[DB] = DB_NAME(qt.[dbid]),
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
where qs.execution_count > 5 --more than 5 occurences
ORDER BY [Total MultiCore/CPU time(sec)] DESC
Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Check Database Mirroring Status

  By ,

Below is a script to check the database mirroring synchronization state.

SELECT 
	@@SERVERNAME as Server_Name,
	DB_NAME(database_id) as Database_Name,  
	mirroring_state_desc,
	mirroring_role_desc,
	mirroring_safety_level_desc
FROM 
	sys.database_mirroring
WHERE  
	mirroring_role IS NOT NULL

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
9 Comments

SQL Server – Get Size of All Databases in MB and GB

  By , ,

The script below will retrieve the size of all your databases in MB and GB.

 SELECT d.NAME
    ,ROUND(SUM(CAST(mf.size AS bigint)) * 8 / 1024, 0) Size_MBs
    ,(SUM(CAST(mf.size AS bigint)) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.NAME
ORDER BY d.NAME

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – Find Owners of SQL Server Agent Jobs Using TSQL

  By , ,

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

2 Comments

SQL Server – Script to Check Plan Cache Bloat

  By ,

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

No comments

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

  By , ,

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

No comments

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

  By ,

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')

No comments

SQL Server – View FileNames and Paths Using TSQL

  By , ,

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

No comments

SQL Server – Check Installation Date of SQL Server

  By ,

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

No comments

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

  By ,

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