Search This Blog

Microsoft SQL Server Tuning Tips (Part 1)

Microsoft SQL Server Tuning Tips(Part2)

The following excerpts are summaries from Microsoft sites on the topics of tuning SQL Server for large production environments. See URL at end for the full 94-page article. We found the following performance-tuning related extracts highlights the article. Judge for yourself.
RAM Caching

RAM is a limited resource. A major part of any database server environment 
is the management of random access memory (RAM) buffer cache. Access to 
data in RAM cache is much faster than access to the same information from disk. 
But RAM is a limited resource. If database I/O (input/output operations to 
the physical disk subsystem) can be reduced to the minimal required set of 
data and index pages, these pages will stay in RAM longer. Too much unneeded 
data and index information flowing into buffer cache will quickly push out 
valuable pages. The primary goal of performance tuning is to reduce I/O 
so that buffer cache is best utilized. 

To enable Windows 2000 Advanced Server or Windows 2000 Dataleft Server 
to support more than 4 GB of physical memory, you must add the /pae 
parameter to the Boot.ini file. 

For computers with 16 GB or less you can use the /3gb parameter in 
the Boot.ini file. This enables Windows 2000 Advanced Server and 
Windows 2000 Dataleft Server to allow user applications to address 
extended memory through the 3 GB of virtual memory, and it reserves 1 GB 
of virtual memory for the operating system itself.

If more than 16 GB of physical memory is available on a computer, the 
Windows 2000 operating system needs 2 GB of virtual memory address 
space for system purposes. Therefore, it can support only a 2 GB virtual 
address space for application usage. For systems with more than 16 GB of physical 
memory, be sure to use the /2gb parameter in the Boot.ini file.  See how to 
turn on these settings here.
Memory Usage Considerations

--
To enable the use of AWE memory by an instance of SQL Server 2000, use 
sp_configure to set the awe enabled option. Next, restart SQL Server to 
activate AWE. Because AWE support is enabled during SQL Server startup and 
continues until SQL Server is shut down, SQL Server will notify users when AWE 
is in use by sending an "Address Windowing Extension enabled" message to 
the SQL Server error log.

When you enable AWE memory, instances of SQL Server 2000 do not 
dynamically manage the size of the address space. Therefore, when you 
enable AWE memory and start an instance of SQL Server 2000, one 
of the following occurs, depending on how you have set max server memory.

- If max server memory has been set and there are at least 3 GB of free 
memory available on the computer, the instance acquires the amount of 
memory specified in max server memory. If the amount of memory available 
on the computer is less than max server memory (but more than 3 GB), then the
instance acquires almost all of the available memory and may leave only 
up to 128 MB of memory free.
- If max server memory has not been set and there is at least 3 GB of f
ree memory available on the computer, the instance acquires almost all 
of the available memory and may leave only up to 128 MB of memory free.

If there is less than 3 GB of free memory available on the computer, 
memory is dynamically allocated and, regardless of the parameter setting 
for awe enabled, SQL Server will run in nonAWE mode.

When allocating SQL Server AWE memory on a 32-GB system, Windows 2000 may 
require at least 1 GB of available memory to manage AWE. Therefore, 
when starting an instance of SQL Server with AWE enabled, it is recommend 
you do not use the default max server memory setting, but instead limit it 
to 31 GB or less.
IO Considerations

Performance. Hardware RAID controllers divide read/writes of all data 
from Windows NT 4.0 and Windows 2000 and applications (like SQL Server) 
into slices (usually 16-128 KB) that are then spread across all disks 
participating in the RAID array. Splitting data across physical drives 
like this has the effect of distributing the read/write I/O workload 
evenly across all physical hard drives participating in the RAID array. 
This increases disk I/O performance because the hard disks participating 
in the RAID array, as a whole are kept equally busy, instead of some 
disks becoming a bottleneck due to uneven distribution of the I/O requests 

Disk I/O Parallelism

An effective technique for improving the performance of large SQL Server 
databases that are stored on multiple disk drives is to create disk I/O 
parallelism, which is the simultaneous reading from and writing to multiple 
disk drives. RAID implements disk I/O parallelism through hardware and 
software. The next topic discusses using partitioning to organize SQL 
Server data to further increase disk I/O parallelism. 

Objects For Partitioning Consideration

The following areas of SQL Server activity can be separated across 
different hard drives, RAID controllers, and PCI channels 
(or combinations of the three):
- Transaction log
- tempdb
- Database
- Tables
- Nonclustered indexes

egregating the Transaction Log

Transaction log files should be maintained on a storage device physically 
separate from devices that contain data files. Depending on your database 
recovery model setting, most update activity generates both data device 
activity and log activity. If both are set up to share the same device, 
the operations to be performed will compete for the same limited resources. 
Most installations benefit from separating these competing I/O activities. 

alter database tempdb modify file 
     (name='tempdev',filename= 'e:\mssql7\tempnew_location.mDF')
alter database tempdb modify file 
     (name='templog',filename= 'c:\temp\tempnew_loglocation.mDF')

the new locations above are physcially separate disks.

Segregating Nonclustered Indexes

Indexes reside in B-tree structures, which can be separated from their 
related database tables (except for clustered indexes) by using the 
ALTER DATABASE command to set up a distinct filegroup. In the example below, 
the first ALTER DATABASE creates a filegroup. The second ALTER DATABASE adds 
a file to the newly created filegroup.

alter database testdb add filegroup testgroup1
alter database testdb add file (name = 'testfile', 
   filename = 'e:\mssql7\test1.ndf') to filegroup testgroup1

After a filegroup and its associated files have been created, the filegroup 
can be used to store indexes by specifying the filegroup when the indexes are created.

create table test1(col1 char(8))
create index index1 on test1(col1) on testgroup1
SQL Coding Tips

Index Intersection 



The following example makes use of index intersection:
Create index Indexname1 on Table1(col2)
Create index Indexname2 on Table1(col3)
Select col3 from table1 where col2 = 'value'

When the previous query is performed, the indexes can be combined to 
quickly and efficiently resolve the query. 

Execution plan options can also be set within the query by executing 
either of the following commands:

set showplan_all on
go
set showplan_text on
go

Query 1
select ckey1,col2 from testtable where ckey1 = 'a'

Text-based execution plan output
|--Clustered Index Seek
     (OBJECT:([TraceDB].[dbo].[testtable].[testtable1]),
     SEEK:([testtable].[ckey1]='a') ORDERED FORWARD)

he execution plan takes advantage of the clustered index on column ckey1 
to resolve the query, as indicated by Clustered Index Seek.  If the clustered 
index is removed from the table and the same query is executed again, the query 
reverts to using a table scan. The following graphical execution plan indicates the change in 
behavior. 

Text-based execution plan output
|--Table Scan(OBJECT:([TraceDB].[dbo].[testtable]), 
     WHERE:([testtable].[ckey1]=[@1]))

A table scan instead. So we need the index.
The full 94-page article is here SQL Server 2000 although the methodology still applies to the latest version of SQL Server too!
Another useful DYI article on SQL server performance monitoring where it talks about how to use both perfmon output and a performance table to save the data, and use queries to display useful performance statistics.
The typical SQLPerfmon counter logs (Windows 2003) to include the following:

Memory counters 
     - Pages/sec 
     - Page Reads/sec 
Disk counters for every physical disk 
     - Avg. Disk Queue Length, 
     - Avg. Disk sec/Transfer 
Processor counters 
     - % Processor Time - _Total 
System counters 
     - Processor Queue Length 
SQL Server counters 
     - Access Methods - Page Splits/sec 
     - General Statistics - User Connections 
     - Buffer Manager - Buffer Cash Hit Ratio

On the SQL Server db, do:

CREATE TABLE [PerfmonDataCustomer01] ( 
     [CounterDateTime] [datetime] NOT NULL, 
     [Page Reads/sec] [numeric](18, 2) NULL, 
     [Pages/sec] [numeric](18, 2) NULL, 
     [Avg Disk0 Queue Length] [numeric](18, 2) NULL, 
     [Avg Disk0 sec/Transfer] [numeric](18, 2) NULL, 
     [Avg Disk1 Queue Length] [numeric](18, 2) NULL, 
     [Avg Disk1 sec/Transfer] [numeric](18, 2) NULL, 
     [Processor Time] [numeric](18, 2) NULL, 
     [Page Splits/sec] [numeric](18, 2) NULL, 
     [Cache Hit Ratio] [numeric](18, 2) NULL, 
     [User Connections] [numeric](18, 2) NULL, 
     [Processor Queue Length] [numeric](18, 2) NULL 
) 
GO 
ALTER TABLE [dbo].[PerfmonDataCustomer01] WITH NOCHECK ADD 
     CONSTRAINT [PK_PerfmonDataCustomer01] PRIMARY KEY CLUSTERED 
     (  [CounterDateTime]  )
GO
See the full article Part 2.