15

I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the experts in the field.

One of my questions is:

What are the top 3 performance issues that you encounter with your SQL Server Instances and how do you identify those problems?

Particularly I am interested in the scripts and the tools used to measure this.

Improve this question

10 Answers 10

Reset to default
22

Off the top of my head - top 3 query problems:

  • Implicit conversions
  • Bad indexing strategies (too many or not enough or the wrong kind)
  • Using SELECT * instead of naming just the fields you need

There's a lot more around server-level config problems, database schema problems, hardware problems, etc. I wrote a script to quickly analyze servers looking for these kinds of problems:

http://www.brentozar.com/blitz/

Improve this answer
0
15
  • Poor design/queries/indexing
  • Not allowed to buy correct hardware
  • Braindead ORMs (a.k.a "SQL is dead")
Improve this answer
14

Not the top 3, but thought I would mention things not mentioned yet:

  1. SQL put on virtual machines with no details/transparency provided to the DBA. The host server will dynamically change the guest machines settings causing a drop in performance, and leaving the DBA without a clue. Features like hypertreading, network teaming, and memory ballooning makes the performance counters a moving target to monitor. Tools: sysmon/perfmon, DMVs, maintaining a history of performance counters in tables.
  2. Similarly, no transparency/verifiability on the SAN settings provided to the DBA. I have had LUNs with different read/write cache preferences set but told that they were all the same. Tools: IO DMVs, SQLIO.
  3. Bad DB architecture: like sizing and placement of the data and log files, and tempdb. Improper use of parallelism. Creating multiple filegroups on the same physical disks. Tools: experience.

Another tool that I am checking out now is Project Lucy. Seems neat.

Improve this answer
0
10
  • lacking proper indexes
  • use of with nolock in productioncode by someone to try to solve performance issues. Especially bad if the code modifies data in tables
  • application selecting more data than needed at more times than is necesary. Ex having a binary returned everytime even if you just want the textdata of same table.
Improve this answer
2
  • 2
    +1 for the nolock mention. Every developer I know think it is a good idea to use it because "it does not lock the table in readings"
    – tucaz
    Commented Aug 16, 2011 at 23:00
  • Don't you just hate it when one customer of yours bought THAT huge system for multimoney and first time you look at it they use with nolock everywhere? And then ... :-/
    – Martin Sjöberg
    Commented Aug 17, 2011 at 23:08
9

Queries that scale badly (get all orders for X years for all customers etc including all orderlines including summed data and other average data badly calculated)

Just querying everything at once.

Tables that contain 'descript' varchar/text fields that have to be searched through every query.

Improve this answer
7
  • Improper maintenance, i.e. no index reorgs, stats, no log backups
  • Missing indexes
  • Poorly written queries
Improve this answer
7
  • Poor database and application design
  • poor usage of platform advantages (developers wanted to have platform-intepended database-access code. no SPs, no functions etc)
  • bad indexing, of course.
Improve this answer
7
  • ad-hoc queries on prod data - yep, developers do believe it's necessary and some might even have access :-)
  • bad design of app that uses the database - eg: too much data added and never removed even if it's not necessary anymore (which leads to performance problems because backups grow big, maintenance tasks take longer..etc)
  • all database files on the same raid or worse, on the same drive (eg: system dbs, tempdb, user dbs all together on the same drive/raid)
Improve this answer
3
  • Poor database design
  • Poor Indexing Strategy (including too many indexes, missing indexes, and lack of index maintenance)
  • Poor hardware architectural decisions
Improve this answer
2

Indexing is critical to performance, but I've found most DBAs know that, and so it tends to be one of the first things that gets fixed through query optimization. The areas that often aren't well-addressed:

  1. Too many DB round-trips. Chattiness is one of the main performance problems I see.
  2. Getting the right transaction boundaries. Transacting every INSERT/UPDATE/DELETE can be a big performance killer.
  3. Failure to optimize the hardware side; particularly, putting the DB log on a different volume from DB data.

If I could add a fourth item to the list, it would be excessive and inappropriate use of triggers and/or cursors. Doesn't seem to happen too much these days, but when it does, it's painful from a performance perspective.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.