Search results matching tag 'seminars'

Did You Know? I’m delivering my first SQL Server 2012 class this week!

Kalen Delaney — Wed, 08 Aug 2012 04:14:00 GMT

This is actually just a very short post to get my name back up in the list on the right side of SQLBlog, because Adam’s configuration removes people who haven’t posted in 3 months. I’ve been extremely busy trying to get my new book finished, and then get my course updated for its first delivery this week. I’ll be teaching it again in Norway at the end of the month.

It’s a great class this week, and a couple of questions have inspired longer posts, which hopefully I be able to get to next week.

Hopefully I’ll also have an update on the timeline for the new book by next week.

My schedule is always available if you are interested in learning SQL Server 2012 Internals with me!

http://schedule.KalenDelaney.com

Thanks

~Kalen

Geek City: Ordered Seeks and Scans

Kalen Delaney — Fri, 21 Jan 2011 23:28:00 GMT

I got a couple of really great questions during my SSWUG Workshop this morning, as I was discussing seeks and scans, and since the answers to the two questions are very related, I decided to address both of them in more detail in this blog post.

Most people think about a seek operation as retrieving just one or a few rows, and a scan as accessing the entire table or index leaf level. And that usually is what I mean when I am just describing SQL Server behavior. But there is also the possibility that SQL Server could perform a 'partial scan', starting at some row in the table or index leaf, and then scanning only to a particular ending row. However, there is no operator in a query plan called 'partial scan'. So how can you tell when you have one?

Before I specifically answer that, let me tell you about the first question in the workshop this morning. I had been showing the Properties window (available from View/Properties Window), pointing out that when you click on one of the icons in a graphical query plan, the Properties window shows the same information that is in the yellow box that pops up, plus additional information. One of the values shown in one called 'Ordered', and when 'Ordered' has the value true, then there is also a value called 'Scan Direction', which might make you think that ordering is only relevant to scans. But you will see True values for 'Ordered' even when the operation is a seek. This is usually an indication that a partial scan is being performed.

And because I was talking about heaps being unordered sets of rows, one of the delegates asked: "Are index seeks against a heap always unordered?"

The answer is, of course, "it depends". If you have a covered query, the index access could be an ordered seek of the covering index, even though the underlying table is a heap. Here is an example from the AdventureWorks database. I make a copy of the Sales.SalesOrderHeader table, so it is a heap, and then build a nonclustered index on the Freight column.

IF EXISTS (SELECT * FROM dbo.SalesOrderHeader)
DROP TABLE dbo.SalesOrderHeader;
GO
SELECT * INTO dbo.SalesOrderHeader
FROM Sales.SalesOrderHeader;
GO
CREATE INDEX ix_Freight ON dbo.SalesOrderHeader(Freight);
GO

Then I look at the plan for a covered query using the ix_Freight index:

SELECT AVG(Freight)
FROM dbo.SalesOrderHeader
WHERE Freight < 100;

And here is the plan showing the index seek:

And the Properties sheet for the Index Seek shows that Ordered - True and Scan Direction = FORWARD.

This is an example of an ordered seek on a nonclustered index, or what we can think of as a partial scan, or range scan.

The other question during the workshop was how to tell if the seek was really a range scan. Part of the answer is to look for the Ordered = True value, as I just illustrated. Another thing to notice in the graphical query plan is the thickness of the line moving rows to the left from the Index Seek, indicating that LOTS of rows are being returned from the Seek. That's another clue that we have a partial scan.

We can also get partial scans with clustered indexes. I'll build a clustered index on my new table.

CREATE CLUSTERED INDEX clus_OrderID ON dbo.SalesOrderHeader(SalesOrderID);
GO

There are 31465 rows in the dbo.SalesOrderHeader table, with SalesOrderID values ranging from 43659 to 75123. I'll SELECT a small range of data:

SELECT * FROM dbo.SalesOrderHeader
WHERE SalesOrderID < 44000

The plan shows a Clustered Index Seek, and the Properties shows Ordered = True.

If I make the range bigger, and look for SalesOrderID < 60000, I still get an Index Seek, and the same when using an upper limit of 75000. If I run a query that returns ALL the rows, I still get an Index Seek.

Both queries are using an Index Seek, but notice the difference in the thickness of the arrows.

It turns out that the only time the optimizer will tell us it is doing a scan is when it knows absolutely that it will be looking at every row in the table or index leaf. And the only way it can know this is if there is no filter condition (NO WHERE clause) or no index on the filter condition. With the index on SalesOrderID, there is no way the optimizer can be absolutely sure that it will have to scan the whole table. Someone might have inserted a value of 100001 since the last time statistics were updated, and we wouldn't want to perform a scan and return a value that is not in the specified range. So the Index Seek in the plan really just means that SQL Server will access the data using the index, but not with a complete scan. There is something in the query that give a starting point, a stopping point or both.

I've got another Workshop for SSWUG on Index Internals, coming up on February 25th. Hopefully, I'll get a lot more great questions during that one!

Thanks!

~Kalen

Geek City: 24Hours of PASS Followup

Kalen Delaney — Thu, 16 Sep 2010 18:20:00 GMT

Thank you everyone who attended my 24HOP session yesterday. It was a wonderful event to be part of.

Here are a few of the questions that I was asked, that didn't get completely answered.

Question: Can we set READ_COMMITTED_SNAPSHOT ON for just a table rather than at database level?

I apologize that I answered this incorrectly. I was trying to remember all of the locking hints on the spur of the moment, and remembered that there is a hint READCOMMITTED LOCK to force default locking/blocking behavior when the database has been set to READ_COMMITTED_SNAPSHOT, so I was thinking there should be one to go the other way. But now that the stress of the moment is off, I realize that there could not be such a hint. Enabling snapshot isolation means that SQL has to save the old committed values of any changed data as the change is taking place. For a SELECT to just request to read older committed values, when the database has not already been set to save them, would not be possible. The data you're looking for would not be available.

Question: Is it ever appropriate to use the No Lock hint? I'm thinking of using it in certain Select statements.

What does 'appropriate' mean? Like all the phenomena I discussed in my presentation, allowing dirty reads is a choice. You need to be aware of the costs and the tradeoffs, but you might decide in some situations that the value you get is worth the (potentially high) price. I only hope that people don't just choose to use it arbitrarily, just because it's "faster", but that they are aware of the ramifications of performing dirty reads.

Question: Where can I get more information about the tradeoffs between the different isolation levels and concurrency models?

I'm glad you asked. This presentation was only a PREVIEW of what I'll be talking about for an entire day in a pre-conference session for PASS. You can get more details here:

http://sqlpass.eventpoint.com/topic/details/DBA301P

Question: Is there a way to get access to these slides at the end of the presentation please?

I am attaching them to this blog post, along with a PDF of the presentation, and I will also be adding them to my web site at www.SQLServerInternals.com/conferences by the end of the week.

Question: Can Kalen unprotect her tweets?

I could, but it wouldn't be very interesting since I don't tweet and there would be nothing to read. But lately I have started thinking more about it. Maybe I'll start… and if I do start tweeting on a regular basic, my tweets will be open and available.

Thanks again!

~Kalen

Geek City: Plan Caching Internals

Kalen Delaney — Mon, 23 Aug 2010 17:04:00 GMT

This Friday is the last of my summer seminars. On August 27, I am presenting on plan caching, plan reuse and recompile and plan cache metadata. This is one of my favorite topics to talk about, because a lot of the information is not very widely known, and you can have a tremendous performance impact by making sure when plans are reused when that is the best option and when a new plan is compiled when that is best choice. By examining the plan cache metadata, you can see exactly what plans are currently in cache and what those plans are doing, i.e. index seeks or scan? hash joins or loop joins? extra sorting? parallelization?

Here is the link for more info and registration:

http://www.vconferenceonline.com/shows/workshops/plancaching.asp

The seminar has been pre-recorded, but I will be available concurrently for online chat and Q&A.

See you there!

~Kalen

Did You Know: My Online Seminar Lasted Almost 5 Hours!

Kalen Delaney — Sun, 25 Jul 2010 00:06:00 GMT

Yesterday was a rebroadcast of the index internals seminar I presented last February, and I had forgotten how long it was. I was in a chat room the whole time, and the questions just kept coming, fast and (not so) furious! Thanks to all the participants for making it a really exciting 5 hours for me. I really appreciated all your enthusiasm.

My next seminar with SSWUG will be on Plan Caching and Recompiling. I always love teaching this topic, because there are so many ways you can make a major difference in performance by understanding when reusing a cached plan is a good thing and when it is NOT! This includes Parameter Sniffing and how to avoid it.

We'll look at when automatic statistics updating kicks in and when that causes recompiles, as well as looking at what other changes can cause automatic recompilation.

We'll talk about XML query plans, and -I'll show you how to access the plan cache metadata to see how to look at the plan for every query currently in cache. It's an awesome capability!

I hope to see you there!

~Kalen

Geek City: Index Internals

Kalen Delaney — Mon, 19 Jul 2010 14:15:00 GMT

Once again, I'll be presenting an online seminar through SSWUG. This on is on Index Internals. The presentation lasts over 3 and a half hours, and I'll be online for live chat for over 4 hours. Ask away! We'll look at juicy details of exactly how clustered and nonclustered indexes differ from each other'. I'll show you how to display the actual contents of your index rows so you can really see what the difference is between a key column and an included column.

The seminar starts Friday, 9 AM Pacific Time.

Here is the link to the registration site:
http://www.vconferenceonline.com/shows/workshops/indexing.asp

I hope to see you there!

~Kalen

Did You Know: Good Stuff!

Kalen Delaney — Wed, 30 Jun 2010 21:33:00 GMT

I know, I promised a technical post after my online seminar today, and I'm still planning on that. There weren't all that many really deep questions, just a lot of basic understanding questions, plus questions about topics I'll be covering in my Plan Caching and Recompilation Seminar in August. There was one really interesting question about a particular behavior that I am researching, so that might turn into a post. Also, it's always fun to see people react to using GO with a number, if they've never seen it before, as I discussed here:

http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/22/did-you-know-run-a-batch-multiple-times.aspx

I don't do a lot of pointers to other bloggers, but this one I just can't resist. I found two excellent posts by Jay Choe, of the SQL Server Engine team at Microsoft, explaining about Memory Grants:

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/03/11/mystery-of-memory-fraction-in-showplan-xml.aspx

These should keep your brain busy until my next technical post …

Have fun!

~Kalen

Did You Know: My next web seminar is this Wednesday!

Kalen Delaney — Mon, 28 Jun 2010 03:40:00 GMT

I know, I haven't made a geeky post in a while... but I promise that I'll do one after this seminar. I'll take some of the best questions that I get during the chat and write them up here.

Query Plans Workshop (June 30, 2010)
Learn about basic elements, tools for exploring your query plans, how to detect inefficient plans and best practice guidelines

The workshop starts at 9 in the morning Pacific Time,and I'll be available on live chat to answer questions during the broadcast.

Thanks!

~Kalen

Did You Know? I'm doing 3 more online seminars with SSWUG!

Kalen Delaney — Fri, 11 Jun 2010 22:46:00 GMT

As I told you in April, I recorded two more seminars with Stephen Wynkoop, on aspects of Query Processing. The first one will be broadcast on June 30 and the second on August 27. In between, we'll broadcast my Index Internals seminar, on July 23. Workshops can be replayed for up to a week after the broadcast, and you can even buy a DVD of the workshop.

You can get more details by clicking on the workshop name, below, or check out the announcement on the SSWUG site at http://www.sswug.org/editorials/default.aspx?id=1948

Query Plans Workshop (June 30, 2010)
Learn about basic elements, tools for exploring your query plans, how to detect inefficient plans and best practice guidelines

Indexing Workshop (July 23, 2010)
Learn about basic index structures, tools for exploring those structures, index design considerations and best practice guidelines

Plan Caching Workshop (Aug 27, 2010)
Learn about basic management of the plan cache, tools for exploring the plans in cache and detecting when they are reused, query design considerations to provide the optimal caching behavior and best practice guidelines

For those of you who attended my presentation at the Colorado PASS seminar last Wednesday in Denver, note that the 3rd workshop (Plan Caching), includes most of what I talked about in my presentation, and a lot more besides. The scripts from that session are now up on my site at www.sqlserverinternals.com/conferences

Have fun!

~Kalen

Did You Know? More online seminars!

Kalen Delaney — Thu, 22 Apr 2010 23:20:00 GMT

I am in Tucson again, having just recorded two more online workshops to be broadcast by SSWUG. We haven't set the dates yet, but we are thinking about offering a special package deal for the two of them. The topics really are related and I think they would work well together.

They are both on aspects of Query Processing. The first was on how to interpret Query Plans and is an introduction to the topic. However, it only includes a discussion of how SQL Server actually processes your queries. For example, what exactly does a Hash Join or Stream Aggregation mean? The second was on Caching and Recompiling, basically discussing how SQL Server stores and reuses the plans once it creates them. And, how to tell if reusing or recompiling the best option.

I'll post more details and links once they are available.

Also, I just realized I had never announced the winners of the drawing from my previous seminar.

Lorna M. won a copy of my SQL Server 2008 Internals book, and
Bob L. won a copy of SQL Server MVP Deep Dives

More books will be given away at future seminars!

~Kalen