La bodeguita de Nacho
SQL Server Troubleshooting Stories - "y una ración de bravas"
Where is the table cardinality taken from when that information doesn’t match in the different statistics associated to an object?
My colleague Tony O’Grady asked last week the following question, which I think is interesting to...
Author: Nacho Alonso Portillo Date: 07/07/2012
About the transaction-level snapshot "start" time (for statement reconciliation)
A few days ago I received an email from my good friend Kimberly telling me about an interesting...
Author: Nacho Alonso Portillo Date: 06/20/2012
How does sys.dm_server_memory_dumps work in SQL Server 2008 R2?
It uses FindFirstFile and FindNextFile to enumerate the files matching a pattern like this:...
Author: Nacho Alonso Portillo Date: 06/19/2012
What does the DTC Calls performance counter from the Exec Statistics object accounts for?
I came across the following question today: “sys.dm_os_performance_counters has a counter name...
Author: Nacho Alonso Portillo Date: 06/19/2012
How does the ODBC driver for SQL handles conflicting security settings between DSN and connection string?
My colleague Arvind Shyamsundar asked the following interesting question the other day in an...
Author: Nacho Alonso Portillo Date: 06/18/2012
SQL Server changes behavior of the file autolocation invoked during the planning phase of a full restore with REPLACE clause
A colleague reported the following concern from one of the customers he works with: We just ran into...
Author: Nacho Alonso Portillo Date: 06/08/2012
Farewell DBCC PRTIPAGE
Recently discovered that this DBCC (along with some other less commonly known...
Author: Nacho Alonso Portillo Date: 06/06/2012
Misconceptions around connection pooling
With this post I want to explain certain details about the way connection pooling works with SQL...
Author: Nacho Alonso Portillo Date: 06/04/2012
How the density and distribution of data in the leading key column of an index affects the degree of parallelism chosen for index operations targeting that index?
There’s a topic in SQL Server’s documentation whose title is Configuring Parallel Index Operations,...
Author: Nacho Alonso Portillo Date: 05/30/2012
DBCC DBRECOVER finally removed from SQL Server
For those of you who relied on that command, typically used in data recovery learning labs, just...
Author: Nacho Alonso Portillo Date: 05/14/2012
The seven reasons why Auto Update Stats event will not trigger despite how many modifications affect any of the tables involved in a compiled plan?
A colleague from PFE recently asked the following: ****************************** Hi Folks,...
Author: Nacho Alonso Portillo Date: 05/04/2012
How the query execution engine calculates the threshold it uses to detect inaccurate cardinality estimation and fire the inaccurate_cardinality_estimate extended event?
Starting with SQL Server 2012, the query engine folks introduced an extended event,...
Author: Nacho Alonso Portillo Date: 04/16/2012
Want your SQL Server to simply ignore the AUTO_CLOSE setting, for all open databases for which it has been enabled?
Under normal circumstances, once there is no one referencing any object from a database whose...
Author: Nacho Alonso Portillo Date: 04/11/2012
What’s in Enterprise only? (Change Data Capture)
Starting with version 2008, SQL Server introduced a feature called Change Data Capture which...
Author: Nacho Alonso Portillo Date: 03/09/2012
What’s in Enterprise only? (Transparent Data Encryption)
Starting with version 2008, SQL Server solves the problem of protecting data at rest, encrypting...
Author: Nacho Alonso Portillo Date: 03/02/2012
VDI Backups and Backup Compression Default
This week I was in a customer measuring the effects it would have, in one particular environment...
Author: Nacho Alonso Portillo Date: 02/24/2012
What’s in Enterprise only? (Database Snapshot)
If you require one or more read-only, static views of a database, which is transactionally...
Author: Nacho Alonso Portillo Date: 02/24/2012
What’s in Enterprise only? (Support for Data Compression & Vardecimal Storage Format)
Starting with version 2008, SQL Server supports both row and page compression for both tables and...
Author: Nacho Alonso Portillo Date: 02/17/2012
What’s in Enterprise only? (Up to 8 CPU sockets)
Each edition of SQL Server supports a specified number of processor sockets multiplied by the number...
Author: Nacho Alonso Portillo Date: 02/10/2012
Backup Compression and Checksum
Notice that when I wrote this repro and captured the screenshots available below, I didn’t think...
Author: Nacho Alonso Portillo Date: 02/08/2012
ALTER TABLE SWITCH fails with 4947 even when all the publicly exposed metadata for the indexes in source and target tables perfectly match
A colleague who is a consultant in Chile, reported a weird scenario he was observing in one customer...
Author: Nacho Alonso Portillo Date: 02/07/2012
What’s in Enterprise only? (Fuzzy Lookup Transformation in Integration Services)
Fuzzy Lookup is a component which, when used as part of a data flow in an SSIS (SQL Server...
Author: Nacho Alonso Portillo Date: 02/03/2012
What’s in Enterprise only? (Hot add CPU)
Starting with SQL Server 2008 the product supports the ability to start using or stop using CPUs...
Author: Nacho Alonso Portillo Date: 01/27/2012
Glitch in SMO causes an error when you attempt to create an FTS Catalog/Table Population Schedule under very specific circumstances
If you connect from SQL Server Management Studio (SSMS) to an instance of SQL Server, whether it is...
Author: Nacho Alonso Portillo Date: 01/26/2012
Reducing the number of queue readers via MAX_QUEUE_READERS parameter might take long time to take effect if there are many messages in the queue
This is another issue that was kindly exposed by our colleague, John Huang, from Canada, through the...
Author: Nacho Alonso Portillo Date: 01/24/2012
Duration of schema locks for certain DML operations might not work as expected
John Huang, a colleague from the MCM community, recently exposed to the rest of us a weird case...
Author: Nacho Alonso Portillo Date: 01/23/2012
Expected behavior of INSERT into views with Instead Of Insert Triggers
The INSTEAD OF INSERT Triggers topic in the documentation describes the following: An INSERT...
Author: Nacho Alonso Portillo Date: 01/22/2012
What’s in Enterprise only? (View Substitution Implicitly Allowed)
As with any index, SQL Server can decide to use an indexed view (materialized views) in its query...
Author: Nacho Alonso Portillo Date: 01/20/2012
What’s in Enterprise only? (honor Lazy Schema Validation option in a Linked Server)
Together with the definition of each linked server goes a series of options that control different...
Author: Nacho Alonso Portillo Date: 01/13/2012
What’s in Enterprise only? (Updatable Distributed Partitioned Views)
SQL Server can use read-only distributed partitioned views as a scale out mechanism, but any attempt...
Author: Nacho Alonso Portillo Date: 01/06/2012
What’s in Enterprise only? (Online Index Operations)
Starting with SQL Server 2005 you can create, rebuild, or drop indexes while concurrent users read...
Author: Nacho Alonso Portillo Date: 12/30/2011
What’s in Enterprise only? (Partitioned Tables and Indexes)
Starting with version 2005, SQL Server introduces the concept of partitioned tables and indexes....
Author: Nacho Alonso Portillo Date: 12/23/2011
Why is the column named “Microsoft SQL Server 2005 XML Showplan” despite the version producing it is not 2005?
This is something many users point out as a defect, but which is not. It is the fact that despite...
Author: Nacho Alonso Portillo Date: 12/21/2011
Under which circumstances would Activity Monitor display duplicate rows in the Processes section?
There are two reasons why Activity Monitor would show duplicate rows in its Processes area, just...
Author: Nacho Alonso Portillo Date: 12/20/2011
Clicking on the XML link containing a query plan doesn’t display the graphical plan but opens the XML editor instead
Recent Note: SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 (10.50.2796.0) is the first build...
Author: Nacho Alonso Portillo Date: 12/19/2011
What’s in Enterprise only? (Concurrent, Shared, or Merry-go-round Scans)
When a new scan is started on any given B-Tree, SQL Server checks whether the new scan can consume...
Author: Nacho Alonso Portillo Date: 12/16/2011
How can you drop the witness of a mirroring session when the partners have been dismantled?
A colleague of mine described the following situation: Long time ago they setup a database mirroring...
Author: Nacho Alonso Portillo Date: 12/13/2011
The Read Ahead that doesn’t count as Read Ahead
As you may know from having read the documentation, whitepapers, or personal synthetic experiments,...
Author: Nacho Alonso Portillo Date: 12/09/2011
What’s in Enterprise only? (Log manager uses locked pages for log cache buffers)
Beginning with SQL Server 2008 R2, log manager uses locked pages whenever possible for log cache...
Author: Nacho Alonso Portillo Date: 12/09/2011
Programmatically enabling/disabling SQL Native Client Protocols
SQL Server Configuration Manager uses the WMI provider for Configuration Management. More...
Author: Nacho Alonso Portillo Date: 12/08/2011
What is the expected behavior from an attempt to enable a trace flag which is not defined in the targeted version of the product?
Imagine, for example, that you run DBCC TRACEON(1117, -1) to globally enable trace flag 1117 on an...
Author: Nacho Alonso Portillo Date: 12/05/2011
What’s in Enterprise only? (Running DBCC CHECK* in parallel)
Parallelizing the execution of DBCC CHECK* can give a significant performance boost to its execution...
Author: Nacho Alonso Portillo Date: 12/02/2011
Attempt to grow all files in one filegroup and not just the one next in the autogrowth chain using trace flag 1117
As you may have noticed, it has been written about Trace Flag 1117 in various blogs, and the general...
Author: Nacho Alonso Portillo Date: 12/01/2011
What’s in Enterprise only? (Fast Recovery)
During a crash recovery or a database mirroring failover, SQL Server Enterprise Edition (or a...
Author: Nacho Alonso Portillo Date: 11/25/2011
How true is it that Missing Indexes feature cannot gather stats for more than 500 missing index groups?
Not much, really. There is a topic in SQL Server’s documentation about the Limitations of the...
Author: Nacho Alonso Portillo Date: 11/21/2011
What’s in Enterprise only? (up to 2TB of memory)
Beginning with SQL Server 2008 R2, there’s a significant difference in the amount of memory...
Author: Nacho Alonso Portillo Date: 11/18/2011
What’s in Enterprise only? (Backup Mirroring)
Increasing the reliability of your backups by providing redundancy is something you can achieve with...
Author: Nacho Alonso Portillo Date: 11/11/2011