Tuesday, September 23, 2014
BI Developer TrainingSQL DBA TrainingSQL Internals TrainingSQL Developer TrainingETL Developer Training

SQL Server - Optimize for Ad Hoc Workload Server Setting (Internals)

By Saleem Hakani on 9/23/2014

By default, when a query is executed in SQL Server, it compiles and generates an execution plan for that query and stores that plan in the plan cache. Now if this happens to be a one-off/ad-hoc query that will never be run again, then the newly generated plan is wasted and its space that is being used in the plan cache is also wasted. This means, the buffer pool now contains compiled plan of queries that will never be executed.

Now imagine if you have hundreds of ad-hoc queries like this?

SQL Server introduced a new server level setting called “Optimize for Ad Hoc Workload” which helps address this issue. This setting improves the efficiency of plan cache for one-off/ad hoc queries/batches or workloads. Once you enable this option it changes the behavior of storing the compiled plan in the plan cache. Internally, when the query is executed the first time, the database engine stores query hash in the plan cache which is very small in size compared to the compiled plan. However, the second time the same query is executed, the database engine checks and recognizes that the query hash exists for the ad-hoc workload and it goes ahead with creating a full compiled plan for that query and stores is in the plan cache by removing the query hash. All subsequent execution of this query will use the plan from the plan cache.

Best Practices
Buffer Pool Memory
Compiled Plan
Improve Query Performance
Optimize for Ad Hoc Workload
Plan Cache
Plan Cache Bloat
Query Hash
Server Level Setting
SP_Configure Setting
SQL Internals

SQL Server - Running Anti-Virus on SQL Server

By Saleem Hakani on 9/21/2014

Running Antivirus software is critical part of server security but it is also important to understand the impact and effect it has on SQL Server. Imagine this: You’ve installed Anti-Virus on a server that already has SQL Server running, once the server reboots, it is highly likely that the Anti-virus software depending upon the scanning schedule will scan the SQL Server folders and lock SQL Server files before SQL Server can gain access to those files. When this happens, SQL Server would not only be able to access those files but also, there’s a possibility of data corruption since both SQL Server and the Anti-Virus programs are trying to access the files by putting an exclusive lock on them.

Many DBA’s would simply exclude .MDF and .LDF files from being scanned by Anti-Virus but that’s not enough. There are other important files that can also have an impact on SQL Server.

Running Anti-Virus on SQL Server
SQL Server with Anti-Virus

SQL Server - Let there be Backups and Backups Happend

By Saleem Hakani on 9/21/2014

Backups are extremely important for the continuity of business. Many companies fire their engineers just because they didn’t have proper backups in place.

Wouldn’t it be great if there was a way that DBA’s and engineers didn’t have to worry about backups and SQL Server was smart enough to perform the backup based on workload pattern (data changes) or based on some defined recovery interval and the backups are auto-magically stored offsite without having to worry about putting them on tapes and then shipping them.

Let’s welcome Managed Backups in SQL Server 2014. The SQL Server product team has done an awesome job in delivering this new feature that takes away the headache of managing and maintaining database backups. SQL Server Managed Backup automatically performs backups of your databases and stores them to Windows Azure Blob Storage Service.

Automated Backup Solution
Backups and Restores
Cloud Backups
Managed Backups
SQL Server Backups

SQL Server - Encrypting Database Backups

By Saleem Hakani on 9/21/2014

Imagine this: You have sensitive information in your database (ex: SSN or Employee Payroll Information). You perform weekly full database backups, daily diffs and hourly T-log backups. You ship out one copy of the database backup to an offsite location. At this point the backup copy of the database is not in your control as it has already left your office. It is very possible for someone to restore the copy of the database since Microsoft supports restoring the database on any SQL Server with the right version in which the backup was performed. However, you would still feel uncomfortable knowing that your database has sensitive information and that it is possible for someone to get their hands on to the backup copy of the database. What would you do?

SQL Server 2014 Now Supports Encrypted Backups.

Backup Encryption
Data Security
Encrypted Backups

SQL Server - AlwaysOn 2012 Vs. AlwaysOn 2014

By Saleem Hakani on 9/21/2014

AlwaysOn (Codename. Hadron ((High Availability Disaster Recovery Always On)) was introduced in SQL Server 2012. It’s one of the most powerful high availability technology introduced in SQL Server and is one of the compelling reasons to migrate to SQL Server 2012 that provides reliable, integrated, flexible and efficient high availability and disaster recovery solution. In brief, AlwaysOn availability groups enable HADR for multiple databases with the need of expensive SAN hardware and you can offload many tasks/operations like (Queries, Reporting, Backups, Maintenance, etc.) to the secondary replicas and Primary replica can be used for write purpose. Data is copied per log record from Primary to secondary servers either synchronously or asynchronously. 

AlwaysOn Comparison
High Availability

SQL Server - Prevent Page Splits

By Saleem Hakani on 9/21/2014

Say you have a glass filled with water and you try to put more water in that glass, what happens? Water in the glass will overflow. Exactly the same way, whenever a new row is added to a full index page, SQL Server moves around half of the rows to a new page to make room for the new row. This is known as PAGE SPLIT. Page splits can make room for new records but can be very resource intensive. Page-Splits can also incur fragmentation which may adversely affect I/O operations.


Preventing Page-Splits:

 In order to avoid PAGE-SPLITS, you must proactively determine the FILL FACTOR value. When an index is created or rebuilt, the fill factor value determines the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of free space for future growth.

Fill Factor
Page Fullness
Page Split

SQL Server - Execute Commands Remotely Using AT Clause

By Saleem Hakani on 9/21/2014

We use EXEC or EXECUTE command almost every day of our database life. It’s a command that allows us execute Stored Procedures (extended, system, user, CLR, etc.)  and Functions. However, EXEC command also has a powerful feature that allows you to run any command remotely. Not many developers are aware of this feature. Historically, EXEC/EXECUTE executed commands locally, meaning if you have a SPROC you can only run that on your local instance of SQL Server. That’s changed now.

EXEC or EXECUTE statement now supports a parameter called AT which allows you to specify the SQL Server instance at which you plan to execute your command from your local SQL Server instance. To use this powerful feature, you will need to establish a linked server from your local SQL Server instance to the remote SQL Server instance. This is required for establishing connections between both local and remote SQL Servers.

AT Clause
Linked Server
Remotely Execute Commands

SQL Server - Audit DML Changes without Triggers

By Saleem Hakani on 9/21/2014

You can now audit your changes using DML statements without even using TRIGGERS:


SQL Server supports an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the result set in a table or table variable.

The functionality is similar to what triggers had with INSERTED and DELETED tables which used to access the rows that have been modified during the DML operation.


SQL Server - Backdoor to SA Account

By Saleem Hakani on 9/21/2014

You are a proud and a trusted DBA of your organization who is responsible for some important services running on SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your company’s SQL Servers from any unauthorized access:

You have removed any and all built-in administrators account from SQL Server logins.

You have removed all users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)

You have set the password of SA account to something extremely complex which would be hard for anyone to guess or remember.

For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on user databases but doesn’t have SYSADMIN privileges on the system.

You have not documented the SA password anywhere to prevent others from knowing the SA password. (Also, it’s not a good practice to document the password)

Hacking SQL Server
SA Backdoor
SA Password Lost

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 Article Tags
Copyright [2014] by SQLCOMMUNITY.COM