Stored Proc / Query Optimization Resources
Database Tuning Advisor
- SQL Server Management Studio > Tools > Database Engine Tuning Advisor
- Microsoft on the Database Tuning Advisor
- With the tuning advisor, you can first set up a pre-defined trace, via the SQL Server Profiler, run it against the Database Tuning Advisor, and have it analyzed for index recommendations, partition recommendations, etc.
Also read SQL interview Questions
- Avoid using SELECT * statements, select only the columns you need. This also helps mitigate source schema changes that your own application may not be able to handle if using a * statement.
- Avoid intermingling DDL and DML statements in a script (such as running a select statement, then creating a temp table, running another select statement, etc)
- Batch as many SQL calls together in one script as possible, instead of making individual calls for each
- Do not prefix stored procedure names with “sp_<procname>”. In that case, SQL Server will attempt to search within the Master database first for the stored proc.
- Avoid the use of temp tables to reduce the amount of recompiles that a query has to do, as well as use less locking
- If you have a query that heavily uses string parsing, manipulation, arrays, consider using the CLR as opposed to T-SQL.
Determining Optimal Queries
- The execution plan chosen by SQL Server may not always be optimal. The plan is determined by looking at a sample subset of the data (10%), instead of the entire dataset.
- SQL Server jobs normally keep these statistics up to date, but to manually update statistics run in SSMS:
UPDATE statistics tablename WITH FULLSCAN
- Note: Run fullscan updates only as needed, and preferably against non-production server.
- In order to get the true performance hit of a new query, caches must be cleared. Note: Perform this only in non-production environments
DBCC FREEPROCCACHE (note:removes all clean buffers from the pool) DBCC DROPCLEANBUFFERS (note:removes cached execution plans)
- Following that, enable the display of IO or time statistics for a query:
SET STATISTICS <IO|TIME> OFF GO
- Goal is to reduce the number of physical and logical reads
- Number of logical reads decreases with proper indexing, proper page filling
- Number of physical reads decreases as data is cached, as well as taking the proper steps above
- When running a query, enable the “Include Actual Execution Plan” button in the toolbar above the script window
- Try to eliminate Scans and Lookups from the Execution plan.
- Scan: This means an entire table or index was iterated, and no key index was used.
- Key Lookup: This means that the indexes used are not “covering” the query. Include all columns in the index so that this extra lookup does not occur.
- Nested Loop / Merge Join: CPU intensive operation
- Hash Join: Memory intensive operation (Can specify a hint within the query to recommend a specific join, e.g. “INNER <LOOP|MERGE|HASH> JOIN”)
- Prioritize the left-most “leaves” of the execution plan for optimizing, since these operations cover the largest datasets
- More info here: https://www.simple-talk.com/sql/performance/graphical-execution-plans-for-simple-sql-queries/
- Start > Administrative Tools > Reliability and Performance Monitor
- Use Perfmon to collect a server’s statistics on a regular interval.
- Use Counters to define what you want to capture. To add a new counter within Perfmon, right click on the running graph, and select Add Counters…
- Important SQL server counters:
- Memory – Available MBytes
- Paging File – % Usage
- Physical Disk – Avg. Disk sec/Read
- Physical Disk – Avg. Disk sec/Write
- Physical Disk – Disk Reads/sec
- Physical Disk – Disk Writes/sec
- Processor – % Processor Time
- SQLServer: Buffer Manager – Page life expectancy
- SQLServer: General Statistics – User Connections
- SQLServer: Memory Manager – Memory Grants Pending
- SQLServer: SQL Statistics – Batch Requests/sec
- SQLServer: SQL Statistics – Compilations/sec
- SQLServer: SQL Statistics – Recompilations/sec
- System – Processor Queue Length
SQL Server Profiler
- Start > Microsoft SQL Server 2008 R2 > Performance Tools > SQL Server Profiler
- SQL Server Profiler can be useful in ways such as:
- Capturing a series of deadlocking transactions
- Identifying long running queries
- Logging data for later viewings
- Details / Tutorial: http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-to-sql-server-2008-profiler-2/
- Note: Proceed with caution when running the profiler against a Production server, as it does have the potential to impact performance, depending on the type and amount of data captured. Keep production trace to a minimum, and/or schedule short start and end datetimes.
Dedicated Administration Connection
SQL Server has a number of schedulers that process threads. One of which is reserved for the DAC. In the case that the server gets overwhelmed, a user can still log directly onto the server to perform lightweight troubleshooting
To access the DAC:
- Open SQL Server Management Studio, and cancel out of the Connect to… pop-up window.
- In the top left corner, next to the New Query button, click on Database Engine Query
- Log on. Specify the Server name as: “ADMIN:<server>” (e.g. “ADMIN:taspmosqlcs101” instead of “taspmosqlcs101”)
- Login account must have sysadmin privilege
- Remote Administration Connections must be enabled, or else the DAC can only be used from the localhost
- Microsoft on designing Indexes
- Clustered index – One index per table. A key is defined, and the physical data is sorted around this key
Typical candidate for a clustered key:
- ID field
- Not often updated
- Incremented sequentially
- Optimized to include a wide range of queries
- Non-clustered index – One or more indexes per table.The defined key contains the location of the physical data
- Indexes are more useful on read-only tables, and are not necessary for small tables
- Though Select performance may improve, indexes can degrade the performance of Updates / Inserts
- Include additional columns in indexes so that all data can be retrieved in a single key lookup
- (1) Use the Include keyword to store additional columns along with the index in the leaf node
- (2) Create a Composite index to store additional columns in all nodes. This is necessary for columns in the WHERE clause. A query can use both the key and the additional columns to optimize.
- When including additional columns, list out the columns, left-to-right, most specific-to-least specific (aka most selective-to-least selective).