Browse by Tags

Tagged Content List
  • Blog Post: Don’t depend on expression short circuiting in T-SQL (not even with CASE)

    There are a fair number of blog posts and forum discussions regarding expression short circuiting in T-SQL. Some of the most authoritative posts, like this one , come to the following conclusions: (a) You cannot depend on expression evaluation order for things like “ WHERE <expr1> OR <expr2>...
  • Blog Post: Query Tuning Fundamentals: Density, Predicates, Selectivity, and Cardinality

    A few days ago I was in the middle of writing up a quick post about a query performance problem I recently ran into. The writeup referenced predicate selectivity , and I found myself wondering whether everyone who came across the post would have a good intuitive understanding of what that referred to...
  • Blog Post: Living with SQL's 900 Byte Index Key Length Limit

    We recently had a situation where we needed to interface with an external non-relational data source that happened to use really long strings to identify entity instances. These identity strings had theoretically unbounded length, but in practice they were never more than 1000-2000 characters. The database...
  • Blog Post: Query Hash Statistics, a query cost analysis tool, now available for download

    Some time ago I described the query fingerprint and query plan fingerprint (a.k.a. query hash / query plan hash) features that were added in SQL Server 2008. A fingerprint identifies all queries or all plans that have the same “shape”. With query fingerprints you can get the cumulative cost...
  • Blog Post: T-SQL UDTs. (Huh!) What are they good for?

    (The title of this post might seem a little inflammatory, but it’s not just a troll -- I really do think that people should seriously question whether it’s wise to use T-SQL User Defined Data Types. Mostly, though, I just like that Edwin Starr song.) Let me start this post off by saying...
  • Blog Post: SQL Server Sizing Resources

    Recently, I was asked whether Microsoft had any SQL Server hardware sizing tools. The asker referred me to http://www.sizinglounge.com as an example of what he was looking for. (Sizing Lounge apparently allows you to select an app like SAP or Exchange, answer a couple of questions about things like concurrent...
  • Blog Post: Purging Data

    This post is about deleting a lot of rows, as you might do in a data archiving or purging task. By “a lot of rows”, I mean anything from a few thousand rows up to billions. This may seem elementary, but there are some surprising problems that can appear if you use a simple DELETE to delete...
  • Blog Post: Finding procedure cache bloat

    Explicitly parameterizing queries is a well-known best-practice for database app developers. There are cases where it can make sense to execute a query unparameterized, but, in general, you should default to explicit parameterization. It can make your queries faster by avoiding unnecessary compilation...
  • Blog Post: Sometimes the Simplest Solution Isn't the Best Solution (The Optional Parameter Problem)

    Programmers should naturally gravitate toward the simplest, most elegant solution. This is because the simplest coding solution is so often the best solution: simple solutions are cheaper to implement; easier for others to understand, maintain, and extend; and less prone to bugs. Simplicity can also...
  • Blog Post: The Death of DateTime?

    SQL Server 2008 added a new data type named “ datetimeoffset ”. This is similar to the old datetime data type, with the following significant differences: · Internally, the time is stored in unambiguous UTC format · The local time zone offset is stored along with the UTC time, which allows the...
  • Blog Post: Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"

    I've received a couple of questions in email and in comments about deadlocks involving mysterious-sounding non-lock resources like " exchangeEvent " and " threadpool ". There are a couple of examples in the comments for post http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part...
  • Blog Post: Defining Complex Server "Health" Policies in SQL 2008

    Policy Based Management (PBM) is a new feature in SQL Server 2008 that allows you to define a set of policies that capture the intended state for a group of servers. For example, you could define a policy that says that your user databases should all have the auto update statistics database option enabled...
  • Blog Post: Query Fingerprints and Plan Fingerprints (The Best SQL 2008 Feature That You've Never Heard Of)

    In versions of SQL Server before SQL Server 2008, it can be difficult to determine the cumulative cost of the queries running on a server if the workload includes unparameterized queries. The only truly reliable method is to capture a Profiler trace during a representative time period, then post-process...
  • Blog Post: Deadlock Troubleshooting, Part 3

    Here’s an example of the classic scenario that is usually used to introduce the concept of a deadlock in a database: Process A Process B 1. Begin Transaction 1. Begin Transaction 2. Update Part table 2. Update Supplier...
  • Blog Post: Deadlock Troubleshooting, Part 2

    In this post I’ll look at an actual deadlock, then troubleshoot it using the steps I described in Deadlock Troubleshooting, Part 1 so you can see them in action. This is a simplified version of a deadlock scenario that an internal customer here at Microsoft called us for help with. To set up the scenario...
  • Blog Post: Deadlock Troubleshooting, Part 1

    A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed. When the deadlock monitor thread in SQL Server detects a circular blocking chain, it selects one of the participants as a victim, cancels that spid’s current batch, and rolls...
  • Blog Post: Joins Dissected on CraigFr's blog

    CraigFr has a great series of posts in his blog describing the difference between the various logical and physical join types, the details of how SQL Server implements these joins, and the things that the query optimizer takes into account when selecting a join type. These five posts are a wonderful...
  • Blog Post: SELECT from a view slower than "equivalent" SELECT from the base table

    Sometime we get complaints that a query is slower than it could be because a filter isn’t pushed very deeply down into a plan. For example, consider this hypothetical poor performance scenario (my apologies in advance for the lack of normalization): USE tempdb IF OBJECT_ID ('Sales') IS NOT NULL...
  • Blog Post: Wide vs. Narrow Plans

    Here's another case where you might see intermittently poor performance that is "by design". Suppose you see that a delete, insert, or update query in a stored proc usually runs quickly, but occasionally the query takes much longer to complete. You captured a detailed profiler trace of both the good...
  • Blog Post: Limited Statistics Granularity

    To set up this scenario, run the script below: USE tempdb GO IF OBJECT_ID ('test1') IS NOT NULL DROP TABLE test1 GO CREATE TABLE test1 (c1 tinyint, c2 smallint) DECLARE @x int DECLARE @msg varchar(1000) SET @x = 1 SET NOCOUNT ON BEGIN TRAN WHILE (@x <= 1000000) BEGIN INSERT INTO test1 (c1, c2...
  • Blog Post: Why a bad plan isn't necessarily a bug

    Everyone that has worked with databases for long enough has run into situations where the query optimizer doesn't select the best possible plan. You may find that you can force SQL to use an index, choose a different join algorithm or join order, or use some other query hint to get a much faster plan...
Page 1 of 1 (21 items)