MSSQLWIKI

Karthick P.K on SQL Server

Posts Tagged ‘Performance’

My “c:\” Drive gets full when I open the profiler trace

Posted by Karthick P.K on July 16, 2011

Profiler filling up space of my system drive…….

When you run the profiler trace (GUI) or open the saved profiler trace file your System Drive gets out of space

SQL Server profile uses ‘TMP’ Environment variable for storing the .Trc and trace replay files

$29BC399D487DBB17 

By default TMP directory points to your system drive, so when we open a large profiler trace file or capture trace in GUI mode system drive gets full.

We can workaround this by changing the TMP environment variable. Changing TMP environment variable system wide can cause other issues so I would recommend changing TMP temporarily for the process

1. Open command prompt

SET TMP=G:\TMP

$130609A642FEC889

2. Open the profiler from command prompt

>Profiler90.exe         –>SQL Server2005

>Profiler.exe             –>SQL Server2008

>Mysavedtracefile.trc –>Open a saved tracefile

$4C3BC270A299D0A3

This would make profiler use G:\TMP  for saving temporary data. So your c:\ would never get full.

Posted in SQL Server Tools | Tagged: , , , , , , | 5 Comments »

Script to free cache

Posted by Karthick P.K on December 6, 2010

 

DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL

GO

DBCC FREESESSIONCACHE WITH NO_INFOMSGS

GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS

GO

DBCC DROPCLEANBUFFERS 

GO

Posted in Performance, SQL General, SQL Query | Tagged: , , , , , | 59 Comments »

How to find SQL Server and system CPU usage history :

Posted by Karthick P.K on November 30, 2010

SQL Server and system CPU usage history can be obtained from sys.dm_os_ring_buffers  using below query

Note: For troubleshooting  high CPU usage in SQL Server follow https://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/

SQL Server 2005

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

 

SQL Server 2008

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime, 

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization, 

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM 

(

  SELECT 

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 , 

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record 

    FROM sys.dm_os_ring_buffers 

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

Posted in Performance, SQL Query | Tagged: , , , , , | 16 Comments »

Script to get current blocking tree with wait types

Posted by Karthick P.K on November 24, 2010

 

 

SET NOCOUNT ON; 

SET CONCAT_NULL_YIELDS_NULL OFF 

GO 

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH,waittype,lastwaittype) 

AS 

( 

   SELECT 

   SPID, 

   BLOCKED, 

   CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, 

   REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH, 

   R.waittype, 

   R.lastwaittype 

   FROM sys.sysprocesses R with (nolock) 

   CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

   WHERE (BLOCKED = 0 OR BLOCKED = SPID) 

   AND EXISTS    (SELECT SPID,BLOCKED,CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, 

   BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH,R.waittype,R.lastwaittype FROM sys.sysprocesses R2 with (nolock) 

   CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID) 

 

UNION ALL 

 

SELECT 

    R.SPID, 

    R.BLOCKED, 

    CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, 

    REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH, 

    R.waittype, 

    R.lastwaittype 

    FROM sys.sysprocesses AS R with (nolock) 

    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T 

    INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID 

) 

 

SELECT N'       ' + REPLICATE (N'|      ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS VARCHAR (10)) + ' '  + BATCH AS BLOCKING_TREE ,  waittype ,lastwaittype,  GETDATE() as Time FROM BLOCKERS with (nolock) ORDER BY LEVEL ASC 

go

 

–By Ajith Krishnan

Posted in Performance, SQL Query | Tagged: , , , , , , , , , | 6 Comments »

 
%d bloggers like this: