Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 primary posts
Index maintenance is confusing. Should you use maintenance plans or customized SQL Agent jobs? Join Kendra to learn the difference between index ‘rebuild’ and ‘reorganize’ commands, and how to avoid the pitfalls of maintenance plans in SQL Server.
https://www.youtube.com/watch?v=6nJKIeJC2lg
Performance tuning of independent software vendor (ISV) databases is a little tricky.
To understand who does what, let's think through all of the work required with building and hosting a database application:
Over the years, I've come up with some rather “interesting” answers to SQL Server problems including:
Warming up SQL Server's buffer pool by selecting all the data from all the tables
Creating indexes with triggers
Adding nonclustered indexes to clustered columnstore indexes
Compressing backups the hard way
Putting databases on a RAM drive
A while back, we posted about how SQL Server index fragmentation doesn't matter. That blog post generated a lot of heated discussion and has served to make people aware that fragmentation may not be the source of their woes. Sometimes, though, there are data access patterns where fragmentation really does matter. Perpetual Fragmentation Some data…
Making index changes in SQL Server is tricky: you immediately want to know if the new index helped your performance and if it's being used, but SQL Server execution plans and their related statistics can be are insanely confusing.
It can be useful to run sp_recompile after you create an index, but not necessarily for the reason you might think.
The only thing you ever need to use for database identity is an IDENTITY, right? Well, maybe. There are a lot of different options and they all have different pros and cons.
IDENTITY columns
The default way to identify objects in SQL Server is to use an INT or BIGINT column marked as an IDENTITY. This guarantees relatively sequential numbers, barring restarts and failed inserts. Using identity columns put the responsibility for creating and maintaining object identity in the database.
Let's say you've got a vendor application that creates tables, loads them, queries them, and drops them all the time. You can't change the code.
The tables involved desperately need an index in order to go faster, and they're your biggest performance bottleneck.
kCura Relativity is a software product for law firms to find interesting stuff to help their case. To get you up to speed on what it does, here's some of the posts I've written about Relativity in the past:
Performance Tuning kCura Relativity - explains what the product is for and how DBAs can help make it faster
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.
Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a "bad plan" gets compiled and starts to be reused. This is "bad" parameter sniffing. "Bad plans" can come in a few…
We've all found those databases. They're on your production SQL Server instance, but nobody seems to know if they're being used, or what's using them.
You could run some sort of trace, but you'd probably drag down performance on your SQL Server. What you need is a quicker way to find out if the database is in use.
Is a Login Using the database?
And if so, what query did they run last? My favorite way to see this is with Adam Machanic's sp_whoisactive.
Do you rely on index rebuilds to make queries run faster? Or do you always feel like statistics are "bad" and are the cause of your query problems? You're not alone-- it's easy to fall into the trap of always blaming fragmentation or statistics. Learn why these two tools aren't the answer to every problem…
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…
There's a couple of new features in SQL Server 2014 that provide options for how you work with temporary objects. Will inline index creation or memory optimized temporary tables forever change the way you code? Let's take a look! Inline Index Creation SQL Server 2014 brings us a TSQL improvement called "inline specification of CLUSTERED…
SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way - that limitation hasn't disappeared in 2014.) SQL Server 2014 brings clustered columnstore indexes, and they're…
kCura Relativity is an e-discovery program used by law firms to find evidence quickly. I've blogged about performance tuning Relativity, and today I'm going to go a little deeper to explain why DBAs have to be aware of Relativity database contents.
In Relativity, every workspace (case) lives in its own SQL Server database. That one database houses:
Temp tables are like real tables, just a little tricker.
When you're starting out writing TSQL, it's easy to want to do all your work in a single query. You learn about derived sub-queries, CROSS APPLY statements, and common table expressions. Suddenly, each of your queries is so complex that you hardly know what you're doing.
I first knew something was up when I looked at the job history for a simple maintenance plan. It had two steps:
Rebuild all the indexes in the database - this took 10 minutes each night.
Update statistics - this took 2-3 hours each night.
In theory, you can add indexes online with SQL Server Enterprise Edition.
In theory, with AlwaysOn Availability Groups, you can add and drop indexes on the primary replica whenever you want.
SQL Server has amazing instrumentation. Dynamic management views and functions give you great insight into what's happening in your SQL Server and what's slowing it down. Interpreting these views and functions takes a lot of time, but with practice and skill you can use them to become great at performance tuning.But nothing's perfect. Some of…
