10 Must Read SQL Server FAQs

Transparent Data Encryption

Q: What is TDE and why would I want to use it?

A: TDE was first introduced with SQL Server 2008, and it protects your data at rest by performing real-time I/O encryption and decryption of a SQL Server database’s data and log files. One of the biggest benefits of TDE is that the SQL Server engine handles all of the encryption and decryption work. Continue reading...

SQL Server and PowerShell

Q:Why don't SQL Server cmdlets run from the PowerShell command line? When I try to use the Invoke-Sqlcmd cmdlet I get the error "The term 'Invoke-Sqlcmd' is not recognized as a cmdlet."

A: Before you can use the SQL Server PowerShell Provider, you need to import the sqlps module into your Windows Server PowerShell environment. The easiest way to do so is to just type sqlps at either the Windows Command Shell or the PowerShell command prompt. Continue reading...

When a Delete isn’t Really a Delete

Q:When I delete lots of data from a table with a clustered index using a DELETE statement, the table size isn’t reduced like I would expect it to be. The DELETE transaction commits without errors. It seems like the space from the deleted records is freed up randomly. Can you explain this behavior?

A: What you’re experiencing is the mechanism SQL Server uses to delete records—it’s called ghosting. When a record in a table with a clustered index is deleted, it isn’t actually deleted right away. Continue reading...

When Do Checkpoints Occur for tempdb?

Q:I’ve just started monitoring the health of a number of SQL Server instances and I’m seeing some strange behavior with tempdb. One of the counters I’m monitoring is the Percent Log Used in the Databases perfmon object. For normal databases it fluctuates but for tempdb on one server it seems to just increase and never drop. What’s going on?

A: The answer is that log management is different for tempdb than for all other databases. For those who don’t know, a checkpoint operation occurs regularly in all databases except tempdb. Continue reading...

In-Memory OLTP Engine

Q:How does the In-Memory OTLP Engine improve performance?

A: The short answer is that in-memory data access is an order of magnitude faster than disk-backed data access. As its name implies, the In-Memory OLTP Engine lets you move selected tables into memory. The memory-optimized tables are fully durable and transactional. The In-Memory OTLP Engine is designed for high concurrency, and a new optimistic concurrency control mechanism handles locking issues. Continue reading...

Visual Studio Online

Q:What is Visual Studio Online?

A: Visual Studio Online isn’t a hosted or web-based version of the Visual Studio desktop code IDE. Visual Studio Online is the updated version of Team Foundation Service. Visual Studio Online provides developers with a hosted team development platform and code repository and eliminates the need to set up any on-premises infrastructure to support team development. Continue reading...

Sizing In-Memory OLTP Hash Indexes

A: SQL Server 2014 has indexes used for point lookups of individual rows. Microsoft recommends that you size them at 1x to 2x the number for rows that you expect to have in the table. Continue reading...

Recovering Database with Missing Transaction Log

Q:We had a SAN problem over the weekend and the upshot is that one of our main databases was shut down with open transactions, but we’ve lost the log file. The last working backup is two weeks old. Is there any way to recover the database without having to resort to the old backup?

A: Yes, but not without consequences.Usually when a database has open transactions and the server crashes, crash recovery will run on the affected database and roll back the open transactions. This prevents the partial effects of transactions being present in the database. If the transaction log is not available when SQL Server starts, the database will be in the SUSPECT state. Continue reading...

Generating a TPC-C OLTP Workload

Q:Are there any free tools that I can use to generate a TPC-like workload on my SQL Server system?

A: Yes. HammerDB is a free open source database load testing tool. HammerDB can be run fully automated and it can produce a TPC-C like OLTP workload, as well as a TPC-H data warehousing workload. Continue reading...

SQL Server Virtualization

Q:How does virtualization help with server consolidation and operations in general?

A: First, because virtualization technology abstracts the server or OS layer from the underlying hardware, it’s easier to move a server to different hardware for better scalability or for disaster recovery. Continue reading...