Guide as to how to go about SQL Server 2K performance tunning and optimization
-
Hi everyone,
I am in a new job where instead of programming with C# and .NET, we have been assigned task to tune the performance and optimise their SQL Serve 2K.
Can you guide me as to how to go about it? Wher should I start looking into for problems and how to decide on what is not good and how to proceed.
Sounds very basic but jumping so drastically from programming user interface to database optimization is a nightmare
(Anyways, now here I am to ask the pros for some help and guide me to resources, I can understand
Hope to get loads of replies.
Wishes and thanks,
Sree
-
I hesitate to give too much direct information because, unfortunately, there is no "silver bullet" to performance tuning. Tuning is one of those things where experience outweighs book knowledge. The best I can offer at this point is a pep talk to get this thread started. Then hopefully other people will chime in with resources that may have helped them.
I have inherited plenty of databases and processes, so I feel your pain. In order to keep from feeling overwhelmed, the first thing you need to be able to do is break all of the processes on the server into pieces. Then examine each piece and determine which is causing the most challenges. Concentrate on fixing that one issue, and then rinse and repeat. This concept is really no different than application coding. When I inherit something, I check 3 things first.
1) look at all of the TSQL code your applications use (procs, functions, etc) and make sure that the TSQL itself is efficient. Proper use of table variables instead of temp tables where necessary, effecient joins, making sure you don't use "select * from...", etc). Look at execution plans and run traces to help track down these types of issues.
2) look at the indexes that the procs use. I can't tell you how many times I've found poorly thought out indexes.
3) look at the server itself to make sure that there aren't any major I/O problems. Having an efficient database means squat if your hard drives or cpus are pegged out.
You are by no means guaranteed to fix any or all of your problems by looking at these things, but without the benefit of experience, it is a decent place to start looking. Also, keep in mind that a lot of performance issues are caused by several things working together to cause a problem. Search engines and discussion boards are your best friends. Until you are able to go off of your own experience, use the experience of others as a guide.
First, just dig into the database and applications that use them and determine what is running slowly. Then do some web searching and post specific problems you are having here. We will be more than happy to help you get your feet wet!
-Greg
-
Thanks a lot Greg.
I will start digging and posting subsequently if I go all cloudy and clueless
But now I know where to start, so I will be sure to finish
, counting on everyone's experience and building my own...Wishes
Sree
-
I'm actually in the process of doing pretty much the same thing. Being from the same background I know where you're coming from.
What I've done is start from what I'd consider the ground up by looking at the state of the server in general and working my way up to more specifics.
Essentially, I'm starting off by capturing performance counters for the server so that I can get an idea of what the hardware performance is. There are a number of resources for this and many of which are on this site and Microsoft's site as to what you should capture and where the results should be if your server has the resources it needs.
After I have a baseline for the hardware I can start looking at SQL Server itself by checking the sprocs, functions, etc for inefficient SQL as Greg said. It may be possible that poor code/indexes/database design could be causing some of your performance counters to get out of sorts, so be sure to keep this in mind. Being able to understand and evaluate execution plans is a big help here as well.
I think that being a developer is a big help here. Many developers are not that great at coding SQL or take the time to understand the right way to do things when dealing with a database, so being able to understand their code will go a long way in troubleshooting issues and recommending alternate solutions.
-
Humm....I have been playing around with few store procedures, to understand how to understand and evaluate store procedures. It is understandable for simple queries but sometimes I just get confused
Thanks a lot for a second opinion on how to start, all this gives me a direction to work for.
Thanks Mike and Greg.
More suggestions, view points and resources will be much appreciated
-
Start doing some homework first. On this site use the search feature for "tuning", "optimization", "performance", "join", "cursor". That ought to give you a pretty large start. Next visit http://www.sql-server-performance.com/ and kick around the site then use their search for the same terms.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy, where's that list you and I came up with many moons ago? I think step #1 was something like "Write good set based code"...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sreemati,
Most of the suggestions folks have given so far in this thread are spot on...
I'd like to add that you must change your thinking, as well. You must stop thinking about what to do to a ROW... you must start thinking about what to do to a COLUMN instead... it's the first step towards what some of us call "set based thinking". If, for any given problem, you are thinking that you need a cursor, there's a very high probability that you are not yet thinking set based. Same goes for most WHILE loops although there are a few exceptions (VERY few).
Think COLUMNS, not ROWS...
I don't know what they teach in schools now, but in the old days, the first thing they taught after the classic "Hello World" example, was how to count from 1 to 100... personally, I think it's a great way to introduce set based thinking. How would YOU write SQL to count from 1 to 100 and display it on the output device? Most folks would start some bloody counter at 1 with a cursor or While loop... What's the setbased way of doing it?
Here's one possible answer...
SET ROWCOUNT 100
SELECT IDENTITY(INT,1,1) AS TheCount
INTO #MyHead
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
SET ROWCOUNT 0
SELECT *
FROM #MyHead
DROP TABLE #MyHead
Again, the key to high performance code is to forget about the ROWS found in most GUI code... think in COLUMNS and you'll be well on your way to writing great set based code.
Let's up the stakes a bit... let's count to 1 MILLION (without displaying the results)...
--===== This takes about 2 seconds...
SET ROWCOUNT 1000000
SELECT IDENTITY(INT,1,1) AS TheCount
INTO #MyHead
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
SET ROWCOUNT 0
DROP TABLE #MyHead
... now, compare that to how long it takes a nice tight WHILE loop, 1 row at a time...
--===== This takes about 38 seconds and produces the same result
SET NOCOUNT ON
DECLARE @Counter INT
SET @Counter = 1
CREATE TABLE #MyHead (TheCount INT)
WHILE @Counter <= 1000000
BEGIN
INSERT INTO #MyHead (TheCount)
VALUES (@Counter)
SET @Counter = @Counter+1
END
DROP TABLE #MyHead
The 2 second example says "Create a column filled with a range of numbers"... the 38 second example says "Create rows from to 1 some maximum number".
As my good friend Serqiy would say... "Feel the difference".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sree, you've gotten some great advice here so far. I only have one thing to add. If you've been tasked with looking into performance tuning an application, a good first step may be to find out where the users (or the powers that be that assigned the task to you) percieved areas of concern are. One thing that I've found is that, once environment/hardware bottlenecks have been eliminated, you can tune code and indexes until you are blue in the face. If you are looking for things that can be done better, you'll find them. Now you may get to a point in smaller, less complex systems where you can have most everything tuned, but when working in large complex systems where many developers have had their hands in it, you'll always find something you could do better or more efficienet if you look hard enough. If you were tasked to tune it, there's probably a certain area they would like to see perform better.
-
Hi Everybody,
Thanks John, Jeff and Rudy.
I never knew what I have learnt today thanks to Jeff. I was always a ROW based thinker, but from now onwards, I will start thinking SET based.
Now off I go to dig into everything and then come and bug each one of you, if I am not clear with something. Hope you all don't mind 🙂
Thanks a lot....my week for happy learning is all set 😉
Wishes
Sree
-
I would be greatful to Rudy and Jeff, if they can pass me their list for performance tunning and optimization
Thanks once again.
Wishes
Sree
-
Sreemati,
The list isn't what you think... it's a bit sarcastic and starts off with something like "#1. Write good code."
The big key is as I said... think columns, not rows. Something else to consider... there's really no hard and fast rules... for example, although it's normally best to avoid inequalities in the WHERE or ON clauses, one of the fastest dupe checks contains them... and although it's normally best to avoid correlated subqueries, one of the fastest "missing identity" methods uses those. But one good rule to always remember is to consider how many rows must be "touched" in order to get the job done. Inequalities usually mean that rows must be touched more than once AND they can spawn "triangular joins" (about half a cross or Cartesian join).
So program to solve columns... think how many rows the query touches.
We look forward to you "bugging" us about set based thinking.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Also use profiler and look for high cpu and duration values. This will give you what procs and sql is consuming resources.
Far away is close at hand in the images of elsewhere.
Anon. -
New to this thread. The single most USEFUL thing you can do is to hire an expert to give your databases, servers and code a performance review. First, this will give you an INCREDIBLE bang for the buck as far as how much improvement can be done in a short period of time. Second, and most important, you will be able to watch over his/her shoulder and gain knowledge you would likely never have picked up trying to learn stuff from scratch. Any scripts run would be a gold-mine for you to review at your leisure when the consultant is gone as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Hi TheSQLGuru,
We have hired an expert and he has done an incredible job by reducing the data prcoessing time from 14 minutes per record to 3 minutes per record. Also put forward some wonderful job with error handling and error reporting but he is not too helpful when it comes for us new-comers to ask him how to go about it. All he says is test and error, there is no hard and fast rule, you will learn with experience. I know a lot of what he says is true. I am trying to work my way out and trying to understand what it is? So as to carry on with his recommendations which he left for us to work on for future
Anyways, thanks for your suggestion and surely we have already went about it. So many thanks.
Wishes
Sree
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply