Home » SQL Server » Page 666

SQL Server

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

sql server DBA 999

Different Restore options in SQL Server 2008

Different Restore options in SQL Server 2008:
1. Restore can be done for an entire database from a full database backup (a complete restore).
2. Restore can be done for a part of a database (a partial restore).
3. Restore can be done for specific files or filegroups to a database (a file restore).
4. Restore can be done to specific pages to a database (a page restore).
5. Transaction log Restore can be done onto a database (a transaction log restore).
6. We also have a option to Revert a database to the point in time captured by a database snapshot.

Restoring a full databaseThe following example restores a full database backup from the AdventureWorks2008R2.bak file.

RESTORE DATABASE … Read the rest

sql server DBA 999

Different Backup options in SQL Server 2008

Different Backup options in SQL Server 2008:
1. Backup’s can be taken for a complete database, or one or more files or filegroups.
2. If the Databases are in Full recovery model or bulk-logged recovery model(discussed below) then backup’s can be taken for the transaction log (BACKUP LOG).

MIRROR TO Statement: Specifies a set of up to three secondary backup devices, each of which will mirror the backups devices specified in the TO clause. The MIRROR TO clause must be specify the same type and number of the backup devices as the TO clause. The maximum number of MIRROR TO clauses is three.

Backup Set Options

COPY_ONLY: Specifies that the backup is a copy-only backup, which does not affect the … Read the rest

sql server DBA 999

SQL Server 2008 BACKUP LOG WITH TRUNCATE_ONLY

BACKUP LOG WITH TRUNCATE_ONLY is no longer a recognized BACKUP option. This command get rid of the contents of your SQL Server’s transaction log without really backing it up. This command will to invalidate the point in time recoverability of the database as this breaks the log chain.

This command is used by Database administrators when they end in situations where getting rid of the transaction log is only way by which he can shrink their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.

Microsoft always recommends that DBA’s must switch to simple recovery mode getting rid of the transaction logs instead of truncating logs by above command if no Point in Time recovery is required. … Read the rest

sql server DBA 999

SQL Server 2008 Authentication modes

Before we go into SQL Server 2008 Authentication modes, it is better to have quick look at Active Directory Services.
Active Directory (AD) is a directory service created by Microsoft for Windows domain networks. It is included in most Windows Server operating systems. Server computers on which Active Directory is running are called domain controllers. There can be more than one Domain Controllers in a Organization which are replicated internally. Active Directory serves as a central location for network administration and security. It is responsible for authenticating and authorizing all users and computers within a network of Windows domain type, assigning and enforcing security policies for all computers in a network and installing or updating software on network computers.… Read the rest