October Question: What one thing do you feel has the greatest impact on SQL Server’s performance?
Question of the Month | October 1, 2011 | 10:30 amWhen it comes to databases, many DBAs obsess over performance, and for good reason, as when user’s begain to complain about slow performance, it is the DBA who is often blamed. So, for this month’s question, “Tell us what one thing do you feel has the greatest impact on SQL Server’s performance?”
Post your responses to the SQL Server Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.
Because there is no right or wrong answer, this month’s winner will be selected randomly from all the entries that are received.
|
This Month’s Prizes
|
The prizes this month are an Amazon.com voucher worth US $50.00 and a license for SQL Monitor 2.3, with one year of support and upgrades. SQL Monitor is a web-based monitoring and alerting tool which enables you to keep an eye on SQL server performance in real time on desktop machines and mobile devices. To find out more and see a live demo of SQL Monitor, visit http://www.thefutureofmonitoring.com/. |
- March Question: What’s Your Biggest SQL Server Performance Headache?
- June Question: How Do You Monitor Your SQL Servers?
- December Question: What does it mean to be a Proactive DBA?
- November Question: What steps do you include in your database maintenance?
- October Question: Tell us about your favorite experience as a participant in the SQL Server community.
RSS
Twitter
Linked In
Appropriate indexing. Big systems or small, no matter where your server’s limitations are, having good indexes for the workload is always the biggest performance win.
Budget.
Well, in my experience, there are limits to how much performance you can squeeze out of old hardware, so good hardware is essential. But no matter how much money you’ve thrown at the ‘Tin’ it still can’t beat good DB design. Smart use of indexes, statistics, Optimised query plans and caches, these are where the real performance benefits lie.
What I’m seeing on the servers in my shop is misconfigured tempdb. Could be that it’s not on a separate drive or there are not enough .mdf files for the number of processors.
Poorly performing queries.
Indexing, indexing and indexing!
Can’t count the number of times someone has created and application and tested it with a small amount of data and everything runs fine. When it gets into production and many users are using it and there are thousands of records, it just crawls.
Application architecture/design has the biggest impact on performance in my experience – over and under normalized models that are created independent of any testing of real-world datasets. And, once they’re in place, getting the apps team to refactor is a real challenge.
When it comes to having developers think about performance, the earlier the better.
I think it all start with a good database design, then having a robust architecture to support and be able to handle the load.
I find indexes by far have the greatest impact on database performance. Next I would say DB design, then hardware.
Even a small 3 gig db with with a few million records will crawl without the right indexes.
For hardware — lack of memory.
For DB Design — lack of proper indexes.
I have read many people state “Good Database Design”. While I agree that database design does affect performance. I question what specifically in the database design effects performance. I have also read some comments on hardware. I agree hardware does make an impact but in my experience I have found most machines are severely underutilized. I submit, schema design drastically influences performance and ease of use to retrieve data.
To have accurate INDEX is the most important and certainly the first encountered when an application grows from small to large.
Attention to detail and a committed DBA (or team). and somebody to support you and your research (such as it dept or service owner).
How about parameter sniffing? It causes a specific, not a generic execution plan.
Jus to add something that’s not been said already (which I pretty much agree with y’all).
application that uses SA, sa with blank password or that users have DBO access.
Yes I put this into performance category because performance in SLA with RPO / RTO is important too.
I’ve had 2 cases where I had to do an upgrade of 3rd party app with a small group of 3-5 people working on the upgrade. I said no problem, here’s the db into DBO only mode (because I couldn’t go with single user).
We figured out we had an issue when our data validations where failing because users were doing transactions while we were doing the upgrade. Long story short we busted the upgrade window by 2-3 hours.
When you have 50-80 people not working because of that, it gets real pricey real fast!
Parallel execution plans causing random spikes in execution time – e.g. a piece of SQL that returns in under a second, but when compiled in a stored procedure was still going 45 minutes later. The only difference? The stored procedure was using a parallel plan, the straight SQL wasn’t. Turn off parallel execution plans, and everything runs fine!
(SQL 2005, SP3, fully patched).
I think that the biggest impact of SQL Server Performance is the USER. In an Ideal world our SQL environments would be perfect without users.
Users come in all shapes and sizes (Developers, App Developers, Business Process Owners, Project Managers and Code Rustlers).
You cannot control the user. You cannot control the business led demands and priorities.
The suggestions I’ve read so-far is about indexes and other deep level answers; it’s simply the users that cause us to create the index. It’s the user who generates the growth. It’s the user (SQL Developer) who designs the database schema to suit their customers requirements. It’s the user who wants the path to success to be of little resistance.
DBA’s are the resistance against the user demands.
1. Right db design.
2. Properly configured instances(s)
3. Accurate indexes.
4. Good maintenance plans for index rebuild and statistics.
Disk I/O has a huge impact on performance and does good indexes and file management in combination with it.
Not using indexes in T-SQL code.
Lack of indexes.
Unused indexes.
Size of Indexes.
All together Indexes will be a ongoing performance hinderence.
I am having errors with SQL 2000 Server is also the time when the NOLOCK. This error is encountered, the application unusable. If we’re lucky, if we mistake early in the morning, yesterday evening from the backup, restore, I’m doing. If, at the end of office hours if I have this error with the DBCC commands to rebuild, if necessary, with the loss of data by taking the eye made a good repair. But enough is enough, this problem needs to retrace. Monitor performance profiler from all of these reasons, I need to take meaningful results. The problem is that previously used the performance monitor. In simplest terms, the Disk, RAM, CPU, I need an example for me to active counters. Would you assist me? As for your question, I am the most important factor affecting the performance of the SQL server ce, him in his creators. Because the SQL without the problem of performance would not have to talk about, so performance problems with SQL in an SQL application by the Microsoft Programmer’s the main factor. SQL uncontrolled pitbul, a kamikaze ground is not good
Tempdb management – especially when dealing with an application that you cannot change and it does not have the best design or coding practices in place. Reviewing and monitoring the certain locks on tempdb can guide you on how many files you should have in place as well as using some simple rules so that tempdb can reuse temp tables rather than building new every time which uses which can free up resources (thanks for that tip to Eddie W). Have tempdb set up to build out to a reasonable size and set your file growth to a good increment so that it’s not always autogrowing after a reboot, etc.
The Full Stack
- SQL server
- Windows
- cpu
- pci bus
- I/O controller / HBA
- Cabling
- Array
- Cache
- Spindle
see for more information from SQLCat in the presentation Designing High Performance I/O Systems for SQL Server
There are so many factors affecting performance; I/O, memory, network, design just to name a few. But if I had to select just one I would have to say proper design of the database. And from there I mean, normalization and indexing. No matter how robust your hardware is if the database is not designed well, it will not function well.
Indexing both too few (missing) and too many… why index fields where we never or rarely every query against.
Poorly written SQL by some developers (sorry and developers out there).
Bad (poor) architecture design.. Droping all tables,indexes and all other objects into the Primary filegroup
Poorly written SQL.
The single biggest reason for poorly performing SQL is lack of experience or education. This leads to poor configuration of the server, too many indexes, bad indexes, poor design, ineffective maintenance, etc.
Improper configuration – indexes, RAID arrays, and improper configuration of memory usage that causes the OS to swap memory to disk immediately come to mind, but many other options are out there
And poor queries – nested loops, joins, etc… you can throw all the hardware you want at a SQL server running poorly designed queries and they’re only going to improve so much, if the query is properly designed and written (which usually takes much longer than coming up with just some query that return what your looking for in the first place) performance gains can be far greater than any amount of hardware you can throw at the problem.
In our environment it is hard to pick between insufficient IO subsystems and poorly designed queries, but I think I have to go with queries – lots of locking and blocking inside third party applications that do not properly handle themselves.
The DBA\Architect – It really depends how knowledgeable is your DBA\Architect. Also he can convince the boss for budgeting needs.
The Design – Conceptual, Logical and physical database design. Especially how you handle BLOBS, Structured and unstructured Data.
Hardware – Storage, CPU and Memory and your Network.
DB File Placement – Storage design for tempdb, OS, Log files and DB files.
The SQL Developers – How skill full is your ‘SET Based’ developers. This limits evil cursors, dynamic queries, triggers, loops etc.
SQL Server Version – Only Enterprise edition supports Online Indexing , Indexed Views, Partitioning etc
DB Maintenance – How frequent you are doing the maintenance on Indexes & Statistics.
The design, often, at least in small outfits, this is limited heavily by budget, both financially and in the amount of time available to design and build a working database.
Surprise surprise when 6 months from the initial build the database has to be revisited to heavily alter to get reasonable performance.
There is no right or wrong answer here but I would have to say the Query Optimizer. The Query Optimizer is responsible for how your queries go from a SQL statement submitted to SQL Server, all the way to the selected execution plan including parsing, binding, simplification, trivial plan and the full optimization stages. Granted structures like indexes and statistics play a big role in the Optimizer choosing an optimum execution plan but the shear amount of work the Optimizer goes through gets my vote!
wow – so many combinations.
From experience, usually Disk IO
I agree with many of these comments. Indexing! Indexing! Indexing!
I oversee our reporting databases with millions of records. Queries without proper indexing can take hours to run, but after indexing, only a matter of minutes. On the contrary, the other performance hit that I experience, are UPDATEs on large tables with many indexes. After removing the indexes, UPDATEs that took hours, take only minutes.
Poor database design.
Indexes.
Just one thing is hard. But I’ll go with Indexes. It has the greatest impact to performance.
So many things can impact SQL Server’s performance, but I think the most fundamental thing above all of it good schema design and well written queries.
Ignorance… The question statement is correct in that there is no right or wrong answer, because what makes the greatest difference is fixing what is “wrong” with an end-to-end solution.
So, whether it’s the hidden costs of an ORM framework, or unnecessary round-trips, or badly designed queries, or missing indexes, the greatest impact on performance is not knowing what is having the greatest impact on performance.
I agree with what everyone else has pointed out, indexing, IO, good design, etc. I’d add developers or users who write bad queries because they don’t know better. Most don’t know what impact SELECT * can have. I’d like to train everyone in how to read execution plans to write better queries.
The SQL code.
Indexes are easy (ish) to fix, and while far from perfect the missing index DMVs and DTA help in that regard. However if the code’s been written in such a way that SQL can’t use indexes (nonSARGable predicates, implicit conversions) or in ways that send the optimiser for a loop (like catch-all queries), no amount of indexing is going to help matters.
Indexes are easy enough to fix and a new index isn’t going to suddenly result in a procedure returning incorrect results. Rewriting a 3000 line long procedure that’s full of cursors, temp tables, procedural type code, implicit conversions and nonSARGable predicates? That’s going to take time, time to write, time to debug, time to test and there’s an inherent risk in doing so that adding indexes doesn’t have.
Too many developers…not enough database assassins
I believe database design is going to be high on the list.
Thanks for everyone who contributed to this month’s question. The randomly selected winner this month is Eddie, who will receive a $50 Amazon.com gift certificate and a license for SQL Monitor 2.3. Please participate in the November question of the month.