“ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
Support translation: http://amzn.to/1Z7d5oc
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
NAV 2009 Installation and Configuration Chapter 8 — Optimisation and Troubleshooting Notes
Database Performance Troubleshooting
SQL Server problems can be a result of:
Issues within a database.
Issues relating to instances of SQL Server.
Physical performance of the server.
Resolving Space Issues
Configure database to auto-grow or grow it manually using ‘ALTER DATABASE’.
Monitoring Auto-Grow and Auto-Shrink
Use Profiler to record events as they occur in an instance of the database engine.
Event classes and event categories are available on the Events Selection tab of the Trace File Properties dialogue box in the SQL Profiler.
The Database event category is used to monitor auto-grow and auto-shrink events.
Data File Auto Grow Event Class
Event indicates that the data file grew automatically.
Not triggered if you do not use AutoGrow.
Data File Auto Shrink Event Class
Event indicates that the data file has been shrunk.
Log File Auto Grow Event Class
Event indicates that log file grew automatically.
It is not triggered if the log file is grown through Alter Database.
Log File Auto Shrink Event Class
Event indicates that the log file shrunk automatically.
It is not triggered if the log file shrinks through Alter Database.
After a number of statements, the statistics will not represent the true data distribution in a given column/index.
SQL Server may automatically update the statistics by using auto-update statistics.
This is triggered by query optimisation or by execution of a compiled plan.
If the query optimizer needs statistics, it it updated automatically if it is out of date.
The auto-update statistics feature can be turned off at different levels.
Database level: ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF.
Table level: NORECCOMPUTE of the UPDATE STATISTICS/CREATE STATISTICS command.
The sp_autostats Stored Procedure
Display and change the auto-update statistics settings for a table, index, or statistics object.
You can re-enable the automatic updating of statistics by using ALTER DATABASE, UPDATE STATISTICS, or the sp_autostats stored procedure.
You cannot override the database setting of OFF for auto-update statistics by setting it ON at the statistics object level.
The sp_updatestats Stored Procedure
This stored procedure runs UPDATE STATISTICS against all user defined and internal tables.
It specifies the ALL keyword against all user-defined and internal tables in the database.
Evaluating Index Usage
An index lets SQL Server quickly find specific information in a table or indexed view.
You can improve the performance by creating well designed indexes.
Indexes reduce the amount of data that must be read to return a result.
Indexes also ensures data integrity.
An index contains keys from one or more keys in a table/view.
These are stored in a b-tree structure.
A table/view can contain clustered and nonclustered indexes.
Sort and store data in the table/view based on their key values.
You can define only one clustered index per table because the data rows themselves can be sorted and stored in only one order.
Data rows in a table can be stored in order only when the table contains a clustered index.
A table with clustered indexed is called a clustered table.
If not defined, SQL Server stores its data rows in a heap.
A structure separate from the data rows of the table itself.
A nonclustered index contains nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
Pointer from index row is called a row locator.
The structure depends on whether the actual data pages are stored in a heap/clustered table.
A rule that the database server enforces automatically.
Indexes are automatically created when you define PRIMARY KEY and UNIQUE constraints on table columns.
Well-designed indexes reduce disk I/O operations and use less system resources.
Indexes improve the performance on SELECT, UPDATE and DELETE statements.
Fragmentation of indexes prevent SQL Server from using indexes in an optimal way.
Index fragmentation can be:
Internal — pages that have a lot of free space.
External — pages are not contiguous.
DBCC SHOWCONTIG is used to view the extent of index fragmentation.
Using TABLERESULTS with the above function allow you to see extra output columns with index statistics.
Index defragmentation metrics
Avg. Page Density (full) — shows how filled pages are.
Scan Density — ratio between Best Count of extents and Actual Counts. The higher the percentage the better with a minimum of 75%.
Logical Scan Fragmentation — ratio of pages that are out of logical order. The lower the percentage the better with a maximum of 10%.
Index Defragmentation SQL Command
Drop and re-create the index.
CREATE INDEX… WITH DROP_EXISTING
First three options should ONLY be done when offline.
The last option can be done online. It will not defrag any indexes that are currently in use.
Use SQL Server Management Studio (SSMS) to rebuild indexes on a table.
Rebuilding an index drops and re-creates the index.
This removes fragmentation, reclaims disk space, and reorders the index rows in contiguous pages.
Creating Missing Indexes
Query optimiser generates a query plan.
It analyses what the best filters are for a filter condition.
If the best indexes do not exist, the query optimiser generates a suboptimal query but stores information about missing indexes.
Missing indexes enables you to access information about these indexes so that you may choose whether to implement them.
You then use CREATE INDEX to restore the missing indexes.
Dropping an Inappropriate Index
Remove an index — 1) DROP INDEX SQL statement.
2) SSMS (SQL Server Management Service).
This process reclaims the disk space the index currently uses.
Deleting an index is the same as dropping it.
Dropping a clustered index can take time because in addition to dropping the clustered index, SQL Server must rebuild all nonclustered indexes on the table.
To avoid rebuilding indexes, drop the nonclustered indexes first and the clustered index last.
Use SQL Server Profiler to:
Capture a trace when a script containing the queries you want to analyse is running.
When you execute the queries directly by typing them into the Query Editor.