Home » SQL Server Administration » Page 6

SQL Server Administration

sql server DBA 999

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… Read the rest

sql server DBA 999

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 … 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

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