For years, I’ve recommended data and log files reside on separate drives for performance reasons. But given today’s flash arrays, virtualization, and much of the storage subsystem being abstracted, is separating data and log files still important? Short answer: yes, it should be, but don’t stop there. Long answer: it depends. You should review multiple…
Read more
I’m my last post on SQL Server Setting, I discussed MAXDOP and how it can affect SQL Server performance. In this post, I’ll review another SQL Server configuration setting that can impact performance – Optimize for Ad-hoc Workloads. What is the plan cache? Every time you run a query in SQL Server, an execution plan…
Read more
Recently, we discussed the role of the recovery model in establishing how SQL Server manages database transaction logs. But what is the SQL Server log composed of? How does the logging process work? In this post, we will dissect the SQL Server transaction log to uncover its core anatomy. First, what is the transaction log?…
Read more
As a DBA, one of the most critical aspects of managing SQL Server is ensuring the integrity and reliability of database backups. SQL Server’s native backup functionalities offer robust tools for securing your data, particularly for databases utilizing the full recovery model. However, mixing SQL Server native backups with disk or VM snapshots can lead…
Read more
Did you know that not configuring the MAXDOP setting in SQL Server properly could cause performance problems for your queries?
When we meet with clients for an initial SQL Server Health Check, we’re sometimes asked what a SQL Server recovery model is. Once explained, the natural follow up question often is: well, then which recovery model should we use? In this post, we will address both of these important questions. What is a SQL Server…
Read more
Deadlocks in SQL Server are more than just a nuisance, they can be problematic, and even lead to data loss and inconsistencies.
“Why is my SQL Server slow?” Have you ever asked that question? Have your users? Unfortunately, that’s a commonly asked question. Fortunately, though, something can usually be done about it. Here are fourteen reasons a SQL Server instance could be underperforming. I’ve divided them into four broad categories. Slowness due to resource constraints Often the…
Read more
SQL Server is good. You install it, give it some databases to manage, and let it do it’s thing. When queries come in, it figures out how to resolve them. If it needs some statistics, it creates them. If the database is running out of space, it’ll grow the data file for you. Automatically. And…
Read more
If you’ve worked with SQL Server for very long, you’re probably familiar with the concept of indexes. Indexes can help speed up queries by allowing SQL Server to quickly locate specific data values within a table. Indexes are typically much smaller than their underlying tables so they can be searched much more efficiently. (They aren’t…
Read more
Recent Comments