SQL SERVER – How to Suppress SQL Server messages in Application Event Log

Error messages are a great source of help when it comes to troubleshooting. What can I say, this is maybe be the most repeated phrase from me in this blog about error messages. The messages have grown over ages and got better with the SQL Server dev team putting in extra effort in every release. Having said that, I thought of adding something interesting in this blog.

Logging of errors in ERRORLOG is helpful in many situations. It has helped me many time to fix issues. Recently I came across an email where there was a strange behavior reported. Here in the email from my friend.

Hi Pinal,
We are using 3rd party software which uses SQL Server as backend. We also have monitoring software which keeps track of messages in Event log and sends alert to Wintel team.
Recently there was a brute force attach for sa password on that server so there were many login failure attempts like below:

2015-11-05 20:14:47.040 Logon        Error: 18456, Severity: 14, State: 8.

2015-11-05 20:14:47.040 Logon        Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT:]

There messages were logged many time and IP address listed there is some machine which we don’t know. We were able to stop the connection by firewall.

Here is the problem: Monitoring software didn’t report anything because strangely these messages were NOT logged in application event log at all.

Do you know how that can happen?
Thanks in advance.

Whenever I get such questions, I always asked for ERRORLOG.

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

I looked into ERRORLOG file and found something interesting as below.

Registry startup parameters:

-d D:\MSSQL12.SQL2014\MSSQL\DATA\master.mdf
-l D:\MSSQL12.SQL2014\MSSQL\DATA\mastlog.ldf

Command Line Startup Parameters:

-s "SQL2014"

We can see a non-default startup parameter called “n”. I searched MSDN documentation and found its usage. “Does not use the Windows application log to record SQL Server events”

So I replied to my friend and later it was identified that 3rd party tool has done that change for that instance.

Mystery solved and I learned something new.  Have you ever used any such non-default startup parameter in production server?

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

SQL SERVER – How to Change SQL Server Product Key or License Key?

I try to refrain from talking about licensing related topics because they seem to be complicated and have tons of permutations and combinations. Having said that, in the recent past, I was baffled to see this query land to my Inbox which was interesting and needed some attention from me. I could have easily put it under the rug and forget but this kept haunting me for a long time. So I decided to check with a couple of senior DBA’s who manage 1000’s of servers and how they manage doing something like this. So I called up, learnt a few tips around licensing and this is what I have assimilated from the discussion. Though this discussion and facts stand true as on date of publication of this blog, some of the nuances might change maybe in the future. That might make some of the licensing terms void. But the process to change license key will always be something useful to you in my opinion.

SQL Server can be purchased via various channels like MSDN, Volume License, Full Packaged Product (FPP) or OEM. Sometimes it may happen that:

  • You have installed SQL Server from MSDN media and later you purchased media via Volume License channel and you want to update the license key used by SQL Server.
  • You have an MSSQL install that was purchased by one department, but now a new department wants to take over the install and want to use their own license.

The steps to update product key is simple. Actually, these are the same steps which are required to upgrade the edition of SQL Server. While changing product key, there is no “real” edition upgrade.

  1. Run setup.exe from the media.


If you have SQL already installed, then you can also launch “Installation Center” by going to Start->Programs ->Microsoft SQL Server 2008[R2]->Configuration Tools-> SQL Server Installation Center[(64-bit)].

  1. You would get below screen titled “SQL Server Installation Center”
  2. Go to “Maintenance” and click on “Edition Upgrade” as shown below
    PID Key 01 SQL SERVER   How to Change SQL Server Product Key or License Key?
  3. Go through the wizard and make sure that new key is entered in below screen.

PID Key 02 SQL SERVER   How to Change SQL Server Product Key or License Key?

If you have a requirement to do this on multiple server and going through the setup wizard is not an option, then you can use below command line also.

For Default Instance:


For Named Instance:

Setup.exe /q /ACTION=EditionUpgrade /INSTANCENAME=InstanceName /PID=<PID key for new edition>” /IACCEPTSQLSERVERLICENSETERMS

Have you ever changed product key? What have you done in the past? What techniques have you used to perform this task? Do let me know about your experiences too.

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

MySQL – Different Methods to Know MySQL Version

In SQL Server, to know the current version of the server, you can use system variable @@VERSION

How many of you know that the same can be used in mysql to know the version?

Also there are many ways to know the version of MySQL

Method 1:

Select @@version as version

It will display the current version of the MySQL Server

There is another function named version()

Method 2:

Select version() as version

The above produces the same result as @@version

You can also use another method to know this

Method 3:

Show variables like '%version%'

The above returns a resultset with two columns variable_name and value. You can find the version where variable_name says version.

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

SQL SERVER – What is the OVER Clause? – Notes from the Field #101

Kathi SQL SERVER   What is the OVER Clause?   Notes from the Field #101[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about T-SQL Over clause. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively interesting topic for database experts. Read the experience of  Kathi in her own words.

The OVER clause was added to SQL Server in 2005. Yes, it’s been around for a decade, but most SQL Server professionals know very little about all the ways it can be used. Whenever you add a T-SQL window (also called windowed or windowing) function such as ROW_NUMBER to your query, you must use the OVER clause. Getting the OVER clause right is critical to getting the correct results. Window functions require the OVER clause, but there is one other situation where the OVER clause is used: with the sequence object. In this article, I’m going to stick just with the ways the OVER clause is used with window functions.

The OVER clause has three components: partitioning, ordering, and framing. Partitioning is always supported, but support for ordering and framing depends on which type of window function you are using.

The partitioning component, expressed as PARTITION BY, is optional and is supported for all types of window functions. Partitioning divides up the rows. It’s kind of like a physical window that is divided up into panes. The entire window is a window. The panes are also windows. Say that I partition by CustomerID. The partition for CustomerID 1000 is restricted to rows for CustomerID 1000.  For the calculation on any given row, the window consists of rows from that partition only. This means that the ROW_NUMBER function restarts the row numbers for each partition, for example. You can specify more than one column for PARTITION BY as long as you separate the columns with commas. Partitioning is not the same as grouping with the GROUP BY clause. When you use the GROUP BY clause, only one row is returned for each group. Partitioning is just a way to divide the data in order to perform the calculations; the detail columns are returned. Figure 1 shows an example.

101 1 SQL SERVER   What is the OVER Clause?   Notes from the Field #101

Figure 1: The effects of PARTITION BY

Ordering within the OVER clause is supported when the data must be sorted to perform the calculation. For example, ROW_NUMBER requires an ORDER BY expression within the OVER clause because the rows must be lined up. Actually, most of the window functions support ORDER BY.  ORDER BY in the OVER clause is not supported for calculating subtotals, for example. You don’t need the data sorted to calculate a sum. Note that the ORDER BY within the OVER clause has nothing to do with an ORDER BY clause found in the query itself. Just like partitioning, you can specify a comma delimited list of columns. Figure 2 shows the results of ORDER BY in the OVER clause.

101 2 SQL SERVER   What is the OVER Clause?   Notes from the Field #101

Figure 2: Using ORDER BY

The third component of the OVER CLAUSE is called framing. Framing was introduced with SQL Server 2012 and is used in only a couple of situations: accumulating window aggregates (running totals, moving averages, etc.) and the functions FIRST_VALUE and LAST_VALUE. While partitioning is similar to window panes, framing is like a stained glass window. When performing the calculations, every row might see a different set of rows through the window. For example, when calculating a running total, row 1 sees row 1. Row 2 sees rows 1 and 2. Row 3 see rows 1, 2, and 3. The syntax for framing is a bit complex, so I am going to save it for my next post. By default, the frame consists of the first row of the sorted partition and all subsequent rows up to the current row.  Figure 3 is an example. The small arrows represent the current row where the calculation is being performed.

101 3 SQL SERVER   What is the OVER Clause?   Notes from the Field #101

Figure 3: The results of framing

To see the OVER clause in action, run this query against the AdventureWorks database:

SELECT CustomerID, SalesOrderID, TotalDue,
SUM(TotalDue) OVER() AS GrandTotal,
SUM(TotalDue) OVER(PARTITION BY CustomerID) AS SubTotal,
SUM(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS RunningTotal,
LAG(SalesOrderID) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS PrevOrder,
FROM Sales.SalesOrderHeader;

notes 82 3 SQL SERVER   What is the OVER Clause?   Notes from the Field #101Next time, I’ll provide a deep dive into framing. If you would like to learn more about T-SQL window functions, be sure to check out my latest book Expert T-SQL Window Functions in SQL Server or my Pluralsight course T-SQL Window Functions.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

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

SQL SERVER – How to take InMemory – OLTP FileGroup Offline?

InMemory topics are always interesting and a great learning experience. When I wrote the other post, it was more about how InMemory tables are loaded into memory even if they are not queried. The basics, here is to bring the data for In Memory Tables ahead to memory irrespective of their usage.

After this blog post was published, someone wrote me a mail asking if it is possible to take the InMemory Filegroups offline? And will the table’s data taken offline too? This question was obvious but still needed validation and I was curious to understand why this wouldn’t work in normal circumstances. The script to start working on this post has to start from previous post. Please create the database and then before the cleanup step – start to run the below scripts.

At this stage, your query must return some value for the below query:

FROM sys.dm_db_xtp_table_memory_stats

For all practical purposes, InMemory tables and filegroups are just like normal files. We should be able to take them offline using the same way we can do other filegroups.

-- Take InMemory FG offline

This should complete successfully and when we run the above DMV query to find the memory consumers, this must return no rows.

FROM sys.dm_db_xtp_table_memory_stats

Now that was pretty simple and easy to figure out. I would like to know if you have ever done anything like this in your systems?

Quick Quiz Question:

Now if taking the filegroup offline was easy, what about bringing the filegroup online next?


Simple isn’t it? No. You will encounter an error:

Msg 155, Level 15, State 1, Line 1
‘ONLINE’ is not a recognized CREATE/ALTER DATABASE option.

So the quiz question is, how can we bring the filegroup online in SQL Server 2014 version? What are the steps. Let me know via comments.

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

SQL SERVER – Basics ColumnStore FAQ – Part II

columnstore SQL SERVER   Basics ColumnStore FAQ   Part III wrote the first part of FAQ in here. This is not a typical blog post but more of a process oriented topic when working with ColumnStore Indexes. With the advancements done in SQL Server 2016, I know that this is going to be even more critical to understand some of the nuances of working with ColumnStore indexes inside SQL Server. So what will be target in this post? Here are few questions I asked and I got back in return which form the flow for this post.

ColumnStore index resulted in a performance regression

On first thought when I heard this from one of the tester, I was baffled to see this was the case. How can this happen. I went the route of asking some fundamental questions to get to the root cause. Here are those for a quick reference and the reason for it:

  • Did the optimizer use the ColumnStore index in the first place?

A user can determine whether a ColumnStore index was used in the query plan by looking at the Storage property in SHOWPLAN. Read about batch mode and row mode here.

  • Did the query start to execute using the columnstore index with batch processing and then fall back to row-based processing?

A user can determine that the query started to execute in batch mode then fell back to row mode by looking at EstimatedExecutionMode and ActualExecutionMode in Showplan. In most cases, even in row mode the columnstore index will provide better performance than the rowstore. If performance regression due to the columnstore is suspected, a query hint to force use of a different index can be used to prevent the query optimizer from selecting the columnstore. We discussed about some of the query hints that can be used in the previous post.

  • Did you insert a FORCE ORDER hint on a join?

If a FORCE ORDER hint is used, optimizer will obey the FORCE ORDER hint. If the optimizer would have used the starjoin optimization and created bitmaps, the loss of the starjoin optimization bitmaps could cause performance to be worse than without the FORCE ORDER hint.

Question: In queries processed in row mode, some filters are evaluated in the storage engine. Can the columnstore do that too for optimization reasons?

Yes, very much. Some filters that are pushed down into the storage engine are:

  • comparisons <column, comparison, constant or parameter>
  • IN lists <constant1 or parameter1, constant2 or parameter2 … constantN or parameterN>
  • filters on strings are NOT pushed down

Question: In queries processed in row mode, partitions can (sometimes) be eliminated from the scan. Can the columnstore do that too?

Yes, and no. Partition elimination per se does not occur, however segments (actually row groups – i.e. the corresponding segments for each column) can be eliminated from the scan. Each nonempty partition has one or more segments for each column. Eliminating each segment in a partition is equivalent to partition elimination. Eliminating a subset of segments in a partition for which some values in the partition qualify can be better than partition elimination (because that partition would not be eliminated). Each segment has metadata that includes the min and max values in the segment. Similar min/max metadata associated with bitmaps and filters can be used to determine that none of the values in a segment will qualify without scanning the segment. If a segment is eliminated from the scan, the other segments in the same rowgroup are also eliminated. Column_store_segment_eliminate extended event can be used to see if segments are being eliminated.

Question: Does partitioning work the same way for the columnstore index as for row store indexes?

Mostly, yes. A nonclustered columnstore index can be built on a partitioned table. An empty partition can be split. Two empty partitions can be merged. An empty partition can be merged with a nonempty partition. A partition can be switched between partitioned tables if both tables have a columnstore index and all the other requirements for switching partitions are met. There are some restrictions associated with columnstore indexes:

  • If a table has a columnstore indexes, two non-empty partitions cannot be merged
  • A nonclustered columnstore index must always be partition-aligned with the base table. As a corollary, the partition function used to partition the nonclustered columnstore index and the base table must be identical, not just equivalent. The reason for this requirement is that otherwise the partition function on the base table could be changed, resulting in non-alignment.

As I conclude for this post, I will try to create supporting posts with scripts to show how the above restrictions apply and how one can figure these when they are implementing ColumnStore Indexes in future posts. Out of curiosity, I would like to know how many of you literally use the ColumnStore Indexes in your environments? What has been your experience using the same?

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

SQLAuthority News – 9th Year Blog Anniversary

9th SQLAuthority News   9th Year Blog AnniversaryToday is an amazing day!

9 years ago on this day, I started to write this blog.

Since then I am producing one blog post a day everyday!

During this year there are so many changes have happened in the blog and my professional career as well as on my personal life. I have always shared my true feeling on this blog all the time.

Today, I feel like writing a lot of things, but I am pretty sure that readers are more interested in learning SQL rather than reading my monologue.

Here is my promise, I am going to continue my journey to SQL for the next year!

One more thing – THANK YOU for your love and support!

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

Interview Question of the Week #043 – What is the Difference Between EXCEPT operator vs. NOT IN

Question: What is the Difference Between EXCEPT operator vs. NOT IN

Answer: The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there are no matching rows in the right query. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that do not also return from the right query.

Example of EXCEPT operator is displayed along Example of NOT IN. If you run both of the Query and compare the Execution plan it is exactly the same. EXCEPT and NOT IN does same functions and have the same execution plan, but EXCEPT has much simpler syntax. The row-by-row comparison provided by EXCEPT, combined with the number of rows being returned remaining consistent, provides compelling evidence that re-factored query is correct. EXCEPT works with * as well as aliases.

Example: (Both of the scripts returns the same number of rows)
-- SQL SERVER 2005 Method
USE AdventureWorks;
FROM Production.Product
FROM Production.WorkOrder ;

-- SQL SERVER 2000 Method which works IN SQL SERVER 2005
USE AdventureWorks;
FROM Production.Product
FROM Production.WorkOrder);

Following the execution plan which indicates EXCEPT and NOT IN uses same execution plan. Click on below image to see larger image.
SQLSERVER EXCEPT ExecPlan small SQL SERVER 2005 Comparison EXCEPT operator vs. NOT IN

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

SQL SERVER – Adding Datetime and Time Values Using Variables

Datetime datatype stores date and time part. If you have only date value, You can add time part to a datetime variable using direct literal value and + operator.

It is shown below
SET @date=@date+'15:00:00'

The result is 2010-10-01 15:00:00.000
But it is not possible to add it using a time variable

DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+@time
SELECT @date

The error is

Msg 402, Level 16, State 1, Line 4
The data types datetime and time are incompatible in the add operator.

So the solution is to convert time datatype into datetime and add
DECLARE @date DATETIME, @time time
SET @date='2010-10-01'
SET @time='15:00:00'
SET @date=@date+CAST(@time AS DATETIME)

The result is 2010-10-01 15:00:00.000

So when adding data of datetime and time datatype values, you need to be aware of this.

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

SQL SERVER – Who is consuming CPU on my SQL Server?

The topics around performance tuning is always interesting and I get quizzed by a number of you during conferences on these topic areas post my presentation. A relatively simple yet a powerful question was asked by one of the attendee in my session at MODS (Mobile Developer Conference) this year. I was walking about databases in the web world and how one needs to preserve, protect and optimize for web based workloads. I was fortunate to have a packed crowd but couldn’t take all the questions on stage during the session. As it always happens, there is a mob that follows me outside and the session’s second innings happens there for close to an hour. These are conversations to remember and many bring back questions that I need to work on.

This blog got inspired by one such question one of the gentleman asked at this corridor conversation couple of weeks back. He said, they had a server which was consolidated from 10’s of servers into a monolithic big machine. The consolidation activity went well and everything was running absolutely fine till one fine day the server was not responsive. This resulted in a number of applications suffering to connect to SQL Server.

They were wondering if the consolidation exercise was worth all the pain on first place? When they were on separate servers they exactly knew which server / application was creating the problem and the fix was easy. Now they have about 60+ database on this one server and they are not able to find the cause for CPU spike for an elongated period of time.

So the question was – how can I find out what are the databases that consumed maximum time on my CPU relative to other databases? Do I know which are the consumers of my CPU on a given server? The initial instinct was to search SQLAuthority and I was surprised I haven’t written about this simple need. This is my first shot for this requirement using DMVs:

WITH DB_CPU_Statistics
(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS pa
GROUP BY DatabaseID)
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Statistics
WHERE DatabaseID <> 32767 -- ResourceDB

 SQL SERVER   Who is consuming CPU on my SQL Server?

The result for me on a freshly restarted SQL Server looks like this. Do let me know does this query make any sense on your actual environments? Did you ever need such a requirement in your troubleshooting of SQL Server? What have you been doing to get to this information in the past? Please let me know via your comments as it would be really helpful for all to know the other methods.

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