Performace Tuning

Question

Post reply

Performace Tuning

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
September 6, 2007 at 2:03 am

#153803

Hi all,
I have a question, it's not really a problem but some help would be much apprechiated.
I have been using SQL for sometime now and the more I work with Queries and tuning the more I start to realise that all I want to do is became a pure Query Tunning Master he he he he
Do these roles exsist?
What is the best practical way to enhance my skills in this area?
And does anyone have any advice or tips for someone who is looking to move more into this area of work?

Thanks
Enjoy the code the code enjoys you
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 1

- yes

- read the (inside) books, play, test

- test, test, test, play

- follow some good forums like SSC

learn to play, play to learn

And if you can, join a user conference like sqlpass

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Yes, but not common. It's a role I have, though I often do other stuff too. As for advice...

Read, as much as you can. Books, white papers, blogs, forums. Also podcasts and web casts

Play. Try things out. Get familiar with the internals of SQL. Know how to read the plan cache, how to read a page header, a deadlock graph, an execution plan. Find a nasty query and experiment with variations and see what they do to the query performance.

Go to conferences. PASS this year has a number of presentations on query performance in one form or another. I'm sure the other conferences do also.

Teach. Present at conferences or user group meetings. Answer questions here or on the ms newsgroups or other forums. Start a blog. Helping others forces you to learn more. I've heard it said that you never understand a subject until you teach it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

You really want to get good at performance tuning? Start answering questions on this forum And learn how to make millions of rows of test data... here's a start...

--===== 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 42 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)

--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)