Saturday, January 20, 2018

SQL Server - File Operations on Steroid

By Saleem Hakani on 9/25/2014

Ever wondered why SQL Server takes long time to perform below operations?

  • Create a database

  • Expand data files for additional growth

  • Automatic data file growth due to auto-grow settings

  • Restoring a database, etc.

Today we will be looking in to a powerful and yet not very popular feature that many DBA’s can benefit from called SQL-WIFI (Windows Instant File Initialization).


Every time SQL Server needs space for data or log file for any activities listed above, it will grab the required space on the disk and initialize every block of that disk with Zero’s before it can use that space for data or log files. It does that to overwrite any existing data left on the disk from previously deleted files.

Imagine if you were to create a 100GB database or a restore a 500 GB database with the default configuration of SQL Server, it will first call the API’s that will overwrite every block on the disk where the file will be restored with 0 before it starts creating or restoring the database on that disk. This can take a long time depending upon how powerful your server is or the resources available on the server.

Improve File Operations
Instant File Initialization
Perform Volume Maintenance Tasks
Windows Instant File Initialization

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