SQL SERVER – Live Plans for Long Running Queries

If you are a SQL Server DBA or developer who is majorly dealing with queries which are running for a long time, you may have one request every time you see a long running query. The request is how to see a plan for a query when it is still running. For query before you execute it, you can see the estimated execution plan and after you executed you can see the actual execution plan – however, how to see a query plan while the query is still being executed. This is a very interesting question.

Well with SQL Server 2014 we have now a new DMV, which tracks execution stats at each operator level while the query is still running. The dmv for the same is sys.dm_exec_query_profiles.

First execute any long running query. In this example, I will build a test table.

-- Create a table to host data
CREATE TABLE Customers (ID INT, Name CHAR(2000) DEFAULT 'SQLAuthority')
GO
CREATE CLUSTERED INDEX CCI_Customers ON Customers(ID)
GO

Right after that, I will populate it with random data in loop.

-- This can take upto a minute or two based on your system.
DECLARE @loop  INT = 1
WHILE @loop <= 100000
BEGIN
INSERT INTO
Customers (ID) VALUES (@loop)
SET @loop = @loop + 1
END
GO 3

Now I will enable actual execution plan for the query. You can enable that with CTRL + M. If you forget this step, you will be not able to see the execution plan in the next script.

Next, execute sample query which will take long time to execute.

SELECT *
FROM Customers c
INNER JOIN  Customers c1 ON c1.ID = c.ID
WHERE c.ID > 50
GO 

Now I will run DMV, which will show me live plans at operator level for the query.

-- Open this on a different session and run it.
SELECT physical_operator_name, row_count, estimate_row_count, session_id
FROM sys.dm_exec_query_profiles

Well, that’s it. It is that simple. You can see various operators and its plan in the next image. This is very handy, when your query is going to take long time to execute.

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

About these ads

SQL SERVER – How to Know Cardinality of Executed Query?

Earlier I wrote three blog posts on SQL Server 2014 Cardinality. Read them over here to get the context of this blog post.

I explained in the last blog post that you can execute query in different cardinality even though your database has different compatibility. One of the questions which I have received right after the blog post was how does the user know what is the compatibility of the query which is just executed. It is indeed a very good question. There are multiple ways to know the cardinality of the query just executed. Here are two ways to do the same.

1) Execution Plan Properties

I enabled execution plan (CTRL+M) when executing queries and right after that I selected one of the operators of the execution plan. Right after that I right clicked and selected properties of the execution plan. You can also enable the properties with shortcut key F4 as well. It will display the properties window on the right side in SSMS.

Over here see the property CardinalityEstimationModelVersion. If the value of this property is 70 it is of previous version. If the value is 120 it means cardinality estimator algorithm is of latest SQL Server version.

 2) XML Execution Plan

This method also requires you to enable execution plan with (CTRL+M). Once the execution plan is enabled right click over the execution plan and click on the option Show XML execution plan. Once this option is clicked it will open a new window with XML plan.

In the XML execution plan, look for the property CardinalityEstimationModelVersion. If the value of this property is 70 it is of previous version. If the value is 120 it means cardinality estimator algorithm is of latest SQL Server version.

Well, it is very simple to know what is the cardinality of query execution.

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

SQL Authority News – 3000th Blog Posts and Thank You

3000th Blog Posts! 

When I realized that I am writing my 3000th blog post (list of all of my blog posts), I just got blank with happiness. I could not come up with what exactly to write. It has been a long time since I have stopped writing about milestones. I believe writing about milestone is not helpful to users, it is just plain bragging about oneself. However, when I reach 3000th blog post. I am just stunned and not sure what to talk about. I will now answer three questions which I have received about this blog in the last month, maybe that will give some insight in my blog post.

Q: How can you write every single day from last 7 years?
A: It is about to be 8 years soon. I just enjoy it. Every day, I write one blog post before I start my daily routine. May be this discipline has helped me. I have talked about secrets of this in my latest Play by Play: Pinal Dave  Course.

Q: Do you have a day job or you just do blogging?
A: Yea, I have a day job where I go to the office, work for over 8 years, take a lunch break and have amazing people to work with. I work for Pluralsight as an Evangelist. However, blogging is my passion and heart beat. That is why I have recently written a book on Secret Tool Box of Successful Bloggers: 52 Tips to Build a High Traffic Top Ranking Blog and it is doing quite well.

Q: I want to learn more about blogging, is there any other resource besides your book do you suggest?
A: Well, I suggest you read my book as that is the latest additional to my library. It is 12th book I have authored in last 3 years and a very first non-technical book. If you do not want to read books, you can watch my Pluralsight courses on Building a Successful Blog and Building a High Traffic, Profitable Blog. Both of the courses are very unique in nature and gives perspective in my methodology in blogging.

Bonus Q: If you have a day job and you spend so much time in writing books as well as authoring video courses – when do you sleep?
A: Well, let me quote my favorite author Robert Frost.

The woods are lovely, dark and deep,   
But I have promises to keep,   
And miles to go before I sleep,   
And miles to go before I sleep.

I am just humbled and thank you to all of you. I really mean it.

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

SQL SERVER – CTRL + R Shortcut Does Not Work in SSMS

I just formatted my computer and installed fresh SQL Server 2014. The very first thing which I did was to open SQL Server Management Studio and execute a sample query. As soon as I executed the query, it returned me some result. Once I did not need the result, my next instinct was to hide the result as I did not need it any further. I am very used to the keyboard shortcut CTRL + R to hide the result. As soon as I typed in the CTRL + R the result pane was still visible. My pressing CTRL+R did not do anything further.

I suddenly remembered that last time when I had installed SQL Server 2012 I had faced the similar situation when a few of my shortcuts did not work. Here is what I did to make my shortcut work again.

Steps to Get Shortcut working again:

  1.  Go to Tools Menu in SSSMS
  2. Click on Option
  3. Go to Keyboard and expand the menu
  4. Click on option Keyboard again
  5. Keep the option on the drop down – Apply the following additional keyboard mapping scheme as (Default)
  6. Click on Reset

and you are done!

Well, on the internet, there are many different solutions and many of them are valid in different scenarios. In my scenario following above steps had fixed my issue.

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

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype – Part 2

Seven years ago, I wrote a blog post about – Query to Find Seed Values, Increment Values and Current Identity Column value of the table. It is quite a popular blog post and lots of people like it as it gives immediate details about Identity Values. SQL expert Mark Hickin has further improved this query and posted an interesting modification where the query also display upper limits of the data type used in the query. You can read the blog post over Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype.

However, this query has a small limitation that it only works in those cases when increment value is 1 and seed is 1. However, if you have any other value as identity or seed, you will need a bit more modification in the script. SQL Server Expert and Guru Harsh has provided amazing script where he has provided query with the said adjustment.

SELECT Seed,Increment,CurrentIdentity,TABLE_NAME,DataType,MaxPosValue ,
FLOOR((MaxPosValue -CurrentIdentity)/Increment) AS Remaining,
100-100*((CurrentIdentity-Seed)/Increment+1) / FLOOR((MaxPosValue - Seed) /Increment+1) AS PercentUnAllocated
FROM (
SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed ,
IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment ,
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS CurrentIdentity ,
TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME ,
UPPER(c.DATA_TYPE) AS DataType ,
FLOOR(t.MaxPosValue/IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME)) * IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS MaxPosValue
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) - 1) AS MaxPosValue
FROM sys.types
WHERE name LIKE '%Int'
) t ON c.DATA_TYPE = t.Data_Type
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'IsIdentity') = 1
)T1
ORDER BY PercentUnAllocated ASC

Here is the screenshot of the image. Thanks Harsh- a very nice query.

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

SQL SERVER – Detecting Corruption with Suspect Pages Table – Notes from the Field #043

[Note from Pinal]: This is a new episode of Notes from the Fields series. I often hear about database corruption and I like to stay away from the problems related to corruption. When database gets corrupted, there are not enough expert out in the world who can fix it properly to its original state. John who is my good friend explains a very interesting situation in this blog post where we can learn about corruption with the help of suspect pages table. It is indeed a great tip for everyone to learn even though your database is not corrupted.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces related to Detecting Corruption with Suspect Pages Table. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


When I get asked to review a database server instance, a very important check that I perform is to review if corruption has occurred with the existing databases. In today’s tip from the field we are going to go over a simple query that will identify existing corrupted pages without running DBCC CHECKDB.

In SQL Server you will not be notified of corruption until an attempt to read a corrupt page occurs. Therefore a page could have been corrupted weeks ago and no one would have noticed if the page was never accessed since the corruption occurred. We strongly recommend using an automated schedule to check database integrity by running DBCC CHECKDB.

DBCC CHECKDB is a very I/O intensive operation so you shouldn’t run the command at will. It should be executed during defined maintenance windows. Today, you are going to see that we can track down corrupt pages with the query below.

SELECT * FROM msdb.dbo.suspect_pages

Ideally, the result set will return zero rows. Zero rows means that no pages have been identified as being corrupt. It doesn’t guarantee that you don’t have corrupt pages. It just means no corrupt pages have been accessed. If you have results then you just identified pages within database files that have a history of corruption. The error count will let you know how many times the page(s) have been accessed since it was added to the suspect pages table. The event type column will identify the type of corruption.

NOTE: The suspect_pages table in msdb database will only keep 1,000 pages. Hopefully you will not have any rows yet alone more than 1,000 corrupt pages. If you do have more than 1,000 than you would only see the last 1,000 pages in this table.

Keep in mind that the suspect_pages table will only include marked corrupted pages. Corrupt pages are only classified as marked if they have been accessed since the corruption occurred. This is why you need to include DBCC CHECKDB in your maintenance plans.

CALL TO ACTION:

  1. Be proactive and monitor suspect_pages table in msdb database
  2. Implement DBCC CHECKDB in your maintenance plans.

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 – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype

Seven years ago, I wrote a blog post about – Query to Find Seed Values, Increment Values and Current Identity Column value of the table. It is quite a popular blog post and lots of people like it as it gives immediate details about Identity Values. SQL expert Mark Hickin has further improved this query and posted an interesting modification where the query also display upper limits of the data type used in the query.

SELECT IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed ,
IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment ,
IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS CurrentIdentity ,
TABLE_SCHEMA + '.' + TABLE_NAME ,
UPPER(c.DATA_TYPE) AS DataType ,
t.MaxPosValue,
t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Remaining,
((
t.MaxPosValue -IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME))/t.MaxPosValue) *100 AS PercentUnAllocated
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) - 1) AS MaxPosValue
FROM sys.types
WHERE name LIKE '%Int'
) t ON c.DATA_TYPE = t.Data_Type
WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME,
'IsIdentity') = 1
ORDER BY PercentUnAllocated ASC

Here is the screenshot of the image. Thanks Mark – a very nice query.

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