MSSQLWIKI

Karthick P.K on SQL Server

Posts Tagged ‘SQL Query’

Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG)

Posted by Karthick P.K on February 13, 2011

Parameters which would be useful when you run xp_readerrorlog or sp_readerrorlog

XP_READERRORLOG

 

Syntax:

xp_readerrorlog -> Reads the current SQL Server errorlog

xp_readerrorlog 3 -> Reads SQL Server errorlog “errorlog.3”

xp_readerrorlog -1, 2 -> Reads the current SQL Agent errorlog

xp_readerrorlog 3, 2 -> Reads the SQL Agent errorlog No 3 archive

xp_readerrorlog -1, 2, “ab” -> Reads the current SQL Agent errorlog but returns only those lines that contain the string “ab”

xp_readerrorlog 1, 2, “ab”, “cd” -> Reads the SQLAgent errorlog No 1 archive but returns only those lines that contain the strings
“ab” AND “cd” lines that contain the strings “ab” AND “cd”

xp_readerrorlog [-1 – n], 3, database_id, fulltext_catalog_id -> Reads full
text crawl log

Note:The second INT param is LogType. 1 = SQLServer logs, 2 = SQLAgent logs, 3 =
full text logs(SQLServer log is the default)
Additionally, the last 3 parameters (parameters 5, 6, 7) are used for Date/Time
filtering and ordering

xp_readerrorlog -1, 1, NULL, NULL, ‘2004-09-01’, ‘2004-09-02’, [‘asc’ | ‘desc’]
-> Reads the current SQL Server error log entries starting from midnight of 2004-09-01 until midnight of 2004-09-02.
Time can be included too. The date-time can be in any format recognizable by SQL server.
The last parameter specifies the datetime order that the entries are returned in. ‘asc’ or no
parameter is used for the normal, ascending, order. ‘desc’ is used to return entries in reverse order,
newest entries first.

Note:Also, to increase the number of log files, add a new registry key “NumErrorLogs” (REG_DWORD) under below location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL.X\MSSQLServer\
By default, this key is absent. Modify the value to the number of logs that you want to maintain.

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in SQL Query, 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 »

How to rebuild index and update statistics for all the tables in database.

Posted by Karthick P.K on September 26, 2010

 
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'   --  {can be run anytime}

Exec sp_MSforeachtable "dbcc dbreindex('?')"      --- {Always run this on a off-peak hour on any SQL Server instance}
 



Thanks


Karthick

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

How to find all the profiler traces running on my SQL Server

Posted by Karthick P.K on April 26, 2010

select

      [Status] =

      case tr.[status]

            when 1 THEN 'Running'

            when 0 THEN 'Stopped'

      end

      ,[Default] =

            case tr.is_default

                  when 1 THEN 'System TRACE'

                  when 0 THEN 'User TRACE'

            end

       ,[login_name] = coalesce(se.login_name,se.login_name,'No reader spid')

      ,[Trace Path] = coalesce(tr.[Path],tr.[Path],'OLE DB Client Side Trace')

      from sys.traces tr

            left join sys.dm_exec_sessions se on tr.reader_spid = se.session_id

Posted in SQL Query | Tagged: , , | 4 Comments »

How to get SQL Text and Query Plan for statements which are executing now

Posted by Karthick P.K on February 1, 2010

 
SELECT getdate() as "RunTime", st.text as batch,
SUBSTRING(st.text,statement_start_offset / 2+1 , 
( (CASE WHEN a.statement_end_offset = -1 
THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2) 
ELSE a.statement_end_offset END)  - a.statement_start_offset) / 2+1)  as current_statement
,qp.query_plan, a.* FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as st CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) as qp 
order by CPU_time desc

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Posted in SQL General, SQL Query | Tagged: , , , , | 7 Comments »

Script to clear stats

Posted by Karthick P.K on January 20, 2010

How to reset SQL Server stats with out restarting.

DBCC SQLPERF ('spinlockstats', CLEAR);

 

GO

 

DBCC SQLPERF ('netstats', CLEAR);

 

GO

 

DBCC SQLPERF ('rastats', CLEAR);

 

GO

 

DBCC SQLPERF ('iostats', CLEAR);

 

GO

 

DBCC SQLPERF ('threads', CLEAR);

 

GO

 

DBCC SQLPERF ('logspace', CLEAR);

 

GO

 

DBCC SQLPERF ('umsstats', CLEAR);

 

GO

 

DBCC SQLPERF ('waitstats', CLEAR);

 

GO

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

Script to get all the Query and cached plans

Posted by Karthick P.K on February 18, 2009

 

 

SELECT

    *

 FROM sys.dm_exec_cached_plans cp

 

  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) eqp

 

  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) est

 

 --WHERE   est.dbid = DB_ID('DBNMAE')

Posted in SQL Query | Tagged: , | 1 Comment »