Check if PowerShell is Installed on SQL Server

Many database related tasks can be automated using PowerShell. This T-SQL script can be used to check the following: Is PowerShell installed on your local SQL Server? Is it enabled for script execution? What is the version of PowerShell installed on your machine? You can obtain all the above information using the below T-SQL script. Since we have to obtain information from the server registry and from the output of PowerShell.exe file, we will be using both... Read more

Server side lightweight SQL Server tracing

There are times when you would want to capture database activity and the most popular tool that database engineers use is SQL Server Profiler. Note: SQL Server profiler has been deprecated and it is recommended to use Extended Events for tracing any database related activity. However, in this article, we will look into how we can use SQL Server profiler without the need to run from the SQL Server Profiler tool and how we can minimize the performance impact by... Read more

T-SQL Script to find High CPU Queries

There are times when you would want to know what query in SQL Server may be causing CPU spikes. There are many reason why a query may hit the CPU and the best way to troubleshoot it to: Identify the query that is causing high CPU Identify the total time taken by query to execute that query Find out when was the last time the query was executed Obtain Query execution plan for the offending query There are many scripts available on the internet and many-a-times that... Read more

How to restore damaged pages from a backup

Are your database pages corrupt or damaged? Now you can restore damaged pages without impacting the availability of the database. SQL Server provides an extension to the RESTORE command that can help restore damaged pages from the backups. (Note: Only the portion that is being restored will be unavailable) Important things to Keep in Mind: You cannot restore log pages You cannot restore GAM, SGAM and PFS pages. You cannot restore Page 0 of all data files (Page 0... Read more

Why do Queries behave differently in production

You may have experienced something similar where the query or a stored procedure performs bad when deployed in the production and the same query / stored procedure works perfectly well in the test or development environment. Behind the scenes (Query Internals) It is possible for the query or the stored procedure to behave differently in lower environments than Production if you have not cleaned up the query execution plan cache when performing tests. Internally,... Read more

How to Recover Data from a Corrupt Database

Remember those days when the database would go in to suspect mode and you had to perform various steps to recover the database by putting the database in the emergency mode and then extracting the data out of that emergency database? Here’s a flash back on how we used to do in the past: Enable modifications to system catalogs. Change the status of the database in SysDatabases system object to 32768 Restart SQL Server services (This would put the database in... Read more

Improve Performance of SQL Server File Operations

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 DBAs can benefit from called SQL-WIFI (Windows Instant File Initialization). What Happens Internally: Every time SQL Server needs space for data or log file for any... Read more

Why to Optimize Ad-Hoc Workload in SQL Server

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... Read more

Automatic database backups in Azure Cloud

Backups are extremely important for the continuity of business. Many companies fire their engineers just because they didnt have proper backups in place. Wouldnt it be great if there was a way that DBAs and engineers didnt 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... Read more

Hacking SA Password in SQL Server

You are a proud and trusted DBA of your organization who is responsible for maintaining and managing 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 companys 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... Read more

T-SQL Script to Update Statistics

Index Statistics are important for query optimizer to generate optimal query plans. If indexes have outdated or stale statistics, your query’s performance will suffer. It is important to update the statistics to make sure query optimizer always generates the best and optimal plan. This script allows you to report and update outdated statistics. In reporting mode, this script provides the following information: 1. Name of the Database 2. Table Name 3. Index... Read more

T-SQL Script to Check Database Compatibility

Setting the database to the right compatibility level is very important as it can have negative impact on the performance and behavior of your databases. I’ve seen many customers upgrade their SQL Servers from older versions to newer but forget to upgrade the compatibility of their databases. This means that even though you have an upgraded version of SQL Server, your database cannot benefit from any new or enhanced features of SQL Server since your database... Read more

EXECUTE Query AT Remote Server

We use EXEC or EXECUTE command almost every day of our database life. Its 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/DBA’s are aware of this feature. Historically, EXEC / EXECUTE executed commands locally, meaning if you have a stored procedure, you can only run that on your local... Read more

Running Antivirus on Production SQL Server

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: Youve 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... Read more

Use LOGON Triggers to Audit or Control Users

Ever felt the need for knowing who is logging on to your SQL Server and at what time? Ever felt the need to restrict specific users for certain time-period or firing a trace to track down user activity? Ever felt like limiting the number of concurrent connections for specific users? Well, you can do all that now with Logon Triggers. Logon Trigger allows you to fire a T-SQL, or a stored procedure in response to a LOGON event. You may use logon trigger to audit and... Read more

Best Protocol for Connecting to SQL Server

I often get this question from the community and my answer to this question depends based on your network environment whether you are on a local computer with no networks or whether you are on a Local Area Network or whether you are on a Wide Area Network. Ive tried to provide some guidance / recommendation below that works best for many SQL Server installations. In order to connect to SQL Server, your client machine must have a network protocol enabled that can... Read more

Using Database Snapshot for Mirror Server

In the database mirroring environment Principal interacts with all the users but the mirror server only receives transaction log data from the principal server and other than that mirror database doesnt do anything as its in “RECOVERING” state all the time. In this knowledge nugget, we will explore how you can use Mirror database for Reporting or Querying purpose. What are Database Snapshots?  Database snapshot is a static, read-only, transaction-consistent... Read more

Using Synonyms for Creating Shortcuts

What would you do when an object that is referenced in several places and in several databases (Stored Procedures, Functions, etc.) is being moved to a different database or is being renamed due to a new object naming policy? Think of how much work this change would generate for you? Wouldnt it be nice if there was a way to take care of these changes automatically? A synonym is an alternative name (think of it like an alias) for objects in SQL Server that provides... Read more

Best Practices for SQL Server Service Account

Securing SQL Server is one of the top priorities of any DBA. It is important for DBA’s to make sure that the database server is TIGHTLY SECURED and is not exposed to users that do not need access to SQL Server. Only valid and approved users MUST be granted “required” permissions. Apart from configuring the right security/permissions for SQL Server users/logins, it is also important to select the right account to run SQL Server services. In todays tip, we... Read more

Using CONCAT Function for String Concatenation

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. This eliminates the need of explicit data conversions when concatenating two values. Note: NULL values are implicitly converted to an empty string. If all... Read more

Data Pagination Using OFFSET and FETCH

SQL Server offers new query hints that allow you to implement query paging solution. In the past, we’ve 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. Let’s 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... Read more

Improve Index Performance using MAXDOP

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: Load on SQL Server Out of date statistics Server configuration setting for degree of parallelism (MAXDOP) Amount of available memory Amount of available resources, etc. SQL Server can intelligently detect the load on the... Read more

Using SEQUENCE for Incremental Numbers

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, weve 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. Youve tried IDENTITY() and now try the new feature introduced in SQL Server 2012 and experience the difference. SQL Server introduces a brand new schema bound object... Read more

How to backup large databases

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 todays topic, I 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... Read more