NAV 2009 Installation and Configuration Chapter 8 — Optimisation and Troubleshooting Notes

 —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  — —

Sup­port trans­la­tion:
 — —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  —  — —
NAV 2009 Install­a­tion and Con­fig­ur­a­tion Chapter 8 — Optim­isa­tion and Troubleshoot­ing Notes

Data­base Per­form­ance Troubleshoot­ing

SQL Server prob­lems can be a res­ult of:
Issues with­in a data­base.
Issues relat­ing to instances of SQL Server.
Phys­ic­al per­form­ance of the server.

Resolv­ing Space Issues
Con­fig­ure data­base to auto-grow or grow it manu­ally using ‘ALTER DATABASE’.

Mon­it­or­ing Auto-Grow and Auto-Shrink

Use Pro­filer to record events as they occur in an instance of the data­base engine.
Event classes and event cat­egor­ies are avail­able on the Events Selec­tion tab of the Trace File Prop­er­ties dia­logue box in the SQL Pro­filer.
The Data­base event cat­egory is used to mon­it­or auto-grow and auto-shrink events.

Data File Auto Grow Event Class

Event indic­ates that the data file grew auto­mat­ic­ally.
Not triggered if you do not use AutoGrow.

Data File Auto Shrink Event Class

Event indic­ates that the data file has been shrunk.

Log File Auto Grow Event Class

Event indic­ates that log file grew auto­mat­ic­ally.
It is not triggered if the log file is grown through Alter Data­base.

Log File Auto Shrink Event Class

Event indic­ates that the log file shrunk auto­mat­ic­ally.
It is not triggered if the log file shrinks through Alter Data­base.
Updat­ing Stat­ist­ics

After a num­ber of state­ments, the stat­ist­ics will not rep­res­ent the true data dis­tri­bu­tion in a given column/index.
SQL Server may auto­mat­ic­ally update the stat­ist­ics by using auto-update stat­ist­ics.

This is triggered by query optim­isa­tion or by exe­cu­tion of a com­piled plan.
If the query optim­izer needs stat­ist­ics, it it updated auto­mat­ic­ally if it is out of date.

The auto-update stat­ist­ics fea­ture can be turned off at dif­fer­ent levels.

The sp_auto­stats Stored Pro­ced­ure
Dis­play and change the auto-update stat­ist­ics set­tings for a table, index, or stat­ist­ics object.
You can re-enable the auto­mat­ic updat­ing of stat­ist­ics by using ALTER DATABASE, UPDATE STATISTICS, or the sp_auto­stats stored pro­ced­ure.
You can­not over­ride the data­base set­ting of OFF for auto-update stat­ist­ics by set­ting it ON at the stat­ist­ics object level.
The sp_up­datest­ats Stored Pro­ced­ure
This stored pro­ced­ure runs UPDATE STATISTICS again­st all user defined and intern­al tables.
It spe­cifies the ALL keyword again­st all user-defined and intern­al tables in the data­base.

Eval­u­at­ing Index Usage
An index lets SQL Server quickly find spe­cific inform­a­tion in a table or indexed view.
You can improve the per­form­ance by cre­at­ing well designed indexes.
Indexes reduce the amount of data that must be read to return a res­ult.
Indexes also ensures data integ­rity.

An index con­tains keys from one or more keys in a table/view.
These are stored in a b-tree struc­ture.
A table/view can con­tain clustered and non­clustered indexes.

Clustered Indexes

Sort and store data in the table/view based on their key val­ues.
You can define only one clustered index per table because the data rows them­selves can be sor­ted and stored in only one order.

Data rows in a table can be stored in order only when the table con­tains 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.

Non­clustered Indexes

A struc­ture sep­ar­ate from the data rows of the table itself.
A non­clustered index con­tains non­clustered index key val­ues and each key value entry has a point­er to the data row that con­tains the key value.

Point­er from index row is called a row loc­at­or.
The struc­ture depends on wheth­er the actu­al data pages are stored in a heap/clustered table.

A rule that the data­base server enforces auto­mat­ic­ally.
Indexes are auto­mat­ic­ally cre­ated when you define PRIMARY KEY and UNIQUE con­straints on table columns.

Query Optim­iser
Well-designed indexes reduce disk I/O oper­a­tions and use less sys­tem resources.
Indexes improve the per­form­ance on SELECT, UPDATE and DELETE state­ments.

Defrag­ment­ing Indexes
Frag­ment­a­tion of indexes pre­vent SQL Server from using indexes in an optim­al way.
Index frag­ment­a­tion can be:
Intern­al — pages that have a lot of free space.
Extern­al — pages are not con­tigu­ous.

DBCC SHOWCONTIG is used to view the extent of index frag­ment­a­tion.
Using TABLERESULTS with the above func­tion allow you to see extra out­put columns with index stat­ist­ics.

Index defrag­ment­a­tion met­rics

Avg. Page Dens­ity (full) — shows how filled pages are.
Scan Dens­ity — ratio between Best Count of extents and Actu­al Counts. The higher the per­cent­age the bet­ter with a min­im­um of 75%.
Logic­al Scan Frag­ment­a­tion — ratio of pages that are out of logic­al order. The lower the per­cent­age the bet­ter with a max­im­um of 10%.

Index Defrag­ment­a­tion SQL Com­mand

Drop and re-cre­ate the index.

First three options should ONLY be done when off­line.
The last option can be done online. It will not defrag any indexes that are cur­rently in use.

Rebuild­ing Indexes
Use SQL Server Man­age­ment Stu­dio (SSMS) to rebuild indexes on a table.
Rebuild­ing an index drops and re-cre­ates the index.
This removes frag­ment­a­tion, reclaims disk space, and reorders the index rows in con­tigu­ous pages.

Cre­at­ing Miss­ing Indexes
Query optim­iser gen­er­ates a query plan.
It ana­lyses what the best fil­ters are for a fil­ter con­di­tion.
If the best indexes do not exist, the query optim­iser gen­er­ates a sub­op­tim­al query but stores inform­a­tion about miss­ing indexes.
Miss­ing indexes enables you to access inform­a­tion about these indexes so that you may choose wheth­er to imple­ment them.
You then use CREATE INDEX to restore the miss­ing indexes.

Drop­ping an Inap­pro­pri­ate Index
Remove an index — 1) DROP INDEX SQL state­ment.
2) SSMS (SQL Server Man­age­ment Ser­vice).
This pro­cess reclaims the disk space the index cur­rently uses.
Delet­ing an index is the same as drop­ping it.

Drop­ping a clustered index can take time because in addi­tion to drop­ping the clustered index, SQL Server must rebuild all non­clustered indexes on the table.
To avoid rebuild­ing indexes, drop the non­clustered indexes first and the clustered index last.


Use SQL Server Pro­filer to:
Cap­ture a trace when a script con­tain­ing the quer­ies you want to ana­lyse is run­ning.
When you execute the quer­ies dir­ectly by typ­ing them into the Query Edit­or.