Archive for the ‘SQL Query’ Category

SQL Server Query optimization

Posted by Karthick P.K on November 6, 2012

SQL Server Query optimization (or) Tuning slow queries in SQL Server.

How to troubleshoot (or) tune slow queries in SQL Server, Optimize slow queries to run faster , resolve error sql server -2147217871 Query timeout expired and make them run faster?

A query in considered to be slow when it is executing for longer duration than expected. Total duration of the query can be broken in to compile time, CPU time and Wait time.

Before you start troubleshooting the query which is running for longer duration, Identify if the query is slow because it is long waiting (or) Long running (or) Long compiling.

Compile time:Time taken to compile the query. compile time can be identified by looking at the

1. CompileTime=”n” in XML plan

2. SQL Server parse and compile time when Set statistics time on is enabled.

CPU time: Time taken by the query in CPU (Execution time – (compile time+ wait time). CPU time can be identified by looking at the

1. CPU column in profiler.

2. CPU time under SQL Server Execution Times when statistics time on is enabled.

Execution time: Time taken by the query for complete execution ( Execution time = CPU time (CPU time for compilation+execution) +Wait time). Total duration of the query can be identified by using the

1.Duration column in profiler

2. SQL Server Execution Times, elapsed times when statistics time on is enabled.

What is long waiting query?

A query is considered to be long waiting query, when it spend most of its time waiting for some resource.

How to identify if the query is long waiting?

Long running query can be identified by comparing the CPU and duration column in profiler (or) CPU and elapsed time when statistics time on is set .

When a query is waiting for a resource (such as lock, network I/O, Page_I/O Etc) it will not consume CPU. So if you see duration being higher than CPU (Difference between Duration and CPU is wait time),It indicates that the query has spent large amount of time waiting for some resource.

Let us see an example of long waiting query. I have collected profiler trace while executing the query.

set statistics io on

set statistics time on

–Place your query here

select top 10000 * from a

set statistics io off

set statistics time off

Look at the Duration and CPU column in the profiler Cpu=256 and duration =1920. So this query has spent majority of time waiting for some resource.

Look at the output of statistics time and statistics I/O in above image.

SQL Server has spent only 2 milliseconds compiling the query and 256 milliseconds on CPU, but the overall duration was 1920 milliseconds so the query has spent maximum time waiting for some resource.

Identify the resource in which this query is waiting on using one of the steps listed below.

1. Look at the wait type column of the sysprocesses for the spid which is executing query while the query is executing.

2. If there is no other activity on the server collect sys.dm_os_wait_stats output before and after the query execution and identify the wait (Will not help in tuning queries running for short duration)

3. Collect XEvent to gather the wait stats of individual query.

Once you identify the resource in which the query is waiting on tune the resource. Most of the times queries would be slow waiting for below resource.

PAGEIOLATCH_* or Write log: This indicates I/O resource bottleneck follow the detailed troubleshooting steps mentioned in This Link to fix the I/O bottleneck. If you find SQL Server spawning excessive I/O Create necessary indexes.

a. Logical reads + Physical reads in statistics I/O output (Refer above image) or Reads and writes in profiler will indicate the I/O posted by this query. If you see very high reads for query compared with the result rest retuned by query it is an indication of missing indexes or bad plan. Create necessary indexes (You can use DTA for index recommendations.).

PAGELATCH_*: This waittype in sysprocesses indicates that SQL Server is waiting on access to a database page, but the page is not undergoing physical IO.

a.This problem is normally caused by a large number of sessions attempting to access the same physical page at the same time. We should Look at the wait resource of the spid The wait_resource is the page number (the format is dbid:file:pageno) that is being accessed.

b. We can use DBCC PAGE to identify object or type of the page in which we have the contention. Also it will help us to determine whether contention is for allocation, data or text.

c. If the pages that SQL Server is most frequently waiting on are in Tempdb database ,check the wait resource column for a page number in dbid 2 Ex(2:1:1 or 1:1:2). Enable TF 1118 and increase the number of TEMPDB data files and size them equally (You may be facing tempdb llocation latch contention mentioned in http://support.microsoft.com/kb/328551)

d. If the page is in a user database, check to see if the table has a clustered index on a monotonic key such as an identity where all threads are contending for the same page at the end of the table. In this case we need to choose a different clustered index key to spread the work across different pages.

LATCH_*: Non-buf latch waits can be caused by variety of things. We can use the wait resource column in sysprocesses to determine the type of latch involved(KB 822101).

a. A very common LATCH_EX wait is due to running a profiler trace or sp_trace_getdata Refer KB 929728 for more information.

b. Auto Grow and auto shrink while query is executed.

c. Queries going for excessive parallelism.

Blocking (LCK*): Use the query in This Link to identify the blocking. Tune the head blocker.

Asynch_network_io (or) network IO: Keep the result set returned by the query smaller. Follow detailed troubleshooting refer This Link

Resource_semaphore waits: Make sure there is no memory pressure on the server Follow steps in This Link for detailed troubleshooting.

SQL Trace: Stop all the profiler traces running on the server. Identify the traces which are running on the server using the query in This Link

Cx packet: Set the Max degree of parallelism. But remember Cxpacket wait type is not always a problem.

a. For servers that have eight or less processors, use the following configuration where N equals the number of processors: max degree of parallelism = 0 to N .

b. For servers that use more than eight processors, use the following configuration: max degree of parallelism = 8.Refer This Link

SOS_SCHEDULER_YIELD : Identify if there is CPU bottleneck on the server. This waiting means that the thread is waiting for CPU.

a. SQL Server worker thread’s Quantum target is 4ms which means the thread(worker) Will ( is expected to) yield back to SQL Server scheduler when it exceeds 4ms and before it yields back it check if there are any other runnable threads, If there is any runnable threads then the thread which is in top of runnable list is scheduled and current thread will go to the tail of the runnable list and will get rescheduled when the other threads which are already waiting in SOS Scheduler (runnable list) finishes its execution or quantum. The time thread spends in runnable list waiting for its quantum is accounted as SOS_SCHEDULER_YIELD. You will see this type when multiple threads are waiting to get CPU cycle. Follow trouble shooting the steps mentioned This Link

Important: In SQL Server instances when there more than 1 CPU it is possible that the CPU is higher than the duration. Because CPU is sum of time spend by query in all the CPU’s when choosing a parallel whereas the duration is actual duration of the query.

What is long running query?

A query is considered to be long running query, when it spend most of its time on CPU and not waiting for some resource.

How to identify if the query is long running ?

Long running query can be identified by comparing the CPU and duration column in profiler (or) CPU and elapsed time when statistics time on is set . If the CPU and duration is close than the query is considered to be long running. If the query is long running identify where the query spend the time ,It could be for compiling or post compilation (For executing the query). compare the duration of the query with CompileTime (XML plan compile time (or) SQL Server parse and compile time when statistics time is on refer above image).

High Compile time:

Compare the duration of the query with Compile Time (XML plan compile time (or) SQL Server parse and compile time when statistics time is on).Compile time will normally be in few millisecond . Follow the below steps if you see high compile time

1. Identify if you have large token perm refer http://support.microsoft.com/kb/927396

2. Create necessary indexes and stats. Tune the query manually (or) in DTA and apply the recommendation

3. Reduce the complexity of query. Query which joins multiple tables (or) having large number of IN clause can taking a while to compile.

4. You can reduce the compile’s by using force parameterization option.

High CPU time:

Compare the duration of the query with Compile Time (XML plan compile time (or) SQL Server parse and compile time when statistics time is on). If the compile time is very low compared to the duration. Then follow the below steps.

1. Update the stats of tables and indexes used by the query (If the stats are up to date Estimated rows and estimated execution will be approximately same in execution plan .If there is huge difference stats are out dated and requires update) .

2. Identify if the query has used bad plan because of parameter sniffing (If the ParameterCompiledValue and ParameterRuntimeValue is different in XML plan). Refer THIS LINK to know more about Parameter Sniffing

3. If updating the stats and fixing the parameter sniffing doesn’t resolve the issue it is more likely optimizer is not able to create efficient plan because of lack of indexes and correct statistics. Run the query which is driving the CPU in database tuning advisor and apply the recommendations. (You will find missing index detail in xml plan but DTA is more efficient).

4. If the query which is running longer and consuming CPU is linked server query try changing the security of linked server to ensure linked server user has ddl_admin or dba/sysadmin on the remote server. More details regarding the issue in THIS LINK.

5. Ensure optimizer is not aborting early and creating bad plan. For details refer THIS LINK

6. Ensure the query which is spiking the CPU doesn’t have plan guides (xml plan will have PlanGuideDB attribute. Also sys.plan_guides will have entries) and query hints(index= or (option XXX join) or inner (Join Hint) join).

7. Ensure that SET options are not changed.

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

Thank you,

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

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Performance, SQL General, SQL Query | Tagged: query optimization, query performance tuning, Query tuning, query tuning in sql server, sql performance, sql query optimizer, sql query tuning, sql server -2147217871, sql server query tuning, Tuning sql server query | 11 Comments »

What is RESOURCE_SEMAPHORE_QUERY_COMPILE?

Posted by Karthick P.K on October 12, 2012

What is RESOURCE_SEMAPHORE_QUERY_COMPILE?

Before we understand the RESOURCE_SEMAPHORE_QUERY_COMPILE let us see what is compile memory.

Compile memory: When a query is compiled in SQL Server, the compilation process needs memory (for parsing, algeberaization and optimization) called compile memory. This memory doesn’t include the memory required to execute the query.

Challenges with Compile memory: This memory used for Query compilations are usually expected to fit into SQL Server main-memory and to be relatively short-lived. Like any other consumers of Bpool, this is implemented by “stealing” pages from the Buffer Pool and hence it blocks other memory consumers from using that memory until a query compilation completes.

Even if the SQL Server has enough memory to service multiple simultaneous query compilation, allowing all of them to occur at the same time might lead to stealing a significant number of pages from the buffer pool, with consequent increased physical IO , poor performance of query execution and causing memory pressure within SQL Server. However on the other side, a throttling mechanism that is too restrictive could lead to a non-optimal usage of the system resources and decreased throughput for compile intensive workloads, So SQL Server came with more dynamic approach to solve the problem which is to better manage system resources, memory in particular. Such management should hence be based on and driven by the amount of memory used.

Let us see it with example:

Assume SQL Server Max server memory is set to 1000MB and Currently data /index pages is consuming 800MB with in Max server memory (bpool) and 3 queries are reaching SQL Server for compilation, each of them requiring 300 MB for compilation.

If all three queries are compiled simultaneously total compilation might take 900MB of memory causing all the data and index pages to be dropped from BPOOL causing consequent increased physical IO and poor performance of query during execution(to bring data pages back to memory). On the other hand let us assume each of this 3 queries need only 2 MB of compilation memory, There is no reason for SQL Server to throttle the number of compilation.

To overcome above challenges SQL 2005+ throttles the number of concurrent compiles that can happen at any time based on memory usage during the compile. SQL Server memory grant is controlled by a object called “Resource Semaphore” and has internal mechanism to detect how much memory has been used by each compile. There are three gateways (semaphores) called the small, medium, and big gateway. When a request is received for compilation SQL Server will start compilation. There is no limit on how many queries can be compiled simultaneously, but when memory usage for a query reaches the threshold for a given gateway it will then acquire that semaphore of next gateway before continuing. The semaphores (Queries which can be compiled concurrently) are set up to allow 4*schedulers count for the small gateway, 1*schedulers count for the medium gateway and 1 (per SQL instance) for the big gateway.

The small gateway has a fixed threshold for how much memory must be consumed before you enter it. The medium and big gateways have dynamic thresholds that vary depending on how much memory is available, how much is stolen, etc.

If the semaphore can’t be acquired then you see this wait type (Query is waiting for memory grant to compile a query = RESOURCE_SEMAPHORE_QUERY_COMPILE wait). This behavior lets SQL Server to allow only a few memory-intensive compilations occur at the same time. Additionally, this behavior maximizes throughput for smaller queries.

How to identify RESOURCE_SEMAPHORE_QUERY_COMPILE waits?

To get an idea look at the sys.sysprocesses table for sessions waiting on RESOURCE_SEMAPHORE_QUERY_COMPILE

select sp.*, st.text from sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st

WHERE sp.lastwaittype LIKE ‘RESOURCE_SEMAPHORE_QUERY_COMPILE%’ ORDER BY sp.waittime DESC;

There could be two possible reasons for RESOURCE_SEMAPHORE_QUERY_COMPILE waits

1. Memory pressure within SQL Server caused by others using lot of stolen memory or OS memory pressure

In this case you will see thresholds for medium and big gateways very low. In this situation you have to identify who is consuming most of stolen memory and tune them to increase the available memory or add additional memory that can used by SQL server. When the available memory decrease, threshold for medium and big gateways would decrease significantly and increase the number of queries which have to enter medium /big gateways, So the number of parallel compiles will decrease increasing the overall wait time.

This DBCC memory status output is from system which has 48 processor and has excessive RESOURCE_SEMAPHORE_QUERY_COMPILE waits because of memory pressure.

Small Gateway (default) Value

—————————————- ———–

Configured Units 192 // 190 number of units . 48 CPU’s * 4=192

Available Units 109

Acquires 83

Waiters 0

Threshold Factor 380000

Threshold 380000

(6 row(s) affected)

Medium Gateway (default) Value

—————————————- ———–

Configured Units 48 //48 number of units. SO 48 CPU’s *1=48

Available Units 0 //There is no available slots. All slots are busy.

Acquires 48

Waiters 34 //34 Queries are waiting

Threshold Factor 12

Threshold 2204603 //Threshold is very low 2 MB (This value is in bytes)

(6 row(s) affected)

Big Gateway (default) Value

—————————————- ———–

Configured Units 1 //1 per instance

Available Units 0 //There is no available slots. All slots are busy.

Acquires 1

Waiters 47 // 47 Queries are waiting

Threshold Factor 8

Threshold 3306905 //Threshold is very low 3 MB (This value is in bytes)

2. There is huge amount of available memory but the available units in gateway is exhausted. This situation normally occurs when we have many queries that have high compile time and use lot of memory for compilation.

Compile time and Compile memory can be captures using the show plan XML for query compile event in profiler.

Below is extract from show plan XML for query compile event in profiler. This query has used approximately 150 MB of compile memory and the threshold for Big gateway is around 143 MB . So only one query which needs more than 143 MB can compile at a time , This can cause contention when there are multiple queries waiting for compile/recompile. Also an important factor to notice in this XML plan is compile time is ~139 times the CPU. So likely this query waited for most of the time for resource semaphore.

Big Gateway (default) Value

—————————————- ———–

Configured Units 1

Available Units 0

Acquires 1

Waiters 47

Threshold Factor 8

Threshold 149640500

How to fix RESOURCE_SEMAPHORE_QUERY_COMPILE waits?

1. Add additional memory to SQL Server.

2. Reduce the number of compile and recompiles happening in SQL Server.

3. Threshold for the gateways are dynamic (except for small gateway) and therefore memory pressure occurs from other sources (Internal to SQL Server or system wide) reduces the amount of memory available for compiles and Queries are throttled to higher gateways sooner. Make sure SQL Server is not starving for memory.

4. Identify the queries which are consuming large compile memory and tune them (CompileMemory in show plan XML query compile can be used).

5. RESOURCE_SEMAPHORE_QUERY_COMPILE waits are very common in 32-bit SQL Server because of virtual address space limitation, so migrate to 64-Bit SQL Server.

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 Memory, Performance, SQL Query, SQL Server Engine | Tagged: Compile memory SQL Server, CompileMemory, RESOURCE_SEMAPHORE, RESOURCE_SEMAPHORE waits, RESOURCE_SEMAPHORE_QUERY_COMPILE, RESOURCE_SEMAPHORE_QUERY_COMPILE waits in SQL Server | 14 Comments »

Optimizer Timeout or Optimizer memory abort

Posted by Karthick P.K on October 7, 2012

Optimizer Timeout

When the query processor finds itself consuming a lot of time optimizing a query, it may decide to stop the optimization process abruptly, and choose the best available plan. This is to ensure that the optimizer doesn’t end up optimizing forever. This is called optimizer timeout (based on the number of plans considered relative to the cost of the best plan so far).

Optimizer memory abort

When queries become more complex number of potential plans to consider can quickly grow in thousands. Optimizer has limit for memory it is allowed to use , when the optimizer reaches the limit it ends with optimizer memory abort.

When timeout or memory abort happens optimizer might choose the best plan from plans which was generated till timeout or abort and it might be far from optimal plan so the query execution can take long time and consume resource.

On SQL 2000 and earlier the only way to detect this condition is compiling the query with trace flag 8675. If one of these conditions occur the output will reflect a timeout abort or memory abort, similar to the following:

End of simplification, time: 2.869 net: 2.869 total: 2.869 net: 2.869

end exploration, tasks: 200094 no total cost time: 16.17 net: 16.169 total: 19.04 net: 19.039

*** Optimizer time out abort at task 614400 ***

Msg 8623, Level 16, State 1, Line 3

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

End of simplification, time: 0.156491 elapsed: 0.156491

end exploration, tasks: 1614 no total cost time: 0.552436 elapsed: 0.708927

end search(0), cost: 1275.32 tasks: 3888 time: 0.195008 elapsed: 0.903935

end exploration, tasks: 7596 Cost = 1275.32 time: 0.548032 elapsed: 1.45197

end search(1), cost: 1263.15 tasks: 21985 time: 2.30564 elapsed: 3.75761

*** Optimizer memory usage abort ***

End of optimization, elapsed: 2.98304

From SQL server 2005 to determine whether the query optimizer times out or MemoryLimitExceeded search for the

StatementOptmEarlyAbortReason="TimeOut" (or) StatementOptmEarlyAbortReason="MemoryLimitExceeded" expression in the XML plan output.

We can avoid optimizer from timing out and picking bad plan by enabling trace flag –T8780. This increases the time limit before the timeout occurs.

Note: Don’t enable this trace flag at server level , enable it only for the session which runs the query and identify if the optimizer is picking up a better plan. If you see optimizer picking up the better plan, right approach is to tune the query manually or using DTA and apply the recommendations . You can use this trace flag till you apply the recommendations made by DTA.

If you experience “ Optimizer memory usage abort” use “SQLServer:Memory Manager\Optimizer Memory (KB)” counter to the amount used for compilation .

select * from sys.dm_os_memory_clerks where type=’MEMORYCLERK_SQLOPTIMIZER’ will tell us the overall memory used by optimizer.

We can also use the CompileMemory= expression in XML plan output starting from SQL server2005 SP2 which will give us the compile memory used by individual plans. If you find optimizer memory is very low then identify what is contributing to memory contention in SQL Server and tune it.

I will discuss compile memory in detail when I blog about Resource_semaphore wait types.

Note: You may also receive below error because of few known issues documented in KB articles 982376, 946020,926773,917888 so if none of the fixes resolve the issue you may have to follow the same steps documented above.

{

"Msg 8623, Level 16, State 1, Line 1

}

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

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Posted in Performance, SQL General, SQL Query, SQL Server Engine, SQL Server memory | Tagged: "The query processor ran out of internal resources and could not produce a query plan", "The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large numbe, contact Customer Support Services for more information.", Level 16, Line 3", Msg 8623, Optimizer memory usage abort, Optimizer time out abort, State: 1., StatementOptmEarlyAbortReason="MemoryLimitExceeded", StatementOptmEarlyAbortReason="TimeOut" | 23 Comments »

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: SP_READERRORLOG parameters, SQL Query, SQLServer Tools | 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: Clear cache, clear sqlserver cahe, free sqlserver cache, Free sqlserver memory, Performance, SQL Query | 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: CPU consumed by SQLServer, Performance, SQL Query, SQL Server 100% CPU, SQLServer CPU, SQLServer CPU usage | 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: Blocker script, Blocking, blocking in SQL Server, blocking script, Blocking tree, database blocking sqlserver, how to fix SQL Server blocking, Performance, SQL Query, SQL Server blocking | 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: Rebuild indexfor all tables, reindex all tables in database, Script to rebuild index for all tables, SQL General, SQL Query | 3 Comments »

Using DMVs to find out the index usage history- SQL Server Index Usage

Posted by Karthick P.K on July 6, 2010

SQL Server 2005 ships with a set of DMVs that can help you identify the missing indexes for your workload, Analyze the effectiveness of the existing ones and help find out index fragmentation.

Using DMVs to find out the index usage history

Over a period of time, you could create a lot of indexes on your tables and modify existing ones. However, in SQL 2000, you couldn’t estimate how effective were each of these indexes. Poorly design indexes could lead to performance overhead instead of enhancing performance.

In SQL 2005, you can query the sys.dm_db_index_usage_stats DMV to find out the indexes that have NEVER been used since the last start of SQL Server. You can use the following query to find that out:

select object_name(i.object_id) as ObjectName,

i.name as IndexName, s.user_updates, s.user_seeks, s.user_scans, s.user_lookups

from sys.indexes i

left join sys.dm_db_index_usage_stats s

on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = <dbid>

where objectproperty(i.object_id, 'IsIndexable') = 1 and

-- index_usage_stats has no reference to this index (not being used)

s.index_id is null or

-- index is being updated, but not used by seeks/scans/lookups

(s.user_updates > 0 and s.user_seeks = 0

and s.user_scans = 0 and s.user_lookups = 0)

order by object_name(i.object_id) asc

In the output, you will ALL the indexes that have never been used by any sort of workload on your server, since the last start of SQL Server.

For indexes that have NEVER been used (either for a SELECT or a DML statement), all columns will be NULL

For indexes that have NEVER been used (for a SELECT), but had to be updated due to a DML statement, the user_updates column will be >0, while other columns will be 0. It is these indexes that could cause severe performance overhead for your DML statements and might be worth dropping.

Give ample time for SQL Server to get exposed to all the workload after a restart, before running this query.

Using DMVs to find out missing indexes

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented.

For more information on how to use this feature, please visit the following link

http://msdn2.microsoft.com/en-us/library/ms345417.aspx

Using DMVs to find out index fragmentation

The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

You can learn more about using this DMV to identify fragmentation, correcting it and possibly automating this activity for your server by visiting the following link:

http://msdn2.microsoft.com/en-us/library/ms188917.aspx

Posted in SQL Query, SQL Server Engine | Tagged: Hoe to find if my indexes are used, Index usage, Query tuning, SQL Server index usage, SQL Server indexes not used | 1 Comment »

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: Find all the SQL Server traces, SQL Query, SQLServer profiler traces listing | 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: Current queries executed by SQLserver, Queries which are executing now with plan, Query and plans which are running now, SQL Query, SQL Server CPU is 100% how to find the query | 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: SQL Query | 2 Comments »

Monitoring Tempdb usage

Posted by Karthick P.K on January 13, 2010

Monitoring Tempdb space usage and identifying the session and query which Consumes Tempdb

The total space used by Tempdb consists of

1. User Objects

2. Internal Objects

3. Version Store

4. Free Space.

Use Below Query to Track which objects (above) is consuming pace in TempDb.

SELECT

SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB],SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB],SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in [MB],SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

go

Once you have identified the objects identify the query and session which is consuming tempdb using the query listed below

–Use below query to identify which Query and Session is consuming the space in TempDB

 
SELECT R1.session_id, R1.request_id, R1.Task_request_internal_objects_alloc_page_count, R1.Task_request_internal_objects_dealloc_page_count,

R1.Task_request_user_objects_alloc_page_count,R1.Task_request_user_objects_dealloc_page_count,R3.Session_request_internal_objects_alloc_page_count ,

R3.Session_request_internal_objects_dealloc_page_count,R3.Session_request_user_objects_alloc_page_count,R3.Session_request_user_objects_dealloc_page_count,

R2.sql_handle, RL2.text as SQLText, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle FROM (SELECT session_id, request_id, 

SUM(internal_objects_alloc_page_count) AS Task_request_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count)AS 

Task_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Task_request_user_objects_alloc_page_count,

SUM(user_objects_dealloc_page_count)AS Task_request_user_objects_dealloc_page_count FROM sys.dm_db_task_space_usage 

GROUP BY session_id, request_id) R1 INNER JOIN (SELECT session_id, SUM(internal_objects_alloc_page_count) AS Session_request_internal_objects_alloc_page_count,

SUM(internal_objects_dealloc_page_count)AS Session_request_internal_objects_dealloc_page_count,SUM(user_objects_alloc_page_count) AS Session_request_user_objects_alloc_page_count,

SUM(user_objects_dealloc_page_count)AS Session_request_user_objects_dealloc_page_count FROM sys.dm_db_Session_space_usage 

GROUP BY session_id) R3 on R1.session_id = R3.session_id 

left outer JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id

OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS RL2
Where 
Task_request_internal_objects_alloc_page_count >0 or  
Task_request_internal_objects_dealloc_page_count>0 or 
Task_request_user_objects_alloc_page_count >0 or 
Task_request_user_objects_dealloc_page_count >0 or 
Session_request_internal_objects_alloc_page_count >0 or 
Session_request_internal_objects_dealloc_page_count >0 or 
Session_request_user_objects_alloc_page_count >0 or 
Session_request_user_objects_dealloc_page_count >0

Known issues related to TEMPDB Shrink

FIX: The used space in the tempdb database increases continuously when you run a query that creates internal objects in the tempdb database in SQL Server 2005

Thank you,

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

MSSQLWIKI

Karthick P.K on SQL Server

Karthick P.K

Categories

Archives

Tags

Facebook

Twitter

Do you have a question in SQL Server or stuck in SQL Server issue? Click here to join our facebook group and post your questions to SQL Server experts

Email Subscription

SQLWiki

Subscribe

feedburner email subscription

Visits

SQL Server Blogs

Author

Other SQL blogs

SQLSERVERSCRIBBLES.COM

Archive for the ‘SQL Query’ Category

SQL Server Query optimization

Disclaimer:

What is RESOURCE_SEMAPHORE_QUERY_COMPILE?

Optimizer Timeout or Optimizer memory abort

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG)

Script to free cache

How to find SQL Server and system CPU usage history :

Script to get current blocking tree with wait types

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

Using DMVs to find out the index usage history- SQL Server Index Usage

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

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

Script to clear stats

Monitoring Tempdb usage

Script to get all the Query and cached plans

SQL Server: Table Variables (VS) Temp Tables

Karthick P.K on SQL Server

Karthick P.K

Categories

Archives

Tags

Do you have a question in SQL Server or stuck in SQL Server issue? Click here to join our facebook group and post your questions to SQL Server experts

Email Subscription

SQLWiki

Subscribe

feedburner email subscription

Visits

SQL Server Blogs

Author

Other SQL blogs

Archive for the ‘SQL Query’ Category

Disclaimer:

Share this:

Like this:

Share this:

Like this:

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: