I have been working on an issue where rebuilding an index leads to additional fragmentation. Using XEvents I debugged the page allocations and writes and was able to narrow in on the behavior. There are lots of factors to take into account when rebuilding the index. I was able to break down the behavior to…
Tag: 2008
Running SQL Server on Machines with More Than 8 CPUs per NUMA Node May Need Trace Flag 8048
Applies To: SQL 2008, 2008 R2, 2012 and 2014 releases Note: The number of CPUs is the logical count, not sockets. If more than 8 logical CPUs are presented this post may apply. The SQL Server developer can elect to partition memory allocations at different levels based on the what the memory is used for. …
A Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index
I was working with a 1.3 trillion row table in the Microsoft lab when I learned more about the ins and outs of this behavior. This issue is alluded to in SQL Server Books Online but allow me to expand on the behavior a bit more. (http://msdn.microsoft.com/en-us/library/ms190787.aspx) The lab machine is a 128GB, 64 CPU…
Every time I ‘ATTACH DATABASE’ SQL logs error 1314 for SetFileIoOverlappedRange
Turns out this is an issue in the SQL Server code and the error is a bit noisy during attach database. When opening the database files, SQL Server calls SetFileIoOverlappedRange (when enabled properly) in order to help improve I/O performance. This is commonly done under the SQL Server, service account; which requires locked pages privilege. …
When Does sp_prepare Return Metadata
I was running an RML Utilities Suite test pass and encountered varying behavior from our sp_prepare suite. Here is what I uncovered. The command sp_prepare returns (or does not return) metadata depending on the server version. For the client version, it is only significant whether it is prior to SQL 2012 or it is a…
How It Works: The I/O Path: SQL Server Running in Windows Azure Virtual Machine (IaaS)
Note: This blog is based on behavior as of June 2013. At Microsoft we continue to evolve and enhance our products so the behavior may change over time. The I/O path for SQL Server, running on Windows Azure Virtual Machines, uses Windows Azure storage (often referred to as XStore.) The following is a link to…
SQL Server–Storage Spaces/VHDx and 4K Sector Size
This blog outlines a new twist to my previous blog outlining issues with 4K sector sizes. SQL Server – New Drives Use 4K Sector Size: http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx In the previous post I discussed that it was unsafe for the I/O subsystem to present a sector size that was smaller than the actual, physical sector size. This…
SQLIOSim Checksum Validations
I had a very specific question asked of me related to the SQLIOSIM.exe, checksum validation logic. It is pretty simple logic (on purpose) but effective so here are the basics. The key is that there are multiple memory locations used to hold the data and do the comparison. 1. Allocate a buffer in memory of…
Don’t change value of that parameter
Parameter sniffing is a well known among SQL User community. But I have seen variations of this frequently that need a bit creative handling and solution may not that straight forward. One of the variation is that a user changes the value of the parameter inside the procedure. When the procedure is compiled at first…
AppDomain unloading messages flooding the SQL Server error log
This blog is built directly from a customer reported issue. As I helped investigate the source of the issue I thought it would be of interest to a broader audience – hopefully you find this interesting, as well. Allow me to provide some history of the problem before I dive into extended details. The SQL…