Welcome to MSDN Blogs Sign in | Join | Help

Browse by Tags

Sometimes the Simplest Solution Isn't the Best Solution (The All-In-One Search Query)

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; Read More...
Posted Sunday, May 03, 2009 8:13 PM by bartd | 11 Comments

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 Read More...
Posted Tuesday, March 31, 2009 10:33 PM by bartd | 3 Comments
Filed under:

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-3.aspx Read More...
Posted Wednesday, September 24, 2008 9:46 PM by bartd | 3 Comments

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 Read More...
Posted Thursday, September 11, 2008 11:17 PM by bartd | 5 Comments
Attachment(s): pbm_health_eval.png

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 Read More...
Posted Wednesday, September 03, 2008 8:20 PM by bartd | 2 Comments
Attachment(s): fingerprint_screenshot_2.png

A simpler way to convert a hexadecimal string to varbinary

This isn't perf-related like most of my earlier posts, but I thought it was useful enough that I should share it. We recently had a situation where we had to convert a hexadecimal string representation of a binary value to a true binary (e.g. varchar Read More...
Posted Thursday, July 26, 2007 1:36 AM by bartd | 2 Comments

Attachment(s): fingerprints_screenshot_1.png

Are you using SQL's Missing Index DMVs?

Did you know that your SQL Server is keeping track of the indexes that it thinks you should create? The "missing index" DMVs in SQL are a really great new feature in SQL Server 2005 that (in my opinion) seem to have been underutilized so far. If you want Read More...
Posted Thursday, July 19, 2007 10:29 AM by bartd | 13 Comments
Filed under:

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 table à 3. Update Supplier table Read More...
Posted Monday, September 25, 2006 7:27 PM by bartd | 34 Comments

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 Read More...
Posted Wednesday, September 13, 2006 1:12 AM by bartd | 3 Comments
Attachment(s): DTA.PNG

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, Read More...
Posted Saturday, September 09, 2006 1:28 AM by bartd | 49 Comments
Attachment(s): SQL2000_Deadlocks_T1204.htm

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 Read More...
Posted Wednesday, August 16, 2006 8:13 PM by bartd | 0 Comments

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 Read More...
Posted Monday, August 14, 2006 11:39 PM by bartd | 4 Comments
Attachment(s): like_views_nopush.sql

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 Read More...
Posted Thursday, July 27, 2006 6:29 PM by bartd | 5 Comments

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 <= Read More...
Posted Tuesday, July 25, 2006 5:37 PM by bartd | 8 Comments

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 Read More...
Posted Tuesday, July 25, 2006 4:54 PM by bartd | 0 Comments
Attachment(s): pbm_health_create.png
More Posts Next page »
 
Page view tracker