Kimberly & Paul: SQL Server Questions Answered

Question: Partitioned tables were the new shiny feature in SQL Server 2005 – why are partitioned views even still available? Are there any benefits that they provide? Answer: This is actually a question I get at almost every event at which I speak. It’s a common question and it’s actually VERY complex to fully describe. I suspect it might take me more than one post to tackle all of the issues but I’ll start with the basics here. First and foremost, I’ll start by saying that partitioning is CRITICAL for VLT. What is VLT? It’s about as descriptive as VLDB and it means very large table. (Yes, I just made it up as a new TLA (three-letter acronym) that I’m planning to start using more. However, I’m also going to quantify it a bit more.) Most people speak of VLDBs (very large databases) and they define that as databases that are 100s of gigabytes (many would say that a database that’s 1TB or larger is a VLDB). For me, and in my experience, *many* customers run into problems long before their databases reach 1 TB; their problems tend to start when they have even just one table that starts to get well into double-digit gigabytes. Think about it, a single table that’s 60 GB presents a variety of problems. And, to highlight where the problems occur – think about these questions in the context of your larger tables: Is all of the data recent? How old is the oldest data in your 5 largest tables? How much of that data changing? Not including the new data coming in – what percentage of the older data needs to be modified? How often are you accessing the older data? How long are your maintenance processes against it? Are you replicating it? Do you have enough memory to fit the table (and all of its indexes) in cache? Do you really need to have indexes on ALL of that data? Or, does your data have different access patterns (which might warrant different indexing strategies)? For many, these questions start to pose many concerns at table sizes in the mi...Read the rest of entry >>

Question: Several times in the last year I’ve been forced to run the repair option of CHECKDB because of corruption affecting a database and backups being unavailable. In one case the database was a replication publication database and I followed the Books Online guidelines to reinitialize it’s subscribers after running repair. Can you explain why this is necessary? Answer: Any time that the REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB (hereafter just called ‘repair’) is used, you need to consider the effect on replication if the database being repaired is a publication database. There are two cases to consider – if the repairs affected the replication metadata tables or not. The simplest is when they did not affect the replication metadata tables. When using merge replication, changes to the publication database are captured using DML triggers. When using transactional replication, changes to the publication database are captured by analyzing the database’s transaction log and converting the logged operations into logical operations on the database. In both cases the logical operations are then applied to the replication subscribers. Neither of these mechanisms allows the capture of operations performed by repair. Repair operations are always direct physical changes to the database structures to fix inconsistencies in the structures (e.g. a database page, table record, or a linkage between two pages), as opposed to physical changes because of queries performing inserts, updates, or deletes on tables. These repair operations cannot translate into logical operations that can be applied to replication subscribers because there are no logical operations than can be expressed using Transact-SQL for the equivalent of the direct structural changes that repair is performing. This means that if any table that is part of a replication publication is changed by a repair operation, the replication subscription is no longer valid and must be reinitialized. As an example,...Read the rest of entry >>

In a departure from our usual Q&A style I want to post a follow-on to my previous discussion about how transaction log corruption can lead to transaction log backup failures, and how to recover from them (see here). Any operation that tries to use a corrupt log record will encounter failure, and DBCC CHECKDB is one of those operations. By default, DBCC CHECKDB will create a hidden database snapshot under the covers to provide a transactionally-consistent view of the database on which to run the consistency checks. The process for creating a database snapshot is to checkpoint the real database, and then essentially run crash-recovery on the real database, but into the context of the database snapshot – not affecting the real database. This pseudo-crash recovery rolls back the effect of any uncommitted transactions that are occurring in the real database, making the database snapshot consistent. If this process encounters a corrupt transaction log record, then the database snapshot creation will fail – leading to the DBCC CHECKDB failing too. A bunch of errors will be generated, including one that identifies the corrupt transaction log record, like below: DBCC encountered a page with an LSN greater than the current end of log LSN (141131:0:4) for its internal database snapshot. Could not read page (9647:-33648758), database 'PaulsDB' (database ID 26), LSN = (-1302554001:2131886119:4432), type = 255, isInSparseFile = 1.   Please re-run  this DBCC command. The page ID and its LSN are obviously completely wrong. All is not lost, however, as there are two ways around this issue. Firstly, you could employ the technique I described in the blog post I referenced above (switch to the Simple recovery model, checkpoint to truncate the log, and switch back to the Full recovery model), but you’d have to make sure there are no uncommitted transactions otherwise the transaction log may not truncate past the corruption. Secondly, you could use the WITH...Read the rest of entry >>

Question: I came across a situation where a transaction log backup failed because of corruption in the transaction log. One of my colleagues put the database offline and then online and problem went away. Can you explain why and whether log corruption can lead to a suspect database? Answer: Transaction log corruption is interesting because it doesn’t usually cause any problems apart from failed backups. However, that doesn’t mean it should be ignored. As I’ve stated many times, the vast majority of corruptions are caused by the I/O subsystem and a transaction log file is just as likely to be corrupted as a data file. Unless the corruption occurs in the active portion of the log (the portion that is required for some reason by SQL Server – see this article for background information) then its likely that no-one will ever know it occurred. This may seem disturbing to you, and in a way it is – undiscovered corruption isn’t good – but there’s no way for SQL Server to process the entire log, only the active portion. DBCC CHECKDB doesn’t analyze the log at all any more since I rewrote it in SQL Server 2005, instead it will only use the active portion of the log as a by-product of creating a database snapshot to run the consistency checks on. There’s no consistency checking of the transaction log – only checksums that are checked as log records are read, for whatever reason. The active portion of the log will also be used if a transaction rolls back, if crash recovery has to run, or when a transaction log backup occurs (plus a bunch of other uses like replication and mirroring – the aforementioned article has more information). If a damaged log record is encountered during one of these uses then an error will be thrown and the operation will fail. The only time a database will be marked SUSPECT because of a corruption in the transaction log is if the corruption is encountered during crash recovery or during a transaction rollback. In that case, the operation will fail...Read the rest of entry >>

Question: I’m involved in a project to design a database schema and some of the tables are expected to have millions of rows and a large amount of LOB data (a mixture of binary and character). I know there are a bunch of options for storing this data but I’m struggling with how to choose between them. Can you help? Answer: The answer to this question depends on the size of the data and how it will be used. There are two kinds of data type for storing LOB data – true LOB data types that can store more than 8000 bytes and the limited data types that can store up to 8000 bytes – and of course there are pros and cons for each type. If your data is going to be less than 8000 bytes then it makes sense to use one of the limited data types – (n)varchar (1-8000) or varbinary (1-8000). When the data type becomes really small (say less than 5 bytes), you need to decide whether to use a fixed-length type – (n)char – instead of a variable length type, to avoid the two-byte overhead that comes with variable-length columns. If you need to store 2-byte character set data, you’ll have to use nchar or nvarchar. These data types are always stored ‘in-row’ – i.e. inline with the row they are part of on the same data file page (except for the special case when the row grows beyond 8060 bytes, but that’s beyond the scope of this answer). This means that the rows may become quite large and so very few rows can be stored per data file page. If the LOB data is not going to be used very often, this reduces the data density of the columns that are going to be used frequently – meaning more data file pages will have to be read and stored in memory to process the columns being used and lowering the efficiency of data processing operations. You may decide, if this is the case, to store the seldom-used LOB columns in a separate table, and JOIN to them when they are needed, or to store them in true LOB columns that are stored off-row (i.e. in separate data file pages, but still in the same ta...Read the rest of entry >>

Question: We’ve set up database mirroring for one of our critical databases with a witness server to allow automatic failover. Everything has been running fine until we had a power outage on the data center recently. Database mirroring performed the failover but people reported that the application just hung. When we manually failed back, the application worked again. Why didn’t the application failover too? Answer: This is a reasonably common problem that people encounter when using database mirroring and a production failure like this often happens because no failover testing is conducted after database mirroring is implemented. We became involved after the failed failover. To avoid taking production downtime, we duplicated the mirroring environment on the client’s test systems. After confirming that the application and database mirroring were working, we powered down the principal server and the application completely hung. We checked that the mirror server had successfully initiated a failover and was online as the principal server. We also checked that the mirror database was online, could be used locally on the new principal server, and the principal server was accessible from a remote client, like the application uses. This led us to check the application. We talked to the developer, who confirmed the application was using ADO.NET to connect to SQL Server and was using explicit client redirection, specifying the mirror server name in the ConnectionString property of the SqlConnection. (As an aside, it is always better to use explicit client redirection rather than relying on the mirror server name being cached automatically on the client once a connection has been made – implicit client redirection). So why wasn’t the application failing over? We dug deeper into how the application was handling connection failures and discovered that it was not coded to cope with the existing connection failing at all! Basically the application would open a connection t...Read the rest of entry >>

Question: I’ve started seeing a problem where periodically the scheduled DBCC job fails and I get 823 errors in the error log. I also see errors from the file system too. Is this an I/O subsystem problem? Answer: These errors are not caused by I/O subsystem issues, although outwardly they seem to be. Under the covers all of the DBCC CHECK commands create a hidden database snapshot. When the database snapshot is created, any active transactions in the database are effectively rolled back into the database snapshot (not affecting the real transactions, of course) so that the database snapshot is transactionally consistent. Also, while the database snapshot exists, any data file pages in the real database that are going to change need to be copied into the database snapshot before they change so that their state at the time the database snapshot was created is preserved. These two factors mean that there is the potential for the database snapshot to grow very large – potentially up to the size of the real database. If that happens, there is the possibility that the database snapshot may exceed the size that NTFS can cope with and error 665 will result, as below: The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000005bd3dc000 in file 'Test.mdf:MSSQL_DBCC8' This error comes from Windows Server 2008 – Windows Server 2003 will give error 1450. You can read more about this in this blog post from Product Support. You may see a variety of SQL Server errors too – including 823, 7928, 1823, and 3314. Check to make sure that the 823 errors are referencing a file with a name like ‘MSSQL_DBCC’ (the database snapshot files) rather than one of the real database data files. If the latter then  you have a real I/O subsystem problem. If you’re seeing these errors when you’re running your consistency checking job at a time when there shouldn’t be any user activi...Read the rest of entry >>

Question: I’ve been told that it’s possible to run out of values when using the bigint data type and that I should use GUIDs instead, regardless of the problems they can cause. Is this true? Answer: This is similar to the question I answered last year on whether it’s possible to run out of virtual log file sequence numbers – where the answer is yes, but it would take 240 million years to do so. See this post for the VLF discussion. Similarly, yes, you can run out of bigints but it’s not practical that you will. Bigint allows you to have +/- 2^63 (or +/- 9,223,372,036,854,775,808). Say for argument’s sake that you’re able to process 100 thousand pieces of data per second, and you assign an ever-increasing bigint value for each one. You’d have to be processing continuously for 2^63 / 100,000 / 3,600 hours – which works out to be 2.925 million years. Now that’s just using the bigints – if you wanted to store them too, you’d run out of storage space before running out of numbers. Doing a quick test of a heap with a single bigint identity column shows me that I can get 453 rows per 8KB data file page (don’t forget the record overhead, slot array overhead, and that the heap pages won’t be filled completely because of the way the free space caching and searching works). A terabyte of data would store roughly 61 billion rows. At that rate, actually running out of bigints AND storing them would take roughly 150 thousand petabytes. This is clearly impractical – especially when you consider that simply storing a bigint is pretty pointless – you’d be storing a bigint and some other data too – probably doubling the storage necessary, at least. So yes, while it is theoretically possibly to run out of bigint values, in reality it’s not going to happen....Read the rest of entry >>

Question: I’m having problems with an application vendor who is mandating that we run regular DBCC SHRINKDATABASE operations against the application databases and tempdb. They say this is necessary for performance – can you give me some advice please? Answer: I get asked this at least once a month, where an application vendor refuses to allow a DBA to remove regular shrink operations because they’re ‘necessary for performance’. We all know that shrinking databases causes index fragmentation, consumes CPU and IO resources, and generates a lot of transaction log (which can cause problems for mirroring, replication, and so on). We also know there are some extenuating circumstances where occasional one-off shrinks may be necessary. Furthermore, we know that *regularly* shrinking databases is a cardinal sin because if the database repeatedly grows after being shrunk, all that shrink work is completely wasted effort – its akin to having auto-shrink enabled for the database. The problem I see is that the vendor application teams *don’t* know these things about shrink, and are loath to listen to anyone trying to educate them. Occasionally I’ll jump on an email thread with the original sender and the application vendor team. The justifications from the application vendor team are usually along the lines of the following (paraphrasing): The indexes in the database are already fragmented so shrinking doesn’t make it any worse. Nobody’s ever complained about performance before so why are you? We have to have a regular shrink because the operations we do cause the database to expand a lot and customers want their disk space back. We have to shrink tempdb because the operations we do cause it to grow continually. None of these are valid reasons for regularly shrinking databases, and in fact it’s documented in KB article 307487 that shrinking tempdb when there’s user activity can lead to tempdb corruption, and the Working with Tempdb in SQL Server 2005 whi...Read the rest of entry >>

Question: I get the idea of filtered indexes but I can’t see a good use to a UNIQUE filtered index? When would that be beneficial? Answer: Actually, I’ve got a fairly straightforward and simple example that might help solve what used to be a common frustration around allowing multiple NULL values in a column where you want to enforce uniqueness (over the non-NULL values). However, I’ll expose a bit of a [minor] limitation if you use it. Imagine having a Employees table where you’ve chosen to create a contrived (identity) column as the EmployeeID. You also want to have an alternate key for the SSN. However, you’d like to allow NULLs in the SSN column. If you create a unique constraint or a unique index on the SSN column, you can insert only one row that has a NULL value for SSN. So, this probably isn’t what you want! Prior to SQL Server 2008 the solution was to create an indexed view to handle this requirement. But, with filtered indexes it becomes even easier: CREATE UNIQUE INDEX EmployeeSSN_UInd ON Employee (SSN) WHERE SSN IS NOT NULL However, there’s a catch. If you need to reference this column from another table – you can’t. SQL Server allows a foreign key to reference any primary key, unique key or unique index – as long as it’s not filtered. This is a bummer IMO. If you don’t need to reference the column then a unique filtered index can be a nice way to handle enforcing uniqueness over your non-NULL values! But, if you need a foreign key then this option won’t work for you. Finally, don’t forget that filtered indexes have the same rules around session settings as indexed views have. This is important to know because applications that connect to SQL Server without the correct session settings will actually fail on INSERT, DELETE or UPDATE. And, queries that don’t have the correct session settings won’t be able to leverage the filtered index (for performance). For more information see the books online topic: Set Options that Affect Results. T...Read the rest of entry >>