cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

The Top 5 Most Common Problems With SQL Server

Level 17

do-not-enter-rz-lg.jpg

I've been working with SQL Server since what seems like forever ++1. The truth is I haven't been a production DBA in more than 6 years (I work in marketing now, in case you didn't know). That means I will soon hit a point in my life where I will be an ex-DBA for the same period of time as I was a production DBA (about seven years). I am fortunate that I still work with SQL Server daily, and consulted from time to time on various projects and performance troubleshooting. It helps keep my skills sharp. I also get to continue to build content as part of my current role, which is a wonderful thing because one of the best ways to learn something is to try to teach it to others. All of this means that over the years I've been able to compile a list of issues that I would consider to be common with SQL Server (and other database platforms like Oracle, no platform is immune to such issues). These are the issues that are often avoidable but not always easy to fix once they have become a problem. The trick for senior administrators such as myself is to help teams understand the costs, benefits, and risks of their application design options so as to avoid these common problems. So, here is my list of the top 5 most common problems with SQL Server.

Indexes

Indexes are the number one cause of problems with SQL Server. That doesn't mean SQL Server doesn't do indexes well. These days SQL Server does indexing quite well, actually. No, the issue with indexes and SQL Server have to do with how easy it easy for users to make mistakes with regards to indexing. Missing indexes, wrong indexes, too many indexes, outdated statistics, or a lack of index maintenance are all common issues for users with little to no experience (what we lovingly call 'accidental DBAs'). I know, this area covers a LOT of ground. The truth is that with a little bit of regular maintenance a lot of these issues disappear. Keep in mind that your end-users don't get alerted that the issue is with indexing. They just know that their queries are taking too long, and that's when your phone rings. It's up to you to know and understand how indexing works and how to design proper maintenance.

Poor design decisions

Everyone agrees that great database performance starts with great database design. Yet we still have issues with poor datatype choices, the use of nested views, lack of data archiving, and relational databases with no primary or foreign keys defined. Seriously. No keys defined. At all. You might as well have a bunch of Excel spreadsheets tied together with PowerShell, deploy them to a bunch of cluster nodes with flash drives and terabytes of RAM, and then market that as PowerNoSQL. You're welcome. It can quite difficult to make changes to a system once it has been deployed to production, making poor design choices something that can linger for years. And that bad design often forces developers to make decisions that end up with...

Bad Code

Of course saying 'bad code' is subjective. Each of us has a different definition of bad. To me the phrase 'bad code' covers examples such as unnecessary cursors, incorrect WHERE clauses, and a reliance on user-defined functions (because T-SQL should work similar to C++, apparently). Bad code on top of bad design will lead to concurrency issues, resulting in things like blocking, locking, and deadlocks. Because of the combination of bad code on top of poor design there has been a significant push to make the querying of a database something that can be automated to some degree. The end result has been a rise in the use of...

ORMs

Object-Relational Mapping (ORM) tools have been around for a while now. I often refer to such tools as code-first generators. When used properly they can work well. Unfortunately they often are not used properly, with the result being bad performance and wasted resources. ORMs are so frequent a problem that it has become easy to identify that they are the culprit. It's like instead of wiping their fingerprints from a crime scene the ORM will instead find a way to leave fingerprints, hair, and blood behind, just to be certain we know it is them. You can find lots of blog entries on the internet regarding performance problems with ORMs. One of my favorites is this one, which provides a summary of all the ways something can go wrong with an ORM deployment.

Default configurations

Because it's easy to click 'Next, Next, OK' and install SQL Server without any understanding about the default configuration options. This is also true for folks that have virtualized instances of SQL Server, because there's a good chance the server admins also choose some default options that may not be best for SQL Server. Things like MAXDOP, tempdb configuration, transaction log placement and sizing, and default filegrowth are all examples of options that you can configure before turning over the server to your end users.

The above list of five items is not scientific by any means, these are the problem that I find to be the most common. Think of them as buckets. When you are presented with troubleshooting performance, or even reviewing a design, these buckets help you to rule out the common issues and allow you to then sharpen your focus.

44 Comments

sqlrockstar​, thanks so much for this!

I'm a neophyte DBA. By that I mean I started as an 'Accidental DBA' and have learned some of SQL Server's inner workings over the past couple of years, but I still have so much to learn. These tips will defanately help me ensure that the SQL instances I deploy Orion on are better optimised going forward.

Incidentally, I have 'learn SQL' on my to-do list for the next 12 months. Any particular set of books you could recommend?

MVP
MVP

sqlrockstar Excellent writeup with links to more info on particular points made !!

Level 14

sqlrockstar​ Thanks for this writeup... I too am an "Accidental DBA"  like  silverbacksays​.... This help a lot!!!

Level 17

Welcome!

Level 17

Thanks!

Level 17

I have a library of books on my blog: SQL Server Books

There may be something there that would be useful. I need to update it a bit for SQL 2016 and for some data analytics, too. If you have a specific question about learning anything SQL, or want info on what book authors you should read, or bloggers to follow (SQLRockstar Blogger Rankings ) just ask and I will do my best to point you in the right direction.

Level 14

Thank you.. will definitely take a look at the list of books on the blog...

Appreciate it!!

Level 15

Great article.

Continuing happiness--thank you.

Level 17

Thanks!

Level 17

Welcome!

Level 13

Great article and ditto on the "Accidental DBA".  It's amazing how that happens..... 

Level 12

I was an Accidental DBA for years. They finally hired a Systems guy that knew just a little more than I did, so I got to go back to being a Network guy. I was happier, and so were the databases!

Level 17

It happens a lot.

Level 17

Nice! Funny thing is that I see many DBAs about to find themselves as accidental network admins as hybrid IT becomes more common.

MVP
MVP

I am not a DBA ... welll .. I am the janitor .. and the information and references will help me dearly ...... I am very proud of the Network - it screams (thanks to SolarWinds ... ok I am the NA!) - every now and then, our Application "freezes" for a second or two.... like when the SQL Transaction log is being backed up!!!   I am using the Confio Ignite (need to upgrade/ migrate to SW) - I am learning a lot about the way SQL runs... and am getting ready to start hammering on the DBA to refine and better manage the database.  The monitoring tool has saved me many times ... I no longer have to defend the network... cause ... IT AINT THE NETWORK!!!   I appreciate you sharing the great information and additional resources.  I do appreciate your time and effort!

Level 17

Thanks for the kind words about Ignite. I loved that tool so much I went to work for the company!

Level 14

Passing this article on to our SQL bubba's.  Good read.

We are blessed with a solid SQL team here.  They are constantly optimizing out indexes.

Level 12

The article title could just be "THE TOP 5 MOST COMMON PROBLEMS WITH DATABASES", as those issues apply to DB2, Oracle, MySQL just as much as SQL Server

I would add a 6th item - Business Intelligence (there's an oxymoron for you) Reporting Tools (Cognos, I'm looking at you). Gives end users a way to generate the most inefficient SQL code known to mankind.

Level 17

Thanks!

Level 17

Yeah, it's nice to have a good team of DBAs on hand.

Level 17

Yep, agreed. I should find a way to include those tools with the ORM section. Thanks!

Level 20

Many would do themselves a favor before just creating a database to learn about:

An Introduction to Database Normalization — Mike Hillyer's Personal Webspace

3 Normal Forms Database Tutorial

A Simple Guide to Five Normal Forms in Relational Database Theory

Planning the organization of the data from the beginning makes everything work better down the road.  Basically poor design decisions will burn you in database realm.  In computer science some people spend their careers doing normalization.  This was never that interesting to me but it's a super valuable skill when it comes to designing a database well.

Level 12

Thanks for the article. I've definitely been the accidental DBA. I'm lucky enough to work with an excellent DBA team now. Working with these guys makes me realize how much I was missing before!

What are your thoughts about the introduction of in-memory computing? Such as SAP's HANA. SAP is pushing their customers to migrate their entire ERP onto HANA whereas when HANA was first released it was only used for analytics.

Just wait until they merge hardware and firmware with our wetware, and this is done by thought.  Talk about change management and tracking issues!

Level 17

I love the idea of in-memory computing. It's the latest trend I see as an effort to improve query times and make it appear that the platform is as fast (or faster) than any other platform available. I do like to talk about things such as disaster recovery. Because memory is volatile I tend to think about things like "what happens if the power goes out in the middle of a transaction" and stuff like that. I think SAP is trying to play catch-up to other vendors right now so it makes sense for them to push into this area.

Level 17

Understanding normalization is a good thing, I believe. If nothing else it helps you to understand the times when de-normalization is necessary. But with hardware advances happening at a faster pace with each passing day it would seem that even the most poorly designed databases can perform well, to a point.

Level 21

Thanks for the great write-up!  SQL Server performance is one of the biggest challenges we run into with our clients.  Just about every client that comes to us with application performance issues has had significant issues with their databases and after significant optimization we have seen drastic improvements.  Our monitoring tools have been key in looking at their databases to identify these problems.

Level 17

That's great to hear, and even better if one of those tools is DPA! The database gets blamed often (IMO), but with so many layers between an end-user and their data it makes accurate troubleshooting difficult at times. We've gone from being mechanics to being F1 race car crew.

Level 20

One thing I've noticed App Insight for SQL Server puts a pretty big load on the SQL Server if it's already busy.

MVP
MVP

Tools of that sort do have a load.  There is a tool for Oracle called "precise".

Great tool for troubleshooting, analytics, and general DB monitoring.  But as you turn up the detail or microscope you also turn up the load on the DB.

The same should be true for any other similar tool.  All monitoring comes at a price of additional load...where and how much load depends on the tool.

Level 17

Yes, that can happen. I've also seen server instances with little to no workload have monitoring tools installed, and the tool can then be shown to be majority of resource usage but only because it's the only thing running!

Level 21

sqlrockstar​ unfortunately it isn't DPA, at least not yet; I have certainly advocated for it.  With that being said, AppInsight for SQL is what we use in most cases and from there our DBA is able to do the rest directly in SQL Server using the tools that come with it.  The problems that we most often see are much more basic than you would expect such as index fragmentation or SQL using all the memory and suffocating the OS.

Level 17

OK, let me know if you need help or if you have any question in general.

MVP
MVP

Good information, thanks for the post.

Level 9

"unnecessary cursors"

Ugh. Cursors are almost never necessary. Fundamentally they break good query design practices. 99.9(999)% of the time they are just lazy shortcuts. Way back in the day when the bulk of our code was written, the contractors we brought in used cursors everywhere. Cursors are what happens when developers are either lazy or short on time and are more comfortable thinking in loops rather than sets.

Last year, I rewrote one of our apps from scratch to bring it from VB 6 to .NET. The back end tSQL had a cursor that called another stored procedure on each row of data which itself used a cursor to select data into variables which were then used in the WHERE statement to SELECT INTO a temp table. It was so ugly and incredibly slow. I ended up needing to rewrite both the front end application and the back end queries.

(PowerNoSQL is hilarious. Hopefully someone makes an implementation.)

Level 17

It depends upon the system you are working with. Some platforms, such as Oracle, can work well with cursors and row-by-row processing. The issue is when that application code is ported over to a new platform, such as SQL Server, when set-based operations can be many times faster. There are many times when cursors are wanted/needed, the trick is to understand when they are appropriate. But for many, RBAR (Row By Agonizing Row) is all they know, so cursors are all they create.

Level 9

Good points; I did not know that about Oracle. And it actually makes sense to use a cursor if you ​need to call a stored procedure on every row. In my case though, the SP it called wasn't used by anything else; it basically was an even worse way of doing nested cursors. And all hyperbole aside, it's really the nested cursors (or nested cursors inside of nested SPs) that cause most of our headaches because they're just used as a procedural way to do relatively simple set operations. When you're 3 SPs and 4 cursors deep something probably went seriously wrong in the design phase, especially if a couple joins and maybe a temp table could've done the same thing.

Level 17

Agreed, no question. When all you have is a hammer...

Level 12

Very informative. Thank you

Level 10

Good information!

MVP
MVP

Good information. Back in the day, there was always a separation between Database Admins and the network/server admins, etc. things are blending so much that we all are becoming DBAs. Some of us willingly with the joy of learning beaming in our faces, some of us kicking and screaming. (most somewhere in between)

About the Author
I've been in IT for almost 30 years beginning in the stockroom and working my way up through operations to help build and develop the Automated Operations Team at Radioshack before Enterprise Management was a cool thing. Working in several different shops over the years has exposed me to a number of different challenges regarding monitoring and alerting. I am a amateur radio operator, Skywarn spotter for the National Weather Service, and a volunteer firefighter in a rural county just West of Fort Worth.