SQL Server 2008 Extended Events 3

Now lets create a New Event Session to capture Deadlock Victims


IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Demo_session_Deadlock')
DROP EVENT SESSION Demo_session_Deadlock ON SERVER
GO

-- CREATES THE SESSION
CREATE EVENT SESSION Demo_session_Deadlock
ON SERVER
ADD EVENT
sqlserver.lock_deadlock
( ACTION ( sqlserver.database_id,
sqlserver.client_app_name,
sqlserver.sql_text ) )
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = 'G:Datadata2.xel' , metadatafile = 'G:Datadata2.xem')
GO

-- STARTS THE SESSION
ALTER EVENT SESSION Demo_session_Deadlock ON SERVER STATE = START
GO

Now create a deadlock by using some updates

--DeadLock Simulation
--First Session
begin tran
update [Demo1] set [Column1] = 'abcd'

/*

--Second Session, Run this from Another window

begin tran
update [Demo2] set … Read the rest

SQL Server 2008 Extended Events 2

SQL Server 2008 Extended Events 2:

Now lets create a New Event Session to capture LongRunning Queries which are running for more that 10 seconds

Now in this post we will write the details of the event Ringer Buffer and XML file. It is not necessary to write at both places but for this demo we will write to both of them.


-- Extended Event for finding the long running queries
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRQ')
DROP EVENT SESSION LongRQ ON SERVER
GO
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LongRQ')
DROP EVENT SESSION LongRQ ON SERVER
GO
-- Create Event Session
CREATE EVENT SESSION LongRQ
ON SERVER
-- Add events to capture events
Read the rest

SQL Server 2008 Extended Events 1

SQL Server 2008 Extended Events 1:

This is a new bee article for Extended Events.


--Now quickly searching to view all events based on sql keyword
select * from sys.dm_xe_objects
where OBJECT_type = 'event'
and name like'%sql%'

--Now for viewing all object columns of any events
select * from sys.dm_xe_object_columns
where [object_name] = 'sql_statement_completed'

--To view actions in Extended events
select * from sys.dm_xe_objects
where OBJECT_type = 'action'

Events: The SQL Server code is populated with event calls that which is in disabled state by default. Adding events to a session enables those event calls. Once enabled, they will execute the set of functionality defined by the session.

Target: This is an Extended Event Object that can be used to … Read the rest

SQL Server 2008 Update Statistics and Index Rebuild

SQL Server 2008 Update Statistics and Index Rebuild:

Whenever a Index is rebuild the statistics are updated but not for the non indexed Statistics on the table. This means if a Index rebuild occurs the Statistics are updated for the indexes which is a known fact. But if we have many other statistics which are not on Indexes then they are not updated by this Index Rebuild.

Let us do a quick Demo below:

Let us create a Demo table with a Index and a non index statistics created on it.


SET NOCOUNT ON

DECLARE @RowID INT
SET @RowID = 0
if exists(select name from sys.tables where name = 'DemoTable')
begin
drop table DemoTable;
end
CREATE TABLE DemoTable(
ID INT,… Read the rest

SQL Server RPO and RTO

Recovery Point Objective(RPO) describes a point in time that data can be restored from. For instance, if there is data corruption, Data loss or unavailability, at what point in time can a valid copy of the data be restored from? RPO is usually measured as minutes, hours, days, weeks, etc… for Business Continuity.

If a database is corrupted then can we restore it back to what it was 15 minutes ago? This would be an RPO of 15 minutes for Business Continuity..

Recovery Time Objective (RTO) is the amount of time which data or hardware is desired to be restored after a data corruption or hardware failure.

If a Database server just died. Time needed to bring it up and … Read the rest

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 various Data collection to ensure
Read the rest

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 dealing with ASYNC_NETWORK_IO Wait types … Read the rest

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

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:

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

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