CPU 100% - Check for Parallel Queries

If CPU is 100% for extended periods and there are performance issues– check for Parallel queries.

This quick and dirty check does not replace an indepth root cause analysis , but it does give you some quick feedback . Use the feedback to take action and then focus on fixing the bottleneck

The CXPACKET wait type occurs when more than one CPU core exists. Queries will run in parallel  and the waits will occur when one part of the query is waiting for another thread to complete it’s workload.

Read more on SQL Server - Waitstats CXPACKET wait type and how to reduce it

 

--Parallel running tasks – with the multiple active requests 
select * from sys.dm_os_tasks as t 
where t.session_id in ( 
select t1.session_id 
from sys.dm_os_tasks as t1 
group by t1.session_id 
having count(*) > 1 
and min(t1.request_id) = max(t1.request_id)); 

--Parallel  Requests –returning detailed information about executed requests 
select * 
from sys.dm_exec_requests as r 
join ( 
select t1.session_id, min(t1.request_id) 
from sys.dm_os_tasks as t1 
group by t1.session_id 
having count(*) > 1 
and min(t1.request_id) = max(t1.request_id) 
) as t(session_id, request_id) 
on r.session_id = t.session_id 
and r.request_id = t.request_id;

 

Read More

Waitstats CXPACKET wait type and how to reduce it - SQL Server DBA

Datawarehouse Wait Stats - SQL Server DBA

SQL Server – More processor speed or cpu cores - SQL Server DBA

Place data definition statements at top of stored procedure – DDL

Place CREATE TEMP TABLE ,  DECLARE TABLE  variables and  ALTER TABLE  data definition lamguage (DDL) statements at the top of any stored procedure or function.    The causes the stored procedure to recompile in one batch.

Avoid placing these statements in multiple places , as a recompile is forced on each occasion.  Keep it to one recompilation by placing the  CREATE TEMP TABLE ,  DECLARE TABLE  , ALTER TABLE  statements at the stored procedure beginning.

Excessive recompilations are CPU cycle expensive. Therefore minimizing recompilations  will assist in maintaining SQL Server performance. Read more on recompilation troubleshooting SQL Server - How to improve Execution Plan Reuse - SQL Server DBA

Use these two event classes to trace and identify recompilation at a stored procedure and stored procedure level:

SQL:StmtRecompile

SP:Recompile

Read More

SQL Server – AUTO CREATE STATISTICS - SQL Server DBA

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server execution plan as text - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

 

SQL Server has good default values

SQL Server – particularly since SQL Server 2005 – has good default values. I invest time in profiling application requirements and setting up the database server from the outset . There must be a compelling reason, backed up by measurements or root cause analysis, to change a default setting .

In most database server systems there isn’t a sweet spot  : maximum connections and buffer pool size combining to handle any type of database activity. In reality, SQL Server  monitoring  ,  performance metrics  and  awareness of resource usage  to identify contention.

This leads me to asking – how does a DBA approach a troubleshooting?  I’ve spent all this time setting up the server,  yet  performance issues start.  Of course, database tasks are added and modified  impacting the time they take to complete. Often I find the problem is not a performance problem but a capacity planning issue. But, from the users perspective  the task is running slower  compared to last week, so I better move quickly!

 Performance troubleshooting is all about first gathering good quality information . Relevant data and systemactic approach. Systematic doesn’t mean a rigid checklist , it’s about keeping the process simple and focusing on facts to solve the root cause. Read more SQL Performance tuning - Asking the right question

In most cases,SQL Server offers enough information to troubleshoot . Use of undocumented features and generally hacking away is a low percentage return on investment in troubleshooting time

You may hit the jackpot and solve the problem. Trying to understand why the problem occurred – is very important in solving the problem – and deciding which SQL Server configuration values or code changes to put in place.

 Read More

SQL Performance tuning - Asking the right question

SQL Performance tuning - Drilling into the problem

SQL Server – SSMS query tuning – simulate the application

SQL Performance Tuning - optimizing for response time

Mobile Commerce and SQL Performance Tuning

 

SQL Server - AppDomain is marked for unload due to memory pressure

On SQL Server Error Log messages I see this error message relating to memory pressure:

AppDomain xxxxxxxxxxxxxxxx is marked for unload due to memory pressure.
AppDomain xxxxxxxxxxxxxxxx is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.

 

Some important points to consider:

1) SQLCLR runs in the SQLOS in limited memory space called MemToLeave.A number of processes run inside the MemToLeave – such as OLE automation and 3rd party backup applications. For a comprehensive list check: SQL server OLE Automation memory pressure - SQL Server DBA

2) Investigate other applications running on the server. Huge memory bottlenecks can occur with external applications on the server.

3) Look at server memory and SQL Server memory. Is there enough memory?

4) Use these Views for informations on assemblies and application domains on the server

sys.assemblies
sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies

5) Watch the memory problem on:

select * from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'

 Read More

SQL Server Performance ,memory pressure and memory usage

SQL Server – Memory leak detection - SQL Server DBA

SQL Server – Troubleshoot memory pressure with Default Trace and Server Memory Change event class

SQL Server – Measuring SQL memory usage - SQL Server DBA

 

SQL Server – DBCC SQLPERF(netstats) and network activity

A useful undocumented SQL Server command is DBCC sqlperf (netstats). It allows the DBA to monitor and report usage of network activity of the SQL Server Instance.

Reporing on network usage is useful in different situations, such as analysing communication between SQL Server and the the network

 The command are :

 

--issue the commnand to view the details
DBCC SQLPERF('netstats')

--clear existing statistics to start a new count
DBCC SQLPERF ('netstats', CLEAR);

 The output is :

Statistic |          Value

Network Reads  2.0427E+07

Network Writes  3.20437E+07

Network Bytes Read      -1.322977E+09

Network Bytes Written   -2.034788E+08

Command Queue Length            0

Max Command Queue Length     0

Worker Threads 0

Max Worker Threads      0

Network Threads            0

Max Network Threads     0

Read More

SQL Server – netstat monitoring and tuning performance - SQL ...

 

SQL Server - How to find Open Transactions

SQL Server – Current queries executing on SQL Server

SQL Server – network packet size - SQL Server DBA

Speed and Duplex settings - SQL Server DBA