How do I learn?
-
Hey Guys,
I know the basics of SQL server 2000 - installing and all the jobs etc. However, I want to now increase my knowledge further as I want to be a DBA. I would like to know about details of system tables, concepts of how to actually performance tune scripts, and further I want to learn about BI skills.
What do you recommend? - books?websites?
Your help is highly appreciated.
Thanks,
TK
-
You really want to learn? The best way to learn is to get good enough to teach. The only way to do that is to practice. The best way to practice is to do as many of us have done... try to answer every question on this forum... no better teacher than figuring out how to answer correctly.
--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) -
I'd agree with Jeff on that one. It gives you real world situations and problems that you have to solve.
Getting a book and working through it will help as well. Most books are similar, so get one and go through it, practicing exercises.
-
another way is to try to understand and answer interview questions, what will be your first steps when you come to work in morning...
-
Tk,
At the risk of ticking off "Mr Rogers", just some additional information... I believe that "performance tuning" effectiveness ratios are as follows:
1% - Hardware
2% - Server configuration
2% - Proper Indexing
95% - Writing performance enabled, scalable code
In other words, I believe, just from personal experience, that the biggest bang for the buck is writing good code... doesn't matter how good the hardware, configuration, or indexes are... if crap code is present, nothing but a rewrite will help. As a DBA, you need to learn to spot it quickly...
For example, what's wrong with this picture? There are two major performance inhibitors and one very minor one in this simple code...
SELECT * FROM sometable WHERE ISNULL(somecolumn,0) <> 0
The "*" is a Bozo-no-no because it typically won't allow an Index SEEK and it probably returns many more columns that what will actually be used.
The ISNULL(somecolumn,0) <> 0 will not allow an Index SEEK because there's a calculation on the table column. Real shame, too, because NULLs can never be equal to 0 or anything else, for that matter. Writing it as somecolumn <> 0 will do exactly the same thing as ISNULL(somecolumn,0) <> 0 except that it will allow an Index SEEK if correctly indexed. That is unless you were foolish enough to change the ANSI NULL settings for the server
FROM sometable should be FROM owner.sometable. In multi-row batch code, it doesn't save but a micro-second or two... in GUI code that may be called thousands of times per second, the tiny cost savings can start to add up.
--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) -
My favorite book
The Guru's Guide to SQL Server Architecture and Internals - Ken Henderson
Read thru some of my posts especially around middle of year before last and there before where the forum users got into discussions about undocumented features such as DBCC PAGE and taking apart how things are managed in the system. Look thru the past postings of anyone you feel has provided a lot of usefull information and see what else they have spoke about. But like said here try to answer as many posts yourself as completely as possible and provide as much supporting documentation as you can.
All that said, a DBA doesn't always mean you have to know anything, depends on the company you work for. I have seen some where installation, configuration, patching and security management are all they actually do. Some others want you to be able to identify code problems and more. But if you are interested in the job within your company see if you can meet with someone who does that job and see if you can sit in while they are working to get a better understanding of what they must know.
-
Thanks heaps guys. I will start doing that.
I have a bit of a problem though- I learn better with examples, does anyone know any website that can help understand the concepts with examples. (Thanks Jeff, that's the kind of examples I am looking for)
The last company I worked for started using Foglight/Spotlight. I helped with the install and all, However, before the official training could start I had to take maternity leave, and hence missed out on it. (And then while on maternity leave, had to move cities, so completely missed it.) I noticed that it came with a software that you can use to fine tune any script/code. Do you guys use such an application when you need to fine tune code? Do these applications help to understand? Are they reliable?
-
The only tool I use is the large Calcium knob that sits upon my shoulders
Most good-to-excellent Developers and DBA's are able to beat those kinds of tools for both performance and reduction of resources... although they may save some time for those that can't... lot's of time.--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) -
If you are looking for training materials, you might consider looking here:
http://www.learnsqlserver.com/VideoTutorials/
http://www.learnintegrationservices.com/
I havent used any of the videos so I dont know how good they are, but they are cheap.
-
I'm in the same boat where I would like to learn how to become a better DBA. I'm still pretty green. So I'm going to take a shot and start asking questions (and then hopefully later I can start answering them). There were a couple of things I didn't understand with your example Jeff. The use of "*" I understand but regaring the Index SEEK, what is it and how does the example prevent it from running? My other questions is where is the ANSI NULL setting on the server so I can check what mine is set at?
Thanks for your help.
-
I'm glad to see I'm not alone on this boat. I too am a novice at SQL and have been trying to build my knowledge base. I like and appreciate the suggestion of reading, answering and most importantly understanding the post in these forums.
Thanks for the heads up.
-
This is where you ask "What is Books Online"?
"Books Online" is affectionately known simply as "BOL". In SQL Server 2000, you can find it easily in Query Analyzer under the [Help] button and is labelled there as "Transact-SQL Help". There is an Index tab, a Search tab, and a Contents tab that all have information in them. There is also a Favorites tab where you can store the link for your favorite BOL "articles".
As with any "reference" material, it's true that you sometimes need to know a bit about what you want to find before you can find it... here's a couple of things to get you started (find these in the BOL Index)...
ANSI_NULLS
Execution Plan
Execution Plan options (look for INDEX SEEK and compare to INDEX SCAN)
One of the best things to do when learning any new language is to familarize yourself with every function (string, math, statisical, etc, etc). You'll find all of those in BOL, as well. It's up to you to figure out what to do with them.
The other thing you must do, is you must "play". You must create test data (sometimes, lot's of it... see below) and "try things out". Searching this forum will many times give you lot's of options for what you want to try to do and some of both the best and worst ways to do things... but you won't normally know for sure until you try the different code and examine the execution plan for each piece of code. Don't forget that most folks don't post what their indexes are, so you need to try different indexes.
When I need to "play" against lot's of data, I'll usually refresh my standard "million row test table" with the following code... I'll start it off at 100 rows just to quickly get the code working... then I'll refresh the table with a 1,000 or 10,000 and start tweaking the code and indexes. Then, I'll refresh with a million rows and really go for broke on the tuning/testing.
--===== Create and populate a 1,000,000 row test table. -- Column RowNum has a range of 1 to 1,000,000 unique numbers -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers -- Column "SomeString" has a range of "AA" to "ZZ" non-unique 2 character strings -- Column "SomeNumber has a range of 0.0000 to 99.9999 non-unique numbers -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Takes about 77 seconds to execute. SELECT TOP 1000000 RowNum = IDENTITY(INT,1,1), SomeInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT), SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)), SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)), SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY), SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) INTO dbo.JBMTest FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN--===== A table is not properly formed unless a Primary Key has been assigned ALTER TABLE dbo.JBMTest ADD PRIMARY KEY CLUSTERED (RowNum)
There's a lot to just SQL and even more to MS SQL Server... there's system tables and views to be exploited, whacko methods of generating lot's of test data (like above... just gotta figure it out
), Tally (numbers) tables, and a whole lot more. Lot's of us have been working with SQL Server for years and years... I don't think I'm alone in saying that I'm still learning different methods (mostly for the better) of doing things. Becoming an SQL "Ninja" in less than a year is probably not going to happen for you. Start with the basics... lookup SELECT, INSERT, UPDATE, DELETE in BOL and read all about them very thoroughly... don't think you know these just because you've done one or two... for example, did you know the UPDATE in MS SQL can update both a variable and a column with the same value at the same time? It's in BOL. Did you know that UPDATE and DELETE both have TWO From clauses?
Play... search/read forums... study... play some more...
--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) -
hey guys, does anyone know where I can get the chart of system tables and how do they relate to each other?
-
But this is an example of what I'm talking about... this was first on the returns for Google when I searched for "CHART OF SYSTEM TABLES FOR SQL SERVER". Why didn't YOU Google for it?
If you want to learn well, you simply MUST start looking for stuff on your own. You need to develop the intellectual curiosity to start seeing what's under the hood using your own mind instead of others... otherwise, you'll end up just like lots of others... able to write poor performing SQL.
--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) -
I guess BOL doesn't have a really simple explanation of what an INDEX SEEK is nor why it's better than an INDEX SCAN...
Basically, the name of each says it all... An index SEEK does a search in the index that basically leads the system right to the data... almost like opening a telephone book to the correct page on the first try. An index SCAN is more like starting at the beginning of a telephone book and turning every page until you get to where you want. Not quite that bad, but almost.
And, the only reason why index SCANs are better than TABLE SCANS is because an index doesn't have as much data in it (normally).
There's a much more detailed explanation in BOL about branches and leaf levels, etc, and it's much more correct that the simple explanation I gave... but it does give you the idea of why it's usually good to go for SEEKs instead of SCANs.
Then, there's the "Holy Grail" of indexes... the CLUSTERED INDEX SEEK. A table may have only 1 clustered index, so choose wisely (depends on what is more important... input speed or select speed). It does NOT have to be the Primary Key but it usually is. What makes a clustered index different from other indexes is that it's embedded in the data itself and will affect the order of pages (although not always the order of Selects so always use an ORDER BY to guarantee the correct order of returns if one is needed.).
There's a lot more about indexes in straight English on Google and the Microsoft web site... take a look...
--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)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply