Why you should not WAIT to fix Query WAITS

If you are a SQL Server DBA or a Database enthusiast I am sure you have heard about Wait Statistics.Wait Statistics are the most important concept in SQL Server when it comes to performance troubleshooting.

You may wondering why any query has to wait? But you may be surprised to know every query has to wait! Yes, that is true when you execute a query, the query always has to wait.There are two reasons why a query has to wait

• Resource Waits

A resource wait happens when the query is waiting for a resource such as I/O or may be on some table data which is currently locked by some other process.

• Cooperative Scheduling Waits

SQL Server bypasses the Scheduling of Windows OS to scaling the thread management efficiency. To do this SQL Server manages CPU resources by managing the CPU time by splitting it between queries. SQL Server has a time slice of 4 milliseconds, by doing this SQL Server takes a query off the CPU when it exceeds this value and let another query the ability to use CPU. This is the main reason we said each query has to wait even there are no resource waits.

In a query life cycle of SQL Server it can go in to three states. Which are;

• Running
• Runnable
• Suspended

When a query is actually doing something it is in Running state, if the query in a Resource wait then it is in Suspended state. A query in Runnable state means, it has all the resources it needs to execute but waiting for a CPU thread released from SQL Server.

SQL Server reports all the details about above mentioned query waits through Wait Statistics. SQL Server exposes the Wait Statistics through the Dynamic Management View sys.dm_os_wait_stats. The data returned from this DMV is about all the wait reasons in SQL Server.

Now the interesting part is in SQL Server 2014 we are getting total of 771 different reasons why a query can wait. Yes that is a lot but there are very specific wait types we normally worry about.

There is a very good survey done on what are the most worrying wait types in SQL Server here

However purpose of this article is not to go deep in to wait types. But what is more important is understanding what wait statistics are and when to use them.

When its come to query optimization the first step we can take is have a good look at the wait statistics, because they can tell you why queries in SQL Server were waiting and if there is a problem or not, if there is a problem then you can dig deep and find out whether it is a missing index or blocking issue or something else.

In conclusion wait statistics will only show you the symptoms not the root cause. But when you see the symptoms you must take actions to cure the root cause. This is why you should not wait to fix Query waits. We hope now you have clear idea about Query waits and what wait statistics are. We will talk more about trouble shooting with wait statistics in a future article.

Reading an Execution Plan - Part 1

Execution plan is something similar to the Bible, everyone know it is there but very few reads and understand what is in there. We here lot of people saying "Why this query is slow" but if you ask them "Have you look at the execution plan?" you rarely get the answer "Yes".

So first of all what is Execution plan,  in simple terms, it 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. In other words it says how the query was or will be executed in side SQL Server.So when you know how to read it you can find the places which are making the query slow.

Execution Plan Types

When you look at a execution plan of a particular query SQL Server presents two types of plans;

Estimated Execution Plan

When estimated execution plans are generated, the Transact-SQL queries or batches do not execute. Instead, the execution plan that is generated displays the query execution plan that SQL Server Database Engine would most probably use if the queries were actually executed.

Actual Execution Plan

When actual execution plans are generated, the Transact-SQL queries or batches execute. The execution plan that is generated displays the actual query execution plan that the SQL Server Database Engine uses to execute the queries.

Execution Plan Formats

In SQL Server execution plan can be viewed in three different formats;

• Graphical Plan
• Text Plan
• XML Plan

Graphical Plan

These are quick and easy to read but the detailed data for the plan is masked. Both Estimated and Actual execution plans can be viewed in graphical format.

Text Plans

These are a bit harder to read, but more information is immediately available. There are three text plan formats:

• SHOWPLAN_ALL : a reasonably complete set of data showing the Estimated execution plan for the query
• SHOWPLAN_TEXT : provides a very limited set of data for use with tools like osql.exe. It too only shows the Estimated execution plan
• STATISTICS PROFILE: similar to SHOWPLAN_ALLexcept it represents the data for the Actual execution plan

XML Plans

XML plans present the most complete set of data available on a plan, all on display in the structured XML format. There are two varieties of XML plan:

• SHOWPLAN_XML : The plan generated by the optimizer prior to execution.
• STATISTICS_XML : The XML format of the Actual execution plan.

Now let's look at a query and how  it's execution plan looks like. I am using the AdventureWorks sample database for the examples.

Here I am writing a full select on person table. And you can see the Actual Execution Plan below in which it does a full Clustered Index Scan. This is because SQL Server has to return all the values in the table using the clustered index.

Let's write a query which will do a restrict (WHERE filter) to the primary key, now you can see it is using a Seek to the clustered index to get the value.

Scan operations are very costly and Seek operations are performance friends since they use Indexes to get information. Simply a Clustered index scan means a full table scan which is the most costly operation to retrieve data.

Now let's look at how a non-clustered index being used is shown in an execution plan. Below query is asking for the people who has PersonType attribute is set to 'EM'. In the table PersonType column does not have an index so the Query Optimizer has no any other option but to go for a full clustered index scan to get the results.

Now let's ask SQL Server to return people who has first name Alan. Person table has a composite index for FirstName, MiddleName and LastName. As you can see it uses a index scan to on that composite index first and then do a Key_Lookup on the clustered index to present the data.

Let's ask SQL Server to get the people who has name Dylan A Millar. In this scenario it uses an Index Seek on the composite index. Which is fully utilizing the index for optimal performance. In simple terms Index Seek is the most optimal way to access the data but in the previous scenario it had to scan through the whole index since the first name didn't have a  proper index.

In conclusion you must look at the execution plan when optimizing a bad performing query. If a Index is missing you can simply identify it by looking at the execution plan as explained above. We will be discussing more about reading different type of execution plans in the next chapter of this article series.

Impersonating a Windows Login in SQL Server

In our day to day life as DBA's  most common issues we get are related to security. Specially some accounts not having required access to do certain things. This can be real messy if you don't have a well defined security architecture in place. From SQL Server 2005 on wards Microsoft has introduced Role Based security which has reduced the effort of DBA's remarkably. However if you mess up the Security Roles it will be a nightmare to fix a security issue.

So in this article I am providing a simple script which can be really handy when you want to check how a DB object behaves when its been called using a specific windows login in SQL Server.

SETUSER 'DomainName\UserName'

-- Here Goes Your SP Call or any other Query you want to check, this query will run using the credentials of the above user and report any errors that user may encounter

Always happy to answer any queries via comments.

FIX : CLR .NET Assemblies security error after Database Restore

Recently I had to migrate a massive database from one hosting provider to another (Awesome Amazon Web Services). After backup and restore I noticed all the Stored Procedures calling CLR assemblies fails with a security error. 

"Msg 10314, Level 16, State 11, Procedure xxx_xxxxxx, Line 285

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65627. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly" 

This was really strange as it was a direct restore and all the assemblies resides in side the DB itself. So here is what you have to do to fix this issue;

1. Query the table sys.assembly_files and sys.assemblies make sure your assemblies are present in the restored DB. If not you have to re register the assemblies.

2.  Make sure your restored DB has a owner (Ideally sa)

3. Make sure the login who owned the db in the previous server is created in the new db server, if it is a SQL Server login the new login should have the same SID as the previous servers login, Windows logins just having the same name would sufficient.

4. Then run the below command to make your DB trustworthy.

ALTER DATABSE [DB Name]

SET TRUSTWORTHY ON  --after restoration database has this option equal to off

GO

This should simply solve your issue. Please share with me if you find any trouble doing this workaround.

FIXED - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Problem :
After installing and configuring a SQL Server instance when I try to connect to it using SSMS from another machine, I got the below error;

TITLE: Connect to Server
------------------------------
Cannot connect to mmsdevelopment.
------------------------------
ADDITIONAL INFORMATION:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476


Solution :
After digging in to the issue I found that windows firewall on the server is enabled and it refuses any connections to SQL Server used port (1433). The solution is adding this TCP port (1433) to the Windows firewall exceptions list.

This is a technet article which details the steps of adding a port to Exception list
http://technet.microsoft.com/en-us/library/cc784523(WS.10).aspx

Apart from this specific scenario there are other situations which can cause the same error to appear. Below are the steps you should take to rectify this as detailed in the MSDN;

Cause	Resolution
Server name was typed incorrectly.	Try again with the correct server name.
The SQL Server service on the server is not running.	Start the instance of SQL Server Database Engine.
The TCP/IP port for the Database Engine instance is blocked by a firewall.	Configure the firewall to permit access to the Database Engine.
Database Engine is not listening on port 1433 because it has been changed, or because it is not the default instance, and the SQL Server Browser service is not running.	Either start the SQL Server Browser service, or connect specifying the TCP/IP port number.
The SQL Server Browser service is running but UDP port 1434 is blocked by a firewall.	Either configure the firewall to permit access to the UPD port 1434 on the server, or connect specifying the TCP/IP port number.
The client and server are not configured to use the same network protocol.	Using SQL Server Configuration Manager, confirm that both the server and the client computers have at least one enabled protocol in common.
The network cannot resolve the server name to an IP address. This can be tested using the PING program.	Fix the computer name resolution problem on your network or connect using the IP address of the server. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.
The network cannot connect using the IP address. This can be tested using the PING program.	Fix the TCP/IP problem on your network. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.

How to save a SSMS result set with ANSI Encoding

It is a common practice for DBAs and Database Developers to save SSMS(SQL Server Management Studio) query results to CSV files. Most of the time the purpose of this is to use the CSV file in another 3rd party application.

The easiest way to do this is by selecting the whole results set in the results window and right clicking on it and then selecting Save Result As option. This will pop up a window to name the output file, in the file name text box enter the CSV file name and you can save the results to a CSV file without any hassle.

The problem is when you save it like this the output files encoding type is UNICODE. It is perfectly fine if you are processing the file in SQL Server itself.  But I have seen many other 3rd party applications still doesn’t support UNICODE character encoding. So we have to save the files with commonly supported encoding type such as ANSI.

You can do this by following the same process and when the Save dialog box appears enter the CSV name and instead of clicking on the Save button, click on the small down arrow on the Save button;

Then select Save with Encoding option. It will popup the below screen.

In this screen select Ansi from the drop down list of available encodings. Then Ok and click Save on the Save dialog box. Now the CSV is saved with Ansi Encoding instead of UNICODE.

Cannot connect to SQLServer, with newly created login using SQL Server Authentication

Connection issue is one of the widely reported issue in SQL Server forums. Also an issue people who work with Databases come across very frequently.

In this post I am going to point out a solution for one of those instances. When you have created a new login with SQL Server Authentication and try to log in with it you get following error;

TITLE: Connect to Server
------------------------------
Cannot connect to SQLServer.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'username'. (Microsoft SQL Server, Error: 18456)

This can sometimes freak you out, but solution is simple. As you may aware there are two types of authontication supported in SQL Server. One is Windows Authontication and the other one is SQL Server Authontication (which is the case here). In SQL Server configuration you have to enable mixed mode authontication in order to use SQL Server Authontication. This error is most of the cases pops up because you havent enable the mixed mode authontication for the server.

Here how we can do this, right click on the server instance name on the object explorer, go to security page, select SQL Server and Windows Authontication option under Server Authontication. In order to make these changes effective you have to restart the SQL Server service for your server instance.

Can not delete the mdf and ldf files after detaching

I saw several people have reported this issue in forums. The probelm is when you detach a database from SQL Server and then try to delete the mdf and ldf files you get the following error
"Make sure the disk is not full or write protected and that the file is not currently in use"

So the error message is can be misleading as you tend to think it is a space issue, well it can be sometimes but most of the times it is not.

First you much check whether the file is actually in use by SQL Server. You can run below command to get that information

SELECT * FROM sys.Master_Files WHERE physical_name = 'Path To Your File'

 If there are no records as I have seen in several cases, the issue's root cause is file ownership is not assigned for the logged in user. As I have seen most of the cases files owned by the SQL Server service account as well as the user account that restored/ created the database.

To fix this you have to get the ownership of the file. You can do this by right clicking the file go to properties and then going to security tab. On the security tab click on advanced and then go to owner tab. There you can change the ownership either to your logged in account or to a group which you have rights to.

This should solve your issue and then you can delete the files. Please drop a comment if you come across any issues, I am always here to help.