SQL Performance Tuning - optimizing for response time

Different  queries are optimized for different objectives

Response Times and Throughput are two standard objectives.

Response Time is about retrieving the  hits as quickly as possible. In a search engine , that would mean returning the first few rows immediately

Throughput is about returning ALL the hits (rows) in the smallest time frame

Factors to consider in improving Response Time

1)       Parallelism

2)       Partitioning

3)       Data types – such as CLOBs in the table

4)       Sorting

5)       Memory

6)       SQL Statistics

See Also

Waitstats CXPACKET wait type and how to reduce it

SQL Server Performance ,memory pressure and memory usage

SQL Server Buffer Cache Hit Ratio and memory pressure

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server – Logical Disk Read Bytes/sec and disk io

In the SAN environment , the Logical Disk Performance objects counters monitor the logical partitions.

 I use the Logical Disk Read Bytes/sec  as a starting point when I troubleshoot Disk IO issues. If I can isolate the activity , this gives me a good guideline as to Read rates from the SAN as viewed from the OS.

 The Logical Disk Read Bytes/sec  and the Logical Disk Writes Bytes/sec  display rates of transfer for read\write .

Check SQL Server – Backup to NUL trick - read throughput as an example of how to use the counter to troubleshoot disk IO .

Use Perfmon to initiate a monitor. Isolating the activity onto one disk and stopping all other activity is a good starting point to define the rates.

See Also

Window Perfmon scripting, SQL Server perfmon and how to perfmon

IO_completion on disk solved with Wait Stats and Perfmon

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server Latency

Latency is the time delay measure in the system.  SQL Server  can only be as fast as the slowest component in the system.

Database servers are particularly sensitive to disk IO speeds. For example, introducing an SVC system may speed up writes , but will add a cost to Reads.

Latency is about benchmarking certain limitations , such as disk speed  , and acknowledging that relative to the speed of light – delays occur. Use Production data or SQL Server test data generation testing tools to benchmark systems.

Database server latency is also about finding the acceptable level of delay. Yes, the IO request may not be 3 ms – but 6 ms may be acceptable to the users.

In a large database server ecosystem – multiple requirements are considered. Balancing budgets, business requirements  and system management  are factors in finding the acceptable level of latency.

SQL Server since 2005 has offered some useful methods of starting the analysis.

Use these two DMVs to develop a quick overview of where some delays are occurring. Use the information to focus on bottlenecks. There are other ways of measuring Disk IO performance such as  SQLIO

sys.dm_io_pending_io_requests  - View pending I/O request(s) in SQL Server.

sys.dm_io_virtual_file_stats  Returns I/O statistics for Database  data and log files. Measure IO transaction rates and sys.dm_io_virtual_file_stats

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Server sudden shutdown unavailable

It’s not always straightforward to diagnose a server problem. You look through the SQL Server Logs and Event Viewer for clues . There aren’t any obvious clues.

The steps I follow are:

1)       Is it a SQL Server problem?Is it an OS shutdown ? Is it a hardware problem?

2)       Write a report , outlining the facts.

3)       Gather logs, kernel crash dump files

4)       Send to server managers or vendor

5)       Prepare for a failover (if necessary)

This is an example of  events occurring recently. The conclusion was a hardware failure. Specifically a processor problem.

What’s interesting is the lack of log entries for a Processor failure .

All systems on a Production SQL Server  SERVER1 were running OK – up to at 21:55 . Then an unexpected shutdown occured

 There was then an unexpected server shutdown and Windows server was booted again at 15/06/2011 at 22:29

This caused backups to fail and  nightly jobs to fail .

 A series of Event Viewer messages :

 15/06/2011 – 21:56

The system failed to register pointer (PTR) resource records (RRs) for network adapter

with settings:

  Adapter Name : {0668572E-B074-4F48-8D2A-E2D5FF556AE9}

   Host Name : SERVER1

   Adapter-specific Domain Suffix : euprod.travp.net

   DNS server list :

                1

   Sent update to server : xxx.xx.xx.xxx

   IP Address : xx.xx.xx.xxx

 The reason that the system could not register these RRs was because of DNS server failure. This may be due to a zone transfer lock on the DNS server for the zone that your computer needs to update.

  You can manually retry DNS registration of the network adapter and its settings by typing "ipconfig /registerdns" at the command prompt. If problems still persist, contact your DNS server or network systems administrator. For specific error code information, see the record data displayed below.

 15/06/2011 – 22:21:27

 The previous system shutdown at 22:21:27 on 15/06/2011 was unexpected.

 Related Posts

How to report a SQL Server performance problem

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Mobile Commerce and SQL Performance Tuning

I was reading through Gartners Top Predictions and one prediction captured my attention:

 “By 2014, over 3 billion of the world's adult population will be able to transact electronically via mobile or Internet technology”

 The reasons given are:

 1)   “6.5 billion mobile connections by 2014”

2)   “Global adult population to be 5.5 billion by 2014”

3)   “Everyone with internet access or a mobile phone can transact electronically”

 

  What does this mean for DBAs and SQL Performance Tuning?

 1)       More electronic transactions through mobile phones than the PC.  Applications will be redesigned for a thinner\faster client.

2)       More emphasis on database servers and universal caching , for data reads and commits.

3)       SQL Performance Tuning requirements will increase.  Why?

Regular DBA change requests for :

Users and security management

Data volume changes – company acquisitions and new products\markets

Transaction volume changes – multiple sales channels

Availability  (Disaster Recovery and High Availability) – 24 x7

Server consolidation model – OLTP and real time analysis

 A greater emphasis will be placed on  automating database server monitoring.  The typical monitoring model is :   online inspection > gathering statistics > query usage > resource consumption.

Automating the database tuning process is a goal of many systems managers. The  monitoring model presented , should be a standard for DBAs. Whether every tuning problem can be diagnosed and fixed automatically is another question.

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)