This is something I touched on back in 2017 a little after the Live Query Statistics feature was introduced with SQL 2016, but I was using the functionality this morning and felt like it was worth a reminder.
You can use Live Query Stats to check on the progress of an executing query – and you can do it through the GUI in SSMS.
I created a long running query for a demo, and after 15 minutes I was still waiting for it to finish. That was a bit longer than I intended. Should I kill it – or did I just need to wait a few more minutes for it to complete.
You can check this quickly via the Activity Monitor:
Find the query you are interested in in the processes list:
Right-click and select “Show Live Execution Plan”. That will show you something like this:
I can see from this that my query is about 83% complete, so maybe I’ll just wait a little longer. Note that this is a live view, so the numbers keep updating. If I want I can watch the progress.
This is against a SQL 2019 instance and is the out of the box behaviour. Before SQL 2019 you had to enable trace flag 7412 if you wanted this to work:
I think it’s appropriate to give a shout-out to Microsoft at this point, because over the last few releases they’ve given us some of the items that are top of my list.
Recommending and setting MAXDOP during setup (coming with SQL 2019) will hopefully mean I no longer have to have arguments about why the out-of the-box setting isn’t appropriate.
The same with setting max memory in the setup (also with SQL 2019).
A more verbose error where string or binary data might be truncated – we got that in SQL 2017.
It’s the little things like these that make me happy – and make my job easier.
A few other little things I’d like – though I accept that something small to describe isn’t always small in its execution…
A change to the default cost threshold for parallelism – be it 20, be it 30, be it 50. I’d prefer any of those to 5.
I think it would also be great to have a cardinality optimizer hint, e.g. OPTIMIZE FOR 5 ROWS. Oracle has this, and it’s not good having to feel jealous of those working on the dark side 😉 You can do the equivalent but it’s convoluted and not clear what’s going on when the uninitiated see the code:
There is one big thing I’d like – but it’s never going to happen. Get rid of Enterprise Edition – or rather, make Enterprise Edition the standard. Enterprise is comparatively so expensive, it’s rare I’m going to recommend it. It’s interesting to see that in Azure SQLDB we only have one edition to work with – I’d love to see that in the box product. I understand that change would be a massive revenue loss so can’t see it happening.
If not that though, if we could just have at-rest encryption (i.e. TDE) in the Standard Edition that would make me very happy. In these days of security and privacy consciousness it seems that should be a core functionality
Finally, I’d just like to upvote Brent’s idea that it would be great to be able to restore just a single table.
That all said, I’d like to go back to my first point. I love what MS is doing with SQL Server, and the continual improvements that are being made. I particularly love that we sometimes get them for free in service packs and cumulative updates – without having to upgrade to a new version.
There are so many options for running SQL Server now. It can feel a little intimidating.
You’ve spent years gaining experience with SQL running on Windows Server. On bare metal or in a VM. Locally or in a data centre.
Now people are talking about SQL on Linux. SQL on VMs in the cloud. SQL Server Platform as a Service (PaaS) offering such as Azure SQL DB, Azure Managed Instance, or Amazon RDS.
Running SQL in a container on Docker or Kubernetes.
It’s easy to feel like you’re at risk of getting left behind. Feel like you’ve got to start learning from the beginning all over again.
The good news is that whatever the flavour – it’s still SQL Server. For everything that’s different there are a hundred things that are still the same.
Database design, writing and executing queries and stored procedures – it’s still the same.
Query tuning, indexing, looking at execution plans. All the same.
Even administration, many of the core concepts are the same, understanding how security works, backups, high-availability. The main difference is often that some of these might be taken care of for you and you don’t need to worry about them any more.
Caveat – you still need to worry about them a bit!
The point is, most of what you already know, the experience you have gained over the years, is still totally valid. Learning about SQL Server on a new platform may feel like a big learning curve, but in reality, the new stuff you need to get to grips with is small compared to all the stuff you already know.
And in some cases, the skills you already have become even more valuable. People might not care if your query tuning on physical kit takes your CPU down from 50% to 10%. But tell them you’ve just reduced their cloud bill by 80% and they really care!
So don’t be intimidated, and don’t feel you need to learn every flavour. Have a play with SQL Server in the cloud, have a play with containers, set up SQL on Linux. You’ll quickly find it’s not that hard, and once it’s running – it’s pretty much the same as ever.
And remember, if someone comes to you with a question about why SQL is running slow, or why a query isn’t doing what they want – on RDS, Docker, Linux, or whatever. You don’t need to know that platform inside out to be able to help, you already know SQL Server and that’s the important bit.
To paraphrase a popular lyric :
If you’ve got SQL problems I can help you son. I’ve got 99 problems but SQL aint one.
One of the powerful aspects of Query Store is the ability to directly query the DMVs for details of historical executions and performance.
A key view for this is sys.query_store_runtime_stats (but also sys.query_store_runtime_stats_interval).
If you’re querying these views to look at what was happening during a particular time period then it’s important to understand that the dates and times are stored according to UTC time (which is equivalent to GMT with no adjustment for daylight savings times).
You can see this if you look at a few rows from the view:
SELECT runtime_stats_id, first_execution_time, last_execution_time
Though it will be more obvious to you if you’re in a time zone other than the UK.
The datetimes are stored as DATETIMEOFFSET which you can see from the +00:00 at the end of each entry. DATETIMEOFFSET allows you to store both the datetime as well as the timezone being used.
This means that if you’re querying against these columns you need to convert the values you are looking for to UTC first. You can do that my making sure you use GETUTCDATE() instead of GETDATE(), or if you are using specific local times you can use the AT TIME ZONE function e.g.
SELECT CAST('2019-08-21 11:50:40.400 +9:00' AS datetimeoffset) AT TIME ZONE('UTC');
I’m a big fan of storing all datetimes as the UTC version. It avoids things going out of sequence due to daylight saving changes – and can be helpful in mitigating problems when you have application users in multiple countries accessing the same database.
I’ll admit I might be biased though as – being based in the UK – UTC is the same as my local time half the year.
I had a server that looked like it had been suffering from memory contention. I wanted to see what queries were being run that had high memory requirements. The problem was that it wasn’t happening right now – I needed to be able to see what had happened over the last 24 hours.
Enter Query Store. In the run-time stats captured by Query Store are included details relating to memory.
I decided to use the column max_query_max_used_memory from sys.query_store_runtime_stats. In books online this is defined as:
Maximum memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval.
Here’s the script, it collates figures across all databases that have Query Store enabled and returns the top 50 queries with the highest memory grants. This is looking over the last 24 hours, but you can easily modify that to look at details for any interval you are interested in:
--Gather and report on most memory hungry queries
DECLARE @Reportinginterval int;
DECLARE @Database sysname;
DECLARE @StartDateText varchar(30);
DECLARE @TotalExecutions decimal(20,3);
DECLARE @TotalDuration decimal(20,3);
DECLARE @TotalCPU decimal(20,3);
DECLARE @TotalLogicalReads decimal(20,3);
DECLARE @SQL varchar(MAX);
--Set Reporting interval in days
SET @Reportinginterval = 1;
SET @StartDateText = CAST(DATEADD(DAY, -@Reportinginterval, GETUTCDATE()) AS varchar(30));
--Cursor to step through the databases
DECLARE curDatabases CURSOR FAST_FORWARD FOR
WHERE is_query_store_on = 1
AND state_desc = 'ONLINE';
--Temp table to store the results
DROP TABLE IF EXISTS #Stats;
CREATE TABLE #Stats (
SchemaName sysname NULL,
ObjectName sysname NULL,
FETCH NEXT FROM curDatabases INTO @Database;
--Loop through the datbases and gather the stats
WHILE @@FETCH_STATUS = 0
SET @SQL = '
USE [' + @Database + ']
INSERT INTO #Stats
s.name AS SchemaName,
o.name AS ObjectName,
SUBSTRING(t.query_sql_text,1,1000) AS QueryText,
(MAX(rs.max_query_max_used_memory)/128) AS MaxMemoryMB
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text t
ON q.query_text_id = t.query_text_id
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi
ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
LEFT JOIN sys.objects o
ON q.OBJECT_ID = o.OBJECT_ID
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE rsi.start_time > ''' + @StartDateText + '''
GROUP BY s.name, o.name, SUBSTRING(t.query_sql_text,1,1000)
FETCH NEXT FROM curDatabases INTO @Database;
SELECT TOP 50
WHERE QueryText not like 'INSERT INTO #Stats%' --Exclude current query
ORDER BY MaxMemoryGrantMB DESC;
DROP TABLE #Stats;
Last week a question came up about adding a column to a table, and giving that column a default constraint. Would that default value be assigned to all existing rows, and how much processing would be involved.
Unsurprisingly, the answer is that – “it depends”.
I’ve got a table with about a million rows that just has an identity column and a text column I’ve populated from sys.objects:
CREATE TABLE dbo.TestAddColumn (Id int IDENTITY(1,1), TextValue sysname); INSERT INTO dbo.TestAddColumn(TextValue) SELECT a.name FROM sys.objects a, sys.objects b, sys.objects c;
Let’s add a nullable bit column and give it a default value of Zero:
ALTER TABLE dbo.TestAddColumn ADD NewFlag bit NULL CONSTRAINT DF_TestAddColumn_NewFlag DEFAULT 0;
If I look at the table I can see that the new column contains Null values:
i.e. the default value that I’ve defined hasn’t been assigned for existing rows.
I remove the column and the default constraint:
ALTER TABLE dbo.TestAddColumn DROP CONSTRAINT DF_TestAddColumn_NewFlag; ALTER TABLE dbo.TestAddColumn DROP COLUMN NewFlag;
Now let’s add the same column but we’ll disallow Null values:
ALTER TABLE dbo.TestAddColumn ADD NewFlag bit NOT NULL CONSTRAINT DF_TestAddColumn_NewFlag DEFAULT 0;
If we check the table again:
This time we can see that the default value has been assigned.
So whether our default value gets assigned to existing rows depends on whether your column is nullable or not, a nullable column will retain Null as the value. A non-nullable column will get assigned the new default value.
If you want to override that behaviour, and have your default assigned even where the column is nullable, you can use the WITH VALUES statement. First I’ll remove the constraint and column then add it again with values:
ALTER TABLE dbo.TestAddColumn ADD NewFlag bit NULL CONSTRAINT DF_TestAddColumn_NewFlag DEFAULT 0 WITH VALUES;
We look at the data again:
You can see that the value has been assigned even though the column is Nullable.
One neat thing to note, is the performance impact when carrying out these actions.
Each time I added the column I captured the execution overhead using:
SET STATISTICS IO, TIME ON;
In all cases the resource usage measured was Zero. The Add Column operation was a meta-data only operation – no data in the table needed to be updated – even where the new column was assigned a value.
This was some clever jiggery-pokery added in SQL Server 2012 .
A few years back I started running regular SQL workshops in my workplace. Teaching beginners the basics of querying databases with SQL, as well as more advanced topics for the more advanced.
During one session we were discussing the issue of knowledge acquired being quickly lost when people didn’t get the chance to regularly practice what they’d learnt. One of the attendees suggested that I should be assigning them homework.
I could see from the faces of everyone else present that the word “homework” struck an unpleasant chord. Perhaps reminding them of school days struggling to get boring bookwork done when they’d rather be at relaxation or play.
Okay, so homework maybe wasn’t going to go down well, but I figured everyone likes a good puzzle. So every Friday I started creating and sharing a puzzle to be solved using SQL. This went on for the best part of a year, then other things got in the way and gradually I stopped.
This is my invitation to you this T-SQL Tuesday. Write a blog post combining puzzles and T-SQL. There’s quite a few ways you could approach this, so hopefully no-one needs be left out for lack of ideas:
Present a puzzle to be solved in SQL and challenge your readers to solve it.
Or give us a puzzle or quiz about SQL or databases.
Show the SQL solution to a classic puzzle or game.
Provide a method for solving a classic sort of querying puzzle people face.
Show how newer features in SQL can be used to solve old puzzles in new ways.
Tell us about a time you solved a problem or overcame a technical challenge that was a real puzzle.
Or just make your own interpretation of “puzzle” and go for it!
There’s some great stuff out there already. Itzik Ben-Gan’s done a bunch of them. There’s Kenneth Fisher’s crosswords. The SQL Server Central questions of the day. Pinal Dave’s SQL Puzzles. And there’s a few on my blog too if you take a look back:
This is a blog devoted to databases, but for once I’m going to go off topic and talk about something I did at the weekend that I’ve never done before. On Easter Sunday I went to London to join in a protest demanding action on climate change.
I’d heard about what Extinction Rebellion were doing for a while. Initially I was put off by the name which I felt made them sound a bit like an anti-capitalist movement and I felt that for us to effect change we need to embrace people from all sides of the political spectrum. I also wasn’t sure how I felt about the actions they were taking – blocking roads and traffic. Was that going to antagonise more people than it gathered support?
In the end though I figured they were at least doing something – and something needed to be done. I’d lost faith that governments were going to act in time to avert disaster. I decided I wanted to show my support, add one more person to the weight of people demanding action. I didn’t want to get arrested but felt that at least being there was something. From their website I discovered there were all sorts of ways to get involved.
I went with Lisa my fiancee, and Millie our dog.
We got the train up to London from Bristol on Sunday morning and headed to the legal protest site near Marble Arch. At first it was confusing. Who could we talk to about being involved? What could we do? We found an induction session that had just started and sat down to join in.
As the leaders of the session talked about their background, their ethos, their aims and methodology I was quickly impressed. They stressed that this was not just a socialist movement, that they recognised they needed to engage people from all walks of life and ideologies.
I was most impressed by the strong focus on non-violent, non-aggressive action. That in all interactions with the police or public, those involved should make sure they were peaceful and reasonable at all times, whatever was going on. That if anyone witnessed someone against going against that guiding principle they should either intervene or find someone else to do so, to suggest to the perpetrator that this was the wrong movement for them. And they managed to make this work, in all arrests and actions there wasn’t a single report of violence or wilful destruction.
I get that some people were frustrated by their actions and the disruption it caused, and I empathise with anyone affected, but they explained that they hadn’t undertaken their actions lightly. Hundreds of thousands of people had attended climate marches to little effect. They knew they needed to do something different and things were becoming more urgent. Research was undertaken looking at movements in the past that were succesful in achieving their aims and it was dicovered that the common theme was creating disruption in a peaceful manner. Only through the threat of continued disruption were authorities forced to take notice and engage with the movements in question.
Marble Arch is a busy place, with lots of people milling around or wandering to the nearby Hyde Park. Lisa and I decided we could best involve ourselves by chatting to people passing by, handing out flyers and explaining to people what was going on and why. Talking to people about the urgency of action and some of the dangers to us all if nothing happens.
We had a lot of nice interactions, and maybe even changed a couple of minds. Millie was a big hit as a protester (it was her first protest too) and a great draw to start a conversation. In particular the police loved her! That was another great thing, to see how good natured the police were through it all, smiling, laughing and posing for pictures with protesters.
All in all it was a surprisingly nice day out and very inspiring. There were times when I felt myself getting quite emotional. It’s definitely something I’d do again.
From the media since it seems like there has been some impact, but there’s a long way to go. If you’re the slightest bit concerned about climate change – and I hope you are – I’d encourage you to get involved. Even if you just sign up on their website or add your name to petitions that circulate. Or you could make a donation, or attend one of their actions.
The pros and cons of parallelism have always been with us in SQL Server and I blogged about this a couple of years ago. This is an updated version of that post to include details of the new wait stat related to parallelism that was added in 2017 (CXCONSUMER), as well as to discuss the options available for cloud based SQL Server solutions.
There’s no doubt that parallelism in SQL is a great thing. It enables large queries to share the load across multiple processors and get the job done quicker.
However it’s important to understand that it has an overhead. There is extra effort involved in managing the separate streams of work and synchronising them back together to – for instance – present the results.
That can mean in some cases that adding more threads to a process doesn’t actually benefit us and in some cases it can slow down the overall execution.
We refer to the number of threads used in a query as the DOP (Degree of Parallelism) and in SQL Server we have the setting MAXDOP (Maximum Degree of Parallelism) which is the maximum DOP that will be used in executing a single query.
Microsoft generally recommend caution setting MAXDOP above 8:
Out of the box, MAXDOP is set to 0, which means there is no limit to the DOP for an individual query. It is almost always worth changing this to a more optimal setting for your workload.
Cost Threshold for Parallelism
This is another setting available to us in SQL Server and defines the cost level at which SQL will consider a parallel execution for a query. Out of the box this is set to 5 which is actually a pretty low number. Query costing is based on Algorithm’s from “Nick’s machine” the box used by the original developer who benchmarked queries for Microsoft.
Compared to modern servers Nick’s machine was pretty slow and as the Cost Threshold hasn’t changed for many years, it’s now generally considered too low for modern workloads/hardware. In reality we don’t want all our tiny queries to go parallel as the benefit is negligible and can even be negative, so it’s worth upping this number. Advice varies but generally recommendations say to set this somewhere in the range from 30 to 50 (and then tuning up and down based on your production workload).
There are many articles in the SQL Server community about how the out of the box setting is too low, and asking Microsoft to change it. Here’s a recent one:
Often in tuning a SQL Server instance we will look at wait stats – which tell us what queries have been waiting for when they run. CXPACKET waits are usually associated with parallelism and particularly the case where multi-threaded queries have been stuck waiting for one or more of the threads to complete – i.e. the threads are taking different lengths of time because the load hasn’t been split evenly. Brent Ozar talks about that here:
High CXPACKET waits can be – but aren’t necessarily – a problem. You can cure CXPACKET waits by simply setting MAXDOP to 1 at a server level (thus preventing parallelism) – but this isn’t necessarily the right solution. Though in some cases in can be, SharePoint for instance is best run with MAXDOP set to 1.
What you can definitely deduce from high CXPACKET waits however is that there is a lot of parallelism going on and that it is worth looking at your settings.
To make it easier to identify issues with parallelism, with SQL Server 2017 CU3 Microsoft added a second wait type related to parallelism – CXCONSUMER. This wait type was also added to SQL Server 2016 in SP2.
Waits related to parallelism are now split between CXPACKET and CXCONSUMER.
Here’s the original announcement from Microsoft regarding the change and giving more details:
In brief, moving forward CXPACKET waits are the ones you might want to worry about, and CXCONSUMER waits are generally benign, encountered as a normal part of parallel execution.
In tuning parallelism we need to think about how we want different sized queries to act on our server.
In general we don’t want these to go parallel so we up the Cost Threshold to an appropriate number to avoid this. As discussed above 30 is a good number to start with. You can also query your plan cache and look at the actual costs of queries that have been executed on your SQL Instance to get a more accurate idea of where you want to set this. Grant Fritchey has an example of how to do that here:
Often the answer is going to be simply to set it to 8 – but then experiment with tuning it up and down slightly to see whether that makes things better or worse.
Very Large Queries
If we have a mixed workload on our server which includes some very expensive queries – possibly for reporting purposes – then we may want to look at upping the MAXDOP for these queries to allow them to take advantage of more processors. One thing to consider though is – do we really want these queries running during the day when things are busy? Ideally they should run in quieter times. If they must run during the day, then do we want to avoid them taking over all the server power and blocking our production workload? In which case we might just let them run at the MAXDOP defined above.
If we decide we want to let them have the extra power then we can override the server MAXDOP setting with a query hint OPTION(MAXDOP n):
You will want to experiment to find the “best” value for the given query. As discussed above and as shown in Kendra Little’s article, just setting it to the maximum number of cores available isn’t necessarily going to be the fastest option.
Exceptions to the Rule
Regardless of the size, there are some queries that just don’t benefit from parallelism so you may need to assess them on an individual basis to find the right degree of parallelism to use.
With SQL server you can specify the MAXDOP at the server level, but also override it at the database level using a SCOPED CONFIGURATION or for individual queries using a query hint. There are even other ways you can control this:
If your SQL Server is hosted in the cloud, then most of this still applies. You still need to think about tuning parallelism – it isn’t done for you, and the defaults are the same – so probably not optimal for most workloads.
There are in general two flavours of cloud implementation. The first is Infrastructure as a Service (IaaS) where you simply have a VM provided by your cloud provider and run an OS with SQL server on top of it in that VM. Regardless of your cloud provider (e.g. Azure, AWS etc.), if you’re using IaaS for SQL Server then the same rules apply, and you go about tuning parallelism in exactly the same way.
The other type of cloud approach is Platform as a Service (PaaS). This is where you use a managed service for SQL Server. This would include Azure SQL Database, Azure SQL Database Managed Instance, and Amazon RDS for SQL Server. In these cases, the rules still apply, but how you manage these settings may differ. Let’s look at that for the three PaaS options mentioned above.
Azure SQL Database
This is a single SQL Server database hosted in Azure. You don’t have access to server level settings, so you can’t change MAXDOP or the cost threshold. You can however specify MAXDOP at the database level e.g.
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
Cost threshold for Parallelism however is unavailable to change in Azure SQL Database.
Azure SQL Database Managed Instance
This presents you with something that looks very much like the SQL Server you are used to, you just can’t access the box behind it. And similar to your regular SQL instance, you can set MAXDOP and the Cost threshold as normal.
Amazon RDS for SQL Server
This is similar to managed instance. It looks and acts like SQL Server but you can’t access the machine or OS. You access your RDS instance through an account that has permissions that are more limited than your usual sa account or sysadmin role allows. And one of the things you can’t do with your limited permissions is to change the parallelism settings.
Amazon have provided a way around this though and you can change both settings using something called a parameter group:
Parallelism is a powerful tool at our disposal, but like all tools it should be used wisely and not thrown at every query to its maximum – and this is often what happens with the out of the box settings on SQL Server. Tuning parallelism is not a knee-jerk reaction to high CXPACKET waits, but something we should be considering carefully in all our SQL Server implementations.
I wanted to update my original article to include the cloud options noted above, but didn’t have access to an Azure SQL Database Managed Instance to check the state of play. Thanks to TravisGarland via Twitter (@RockyTopDBA) and Chrissy LeMaire via the SQL community slack (@cl) for checking this and letting me know!