Home » Performance Tuning » Page 2

Performance Tuning

sql server DBA 999

SQL Server 2008 Index Maintenance Rebuild vs Reorganize

Today, let us discuss on Index Maintenance on SQL Server. Indexes tend to get fragmented as the data gets updated. If the update is under the free space on page which was specified using Fill Factor of index creation or Rebuild then it would not be fragmented. The clustered index is the index by which the Leaf Nodes are Stored and thus does not require additional space.

ALTER INDEX ... REORGANIZE
ALTER INDEX .... REBUILD

Here are some Pros and Cons between the two:
1) Index rebuild works by re-creating the index internally and when that completes, it drops the existing index where as index reorganize is the process of physically re-organizing the leaf nodes of the index.
2) During … Read the rest

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
CREATE TABLE DemoTable(
ID INT,… 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

SQL Server Raid Configurations and Recommendations

A SQL Server DBA given choice to select his Disk configuration should know the different Raid configurations and should select them appropriately.

Common RAID Levels used are:
RAID 0
RAID 0 which is also called a strip set. It is Very fast but have no redundancy.
A RAID 0 array must have 2 or more disks. A RAID 0 array has no redundancy. As data is written to the array it is spread across the disks for maximum speed. This RAID level should never be used for a SQL Server. In the event of a failure of a single disk in the array all data is lost. To calculate the space created from a RAID 0 array multiple the number … Read the rest

sql server DBA 999

Dynamic Management Views in SQL Server

Dynamic Management Views in SQL Server exists in SYS schema and their names start with DM_.
To query a DMV, we should prefix the view name with SYS.
Ex: To know total physical memory of the SQL Server we can use:

SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info

The Mostly used DMV’s to check the current behaviour of SQL Server are:
1. SQL Server related/Hardware Resources DMV
2. Database related DMV
3. Index related DMV
4. Execution related DMV
1. SQL Server Related DMV
This section details the DMV’s associated with SQL Server system.
Below are some of the DMV’s which are related to OS, Disk and Memory.
i.sys.dm_os_sys_info
This view returns the information about the SQL Server machine, available … Read the rest

sql server DBA 999

Creating a Central Management Datawarehouse in SQL Server 2008

What is the Management Data Warehouse?
The Management Data Warehouse(MDW) is a single database/Data Warehouse created inside of a SQL Server 2008 instance, which holds various performance related statistics. The performance statistics in the MDW are gathered via special data gathering routines, known as Data Collections. The MDW can contain Data Collection information from a single instance, or it can hold data collected from multiple instances(Steps shown at the end). The Data Collection process uses SSIS and SQL Agent jobs to gather, schedule and purge information in the MDW.

SQL Server 2008 provides three different system Data Collection definitions. These Data Collections are Disk Usage, Query Activity, and Server Activity. Each of these Data Collection definitions identifies the data that … Read the rest

sql server DBA 999

SQL Server Resource Governor

Microsoft has introduced an interesting and exciting new feature in SQL Server 2008; it is named the Resource Governor. It allows the user to limit the CPU and memory usage by a specific application or user. It would help in preventing any particular user / application to eat up all the resources of the SQL Server.
Terminologies:
Resource Pool
It is the collection of physical resources of the database management system. It is viewed as a virtual SQL Server instance within the SQL Server as visible by the application. It is defined with minimum and maximum values of resource (memory and CPU) utilization. SQL Server 2008 allows a maximum of 18 user defined pools and two built-in pools. There are … Read the rest