Should I use SQL Change Data Capture for data audit ?

Question: There is lots of information around the SQL Server Change Data Capture (CDC) feature, and I’m trying to decide if CDC is suitable for the purposes of a project which requires data auditing. We currently have a sql server triggers based solution in place for capturing data changes on certain tables. Could you outline some the issues to consider when deciding if CDC is an appropriate tool. Answer: Deciding on when to adopt CDC is dependant on the specific requirements. The first step is to understand in detail the project requirements. As you mentioned in your question, there are... Read more →

SQL Server 100 cpu diagnose

Question: Users are reporting long delays in query responses. I checked on the server and noticed CPU counter at 100 % on Performance Monitor. There are 4 cores associated to this server and all the cores were hovering around 100%. How can I diagnose the underlying cause ? Answer: It’s a common problem faced by DBAs , where the server is sitting at 100% for prolonged periods. Check there are no other processes outside of SQL Server which are causing the server to be at 100%. Typically security scans, virus scans and monitoring can be root causes. I have also... Read more →

ASYNC_IO_COMPLETION during sql server backup

Question: I’m getting a very high level of ASYNC_IO_COMPLETION during a SQL Server backup schedule. These are very small databases , usually taking a few minutes, but now taking over 1 hr each. My suspicion is it’s an underlying storage issue, but I need a straightforward method to show the storage guys. Answer: This is one of those situations where it’s difficult to present evidence to the storage team . The usual definition of ASYNC_IO_COMPLETION is SQL Server Books online defines Async_io_completion as “Occurs when a task is waiting for I/Os to finish “. Read more on SQL Server Async_io_completion... Read more →

How Busy is my SQL Server

When managing large SQL Server Inventories , it’s important for me to measure the level of batch requests on a SQL Server. Knowing this figure I can track changes over time. It will give you a measurable method to track impact of changes . It will also give you statistical feedback , which is used to make decisions about these servers. Some typical DBA decisions could be: 1) Should I keep this SQL Server on a physical or VM ? Read more on Virtualization – and database servers and Database Virtual Server Candidacy Criteria 2) Should I distribute some of... Read more →

A 5 MB hard drive in 1956 needed a forklift

The first super computer with a hard disk (HDD) was launched in 1956. It weighed more than 1 ton , storing 5 MB data. Check out the forklift. Read more →