DBA Performance Tuning
SQL Server DBA Performance Tuning Questions
Question Difficulty = Easy
- Question
1: Name five different tools which can be used for performance tuning
and their associated purpose.
- Performance
Monitor\System Monitor - Tool to capture macro level performance metrics.
- Additional
information
- Profiler
- Tool to capture micro level performance metrics based on the statements
issued by a login, against a database or from host name.
- Additional
information: Tip Category - Profiler
and Trace
- Server
Side Trace - System objects to write the detailed statement metrics to a
table or file, similar to Profiler.
- Additional
information: SQL Server Performance Statistics Using a Server Side
Trace
- Dynamic
Management Views and Functions - SQL Server objects with low level
metrics to provide insight into a specific portion of SQL Server i.e. the
database engine, query plans, Service Broker, etc.
- Additional
information: Dynamic Management Views\Functions
- Management
Studio's Built-In Performance Reports - Ability to capture point in time
metrics as pre-defined by Microsoft.
- Additional
information: Built-In Performance Reports in SQL Server 2005
- Custom
scripts - Custom scripts can be developed to monitor performance,
determine IO usage, monitor fragmentation, etc. all in an effort to
improve performance.
- Third
party applications - Performance monitoring and tuning applications from
vendors in the SQL Server community.
- Additional
information: SQL Server Performance Monitoring Tools
- Question
2: Explain how the hardware running SQL Server can help or hinder
performance.
- Taxed
CPUs will queue requests and hinder query performance.
- Insufficient
memory could cause paging resulting in slow query performance.
- Incorrectly
configured disk drives could exacerbate IO problems.
- Additional
information: Hard Drive Configurations for SQL Server and Hardware 101 for DBAs
- Question
3: Why is it important to avoid functions in the WHERE clause?
- Because
SQL Server will scan the index or the table as opposed to seeking the
data. The scan operation is a much more costly operation than a
seek.
- Often
a slightly different approach can be used to prevent using the function
in the WHERE clause yielding a favorable query plan and high performance.
- Additional
information: Performance Tip: Avoid functions in WHERE clause
- Question
4: How is it possible to capture the IO and time statistics for your
queries?
- Use
the SET STATISTICS IO and SET STATISTICS TIME settings in your queries or
enable the settings in your Management Studio session.
- Additional
information: Getting IO and time stats for your queries
- Question
5: True or False - It is possible to correlate the Performance Monitor
metrics with Profiler data in a single SQL Server native product?
- True
- This functionality is possible with SQL Server Profiler.
- Additional
information: Correlating Performance Monitor and Trace Data
Question Difficulty = Moderate
- Question
1: How can I/O statistics be gathered and reviewed for individual
database files?
- By
using the fn_virtualfilestats function to capture the metrics.
- This
process can be automated with a script to determine the file usage with
numerous samples.
- Additional
Information: Gathering I/O statistics down to the database file level
- Question
2: What is a query plan and what is the value from a performance
tuning perspective?
- A
query plan is the physical break down of the code being passed to the SQL
Server optimizer.
- The
value from a performance tuning perspective is that each component of the
query can be understood and the percentage of resource utilization can be
determined at a micro level. As query tuning is being conducted,
the detailed metrics can be reviewed to compare the individual coding
techniques to determine the best alternative.
- Additional
Information:
- Maximizing your view into SQL Query Plans
- Capturing Graphical Query Plans with Profiler
- Tip
Category: Query
Plans
- Tip
Category: Query
Optimization
- Question
3: True or False - It is always beneficial to configure TempDB with an
equal number of fixed sized files as the number of CPU cores.
- False
- With always being the operative word in the question.
- Depending
on the version of SQL Server, the disk subsystem, load, queries, etc., a
1 to 1 ratio of files to cores may be necessary on high end SQL Servers
with intense processing.
- If
you do not have that luxury, a starting point may to be have half the
number of tempdb files as compared to CPU cores.
- This
is a configuration to load test and monitor closely depending on the type
of processing, load, hardware, etc. that your SQL Server is expected to
support.
- Question
4: Explain the NOLOCK optimizer hint and some pros\cons of using the
hint.
- The
NOLOCK query hint allows SQL Server to ignore the normal locks that are
placed and held for a transaction allowing the query to complete without
having to wait for the first transaction to finish and therefore release
the locks.
- This
is one short term fix to help prevent locking, blocking or deadlocks.
- However,
when the NOLOCK hint is used, dirty data is read which can compromise the
results returned to the user.
- Additional
information: Getting rid of some blocking issues with NOLOCK
- Question
5: Explain three different approaches to capture a query plan.
- SHOWPLAN_TEXT
- SHOWPLAN_ALL
- Graphical
Query Plan
- sys.dm_exec_query_optimizer_info
- sys.dm_exec_query_plan
- sys.dm_exec_query_stats
Question Difficulty = Advanced
- Question
1: True or False - A LEFT OUTER JOIN is always faster than a NOT
EXISTS statement.
- False
- With always being the operative word. Depending on the
situation the OUTER JOIN may or may not be faster than a NOT EXISTS
statement. It is necessary to test the techniques, review the query
plans and tune the queries accordingly.
- Question
2: Name three different options to capture the input (code) for a
query in SQL Server.
- DBCC
INPUTBUFFER
- fn_get_sql
- sys.dm_exec_sql_text
- Additional
information:
- Question
3: Explain why the NORECOMPUTE option of UPDATE STATISTICS is used.
- This
command is used on a per table basis to prevent the table from having
statistics automatically updated based on the 'Auto Update Statistics'
database configuration.
- Taking
this step will prevent UPDATE STATISTICS from running during an
unexpected time of the day and cause performance problems.
- By
setting this configuration it is necessary to manually UPDATE STATISTICS
on a regular basis.
- Additional
information: The NORECOMPUTE option of UPDATE STATISTICS
- Question
4: Explain a SQL Server deadlock, how a deadlock can be identified,
how it is a performance problem and some techniques to correct deadlocks.
- A
deadlock is a situation where 2 spids have data locked and cannot release
their lock until the opposing spid releases their lock. Depending on the
severity of the deadlock, meaning the amount of data that is locked and
the number of spids that are trying to access the same data, an entire
chain of spids can have locks and cause a number of deadlocks, resulting
in a performance issue.
- Deadlocks
can be identified by Profiler in either textual, graphical or XML format.
- Additional
information: Capturing Deadlock Information in XML Format and How To: Graphical Deadlock Chain
- Deadlocks
are a performance problem because they can prevent 2 or more processes
from being able to process data. A deadlock chain can occur and
impact hundreds of spids based on the data access patterns, number of
users, object dependencies, etc.
- Deadlocks
could require a database design change, T-SQL coding change to access the
objects in the same order, separating reporting and OLTP applications,
including NOLOCK statements in SELECT queries that can accept dirty data,
etc.
- Question
5: Please explain why SQL Server does not select the same query plan
every time for the same code (with different parameters) and how SQL
Server can be forced to use a specific query plan.
- The
query plan is chosen based on the parameters and code being issued to the
SQL Server optimizer. Unfortunately, a slightly different query
plan can cause the query to execute much longer and use more resources
than another query with exactly the same code and only parameter
differences.
- The
OPTIMIZE FOR hint can be used to specify what parameter value we want SQL
Server to use when creating the execution plan. This is a SQL Server 2005
and beyond hint.
- Additional
information: Optimize Parameter Driven Queries with the OPTIMIZE FOR
Hint
SqlProfiler Question
Pinal Dave Question for DBA
Comments
Post a Comment