Category: Administration

Tim Peters shows that the Dynamics people are probably right with their assertion regarding Cost Threshold for Parallelism:

These plans are from a SQL Server 2008 R2 SP3 instance that runs a version of Microsoft Dynamics. The MS Dynamics team knows their product issues a bunch of really small queries and recommend a Max Degree of Parallelism of 1. In my case there is one query plan with a cost of 34,402 query bucks that a MAXDOP = 1 can’t afford. Increasing the MAXDOP from 1 while keeping the Cost Threshold at 5 will keep all of my little queries running in their single threaded paradise while allowing some infrequent biguns to spread their lovely wings across a few more threads. My Cost Threshold For Parallelism is set to 5 and I hope to never have to defend my settings in a dark alley with Erik Darling.

Different systems will behave in different ways, so it makes sense that they might require different settings.  In the case of Dynamics, it appears that almost all of the queries are extremely low-cost, so relatively few plans would go parallel and the ones that do, you probably want going parallel.

Grant Fritchey continues his thoughts on parallelism:

Microsoft set the default value for the Cost Threshold for Parallelism back in the 1990s. They were developing SQL Server 2000. That means this value was determined as a good starting point for query plan costs over 17 years ago. In case you’re not aware, technology, T-SQL, SQL Server, and all the databases and database objects within them shifted, just a little, in the intervening 17 years. We can argue whether or not this value made sense as a starting point (and remember, the default settings are meant to be starting points covering a majority of cases, not a final immutable value) for determining your Cost Threshold for Parallelism 17 years ago. I think we can agree that it’s no longer even a good starting point.

For more thoughts, check out a prior post on figuring out the cost threshold.

Kevin Hill shows how to recover from a scenario with an unexpectedly large SQL Server transaction log file:

Step 2: Verify if the log is full or “empty”

Verify if the log file is actually full or not.  If you are backing up and the file still grew to ridiculous size…it may have just been a one time thing and you can deal with that easily.  Right-click the database, go to reports, standard reports, disk usage.  This will give you 2 pie charts.  Left is the data file, right is the log.  If the log shows almost or completely full AND the huge size, you need to backup.  If the log file is huge and mostly empty, you simply need to shrink to an acceptable size.

Great read for a junior-level DBA.

Wayne Sheffield shows how to find the physical location of a row in SQL Server:

Acquiring the physical location of a row

SQL Server 2008 introduced a new virtual system column: “%%physloc%%”. “%%physloc%%” returns the file_id, page_id and slot_id information for the current row, in a binary format. Thankfully, SQL Server also includes a couple of functions to split this binary data into a more useful format. Unfortunately, Microsoft has not documented either the column or the functions.

Read on for two functions you can use to format this data more nicely, as well as a short re-write Wayne did to improve performance of one of them.