DBA WORLD

08 March 2011

sql-server-performance-tuning

Use Full Tutorial.

http://sqlserverpedia.com/blog/sql-server-performance-tuning/kevin-kline-tutorial-on-sql-server-query-tuning/

Thanks to Kevin

02 March 2011

Dynamic Management Views

Two types of dynamic management views:

Server-scoped DMV: Stored in Master Database
Database-scoped DMV: Specific to each database

Permission to Execute DMV [Security]

To query a server scoped DMV, the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV, the user must have SELECT privilege on VIEW DATABASE STATE.

GRANT VIEW SERVER STATE to
GRANT VIEW DATABASE STATE to

If you want to deny a user permission to query certain DMVs, you can use the DENY command to restrict access to a specific DMV.

Getting Started

All the DMVs exits in SYS schema and their names start with DM_. So when you need to query a DMV, you should prefix the view name with SYS. As an example, if you need to see the total physical memory of the SQL Server machine; then execute the below TSQL command:

Collapse | Copy Code

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

In this article, I will be explaining some of the DMVs which can be used frequently to understand the current behavior of SQL Server:

SQL Server related [Hardware Resources] DMV
Database related DMV
Index related DMV
Execution related DMV

1. SQL Server Related DMV

This section details the DMVs associated with SQL Server system. SQL DMV is responsible to manage server level resources specific to a SQL Server instance.

This section covers DMVs related to OS, Disk and Memory.

a. sys.dm_os_sys_info

This view returns the information about the SQL Server machine, available resources and the resource consumption.

This view returns information like the following:

CPU Count: Number of logical CPUs in the server
Hyperthread-ratio: Ratio of logical and physical CPUs
Physical_memory_in_bytes: Amount of physical memory available
Virtual_memory_in_bytes: Amount of virtual memory available
Bpool_commited: Committed physical memory in buffer pool
OS_Priority_class: Priority class for SQL Server process
Max_workers_thread: Maximum number of workers which can be created

b. sys.dm_os_hosts

This view returns all the hosts registered with SQL Server 2005. This view also provides the resources used by each host.

Name: Name of the host registered
Type: Type of hosted component [SQL Native Interface/OLE DB/MSDART]
Active_tasks_count: Number active tasks host placed
Active_ios_count: I/O requests from host waiting

c. sys.dm_os_schedulers

Sys.dm_os_schedulers view will help you identify if there is any CPU bottleneck in the SQL Server machine. The number of runnable tasks is generally a nonzero value; a nonzero value indicates that tasks have to wait for their time slice to run. If the runnable task counts show high values, then there is a symptom of CPU bottleneck.

Collapse | Copy Code

SELECT
scheduler_id,current_tasks_count,runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

The above query will list all the available schedulers in the SQL Server machine and the number of runnable tasks for each scheduler.

d. sys.dm_io_pending_io_requests

This dynamic view will return the I/O requests pending in SQL Server side. It gives you information like:

Io_type: Type of pending I/O request
Io_pending: Indicates whether the I/O request is pending or has been completed by Windows
Scheduler_address: Scheduler on which this I/O request was issued

e. sys.dm_io_virtual_file_stats

This view returns I/O statistics for data and log files [MDF and LDF file]. This view is one of the commonly used views and will help you to identify I/O file level. This will return information like:

Sample_ms: Number of milliseconds since the instance of SQL Server has started
Num_of_reads: Number of reads issued on the file
Num_of_bytes_read: Total number of bytes read on this file
Io_stall_read_ms: Total time, in milliseconds, that the users waited for reads issued on the file
Num_of_writes: Number of writes made on this file
Num_of_bytes_written: Total number of bytes written to the file
Io_stall_write_ms: Total time, in milliseconds, that users waited for writes to be completed on the file
Io_stall: Total time, in milliseconds, that users waited for I/O to be completed
Size_on_disk_bytes: Number of bytes used on the disk for this file

f. sys.dm_os_memory_clerks

This DMV will help how much memory SQL Server has allocated through AWE.

Collapse | Copy Code

SELECT
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks

The same DMV can be used to get the memory consumption by internal components of SQL Server 2005.

Collapse | Copy Code

SELECT TOP 10 type,
SUM(single_pages_kb) as [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

g. sys.dm_os_ring_buffers

This DMV uses RING_BUFFER_RESOURCE_MONITOR and gives information from resource monitor notifications to identify memory state changes. Internally, SQL Server has a framework that monitors different memory pressures. When the memory state changes, the resource monitor task generates a notification. This notification is used internally by the components to adjust their memory usage according to the memory state.

Collapse | Copy Code

SELECT
Record FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

The output of the above query will be in XML format. The output will help you in detecting any low memory notification.

RING_BUFFER_OOM: Ring buffer oom contains records indicating server out-of-memory conditions.

Collapse | Copy Code

SELECT
record FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_OOM'

2. Database Related DMV

This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, partition usages, session information usages, etc...

a. sys.dm_db_file_space_usage

This DMV provides the space usage information of TEMPDB database.

b. sys.dm_db_session_space_usage

This DMV provides the number of pages allocated and de-allocated by each session for the database

c. sys.dm_db_partition_stats

This DMV provides page and row-count information for every partition in the current database.

The below query shows all counts for all partitions of all indexes and heaps in the MSDB database:

Collapse | Copy Code

USE MSDB;
GO
SELECT * FROM sys.dm_db_partition_stats;

The following query shows all counts for all partitions of Backup set table and its indexes

Collapse | Copy Code

USE MSDB
GO
SELECT * FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('backupset');

d. sys.dm_os_performance_counters

Returns the SQL Server / Database related counters maintained by the server.

The below sample query uses the dm_os_performance_counters DMV to get the Log file usage for all databases in KB.

Collapse | Copy Code

SELECT instance_name
,cntr_value 'Log File(s) Used Size (KB)'
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Log File(s) Used Size (KB)'

3. INDEX Related DMV

This section details the DMVs associated with SQL Server Databases. These DMVs will help to identify database space usages, Partition usages, Session information usages, etc.

a. sys.dm_db_index_usage_stats

This DMV is used to get useful information about the index usage for all objects in all databases. This also shows the amount of seeks and scan for each index.

Collapse | Copy Code

SELECT object_id, index_id, user_seeks, user_scans, user_lookups
FROM sys.dm_db_index_usage_stats
ORDER BY object_id, index_id

All indexes which have not been used so far in as database can be identified using the below Query:

Collapse | Copy Code

SELECT object_name(i.object_id),
i.name,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
from sys.indexes i
left join sys.dm_db_index_usage_stats s
on s.object_id = i.object_id and
i.index_id = s.index_id and s.database_id = 5
where objectproperty(i.object_id, 'IsIndexable') = 1 and
s.index_id is null or
(s.user_updates > 0 and s.user_seeks = 0
and s.user_scans = 0 and s.user_lookups = 0)
order by object_name(i.object_id)

Replace the Database_id with the database you are looking at.

4. Execution Related DMV

Execution related DMVs will provide information regarding sessions, connections, and various requests which are coming into the SQL Server.

a. sys.dm_exec_sessions

This DMV will give information on each session connected to SQL Server. This DMV is similar to running sp_who2 or querying Master..sysprocesses table.

Collapse | Copy Code

SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51 – All user Sessions

b. sys.dm_exec_connections

This DMV shows all the connection to SQL Server. The below query uses sys.dm_exec_connections DMV to get connection information. This view returns one row for each user connection (Sessionid > =51).

Collapse | Copy Code

SELECT
connection_id,
session_id,client_net_address,
auth_scheme
FROM sys.dm_exec_connections

c. sys.dm_exec_requests

This DMV will give details on what each connection is actually performing in SQL Server.

Collapse | Copy Code

SELECT
session_id,status,
command,sql_handle,database_id
FROM sys.dm_exec_requests
WHERE session_id >= 51

d. sys.dm_exec_sql_text

This dynamic management function returns the text of a SQL statement given a SQL handle.

Collapse | Copy Code

SELECT
st.text
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS st
WHERE r.session_id = 51

Conclusion

Dynamic Management views (DMV) and Dynamic Management Functions (DMF) in SQL Server 2005 give a transparent view of what is going on inside various areas of SQL Server. By using them, we will be able to query the system for information about its current state in a much more effective manner and provide solutions much faster. DMVs can be used to performance tune and for troubleshooting server and queries. This article has shown an overview of what they are and how we can use them.

SQL Server 2008 Indexing Best Practices

The best index

There's a best index for every query
You don't want to do that for every query, though
You would end up with too many indexes
You need to select a good set of base indexes

Find a balance

You need to find a balance between no indexes and one ideal index on every query
Do not put a non-clustered index on every columns :-)
You can find (mathematically) if a non-clustered index will help or not
You can sometimes replace a narrow non-clustered index with a wider one that is more useful

Strategies

Create constraints for primary keys and alternate/candidate keys
Add indexes to foreign key constraints. It can help some joins
Capture workloads and use the database tuning advisor (DTA)
Add indexes to help with specific queries using joins, aggregations, etc.
Missing index DMVs query http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
These are only strategies you should consider, not absolutes.
Also, you need to maintain the indexes over time. Things change over time.

OLTP x OLAP

OLTP tables typically uses fewer indexes than OLAP tables
OLTP tables can make good use of indexes
However, there are certain indexes on OLTP that will be bad

Clustered index keys

Clustered Index keys: Unique, narrow, static, ever increasing
Examples: Identity / Date,Identity / GUID (with NEWSEQUENTIALID function)
Add something like identity to avoid ending up with uniquefiers
This means that you will have a lot less management over time
If you can have a really good clustering key, avoid heaps.

Identity

Identity is naturally unique, static, narrow, hot spot for inserts
Being a hot spot in this case is actually a good thing
It minimizes cache requirements, naturally needs less maintenance
Could have contention in GAM/SGAM pages if you have lots of tables with identity
Can overcome with multiple files, see http://support.microsoft.com/kb/328551

Lookups vs. Table Scans

Lookups. Physical vs. Logical I/Os
Potential for physical I/Os is in the leaf level (non-leaf are usually cached)
SQL knows how many pages are on the leaf level, how many rows per page
SQL needs to make a decision on Lookup x Table Scan
If it's in the WHERE clause, it doesn't mean an index on that will be used
Estimating query cost: tipping point for a query to go TS over a lookup
Won't spoil the results, but it is smaller than most would think...
Details at http://sqlskills.com/BLOGS/KIMBERLY/post/Tipping-Point-Queries-More-Questions-To-Really-Test-You!.aspx

Hints

You can force SQL to do Lookup x TS, but SQL usually does a good estimate
There are cases when the statistics for the table are not up to date
People end up using more hints (like INDEX, FORCESEEK) or plan guides than they should
If you use a hint and you're wrong, SQL will follow your hint anyway

Coverage

The power of covering. Leaf level of the non-clustered index has all the rows
People use indexes with "LN,FN,MI including SSN" and "LN, FN, MI including PHONE" and "LN with SSN", etc..
You can cover the same with a single index on "LN, FN, MI including SSN, PHONE"
Order of included columns do not matter. Just the order of the keys
Could this be happening because of the missing index message in query results?
Multiple developers not in sync can be a common source if this kind of thing.

Included columns

INCLUDE option lets you separate what goes in the leaf and non-leaf of index
There is no limit to what you can INCLUDE, but be careful with the duplication
You can create a non-clustered, covering, seekable index just for a query
For instance, you could create a non-clustered index on the SSN key and a few INCLUDED columns just to help a specific set of queries without hitting data pages at all
Full scan on a small non-clustered index could even beat seeks in certain cases.

Filtered Indexes

SQL Server 2008 includes the ability to do Filtered Indexes
For instance, filter for "status=1" to index only active items
For instance, filter for orderdate in last year for certain reports
Now that the index is much smaller, you can now maybe consider including more columns
This will give you more coverage without using a lot of space
Filtered indexes statistics are usually more accurate, if they cover fewer rows
This is not a substitute for partitions. Don't create filtered indexes if all sets are useful
Kimberly's SP_HELPINDEX2 shows included columns and filtered indexes
http://www.sqlskills.com/blogs/Kimberly/post/Updates-(fixes)-to-sp_helpindex2.aspx

Sparse Columns

Combine with sparse columns for indexes on subsets with only certain columns INCLUDED
Beware: Create indexes on tables with sparse columns might materialize sparse columns.

Conclusion

The best case: index covers all you need (key+included), nothing you don't and is seekable
Indexes do use more space and come at a cost for inserts, updates, maintenance
You don't have to do this for every query or every table
Put effort in the top few queries and you'll fix most of your issues
There are usually a few situations that cause you most of the grief
Look at that stored procedure that is executed thousand times per hour
You can overdo it. Be careful

22 February 2011

Max Worker Threads Configuration

In 2005 the default value for this setting has changed from 255 (SQL2k) to 0 (dynamic in SQL2k5). According to various sources, the following table shows the automatically configured number of Max Worker Threads for SQL Servers (2005) with different numbers of CPUs when the SQL Server database engine is left to dynamically set this value:

CPUs	Auto Configured Max Worker Threads (32 bit Server)	Auto Configured Max Worker Threads (64 bit Server)
<= 4 CPUs	256	512
8 CPUs	288	576
16 CPUs	352	704
32 CPUs	480	960

The formula to determine the max worker processes is as follows:

For x86 systems where total number of logical processors <=4

# max worker threads = 256

Otherwise:

# max worker threads = 256 + ((# Procs – 4) * 8)

For x64 systems where total number of logical processors <= 4

# max worker threads = 512

Otherwise

# max worker threads = 512 + ((# Procs – 4) * 16)

On an 8 processor x86 machine (could be 4-proc HT or 4-proc dual core machine, therefore shows 8 logical procs), max worker threads will be configured to 288 (256 + (4 * 8))

You can find out how many threads have been configured by looking at the max_workers_count column in the sys.dm_os_sys_info DMV.

Note:

SQL and indeed Windows has NO method of identifying the difference between physical and logical processors – i.e. multi-core and hyper-threaded processors appear to windows (and therefore) SQL as processors. As such 4 x Dual core would appear as 8 logical processors – and max worker threads would be sized on this basis regardless of whether these are 8 x physical, 4 x dual core or 4 x HT.

21 February 2011

Script for REBUILD/REORG

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Rebuild 30% Fragmented Indexes',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'ADITIDT634\Pradyothana', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Rebuild Index] Script Date: 04/29/2010 18:41:42 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Rebuild Index',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE DBCURSOR CURSOR FOR SELECT NAME FROM SYS.DATABASES where database_id not in (1,2,3,4,5,6,7,8,10)
OPEN DBCURSOR;
DECLARE @dbnames varchar(100),
@sql nvarchar(4000);
FETCH NEXT FROM DBCURSOR into @dbnames
WHILE (@@FETCH_STATUS<>-1)
BEGIN
set @sql=''USE ''+@dbnames+'';
PRINT ''''REBULDING INDEXES ON DATABASE ''''+DB_NAME(DB_ID());
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = ''''work_to_do'''')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''''LIMITED'''')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag < 30.0
BEGIN;
SELECT @command = ''''ALTER INDEX '''' + @indexname + '''' ON '''' + @schemaname + ''''.'''' + @objectname + '''' REORGANIZE'''';
IF @partitioncount > 1
SELECT @command = @command + '''' PARTITION='''' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;

IF @frag >= 30.0
BEGIN;
SELECT @command = ''''ALTER INDEX '''' + @indexname +'''' ON '''' + @schemaname + ''''.'''' + @objectname + '''' REBUILD'''';
IF @partitioncount > 1
SELECT @command = @command + '''' PARTITION='''' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT ''''Executed '''' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;''
--print @sql;
exec (@sql);
FETCH NEXT FROM DBCURSOR INTO @dbnames;
end;
CLOSE DBCURSOR;
DEALLOCATE DBCURSOR;
-- drop the temporary table
--IF EXISTS (SELECT name FROM sys.objects WHERE name = ''work_to_do'')
-- DROP TABLE work_to_do;
--GO',
@database_name=N'master',
@output_file_name=N'C:\Program Files\Microsoft SQL Server\Index_Rebuild_30_Log.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1,@new_name='JOB FOR REBUILD OR REORG INDEXES'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Script for REBUILD/REORG

max worker threads Option

SQL Server 2008 R2

Other Versions

Use the max worker threads option to configure the number of worker threads available to Microsoft SQL Server processes. SQL Server uses the native thread services of the Microsoft Windows 2000 and Windows Server 2003 operating systems so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users.

Purpose of max worker threads Option

Thread pooling helps optimize performance when large numbers of clients are connected to the server. Usually, a separate operating system thread is created for each query request. However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query request, which improves performance.

Calculating max worker threads

The default value for max worker threads, 0, allows SQL Server to automatically configure the number of worker threads at startup. This setting is best for most systems; however, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance.

The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server.

Number of CPUs	32-bit computer	64-bit computer
<= 4 processors	256	512
8 processors	288	576
16 processors	352	704
32 processors	480	960

Caution
We recommend 1024 as the maximum for 32 bit SQL Server.

When the actual number of query request is less than the amount set in max worker threads, one thread handles each query request. However, if the actual number of query request exceeds the amount set in max worker threads, SQL Server pools the worker threads so that the next available worker thread can handle the request.

DBA WORLD

Ads

08 March 2011

sql-server-performance-tuning

02 March 2011

Dynamic Management Views

Permission to Execute DMV [Security]

Getting Started

1. SQL Server Related DMV

a. sys.dm_os_sys_info

b. sys.dm_os_hosts

c. sys.dm_os_schedulers

d. sys.dm_io_pending_io_requests

e. sys.dm_io_virtual_file_stats

f. sys.dm_os_memory_clerks

g. sys.dm_os_ring_buffers

2. Database Related DMV

a. sys.dm_db_file_space_usage

b. sys.dm_db_session_space_usage

c. sys.dm_db_partition_stats

d. sys.dm_os_performance_counters

3. INDEX Related DMV

a. sys.dm_db_index_usage_stats

4. Execution Related DMV

a. sys.dm_exec_sessions

b. sys.dm_exec_connections

c. sys.dm_exec_requests

d. sys.dm_exec_sql_text

Conclusion

SQL Server 2008 Indexing Best Practices

22 February 2011

Max Worker Threads Configuration

21 February 2011

Script for REBUILD/REORG

Script for REBUILD/REORG

max worker threads Option

max worker threads Option

Difference between NOLOCK and READPAST