Performance impact of TDE

Question: What is the performance impact of enabling Transparent Data Encryption (TDE) on SQL Server database query performance? How can I measure the performance impact ? Answer: There is a general outline about the pros and cons of different Encryption on Encrypting data in SQL Server . So once you\ or your organisation has made the decision to enable TDE for SQL Server databases . it's time to assess the impact : performance , volume and DBA management overhead. According to the Microsoft documentation "TDE has an estimated performance impact around 3-5% and can be much lower if most of... Read more →

Should Developers manage database read status

A programmer asked me if he can include the a function to change the database status out of READ_ONLY , add some data and then put it back into READ_ONLY. These are the basic commands ALTER DATABASE database-name SET READ_ONLY ALTER DATABASE database-name SET READ_WRITE There are some considerations for deciding if a Developer should be able to include as part of an ETL process , the capacity to change the READ STATE of a SQL database 1) Requires ALTER permission on the database. This is an elevated privilege - and may break the organisations sql server security policy 2)... Read more →

Day 2 Operations - Are you ready?

Question: I was in a meeting yesterday and heard a new term - "Day 2 Operations". In the context of the conversation Iit made sense, but I'd never heard it. Is this a formal concept? What are the specifics? Answer: In general terms - Day 2 operations refers to features of a product or platform which are in an operational state. Thinking about Day 2 operations as part of a lifecycle - we'd be discussing product usage in the advanced stages of the lifecycle, Day 0 -customer requirements -Architectural Design Day 1 -Build -Deployment -Best practises configuration Day 2 -Operations... Read more →

Most important SQL Server wait types

Before I discuss the most important wait type list , I want to say that really there is no definitive list of most important sql wait types. Understanding what is normal and the context of the wait type is essential when discussing wait types. For example , you may see the sql wait type WRITELOG as high on the list of waits on a SQL Server. This may or may not be important depending on the "normal" behaviour. Benchmarking your systems is a great way of understanding the patterns your systems. Based on monitoring and analysing various SQL Server performance... Read more →

SELECT 1 FROM TABLE

Question: I'm working on an Enterprise application upgrade and have hit some problems. As part of the debugging process I'm inspecting the sql queries executing. The program is currently running SELECT 1 FROM TABLE. (replace TABLE with actual table name). What is the purpose of SELECT 1 FROM TABLE ? Answer: The SELECT 1 FROM TABLE technique is often used in certain products. The first question is what does this sql statement actually do? When you execute SELECT 1 FROM TABLE , there will be a Scan , because the purpose of SELECT 1 FROM TABLE is to return a... Read more →