When I start a SQL Server Critical Care with a client, the first thing I ask is, “What is your #1 pain point? Where does it hurt?” The answer I hear most often is, “The server is too slow”. I’m a runner. If I was able to run a 5K race at an 8:30 per…
I have a new mission: to convince you, the long-time Profiler user, to switch to Extended Events. I realize I have a long, difficult task ahead of me. Profiler has been around for a long time. You're comfortable with it. You know it's not perfect, but you have learned to live with its imperfections. Now I want you to step outside your comfort zone and learn a new tool, with new terminology, a new interface and new capabilities.
When Extended Events (XE) were released with SQL Server 2008, I was excited - something new to learn! I read Microsoft articles about them, read blog posts about them, and tried to use them. At that time, there was no GUI for XE. I had to create the sessions with T-SQL. That wasn't awful - I…
One of the many restore features in SQL Server is the ability to restore one or more pages of data. This can be very convenient in some narrow situations - for example, corruption occurs on one page or an oops update is made to one record. The page restore process is not straightforward, however, and,…
In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you need to format phone numbers a certain way, or parse for specific characters, you can do so using a function.
SQL Server Agent is one of my favorite tools - it allows you to automatically schedule jobs, alert you if things are going badly, and capture information into database tables. One common request is to execute a query and send the results via email on a regular basis. This task is a great match for SQL Server…
Let’s say you want to have an addition built on your house. You contact a contractor, who comes to your house, looks at your lot, looks at what exists, and asks you questions about exactly what you want. He then gives you an estimate of the work – approximately how long he thinks it will take his crew, what supplies will be needed, and the cost of those supplies.
What's a deadlock? Well, let's say there's a fight going on between Wonder Woman and Cheetah, and, in the same room, a fight between Batman and Mr. Freeze. Wonder Woman decides to help Batman by also attempting to throw her lasso around Mr. Freeze; Batman tries to help Wonder Woman by unleashing a rope from the grappling gun at Cheetah. The problem is that Wonder Woman already has a lock on her opponent, and Batman has his. This would be a superhero (and super) deadlock.
We recently got the following question for Kendra and Jes in the Brent Ozar Unlimited® mailbox:Six months ago I stumbled into the IT world. Do you have any advice for someone (a woman) starting off in the IT industry, specially someone without a computer science degree? I really enjoy working with databases and data. I…
While chatting with attendees before my Don’t Fear the Execution Plan webcast, a question was posed: “As a DBA who mainly worries about backups, index fragmentation and checkdb, should I also be looking at execution plans?”
YES! Here’s why.
Data compression can have many uses and advantages, but it also has its drawbacks. It’s definitely not a one-size-fits-all strategy. One of the things to be aware of is that initial compression of a table or index can take quite some time, and will be resource-intensive. It also is an offline operation, so the object…
It’s Friday afternoon. You, the DBA, are looking forward to a long weekend. You’re going to walk out the door in 30 minutes and enjoy some family time. The phone rings and you pick it up. On the other end, Joe Developer says, “There’s a query that’s been running for a long time. Can you kill it?”
“How many rows exist in a table?” It seems like such an innocent request. It isn’t too hard to get this information out of SQL Server. But before you open SSMS and whip out a quick query, understand that there are multiple methods to get this information out of SQL Server – and none of…
There will come a time when your database backups will not be as fast as you would like them to be. Over time, the volume of data you’re storing will grow, and backups will take longer – but your weekly or daily maintenance windows don’t grow as well. Backups can be resource-intensive, using I/O and network resources – and if they’re running when users are trying to work, the users may notice the performance impact.
It was a dark and stormy… Oh, wrong story. It was actually a warm, sunny afternoon in Charlotte, NC. I was presenting “Index Methods You’re Not Using” at PASS Summit. In this talk, I discussed how indexed views, filtered indexes, and compressed indexes can improve your query performance by reducing I/O.
From stage right, an intrepid audience member raised his hand and asked, “Can you think of an example of when you would use filtered indexes instead of partitioning?”
Views are logical objects in SQL Server databases that present you with a “virtual table”. Views are typically created for one of three reasons: security, simplification, or aggregation. Security: we create views so that a user can read specific columns out of certain tables, but not all the data. Simplification: sometimes, it’s easier to write…
Memory is one of the most-used resources in SQL Server. Generally, the more you have, the better query performance you'll get. This blog isn't about the magic of the buffer pool or the plan cache, but you should understand how important they are to your server’s performance. Since memory is such an important resource, you want to know how much of it you’re using at any time.
Let me ask you a question: do you want to boss the SQL Server query optimizer around?
If you answered no: good. You’re willing to let the query optimizer do its job, which is to find the least expensive way to run a query, as quickly as possible.
Problem: your database server’s performance stinks like Limburger cheese.
Solution: find the queries that are making that smell.
Hiding in Plain Sight
Your database server knows exactly which queries are performing the worst. When queries are executed, the execution plans – and associated execution statistics – are stored in memory (with a few exceptions). This information is stored in the plan cache, and you can access it by querying a couple of DMVs.
I could be talking about the tables in my database - I do make them skinny by using the right data types - but in this case I'm talking about SQL Server Reporting Services tables. You've seen them before. The average table is bland and boring, with nothing to distinguish it from the next, and no reason for you to remember the data in it.
