Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- How to find privilege changes with SQL Server Default Trace
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
- sqlserver-dba.com switching from feedburner to follow.it
Notes on Performance Tuning very large databases for OLTP
16 January,2017 by Tom Collins
Performance Tuning very large databases with OLTP presents a whole new set of problems. There are thousands of articles and tutorials on tuning databases of a generic shape & size . Here are some thoughts on approaching database servers with a high level of transactions , with minimal downtime.
Stability of response times is just as important as improving the amount of queries per second. When faced with millions of queries , a smooth customer experience is critical. Pages hanging because of slow queries is newsworthy in the negative sense.
Minimise variance. When there are thousands of requests per second, more energy should be placed on the extreme slow responses. With this approach you don’t focus on average query response time , but focus on the 5 slowest. Average query response time is important , as it reflects architectural decisions. Also , if you improve the performance of the slowest queries the average response time will improve,
When you’ve identified the 5 slowest queries , analyse them and fix them. The focus turns to QOS (Quality of Service). Typically this term is used on network systems, but applies equally to Database Servers. The main point of QOS is the performance experienced by the users.
What needs to be in place?
Develop a method of measuring how the database servers actually operate. Think in terms of subsecond detail
Measure the waits.SQL Server RUNNABLE – SUSPENDED – RUNNING status model
Find of way of recording and analysing the queries where something is wrong.
Don’t assume anything. I’ve seen all sorts of reasons for slowdown some examples include hourly jobs on ESX hosts grinding systems to a halt, dirty page flushing, statistics issue. All of these problems can also impact when the system is not at full load.
All aspects of the performance should be analysed.Application , DBMS – Data System ,DBMS - Access system,DBMS – Storage System,DBMS Buffer System,OS,Hardware Tuning,Network
Develop a method to observe the performance degradation and how to quickly drill down to the queries for troubleshooting.
Keep the analysis team small.Performance Tuning by Committee - SQL Antipattern #006
Read More
7 habits of highly effective DBAs (SQL Server DBA)
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |