Process Obtaining Query Tuning Skills
Performance tuning in database world is a complex but challenging task. Many aspects should be investigated to find the culprit. If you do not have interest and a bunch of curiosity and patience, it seems to be a daunting task and I bet you will simply say “Upgrade/add memory/processor”, or “this is too complex query to be handled by SQL Server” or something like that.
At the beginning, these advices seem appropriate solution. As time goes by, it becomes inefficient solution as I learn for prominent MVP SQL Servers particularly from Kalen Delaney (regular contributor “Inside SQL Server” column of SQL Server Magazine – http://www.sqlmag.com, discussing about how SQL Server internal engine works), Itzik Ben-Gan (focusing on T-SQL capability), and Brian Moran (focusing on Performance case) about how to investigate query problem, find the culprit and solve by rewriting (refactoring) the corresponding query. But this is not enough for me. Why? Because they deliver their articles are usually in simple examples to proof their theories, and in fact I face sophisticated queries with their respective performance problem.
Investigating query problem is easy, finding the exact culprit is not easy (need some techniques), and rewriting queries is highly difficult. There is a gap between theories and problem areas faced. To cope with this, I must understand how query optimizer works by interpreting hundreds lines of execution plan. For some time, I do trial and error mapping, so that time I think query tuning is half science and half art, and query optimizer is unpredictable so that SQL Server could not handle complex query. But recently, my trial and error efforts direct me to convergent point about what those MVP SQL Server experts describe so far before. I believe now that query tuning is truly science, could be described its behavior and it is capable to handle complex queries with its significant improved query optimizer since SQL Server 7.0 first released. SQL Server could handle even complex queries, not solely as bin repository! I think set-based oriented problem is still best served by SQL Server besides CRUD stored procedures.
Usually during development phase, in practice, query tuning gets insufficient portion in software engineering development life cycle, until for several months pass, system begins running slowly and end-users feel inconvenient with slow running application as data grow from few hundreds into hundreds of thousands or even million of rows. I am grateful for these happenings that force me to go through some processes of very valuable query tuning skill. The more you face query problems, the better you resolve it. Theory is important but immersion is much more important if you want to get better insights how query optimizer process long running queries.
Tools to investigate query problem
I will show you practice how I usually handle performance problem. Tools provided by windows and SQL Server are Performance Monitor and Profiler. For general investigation, you can use Performance Monitor (click Start – Run, type PerfMon for shortcut), but for specific query activities you can rely on Profiler.
Performance monitor have complete aspects exposed through Performance object. For example if you want to measure whether processor activities, you should choose Processor in Performance object, then it will show counters available.
If you want to know description for selected counters, you can click Explain button. Here is complete list of important counter you should gather:
Memory—Pages/sec
The number of pages read or written to disk per second. This counter is a primary indicator of the types of faults that cause system-wide delays or performance problems.
Network Interface—Bytes total/sec
The number of bytes traveling over the network interface per second. When this counter drops or trends lower, it indicates that network problems may be interfering with your application.
Physical Disk—Disk Transfers/sec
The rate of disk read/write operations. Define a counter for each physical disk on the server.
Processor—% Processor Time
Percentage of time that the CPU is executing a non-idle thread. This counter acts as the primary indicator of processor activity. If all processors devoted to SQL Server show 100 percent usage, end-user requests are likely being ignored.
SQLServer:Access Methods—Full Scans/sec
The number of unrestricted full-table or index scans per second. The lower this counter is, the better because scans often cause bottlenecks and caching problems.
SQLServer:Buffer Manager—Buffer Cache Hit Ratio
The percentage of pages that didn't require a read from disk. The higher the number, the less disk I/O is being generated. In a well-tuned system, this value will be 80 or higher.
SQLServer:Databases Application Database—Transactions/sec
The number of transactions started for the database. This counter temporarily dips during checkpoints. Watch for when transactions start to queue, which indicates that disk I/O may be too slow.
SQLServer:General Statistics—User connections
The number of user connections to the database server. Watch for and check any dramatic shifts in this counter's value. These can indicate network problems and tell when the busy and slow times are.
SQLServer:Latches—Average Latch Wait Time
The average time latch requests had to wait before being filled. This counter may be high when the server is facing contention for resources, particularly memory or I/O.
SQLServer:Locks—Average Wait Time, Locks—Lock Timeouts/sec, Number of Deadlocks/sec
The time locks are held on SQL Server resources. Watch these lock-related counters for upward trending, indicating a possible developing performance issue.
SQLServer:Memory Manager—Memory Grants Pending
The current number of processes waiting for a workspace memory grant. A high or rising value may indicate insufficient memory.
Specify a value for each counter for warning notification. For example, you might set a warning notification when a counter passes 90 percent.
Performance monitor also provides alert facilities. Recommended setting alerts for the following events:
· Errors affecting service, specifically errors with a severity of 19 through 25
· Deadlocks
· CPU utilization
· Disk utilization
· Scans (SQLServer:Access Methods)
Through profiler, you can monitor queries performance; based on what I observe if a query is long running constantly even in normal transaction system environment, it is good candidate for query tuning. Yes, it needs time anyway until you are sure that they should be tuned for corresponding query.
Query Tuning Sample Cases
Many query performance problems, I successfully solve, I will share 2 cases about solution pattern for 2 most drastically improved query response time.
Case 1
There are subqueries select column list with 2 nested level, it takes 5 minutes to return 2000 resultset. Execution plan yields 2 nested loops, for each of them gets loop for almost 1000 times, you can imagine that it is inefficient execution plan. It can be simplified into “select columnA, columnB, … from tableA” form. When new query executed, it takes 1 second to complete. For 5 minutes, new query could roughly serve 600 same query requests.
Case 2
A query with one nested subquery, it takes almost 4 minutes to complete for 4600 resultset. I move the nested query into temporary table and it is referenced by later query. When it is executed, it takes less than 10 seconds to complete. For 4 minutes, new query could server 240 same query requests.
From above cases, I can say that query tuning is effort of scaling up SQL Server in efficient way resulting good response time.
Conclusion
Query tuning is complex task, but once you understand how query optimizer works, it will be easier to rewrite the query. From these two cases above, query tuning should be a viable option besides upgrade hardware option.
Offering
If any corporate clients need consultation or even have me to be engaged in performance tuning projects, please feel free to contact me at wkww2004@yahoo.com. Growing up your query tuning skill by mentoring session and study case on the field is most effective one.
Best Regards,
Kasim Wirama, MCDBA