Interview Question of the Week #034 – What is the Difference Between Distinct and Group By

Question: What is the difference between DISTINCT and GROUP BY?

Answer:

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates, then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Example of DISTINCT:

SELECT DISTINCT Employee, Rank
FROM Employees

Example of GROUP BY:

SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Example of GROUP BY with aggregate function:

SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Remove All Characters From a String Using T-SQL

In this post, How to Remove All Characters From a String Using T-SQL? we have seen How to Remove All Characters From a String Using T-SQL. But it only removes characters from the string. What if the string has some other special characters? Here is the method that handles this

SET NOCOUNT ON
DECLARE
@loop INT
DECLARE
@str VARCHAR(8000), @output VARCHAR(8000)
SELECT @str = 'ab123ce23,4f$e', @output=''
SET @loop = 1
WHILE @loop < LEN(@str)
BEGIN
SET
@output=@output+CASE WHEN ASCII(SUBSTRING(@str,@loop,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@str,@loop,1) ELSE '' END
SET
@loop = @loop + 1
END
SELECT
@output

Now here is the result:

123234

The idea here is instead of finding and removing non numerics, find only numbers and concatenate them. You can use if the ASCII value of a string is between 48 and 57 (which is a digit from 0 to 9). If it is in the range, it is a digit then concatenate it. This way we can remove anything which is not a number.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Different Methods to Extract Scale Part From Decimal Number

One of my blog readers asked me “Is there a way to extract only the number after the decimal point?”. For example, in decimal number 45.90, 45 is the precision and 90 is the scale value.

There are several methods to get this done

Let us create this dataset

CREATE TABLE #Numbers (value DECIMAL(16, 3));
INSERT INTO #Numbers (value)
VALUES (100.00);
INSERT INTO #Numbers (value)
VALUES (- 23.89);
INSERT INTO #Numbers (value)
VALUES (390.077);
INSERT INTO #Numbers (value)
VALUES (12.87);
INSERT INTO #Numbers (value)
VALUES (390.1);

Method 1 : truncate scale part and subtract it from the original number

SELECT VALUE , ABS(VALUE-CAST(VALUE AS INT)) AS DECIMAL_ONLY FROM #NUMBERS

Method 2 : Convert the number into a string and get the number after the decimal point

SELECT VALUE , SUBSTRING(STR(VALUE,16,3),CHARINDEX('.',STR(VALUE,16,3))+1,LEN(VALUE)) AS DECIMAL_ONLY FROM #NUMBERS

Note that str function is a string representation of a number. The second parameter 3 restrict the number to have maximum of 3 digits and identifying the position of a dot, we will be able to extract only scale part

Method 3 : Use pARSENAME function

SELECT VALUE , PARSENAME(VALUE,1) AS DECIMAL_ONLY FROM #NUMBERS

Parsename split the data based on the dot and parameter value 1 is used to extract the right most part which is scale in the decimal number

The result of the able queries are

decimal SQL SERVER   Different Methods to Extract Scale Part From Decimal Number

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Are Power Options Slowing You Down? – Notes from the Field #095

[Note from Pinal]: This is a 95th episode of Notes from the Fields series. When it is about tuning SQL Server, we always look at the configuration of the SQL Server. However, there are few settings of the operating system can also impact the performance of the SQL Server. I recently asked John Sterrett a very simple question – “Which is the one option from the OS, would you check first when you are tuning SQL Server?”

JohnSterrett SQL SERVER   Are Power Options Slowing You Down?   Notes from the Field #095

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very interesting story about how a simple setting of the OS impacts performance of SQL Server. Read the experience of John in his own words.


Doing several SQL Server health checks I have noticed that the operating system settings can slow you down. Today, we are going to focus on power options. By default many companies have the balanced power plan as the default configured option.  You will see that it is also the recommended setting. This is recommended to save power usage which reduces which reduces the amount of money required to power your servers.  Please keep in mind that your virtualization software or BIOS might also have Power Option settings that should be verified and adjusted. Today, we are focusing on the Windows Operating System.

notes 95 SQL SERVER   Are Power Options Slowing You Down?   Notes from the Field #095

The balanced power option is great for most servers where CPU power is not mission critical to the applications living on your server. SQL Server is an exception and you will want to get as much as you can out of your CPU Power.  In fact Enterprise edition licensing is now licensed by core so you want to make sure you are getting the most out of your SQL Server licensing.

How Do We Script Out The Change?

While you can load the power option GUI and manually change the setting I like to automate as much as possible.  The following script could be executed via PowerShell or Command Prompt to make sure High Performance power option is enabled.

Powercfg -SETACTIVE SCHEME_MIN

Conclusion

SQL Server places a different set of demands on the operating system. OS default settings are not always optimal and should be reviewed.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script: Finding queries without JOIN Predicates

I have been in developer’s shoes for close to 5+ years before reinventing myself to do things that made me even better in coding. It is a tough challenge these days being a developer. The life around you and the technology is changing so rapidly that we seldom get time to take a pause from our busy schedule and learn something that has come new. This constant struggle to build new competencies, read documentations of newly released products is something I do religiously.

This blog can never come this far if I had not been doing this till date. The more I read and learn, the opportunities to share become better. To carry a baggage of legacy from time to time is inevitable and I see folks in the industry hold onto some of the practices even without knowing why they are doing so.

I am writing this code blog below because during a code review at one of the software companies, I saw a code that was not adhering to ANSI standards. I quickly jumped onto it and asked – why? The developer gave me the usual long answer of no time, release pressures and lastly – what is wrong, it works right?

I have respect for such pressures as you build your product, but writing a code block that performs and is easier to maintain is one of the critical step. After I explaining, I showed the warning in the execution plan that shows we have done something that can cause poor performance.

I quickly came up with this script to help the team identify all the queries that were written in this fashion and requested them to change the same at the earliest.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  st.TEXT,
qp.query_plan
FROM    (
SELECT  TOP 50 *
FROM    sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes( '//p:RelOp/p:Warnings[(@NoJoinPredicate[.="1"])]' ) AS q(n) ;

The warning is because, poorly performing queries are throwing warnings that there are operators without join predicates. That means somewhere in your query, you’ve logically written a CROSS JOIN without thinking about it. Maybe you are using an old style join syntax (SELECT * FROM a, b, c) or maybe you really did intend to write this code. Either way, a lot of rows can be produced as a result of this operation. It’s best to make sure this was your intent.

The only way out here is to re-write your query so you are always applying a join predicate. This is a script blog. If you run this in your environment, do you get anything? Find out and keep me posted.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Database Mirroring login attempt failed – Connection handshake failed. An OS call failed: (8009030c)

The best way I have learnt about building SQL Server AlwaysOn Availability Group is by contacting my close friends who have written the only book on this topic. It is not common that I ping them for such technical queries but that is what friendship is made off. Recently, while preparing a demo for AlwaysOn Availability Group, I ran into interesting issue and was able to fix it as well.

First, let us look at the configuration.

SERVER1              –              Primary Replica
SERVER2              –              Secondary Replica

They are synchronous and configured for automatic failover. I have shutdown SERVER1 and as expected, SERVER2 became primary. As soon as SERVER1 came back, it became secondary but databases were NOT synchronizing. That was a surprise to me as I was expecting to see the data movement from SERVER2 to SERVER1 now.

As always, my troubleshooting checklist has ERRORLOG is my first point of investigation. If you have not seen earlier, you can use below blog to know how to find ERRORLOG

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

On SERVER1, I found below messages multiple times.

2015-08-24 01:24:18.480 Logon        Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(The logon attempt failed). State 67.'.  [CLIENT: 10.0.0.2]

There are some interesting learning/finding from above messages.

  1. Database Mirroring and AlwaysOn uses same endpoint and that is why we are seeing Database Mirroring in error message even if I am using AlwaysOn Availability Group.
  2. IP Address in the message belong to SERVER2 (which is primary as of now).

This means that SERVER2 is not able to talk to SERVER1 via Mirroring port. I searched on hex code 8009030c is not helping much because it means – The logon attempt failed, which is already there is the same message.

Steps which I tried after searching on internet (and they didn’t help)

  1. I logged in to SERVER2 via remote desktop and tried making connection to SERVER1 via Management Studio and it was working fine.
  2. Restart endpoints on both replicas. No joy!

USE MASTER
GO
ALTER ENDPOINT hadr_endpoint STATE = STOPPED;
GO
ALTER ENDPOINT hadr_endpoint STATE = STARTED;
GO

  1. PING from and to each other. This also worked well which means DNS was not an issue.
  2. Verified that SQL was running under domain account and account was not locked.

None of the above helped. Taking last hint, Then I started backtracking to recall what I did with my SQL Service account. It did not take long to realize that I had change service account domain password in recent past. I rarely use AlwaysOn Availability Group so I changed the service account password on SERVER1 but forgot on SERVER2.

When I was looking at errorlog, I also found below

SQL Server failed to communicate with filter daemon launch service  (Windows error: The service did not start due to a logon failure.). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

Above message came when SQL was trying to start full-text service automatically. Since I didn’t change the password for full-text service, I was seeing above message.

Solution (which worked for me): Correct the Service account password for SQL Server related service via SQL Server Configuration Manager.

handshake 01 SQL SERVER   Database Mirroring login attempt failed   Connection handshake failed. An OS call failed: (8009030c)

handshake 02 SQL SERVER   Database Mirroring login attempt failed   Connection handshake failed. An OS call failed: (8009030c)

When I corrected service account password, the databases were synchronized and I was happy again.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix @@ServerName Property Value When Incorrect

Our software systems are funny and sometimes give us strange values. I don’t quite venture out trying to fiddle around with system level properties on my servers, but having said that, this query had come from one of my colleagues who was working on his SQL Server instance and he found something strange.

When working on the server and when an error occurs, I generally ask for the @@Version. This is a standard practice for me to do it because I don’t have to ask about the version installed, what service pack, CU update has been done on the server etc. This output would give me all this information in black and white.

error SQL SERVER   Fix @@ServerName Property Value When IncorrectAs I was so used to this kind of output, my friend said that the value that is returned by his @@Servername was NULL. This was such a surprise and I wanted to see it in real life. He was kind enough to show me via Skype that this was indeed the case. This got me thinking and I needed to solve this trivial problem. I had gone to bed early as it was a lazy Sunday and I couldn’t put myself to sleep as this was plaguing my mind – I needed a solution. Finally, after an hour and getting some help from my SQL buddies, I found the deal to crack this problem. Strange but I am sure many of you have encountered this somewhere and were looking for a solution.

The @@ServerName refers to the local server that SQL Server is running on.  This property is set during installation, but there are instances where @@ServerName may be NULL or may not be correct.

Running the following will show the current TSQL value for this property:

SELECT @@SERVERNAME;
GO

To Fix the Problem

If the @@ServerName property is NULL, running the following will fix the issue- but only after you restart the SQL Server instance. Yep, fixing this requires an outage.

EXEC sp_addserver '<LocalServerName>', local;
GO

If the @@ServerName property is incorrect, run the following to correct the issue:

EXEC sp_dropserver 'old_name';
GO
EXEC sp_addserver 'new_name', 'local';
GO

Though the solution to this was so simple, I did find it strange about this behavior. I am yet to come to terms this can every happen. If you ever encountered this error on your server, can you please let us know how you landed to this situation. I am eager to learn the cause for this rather than just looking at solution to the problem.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #033 – How to Invalidate Procedure Cache of SQL Server?

icon clean Interview Question of the Week #033   How to Invalidate Procedure Cache of SQL Server?Question: How to invalidate all the stored procedure plans in SQL Server?

Answer: DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Remember not to run this on a production server without proper planning as once you run this DBCC command it will invalidate all the stored procedure plans. This means if the user runs this during pick time, many Stored Procedures will execute at the same time and create a resource bottleneck.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – 100 Million Views and Onwards

I will always remember August 20, 2015 as a landmark milestone for SQLAuthority.com.

100m sky SQL Authority News   100 Million Views and Onwards

On August 20th, this blog crossed 100 Million Views. When I started this journey on November 1, 2006, I had no idea that there will be over 100 Million Views on this blog. I am indeed very proud and happy that my humble attempt of writing my experience online has been appreciated by YOU!

No matter how much I want to thank you, I seriously have no words to express. I am happy that you accepted SQLAuthority.com and showered your love.

100m SQL Authority News   100 Million Views and Onwards

Thank you! Seriously, Thank YOU!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – DBCC CHECKDB errors – Msg 2520 during executions

Some of the simplest recommendations can lead you to some bizarre output that we never anticipated. I always recommend running DBCC CHECKDB on mission critical databases because we need to be 200% sure that the database disk system is healthy from time to time. During one of my sessions, I gave this as a generic recommendation and guess what, I got a strange message from one of the attendees. They then claimed that they were getting errors of mag 2520. One further probing and requesting for the actual error, I understood the problem. They were running CHECKDB on the ResourceDB. I asked – “Why are you doing this on ResourceDB?”. They answered, “Pinal, isn’t ResourceDB a database? Wouldn’t it not get corrupt? What is wrong in doing a CHECKDB on it? If I cant, how can I do it?”

This was a fully loaded question and I had to answer it elaborately. So I took this blog post to explain some of the basics.

Resource database is a system database in SQL Server 2005. Since this database has some special attributes and differs from regular databases in some respects, these indirectly affect the ability to do some operations on this database. One of the areas is the ability to perform DBCC checks and repair on the resource database. The following are the key points to keep in mind when you plan to run any kind of check against the resource database:

When the server is running in multi-user mode, running the command
DBCC CHECKDB ( mssqlsystemresource )

will give the following output:

Msg 2520, Level 16, State 11, Line 1
Could not find database ‘mssqlsystemresource‘. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view.

This is applicable for commands like DBCC CHECKALLOC and DBCC CHECKCATALOG.

When the server is running in multi-user mode, running DBCC CHECKDB on the master database will run DBCC CHECKDB on resource database as well. In the DBCC CHECKDB output result set, we will notice the following sections:

DBCC CHECKDB ( MASTER )

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘master’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
CHECKDB found 0 allocation errors and 0 consistency errors in database ‘mssqlsystemresource’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Errorlog will show the following entries:

2015-08-25 14:44:22.650                spid63   DBCC CHECKDB (master) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 000000e4:0000017c:0001 and first LSN = 000000e4:00000179:0001.
2015-08-25 14:44:22.840                spid63   DBCC CHECKDB (mssqlsystemresource) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

When the above checks are run, for the master database checks alone the internal transient replica is created. For resource database, no replica is created for the checks.

When the server is started in single_user mode to perform repairs in master, a special mechanism is used. Only for master database, the check command is run with the repair option. When it comes to checking the resource database, the normal check is done with repair disabled. You can see the effect of that from the errorlog entries:

2015-08-25 15:44:22.650 spid61      DBCC CHECKDB (master, repair_allow_data_loss) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2015-08-25 15:44:22.650 spid61      DBCC CHECKDB (mssqlsystemresource) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.

If there is a corruption problem in the resource database and there is a need to get it repaired for the efficient functioning of the SQL Server, then you will need to replace the resource database if there is any physical corruption introduced in the database. Normally users will not have the ability to write to this database. So unless there is a hardware problem, there is less chance that this database should get corrupted ever.

Though this blog started for an unusual error message, I thought it was important to talk some of these fine prints when working with ResourceDB. Hope you have never encountered any error with ResourceDB, if you have – do let me know via comments to what you did in that situation. Would love to learn from you.

Reference: Pinal Dave (http://blog.sqlauthority.com)