|
|
|
|
A technical SQL Server blog from New Zealand.
Browse by Tags
All Tags » Internals
-
I love SQL Server execution plans. It is often easy to spot the cause of a performance problem just by looking at one. The task is considerably easier if the plan includes run-time information (a so-called ‘actual’ execution plan), but even a compiled Read More...
|
-
SQL Server 2005 onward caches temporary tables and table variables referenced in stored procedures for reuse, reducing contention on tempdb allocation structures and catalogue tables. A number of things can prevent this caching (none of which are Read More...
|
-
Summary: A deep dive into SQL Server parallelism, and a potential performance problem with parallel plans that use TOP. There was an interesting question asked by Mark Storey-Smith on dba.stackexchange.com back in October 2011. He was looking at Read More...
|
-
This is the final part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Read More...
|
-
This is the third part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Read More...
|
-
This is the second part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Read More...
|
-
This is the first in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia Read More...
|
-
There are interesting things to be learned from even the simplest queries. For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but Read More...
|
-
The following table summarizes the results from my last two blog entries, showing the CPU time used when performing 5 million clustered index seeks: In test 1, making the clustered index unique improved performance by around 40%. In test 2, making the Read More...
|
-
You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance. Some people have gone so far as to write scripts to search the plan cache Read More...
|
-
You might have noticed that January was a quiet blogging month for me. Part of the reason was that I was working on a series of articles for Simple Talk, examining how parallel query execution really works. The first part is published today at: http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/ Read More...
|
-
There is much more to query tuning than reducing logical reads and adding covering nonclustered indexes. Query tuning is not complete as soon as the query returns results quickly in the development or test environments. In production, your query will compete for memory, CPU, locks, I/O and other resources on the server. Today’s entry looks at some tuning considerations that are often overlooked, and shows how deep internals knowledge can help you write better TSQL.
As always, we’ll need some example data. In fact, we are going to use three tables today, each of which is structured like this: Read More...
|
-
Is it possible to see LOB (large object) logical reads from STATISTICS IO output on a table with no LOB columns?
I was asked this question today by someone who had spent a good fraction of their afternoon trying to work out why this was occurring – even going so far as to re-run DBCC CHECKDB to see if any corruption had taken place. The table in question wasn’t particularly pretty – it had grown somewhat organically over time, with new columns being added every so often as the need arose. Nevertheless, it remained a simple structure with no LOB columns – no TEXT or IMAGE, no XML, no MAX types – nothing aside from ordinary INT, MONEY, VARCHAR, and DATETIME types. To add to the air of mystery, not every query that ran against the table would report LOB logical reads – just sometimes – but when it did, the query often took much longer to execute.
Ok, enough of the pre-amble. I can’t reproduce the exact structure here, but the following script creates a table that will serve to demonstrate the effect: Read More...
|
-
A seek can contain one or more seek predicates – each of which can either identify at most one row in a unique index (a singleton lookup) or a range of values (a range scan). When looking at a query plan, we will often need to look at the details of the seek operator in the Properties window to see how many operations it is performing, and what type of operation each one is. As you saw in the first post in this mini-series, the number of hidden seeking operations can have an appreciable impact on performance.
Measuring Seeks and Scans
I mentioned in my last post that there is no way to tell from a graphical query plan whether you are seeing a singleton lookup or a range scan. You can work it out – if you happen to know that the index is defined as unique and the seek predicate is an equality comparison, but there’s no separate property that says ‘singleton lookup’ or ‘range scan’. This is a shame, and if I had my way, the query plan would show different icons for range scans and singleton lookups – perhaps also indicating whether the operation was one or more of those operations underneath the covers.
In light of all that, you might be wondering if there is another way to measure how many seeks of either type are occurring in your system, or for a particular query. As is often the case, the answer is yes – we can use a couple of dynamic management views (DMVs): sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats. Read More...
|
-
Many people believe that whenever SQL Server creates an execution plan that uses parallelism, an alternative serial plan is also cached. The idea seems to be that the execution engine then decides between the parallel and serial alternatives at runtime.
I’ve seen this on forums, in blogs, and even in books. In fairness, a lot of the official documentation is not as clear as it might be on the subject. In this post I will show that only a single (parallel) plan is cached. I will also show that SQL Server can execute a parallel plan on a single thread…
Before diving into the demonstration, I want to quickly run through some background information about the SQL Server plan cache.
Compiled Plans
Queries are expensive to compile and optimize, so SQL Server uses caching to improve efficiency through plan reuse. The server optimizes an entire batch all at once, and the result is known as a compiled plan (or sometimes ‘query plan’).
The dynamic management view sys.dm_exec_cached_plans contains one row for each compiled plan, with a plan_handle that uniquely identifies the compiled plan among those currently in cache (plan handles can be reused over time). This plan handle can be passed to the dynamic management function sys.dm_exec_query_plan to show the compiled plan in XML format. When displayed in Management Studio, we can click on this XML representation to view the familiar graphical plan.
A compiled plan is a compile-time object – no user or runtime context is stored. You might find it helpful to think of the compiled plan as a template – or perhaps as being similar to the estimated execution plans seen in Management Studio.
Execution Contexts
An execution context (or ‘executable plan’) - internally known as an MXC – is generated when a compiled plan is prepared for execution. Execution contexts contain specific runtime information, for a single execution, for a single user. Read More...
|
|
|
|
|
|