Home » Archives for March 2012

March 2012

sql server DBA 999

SQL Server Analysing Database Trends and Setting up DBA Alerts

In this post let’s discuss how to Set-up internal data warehouse to analyse trends of databases Growths in SQL Server and DBA alerts for Maintenance and diagnosing SQL Servers.

The important information which are important for analysing the Database growth and Disk sizes growth etc. Can be captured to a central location for analysing the trends. A DBA is responsible for analysing the trends in SQL Server and providing management the relevant information on like expected growth of the Server/Database, Resources needed going further. If necessary a DBA will be involved in Archiving the older data into another server for various reasons like database growth, database table sizes, query performance etc.
DBA should set up
Read the rest

sql server DBA 999

ASYNC_NETWORK_IO Waits SQL Server 2005 2008 2012 2014 2016

ASYNC_NETWORK_IO Waits SQL Server 2005 2008 2012 2014 2016

ASYNC_NETWORK_IO Wait type in SQL 2005 2008 2012 2014 or 2016 are associated with either a Network Performance issues in the Environment or a the client application is not processing results quickly enough from SQL Server.

As always, if you are One of the DBA’s who check Top Waits Stats on SQL Servers to get a insight on what is SQL Server waiting on actually for this performance issues. Below few tips on troubleshooting ASYNC_NETWORK_IO Waits SQL Server

The Top Waits Stats rollup query is used to find the Top Waits SQL Server is waiting on from last restart of SQL Server.

The First thing when … Read the rest

sql server DBA 999

CXPACKET Waits and High CPU Usage Bottleneck SQL Server 2008 2012 2014 2016

CXPACKET Waits and High CPU Usage Bottleneck SQL Server 2008 2012 2014 2016

When you suspect SQL Server is waiting on CPU and CPU is constantly spiked on 90-100% or SQL server high cpu usage query then it would wise to check the Top Waits Stats on SQL Server to see if the Top Waits is on “CXPACKET” by using Top Waits Rollup Query here.

Most of the time, DBA needs to find the culprit queries which are using high CPU and try to optimize it them by designing proper indexes or by rewriting the SQL more efficiently. SQL Server High CPU Usage query below can be very useful.

The queries useful in troubleshooting CPU Bottleneck issues, which are used

Read the rest
sql server DBA 999

Execution Model in SQL Server 2005 or 2008

Execution Model in SQL Server 2005-2008:

The execution of queries in SQL Server are done One at a time per Scheduler. Which means a scheduler can only have one session running at a time.The SQL Server SQLOS uses schedulers which map to CPUs on the SQL Server(4 CPU = 4Schedulers by default), to manage the execution of user requests. SQLOS Schedulers map to CPUs.

Execution Model showing Running, runnable and suspended tasks:

Execution Model in SQL Server 2005 or 2008 2

There will be only one session Running or executing per scheduler. SPIDs with suspended statuses are placed in Waiter List until the requested resources are available. If a running session needs a data page that is not in cache, or needs a page that is blocked by another … Read the rest

sql server DBA 999

The SQL Server Wait Types with their descriptions

The SQL Server Wait Types with their descriptions

Wait Statistics in SQL Server indicate sql server performance in relation to different resources like CPU, Disk usage, Memory etc..

SQL Server Activity monitor waiting tasks is another important place to observe what SQL Server is waiting on. Activity monitor has different sections like Processes, Resource Waits, Recent Expensive Queries and Data IO Stats.

“Wait_Type, Area, Usage, Version” Description Action
“ASYNC_IO_COMPLETION
I/O
Resource
SQL 2005-2008”
Used to indicate a worker is waiting on a asynchronous I/O operation to complete not associated with database pages Since this is used for various reason you need to find out what query or task is associated with the wait. Two examples of where this wait type
Read the rest
sql server DBA 999

To check where the connections are made to a database

To check where the connections are made to database the below queries are have been very helpful.


-- Get a count of SQL connections by IP address
SELECT ec.client_net_address, es.[program_name],
es.[host_name], es.login_name,
COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections AS ec
ON es.session_id = ec.session_id
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name
ORDER BY ec.client_net_address, es.[program_name];

-- Get a count of SQL connections by login_name
SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;

The above queries can be used in conjuction with Top Waits Stats rollup query here to get the best understanding whats SQL Server is waiting on and where the connections are coming to the SQL Server with aggregated connections … Read the rest