Monday, June 21, 2021
dbametrix
More
    HomeOracle TuningSQL Server performance tuning best practices

    SQL Server performance tuning best practices

    This article explains best practices of SQL server performance tuning to improve performance of sql database.

    In SQL Server, a critical factor in database performance is indexes. The indexes can be of 5 types: clustered, non-clustered, xml, spatial and full text. (We can also include the indexes in the views)

    Clustered indexes: there can only be one per table since the tables can only be ordered in a single way. Normally it matches the primary key of the table and in fact, if we create a table in sql server and define a primary key, it automatically creates a clustered index for us.

    - Advertisement -
    dbametrix

    Non-clustered indices: there can be as many as we want and therefore are the other ways of ordering a table. For example, a table of books can have the ISBN as primary key and clustered index, and several non-clustered indexes such as subject, author, etc …

    Filtered indexes: they are a variant of the non-clustered ones that have a Where clause to filter the content of the index. Ideal when we are looking for data in a column that contains many NULL values. In this index, in the WHERE clause we remove the NULLs, which makes it a compact and fast index to use.

    xml: they are indexes on the content of an xml document, thus significantly speeding up xml queries. Remember that since SQL Server 2005, xml is a native type and therefore we can store data in that type.

    Spatial – These index types are very useful with spatial-type data such as geometric and geographic types (new to SQL Server 2008). They use two levels (primary and secondary) and they work like this: let’s imagine that we are looking for the intersections of an address in Valencia city and we have the entire map of Spain. Without an index, we would search for street intersections throughout Spain (impracticable), but with a spatial index, the primary level would return the Valencia city area and there the search would enter the second level that would be in charge of checking the street intersections that we have indicated.

    - Advertisement -
    dbametrix

    full text: they are indexes on text catalogs. They help us to improve the searches in queries with CONTAINS and FREETEXT in searches on text catalogs on fields of type observations, comments, etc … In this way it is able to understand what we are looking for and not only searches for strings, but words and meanings .

    Index fragmentation is the critical element and is inevitable when we make many updates to a table, and therefore a dba must be constantly attentive to such fragmentation. For each index, with the right button of the mouse Properties, we can consult said fragmentation in the SQL Server Management Studio.

    There are two very interesting parameters of the indexes:

    fill factor: allows us to add a free space in the index sheets, in this way, if we have many updates, we can avoid or minimize the dreaded partitioning of the index sheets (splits)

    pad index: is similar to fill factor, but adds that free space to all index leaves (not only to the root of the index, but to the entire index tree).

    One of the best tools we have to improve query performance in databases are views. Imagine, for example, that we have a query to obtain a list of records and that this list is obtained by making a query with 5 joins. We save the definition of said query in the view. We can think that at the performance level, executing the query with the 5 joins is the same as executing the view that executes the query, but that is where the views give us great advantage using indexes.

    On the result of a view, we can add an index, considerably increasing the execution of said query. In this way, when we look for a record in the previous query of 5 joins, the search will be instantaneous, since we will be using an index on those 5 joins. If we do that without the view, we need to do a join of 5 tables first and then a filter with a where. We now understand the great speed that we can obtain with the indexes in the views. The only downside is that obviously the updates penalize the indexes, so we have to see if the tables of those 5 joins are really updated many times or occasionally, and that is where the dba and the developers have to find that balance.

    Schema binding: This property allows us to configure the view in such a way as to avoid modifying the design of the table without first removing this property. If we enable this property, we avoid having orphan views that are so annoying for the user (views that because a column is missing, a table … do not work), that is, if we want to modify the structure of the table, it is necessary to previously disable the schema binding associated with the view.

    with check option: This property prevents us from making updates other than those we have in the definition of the view. For example, if the definition of the view we have a filter with a where id = 150, then the updates can only be id = 150.

    Views in SQL Server:

    Improve the performance of our database

    Three concepts are essential if we want our database to go at full speed: memory, configuration of the log and indexes with statistics:

    – The more memory the better: All today’s powerful databases (SQL Server and Oracle) work entirely in memory. If there is an update that changes 1000 rows, the change is made in memory and a single entry is noted in the log (on disk). That is why the more memory the better, and obviously 64 bits better than 32 bits.

    – Configuration of the log in an independent disk: The databases of today use a log for the fast storage of the sentences that we send them. Since it is very fast and constant, it has to be on a separate hard drive from the database data one. The log file is sequential and the data file is random. Normally we will install a RAID 5 in the data one and a RAID 1 in the log one, although it may vary, but yes, independent disks so they do not compete with each other. In addition, in case of losing, for example, the data file, with the backup file from the previous day and with the log that we have on the other disk, we are able to recover the database right up to the moment it failed. .

    – Updated indexes and statistics: To speed up searches in our database tables, it is essential that we have indexes as this will avoid having to make table scans of the entire table. In addition, these indexes have to be updated because if they become very fragmented (mainly with updates and deletes) they are useless. Who tells us how useful this index is? The statistics. The statistics tell us how good and updated the index is, that is why we must always keep them up-to-date and up-to-date.

    When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. These all Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.

    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Recent Comments