What is an execution plan?

Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn’t my index getting used? Why does this query run faster than this query?. The correct response is probably different in each case, but in order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query you submitted.

Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA’s primary means of troubleshooting a poorly performing query. Rather than guess at why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.

The aim of this chapter is to enable you to capture actual and estimated execution plans, in either graphical, text or XML for ­mat, and to understand the basics of how to interpret them. In order to do this, we’ll cover the following topics:

• A brief backgrounder on the query optimizer – execution plans are a result of the optimizer’s calculations so it’s useful to know at least a little bit about what the optimizer does, and how it works.
• Actual and Estimated execution plans – what they are and how they differ
• Capturing and interpreting the different visual execution plan formats – we’ll investigate graphical, text and XML execution plans for a very basic SELECT query
• Automating execution plan capture – using the SQL Server Profiler tool

Shrink Operation- Internally what happens?

Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system.

Limitations and Restrictions:

• The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. 
• 
  
• For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
• 
  
• You cannot shrink a database while the database is being backed up. Conversely, you cannot backup a database while a shrink operation on the database is in process.

• DBCC SHRINKDATABASE will fail when it encounters an xVelocity memory optimized columnstore index. Work completed before encountering the columnstore index will succeed so the database might be smaller. 
• 
  
• To complete DBCC SHRINKDATABASE, disable all columnstore indexes before executing
•  
• DBCC SHRINKDATABASE, and then rebuild the columnstore indexes.

Recommendations:

• To view the current amount of free (unallocated) space in the database.
• Consider the following information when you plan to shrink a database: 
• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
• Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

AlwaysON - Prerequisites -Every SQL DBA must know!

Before implementing your AlwaysOn Availability Group, make sure you have everything in your environment ready to go. There are several prerequisites that need to be done to ensure a successful deployment. These prerequisites focus on your Windows environment, the SQL instances, and the databases to be included in your AlwaysOn group.

Windows Level:

Windows Server 2008 R2. Windows Server 2008R2 is the minimum Windows Server edition allowed for deploying AlwaysOn. However, it is strongly recommended that you use

Windows Server 2012 instead, as there are many issues and headaches associated with 2008R2.

Windows Server Failover Cluster. WSFC must be installed on every replica (primary and secondaries) in the AlwaysOn group. This is done through Server Manager > Add Roles & Features > Add Failover Clustering. And each SQL Server that hosts an availability group replica must reside on separate nodes of a single WSFC cluster.

.Net 3.5.1 or greater. If .net 3.5.1 or greater is not already installed on your primary and secondary replicas, you will need to install this. This can be installed at the same time you install your Windows Failover Cluster (via Server Manager > Add Roles & Features > Add Failover Clustering):

Windows Domain. All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.

No domain controller. None of your replicas can be a domain controller. AlwaysOn groups are not supported on a domain controller.

Windows Firewall. Most likely you will need to adjust your Windows Firewall setting to allow the replicas to communicate with each other.

Sufficient Disk Space. You don’t have to have identical hardware on each replica, but you do need enough disk space to hold all of your databases, and to account for the growth of those databases. As your databases on the primary replica grow, the replicated databases on your secondaries will also grow the same amount. And if you have other databases on your secondary replicas that are not part of your AlwaysOn group, your disk space must account for the size and growth of those databases as well.

Sufficient Resources. Again, you don’t have to have identical hardware on each of your replicas, but you need to have sufficient resources on your secondaries to handle the same workload as your primary. If you’re thinking that “we’ll use our good, powerful server as the primary replica, and use the slower, weaker server as a secondary” with the thought that if we do have to fail over, we will just know up front that the performance will be a little slower while we get the primary server back up and running… well guess what. You’re right. It will be slower performance if you have to fail over. But it will also be slower if you don’t fail over! That’s because the primary sends transactions to the secondary, and with synchronous data replication, the primary must wait for the secondary to harden the log before it can move on to the next task. So your primary will only operate as fast as your slowest secondary. Your AlwaysOn group is only as fast and strong as your weakest link. So be very familiar with your workload, and try and make sure your replicas are equally yoked.

Instance:

SQL Server 2012 or 2014 Enterprise Edition. For testing or development purposes, you can install Developer or Evaluation edition, but for a production environment, you must install Enterprise edition. Standard edition will not work.

Database Collation. Databases in your AlwaysOn group must use the same collation…they cannot differ across replicas.

SQL Server Collation. All replicas in your AlwaysOn group must use the same SQL Server collation.

Active Directory Services. No replicas can run Active Directory services. This is not supported with AlwaysOn.

Database Mirroring Endpoint. Each instance needs a database mirroring endpoint. If you have more than one instance on your server, you will need to create an additional endpoint so the instances can communicate with each other.

SQL Service Account. Your account that runs SQL Services must be a domain account. Do not use the local machine service account. The SQL service account must be able to access every replica, and therefore must be a domain account.

Also, this account must belong to the Administrators Group on each of the replicas.

And this SQL service account needs connect permissions (given through SSMS). Right click on the SQL Service login to open the properties dialog box, go to the Securables page, and make sure the ‘Connect SQL’ Grant box is checked. Do this on all your instances

Databases:

Full Recovery Model. Make sure your databases are in Full Recovery Mode, not Simple or Bulk Logged. Also, these databases must be taken out of any tlog backup maintenance process while the AlwaysOn group is being created.

User database. Databases included in your AlwaysOn group must be user databases. System databases cannot participate in AlwaysOn Availability Groups.

Read/Write database. Read-only databases cannot belong to an AlwaysOn group.

Multi-user database. Databases must be in multi-user mode, they can’t be in single user mode.

Don’t use AUTO_CLOSE. Check the properties of your databases, and make sure this option is set to ‘False’.

DB in only one Availability Group. Databases may only belong to one availability group at a time. You can have more than one AlwaysOn Availability Group on your instance, but databases cannot belong to more than one group.

Not configured for database mirroring. Your databases cannot be enabled for database mirroring. Make sure this feature is not enabled.

Full Backups. Make sure full backups of each of your databases are made prior to installing AlwaysOn.

Allow Remote Connections. This can be done in SSMS either through the Instance properties, or by using sp_configure, which ever you prefer.

EXEC sp_configure ‘remote access’, 1;

GO

RECONFIGURE;

GO

SUSPECT DATABASE in SQL Server

EXEC sp_resetstatus 'DBname';

ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname SET MULTI_USER

ALTER DATABASE DBname SET SINGLE_USER

DBCC CheckDB ('DBname', REPAIR_REBUILD)

ALTER DATABASE yourDBname SET MULTI_USER

Application is running slow

1. Check with the application team from when it is running slow.

2. Check my pinging the server.

3. Check network latency (contacting with N/W admin).

4. Check improper responsiveness or OS related issue (Wintel team).

5. If nothing is found, we can ask app team, to verify whether the application is running from other machine or not.

6. Check for Blocking.

7. Kill the process with approval  .