Saturday, January 20, 2018

SQL Server - Index Operations on Steroids

By Saleem Hakani on 9/21/2014

Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time? There are many things that happen behind the scenes that could contribute to the slowness of index operations.

Some of the reasons include:

1. Load on SQL Server,

2. Out of date statistics,

3. Server configuration setting for degree of parallelism,

4. Amount of available memory

5. Amount of available resources,

6. etc.

SQL Server can intelligently detect the load on the server and auto-adjusts the amount of resources that can be allocated to a process. In the case of Index operations; if SQL Server is busy, it will automatically adjust the Max Degree of Parallelism server configuration setting to accommodate the process or the load. This means it could either grant or limit the number of CPU’s Index operations can use. In many cases this is the best practice and you should not change the default value.

With the release of SQL2K5 onwards, SQL Server has introduced a new indexing option called MAXDOP. With MAXDOP, you can now control the number of processors/CPUs that can be used for performing index operations. MAXDOP option enables parallelism for Index operations, which means it can use multiple processors to fulfill a single query statement which can potentially improve the performance of index operations such as:

1. Creating an index,

2. Altering an Index,

3. Rebuilding an index,

4. Dropping a Clustered Index, etc.

Fast Index Creation
Index Operations

SQL Server - Generating Sequence Numbers

By Saleem Hakani on 9/21/2014

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, we’ve been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. You’ve tried IDENTITY() and now try the new feature added in SQL Server 2012 and experience the difference.

SQL Server 2012 introduces a brand new schema bound object called SEQUENCE. Sequence generates numeric values based on the specification of a SEQUENCE object. You can generate numeric values in either ascending or descending order and they can be independent of tables unlike IDENTITY columns.

Auto Generate Numbers

SQL Server - OFFSET and FETCH Query Hints

By Saleem Hakani on 9/21/2014

SQL Server 2012 onwards, Microsoft introduced two brand new query hints that allow you to implement query paging solution. In the past, we have used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator.

Assume you have 50,000 records in a table and you want to query 1200 rows starting from 25000. Traditionally you would use a cursor to implement this solution however with the introduction of OFFSET and FETCH it’s much easier.

Query Hints

SQL Server - Large Database Backups

By Saleem Hakani on 9/21/2014

In an ideal world, hard drives and other hardware never fail, software is never defective, users do not make mistakes, and hackers are never successful. However, we live in a less than perfect world and we should plan and prepare to handle adverse events.  

In today’s topic, we will focus on best practices for backing up large mission critical databases. Performing and maintaining good backups is one of the top priority for any DBA/Developer/Engineer working with SQL Server. 


Backup up Large Databases
Large Database Backups

SQL Server - Security Permission Model

By Saleem Hakani on 9/21/2014

Imagine for a moment that you are a SQL Server production DBA. You arrive at the office and there are two new co-worker requests in your e-mail inbox: Bob works in merchandise and needs read-only access to all the sales data so that he can run reports to forecast purchase volumes, Alice is a new junior DBA who only requires access to the meta-data. These seem like easy requests to fulfill – or are they?

The SQL Server Permission model can be used to solve this.

Securables are entities that SQL Server controls access to through permissions. Permissions enable a principal to perform actions on a securable. Across all securable scopes, the primary commands to control access to a securable are GRANT, DENY and REVOKE.

Database Engine Permission Model
SQL Server Permission Model

SQL Server - Hashing Techniques

By Saleem Hakani on 9/21/2014

A common scenario in data warehousing applications is knowing what source system records to update, what data needs to be loaded and which data rows can be skipped as nothing has changed since they were last loaded. Another possible scenario is the need to facilitate searching data that is encrypted using cell level encryption or storing application passwords inside the database.

Data Hashing can be used to solve this problem in SQL Server.

A hash is a number that is generated by reading the contents of a document or message. Different messages should generate different hash values, but the same message causes the algorithm to generate the same hash value.

MD2 Encryption
MD4 Encryption
MD5 Encryption
SHA Encryption
SHA1 Encryption
SHA2_256 Encryption
SHA2_512 Encryption
SQL Server Hashing

SQL Server - TDE Best Practices (Transparent Data Encryption)

By Saleem Hakani on 9/21/2014

You may have heard about Transparent Data Encryption (TDE), which was introduced in SQL Server 2008. But what does it do, what are its advantages and disadvantages and how can you leverage this technology in SQL Server? Keep reading this tips and tricks article to learn how.

Transparent Data Encryption (also called as TDE) is a technology in SQL Server that offers encryption of data-at-rest. This feature automatically encrypts the entire database (data and log files), as well as database backups, without requiring any programming or code changes to your application. The process is entirely transparent, hence the name Transparent Data Encryption.

When TDE is first enabled for a specific database, SQL Server encrypts the database in the background. During this process, the database remains online and responsive to client requests (similarly, when encryption is disabled, SQL Server decrypts the database in the background). Encryption is performed at the page level, and does not increase the size of the database in any way. Once the entire database is encrypted, new data gets encrypted on the fly as it is written to disk, and all data gets decrypted when read back.  Figure 1 illustrates the typical key hierarchy used for transparent data encryption.

data encryption
Database Encryption
Transparent Data Encryption

SQL Server - Ownership Chaining

By Saleem Hakani on 9/21/2014

Consider the following problem, how would you give someone access to parts of a table without giving them permissions on the table directly? Column level permissions or views can be used to project only the required columns; however, when a permission check is done, it is intuitive to check access to the view and the underlying base table.

Ownership Chaining
SQL Server Ownership Chaining

SQL Server - Using Snippets

By Saleem Hakani on 9/21/2014

SQL Server 2012 onwards, Microsoft introduced a new feature called T-SQL Snippets. T-SQL Snippets allow you to quickly build T-SQL statements without having to remember the commands or its syntax. This helps in reduced development time and in increased productivity for developers and DBA’s. Snippet templates in SQL Server 2012 are based on XML with predefined fields and values. When you use T-SQL snippet, these fields are highlighted and the user can tab through each fields and change the values as required.

Custom Snippets
Default Snippet
T-SQL Snippets

SQL Server - Concatenate Two Values Using CONCAT()

By Saleem Hakani on 9/21/2014

SQL Server 2012 onwards, Microsoft introduces a brand new string function called CONCAT(). CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function. CONCAT takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This eliminates the need of explicit data conversions when concatenating two values.

Concatenate Values
SQL Article Tags
Copyright [2014] by SQLCOMMUNITY.COM