march 31 – april 2, 2009
TRANSCRIPT
-
8/14/2019 March 31 April 2, 2009
1/51
Maximizing SQL Server Performanc e2005/ 2008
Mary Romero Sweeney
Microsoft
March 31 April 2, 2009
Copyright Sammamish Softwa re Servic es 2008 (All rights reserved)
-
8/14/2019 March 31 April 2, 2009
2/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Maximizing SQL Server 2008
Performance
What happens when you upgrade a nd how
to dea l with bottlenec ks and performanceprob lems on mid -range to la rge da tabases.
What you'll lea rn: What a re the rea l benefits and liab ilities with your
SS2005/ 2008 insta lla tion
How do existing job s and pac kages reallytransfer
over The 5 important tec hniques you need to know to get
the best p erformanc e from SS2005/ 2008
What a re the quirks tha t c an hold up la rge queriesand how to troubleshoot them
-
8/14/2019 March 31 April 2, 2009
3/51
Copyright Sammamish Software Services 2008 (All rights reserved)
What s your da tabase supposed to
do?Any d isc ussion on p erformanc e should
address wha t it is your system needs to do OLTP Online Transac tion Proc essing Dynamic data entry highly normalized
DSS Dec ision Support system
Denormalized data usually from a single OLTP Data Warehouse
Pre-aggrega ted da ta for effec tive reporting usua llyfrom multip le OLTPs and other systems
All of the a bove?
-
8/14/2019 March 31 April 2, 2009
4/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
New stuff in SQL Server 2008
Performance Data Collec tionhttp://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx
http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspxhttp://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx -
8/14/2019 March 31 April 2, 2009
5/51
New Stuff in SS2008
Query Governor
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
http://blogs.msdn.com/manisblog/archive/2008/11/16/sql-server-2008-resource-governor-part-ii.aspx
http://blogs.msdn.com/manisblog/archive/2008/11/16/sql-server-2008-resource-governor-part-ii.aspxhttp://blogs.msdn.com/manisblog/archive/2008/11/16/sql-server-2008-resource-governor-part-ii.aspxhttp://blogs.msdn.com/manisblog/archive/2008/11/16/sql-server-2008-resource-governor-part-ii.aspxhttp://blogs.msdn.com/manisblog/archive/2008/11/16/sql-server-2008-resource-governor-part-ii.aspx -
8/14/2019 March 31 April 2, 2009
6/51
New Stuff in SS2008SSAS Improvements
Major improvements so tha t you should see performanc eimprovements out o f the
Performanc e Hints in the MDX langua ge (simila r to query
hints in rela tiona l language)
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
-
8/14/2019 March 31 April 2, 2009
7/51Copyright Sammamish Softwa re Services 2008 (All rights reserved)
SQL Server 2005/ 2008 Performanc eFor SQL Devs
-
8/14/2019 March 31 April 2, 2009
8/51
Setting up a performanc etuning p roc ess
Crea te a baseline
Follow a p roc ess
Determine p ric e vs. Performanc e!
Is the investment of time and $ worth the
gain?Sta y informed ! (see resourc es a t the end
of this p resenta tion)
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
-
8/14/2019 March 31 April 2, 2009
9/51
Crea te a Performancebaseline
Crea te a performance baseline b y app lic a tion
Measure apps c urrent performanc e(profiler tool); ensure you c ap ture trendsra ther than one time numb ers
Measure workload and da ta c hanges over time
Evalaute both pea k and non-pea k usage sta ts
Compare other c onfigura tions forhardware a nd software
What are your app lic a tions performanc e
goals?Copyright Sammamish Software Services 2008 (All rights reserved)
-
8/14/2019 March 31 April 2, 2009
10/51
Follow a p roc ess
See this little gem fromwww.sqlcat.com:
http://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-
performance-and-scalability-flowchart.aspx
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
http://www.sqlcat.com/http://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://www.sqlcat.com/ -
8/14/2019 March 31 April 2, 2009
11/51
Top SQL Server Performanc e
Killers Poor Ind exing (see my
Ind exing sec tion la ter in thispresentation!)
Inac c ura te Sta tistic s
Exc essive b loc king and
deadlocks Non-setbased op era tions
(usua lly using T-SQL c ursors)
Poor Query Design Poor DB design
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Exc essive fragmenta tion
Exec ution p lan issues
Non-reusable
Poor
Freq uent rec omp ila tion TempDB issues
Source : SQL Server 2008 Query Performance Tuning Distilled, Fritchey,Apress 2009
-
8/14/2019 March 31 April 2, 2009
12/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
SS2005/ 20088 Performanc e
Tips for setupSQL Server 2005/ 2008 supports named
instanc es of SQL Server. You c an run up to 16 c onc urrent insta nc es of SQL Server
2005/ 2008 on the same server.
Eac h running insta nc e of SQL Server ta kes up server resourc es.Some resourc es a re sha red by multip le running insta nc es, i.e.,
MSDTC and Searc h servic es, but most a ren t. Bec ause of this,eac h a dd itiona l insta nc e of SQL Server running on the sameserver ha s to fight fo r ava ilab le resourc es, hurting performanc e
For best performance, don't mix p roduc tion da tabases
and development (test or stag ing) da tabases on thesame physic a l server. This not only serves to better separa te the two func tions
(produc tion and development), but prevents deve lopers from
using up server resourc es tha t c ould be bette r used byproduc tion users.
-
8/14/2019 March 31 April 2, 2009
13/51
2008 Query Performanc e ms-
help:/ / MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htm
Troub leshooting SQL Server 2005/ 2008 QueryPerformanc e/ Sc a lab ility Issues flow c ha rt
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
http://../ms-help:/MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htmhttp://../ms-help:/MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htmhttp://../ms-help:/MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htmhttp://../ms-help:/MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htmhttp://../ms-help:/MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htmhttp://../ms-help:/MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htmhttp://../ms-help:/MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htmhttp://../ms-help:/MS.VSCC.v90/MS.VSIPCC.v90/MS.SQLSVR.v10.en/s10de_4deptrbl/html/c2eb5f77-2dc1-49b4-bd19-2cdac4c6cae1.htm -
8/14/2019 March 31 April 2, 2009
14/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved)
SS2005/ 2008 Performanc e
Tips: Dev Use Trunc a te ta b le instead of Delete . Trunc a te is minima lly
logged ; then you must run Upda te Sta tistic s! If you must a llow users to ac c ess to your da ta , e.g. using Exc el or
Ac c ess, instead point them to a rep lic a ted reporting server i.e., aDM or DW, ra ther tha n your OLTP
Use c onsistent c od ing style and format
Instead of SELECT COUNT(*) from A muc h faste r,and more effic ient, way of c ounting rows in a ta b le is to run thefollowing query:
SELECT rowsFROM sysind exesWHERE id = OBJECT_ID('') AND ind id < 2
-
8/14/2019 March 31 April 2, 2009
15/51
Is understand ing indexing
reallya ll tha t important?
Copyright Sammamish Softwa re Services 2008 (All rights reserved)
-
8/14/2019 March 31 April 2, 2009
16/51
-
8/14/2019 March 31 April 2, 2009
17/51
Copyright Sammamish Software Services 2008 (All rights reserved)
Indexing for performanc eIndexing is a key way
to a ffec t performanceac ross multip le systemtypes Multip le Non-c lustered
indexes on a heap c an bepreferab le to theClustered p rimary key,multiple non-clusteredindexes.
Crea te indexes on joinc olumns to avoid hash
lookups
-
8/14/2019 March 31 April 2, 2009
18/51
Copyright Sammamish Software Services 2008 (All rights reserved)
How SQL Proc esses queriesA SELECT sta tement is nonp roc edura l; it does
not sta te the exac t steps that the da tabase
server should use to retrieve the requestedda ta . This means tha t the da ta base server mustana lyze the sta tement to d etermine the mosteffic ient way to extrac t the req uested da ta .
This is referred to as optimizingthe SELECTsta tement. The c omponent tha t does this isc a lled the query optimizer.
The inp ut to the op timizer c onsists of the query,the d a tabase sc hema (tab le a nd indexdefinitions), and the da ta base sta tistic s. Theoutp ut of the op timizer is a query exec ution
plan
-
8/14/2019 March 31 April 2, 2009
19/51
Copyright Sammamish Software Services 2008 (All rights reserved)
Using Exec ution Plans
In SQL Serve r 2008, you c an d isp layexec ution p lans by using thefollowing methods:
SQL Server Management Stud io
Disp lays either an estimated graphic a lexec ution p lan (sta tements do notexec ute) or an ac tual graphic a lexec ution p lan (on exec uted
sta tements), whic h you c an save a ndview in Management Stud io.
Transac t-SQL SET statement options
When you use the Transac t-SQL SETsta tement op tions, you c an prod uceestima ted and ac tual exec ution p lansin XML or text.
SQL Server Profile r event c lasses
Ic on info:
http://msdn.microsoft.com/en-us/ library/ ms175913.aspx
-
8/14/2019 March 31 April 2, 2009
20/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
SQL Server system stored
procedures
sp_who/ sp_who2 Reports sna pshot information about c urrent SQLServer users and p roc esses, inc lud ing whether the sta tement isblocked.
sp_lock Reports snapshot information about loc ks sp_spaceused Disp lays an estimate of the c urrent a mount o f d isk
spac e used by a tab le (or a whole d a tabase).
sp_monitor Disp lays sta tistic s, inc lud ing CPU usage, I/ O usage, andthe amount of time id le since sp_monitor was last exec uted .
Lea rn about some o f the hundred s of Dynamic Ma nagement
views ava ilab le in SQL Server 2005/2008.
-
8/14/2019 March 31 April 2, 2009
21/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Query op timizer hints:selec t TesterContextID
, o.Name Opera tion
, elTeste r.Name Teste r, ts.ComputerName
, elGroup .Name TesterGroup
FROM dbo.TesterContext tc
join dbo.Op era tion o with (nolock) on o.Op era tionID = tc .Op era tionIDleft JOIN dbo.TesterSta tion ts with (nolock) on ts.Teste rSta tionID =
tc .TesterSta tionID
join dbo.EquipmentLoc a tion ElTester with (nolock) onElTester.equipmentLoc a tionID = tc .equipmentLoc a tionID
left jo in dbo.EquipmentLoc a tionHierarc hy elh1 with (nolock) onelh1.c hildLoc ationID = elTester.equipmentLoc a tionID
left join dbo.EquipmentLoc a tion ElGroup with (nolock) onElGroup .equipmentLoc ationID = elh1.ParentLoc a tionID
left jo in dbo.EquipmentLoc a tionHierarc hy elh2 with (nolock) onelh2.childLocationID = elGroup.equipmentLocationID
-
8/14/2019 March 31 April 2, 2009
22/51Co ri ht Samm amish Software Services 2008 All ri hts reserved
Tools: System Monitor
AKA Perfmon(PerformanceMonitor)
You c an view SQLServer ob jec ts,performancec ounters, and thebehavior of otherobjects proc essors,
memory,
cache,
threads, proc esses.
-
8/14/2019 March 31 April 2, 2009
23/51
Copyright Sammamish Software Services 2008 (All rights reserved)
Tools: Ac tivity Monitor
-
8/14/2019 March 31 April 2, 2009
24/51
Copyright Sammamish Software Services 2008 (All rights reserved)
Tools: SQL Server Profiler
SQL Server Profiler is a graphica l user interfac e to SQLTrac e for monito ring an insta nc e of the SQL ServerDatabase Eng ine or Ana lysis Servic es. You c an
c apture and save da ta a bout ea c h event to a fileor tab le to ana lyze la ter. For example, you c anmonitor a p roduc tion environment to see whic h
stored proc ed ures a re a ffec ting performance byexec uting too slowly.
-
8/14/2019 March 31 April 2, 2009
25/51
Copyright Sammamish Software Services 2008 (All rights reserved)
Tools: DETA Database Eng ine Tuning
Advisor ana lyzes theperformance effec ts ofworkloadsrun aga inst
one or more da tabases. A workload is a set o f
Transac t-SQL sta tementstha t exec utes aga instda tabases you wa nt to
tune. DTA p rovides
rec ommend a tions toadd , remove, or mod ify
physic a l designstructures: physic a l c lustered
indexes,
nonc lustered indexes,
indexed views, and partitioning.
-
8/14/2019 March 31 April 2, 2009
26/51
Horizonta l da ta pa rtitioningPartitions enab le o rganiza tions to manage
la rge, growing tab les more effec tively bytransparently b reaking them intoma nageab le b loc ks of da ta.
SQL Server 2008 builds on the adva nc es onpartitioning in SQL Server 2005 by improvingperformance on la rge, pa rtitioned tab les.
Copyright Sammamish Software Services 2008 (All rights reserved)
-
8/14/2019 March 31 April 2, 2009
27/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Horizonta l ta b le pa rtitioningHorizonta l pa rtitioning
is a good way to
inc rease da tabaseperformance in la rgetables
Data is partitionedhorizonta lly by range
New to SQL Server2008 and rep lac es theprac tic e of using viewsand unions to pa rtition
Orders
Orders
Orders
< 2004
2004 -- 2008
2008 -- 2006
-
8/14/2019 March 31 April 2, 2009
28/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Proc ess for pa rtitioning a
table:
1. Crea te a partitioning func tion based on a field suc h
as a da te -- this func tion can be used multip le timesfor d ifferent ta b les
2. Ad d enough filegroups to your DB to
ac c ommodate the # of pa rtitions you want plusone more for the "next" filegroup -- add a file toeac h file group
3. Crea te a partitioning scheme that uses thepartitioning func tion
4. Crea te your tab le using the partitioning sc heme
instead of assigning to a sing le file group .
P i i F i D fi
-
8/14/2019 March 31 April 2, 2009
29/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
< 2004 2004 -- 2008 2008 -- 2006 > 2006
Partition Functions Define
Partition Boundaries Boundary values can be assigned to LEFT or RIGHT
Orders Orders Orders Orders
CREATE PARTITION FUNCTION pf_OrderDate (datetime)AS RANGE RIGHT
FOR VALUES ('01/01/2004', '01/01/2004', 01/01/2008')
C i i i i
-
8/14/2019 March 31 April 2, 2009
30/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Creating a partitioning
Scheme
This is the next filegroup!
Once you have c rea ted the approp ria te numberof Filegroups desired to matc h the # of pa rtitionsyou want -- you will need to c rea te a partitioning sc heme
The partitioning sc heme links the da ta base pa rtitions and thepartitioning func tion that you c rea ted ea rlier
CREATE PARTITION SCHEME ps_OrderDateAS PARTITION pf_OrderDateTO (fg1, fg2, fg3, fg4, fg5)
You will specify the partitioning scheme instead of a filegroup when creating tables
CREATE TABLE dbo.PartitionedTransactions(TransactionID int IDENTITY(1,1) NOT NULL,ProductID int NOT NULL,TransactionDate datetime NOT NULL DEFAULT (getdate()),
TransactionType nchar(1) NOT NULL)ON ps_OrderDate(TransactionDate) -- name of new partition scheme
-
8/14/2019 March 31 April 2, 2009
31/51
A proc ess for performanc etroubleshooting
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
www.sqlcat.com
http://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspx
http://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspxhttp://sqlcat.com/presentations/archive/2008/04/18/troubleshooting-sql-server-2005-2008-performance-and-scalability-flowchart.aspx -
8/14/2019 March 31 April 2, 2009
32/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved)
Indexing Basic s
-
8/14/2019 March 31 April 2, 2009
33/51
-
8/14/2019 March 31 April 2, 2009
34/51
-
8/14/2019 March 31 April 2, 2009
35/51
Copyright Sammamish Software Services 2008 (All rights reserved)
Ba lanced trees
When you c rea ted a p rimary key you got aba lanc ed tree struc ture for your tab le by
default unless you spec ified non-c lustered (in whic h case you got a hea p)
Root nodeAlba
...
Martin
Alba
Barr
Con
Fung
Fung
Martin
Martin
Ornish
Phua
Rudd
Martin
Smith
...
Smith
Smith
Smith
White
White
Alba
Grey
...
Grey
Hall
Jones
Jones
Jones
Leaf nodes
Non-Leaf (intermediary)nodes
-
8/14/2019 March 31 April 2, 2009
36/51
Co ri ht Samm amish Software Services 2008 All ri hts reserved
Indexes Indexes provide an a lternate way
of ac c essing da ta
A tab le c an have multip le indexesjust like a book.
Think of a gardening book with
p lants organized by commonname, like da isy and pansy, andan index with the sc ientific name
as an a lternate, but quic k, way ofac c essing a spec ific kind of p lant
Reasons to use indexes:
speeds da ta ac c ess,
enforc es uniqueness of rows
Scientificnames
Gardening
-
8/14/2019 March 31 April 2, 2009
37/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
How SQL Server Ac c esses Da ta
Table scan
SQL Server reads all table pages (same as clustered
index scan BTW!)
Best for tables stored in a heap!
Index seek SQL Server uses index pages to find rows
Best for tables stored in a specified arrangement! Note: Indexes can facilitate the speed of some
queries and greatly slow others.
Well see why
-
8/14/2019 March 31 April 2, 2009
38/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Index Considera tions Pros
Speeds up da ta ac c ess; Enforc esuniqueness of rows
Cons
Consumes d isk spac e; Inc urs overhead
How SQL Server uses indexes:
Determines Whether an Index Exists and Is
UsefulNavigates Through the Index
Evaluates the Search Value Against Each Key
Value and Repeats This Evaluation
-
8/14/2019 March 31 April 2, 2009
39/51
Copyright Sammamish Software Services 2008 (All rights reserved)
Clustered Indexes
Eac h Tab le Can Have only one Clustered Indexbec ause it rep resents the physic a l storage of the
ta b le Eac h p rimary key gets an index; Clusteredindex is the default when you c rea te a p rimarykey unless you spec ify otherwise
The Physic a l Row Order of the Tab le and theOrder of Rows in the Ind ex Are the Same
Alba...
Martin
AlbaBarrConFungFung
MartinMartinOrnishPhuaRudd
MartinSmith...
SmithSmithSmithWhiteWhite
AlbaGrey...
GreyHallJonesJonesJones
-
8/14/2019 March 31 April 2, 2009
40/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Non-c lustered Indexes Nonc lustered indexes a re the
SQL Server default when using the Create Index
statement
CREATE INDEX IX_ProductVendor_VendorID ON
Purchasing.Produc tVendor (VendorID);
Existing non-c lustered Ind exes Are Automatic a llyRebuilt When:
An existing c lustered index is d ropped
A c lustered index is c rea ted
The DROP_EXISTING option is used to c hange whic hc olumns define the c lustered index
-
8/14/2019 March 31 April 2, 2009
41/51
-
8/14/2019 March 31 April 2, 2009
42/51
-
8/14/2019 March 31 April 2, 2009
43/51
-
8/14/2019 March 31 April 2, 2009
44/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
What c olumns should I
index? Columns to Index
Primary (gets an index by default) and foreign keys(do not get a n index by default)
Those frequently searc hed in ranges
Those frequently ac c essed in sorted order
Columns Notto Index
Those ra rely referenc ed in q ueries (in the filte r i.e.,where c lause)
Those tha t c onta in few unique va lues e.g . the Sta te c olumn when most c ustomers a re loc a l
Those defined with bit, text, or image da ta types
-
8/14/2019 March 31 April 2, 2009
45/51
-
8/14/2019 March 31 April 2, 2009
46/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Composite IndexesCREATE NONCLUSTERED INDEX K_Contact_FullNameON Person.Contact (LastName ASC, FirstName ASC)
CompositeKey
Column 1
Column 2
Person.Contact
Note: yes one column can be involved in multiple indexes!
ContactID
NameStyle Title Firstname M Lastname
1 0 Mr. Gustavo NULL Achong
2 0 Ms. Catherine R. Abel
3 0 Ms. Kim NULL Abercrombie
4 0 Sr. Humberto NULL Acevedo
5 0 Sra. Pilar NULL Ackerman
-
8/14/2019 March 31 April 2, 2009
47/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Prob lems with upgrades?
For those of you p lanning an upgrade:
Chec k the Upgrade advisor andfollow it in deta il
Chec k forums and white papers(see referenc es on last p age)
-
8/14/2019 March 31 April 2, 2009
48/51
Marys Top 5 Performanc eRec ommend ations for SQL Dev
Use c overing indexes and inc luded c olumnsto improve q uery performance (understandindexing!)
Use the DETA to get ind exing advic e
Understand and utilize query exec ution p lans
Use SQL Server file g roup ing and tab lepartitioning
Set up a Performanc e Baseline and ProcessCopyright Sammamish Softwa re Services 2008 (All rights reserved )
-
8/14/2019 March 31 April 2, 2009
49/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
References
Exc ellent Tec hnet a rtic le:
http://www.microsoft.com/technet/prodtechnol/sql/bestpr
actice/dw_perf_top10.mspx Genera l SQL Serve r Performanc e Tuning Tips
Sourc e : SQL Server 2008 QueryPerformanc e Tuning Distilled , Fritc hey,Apress 2009
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspxhttp://../BackupMaterials/SQLPerformanceTips.htmhttp://../BackupMaterials/Top%2010%20SQL%20Server%202005%20Performance%20Issues%20for%20Data%20Warehouse%20and%20Reporting%20Applications.htmhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspxhttp://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx -
8/14/2019 March 31 April 2, 2009
50/51
Copyright Sammamish Softwa re Services 2008 (All rights reserved )
Resourc es (in add ition to STP
Con!): SQL Server white papers:
http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx
SQL Server performanc e web site :
http://www.sql-server-performance.com/
Website for ena b ling SQL server c ustomers
http://www.sqlcat.com/
http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspxhttp://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspxhttp://www.sql-server-performance.com/http://www.sqlcat.com/http://www.sqlcat.com/http://www.sql-server-performance.com/http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspxhttp://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx -
8/14/2019 March 31 April 2, 2009
51/51
Summary
Dev stra teg ies
Set up stra teg ies
Resources
Good luck!