Steve Thompson ConfigMgr MVP Configuration Manager Advisor Dell Services UDB407 How to solve common questions with queries Understand the query process Identify performance issues Statistics ID: 570824
Download Presentation The PPT/PDF document "Advanced Query Techniques: Beyond the ba..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1Slide2
Advanced Query Techniques: Beyond the basics
Steve Thompson, ConfigMgr MVPConfiguration Manager Advisor, Dell Services
UD-B407Slide3
How to solve common questions with queries
Understand the query processIdentify performance
issues
Statistics and Indexes
Agenda
Advanced Query TechniquesSlide4
Document SQL Server
Implementation
Identify (potential) Issues
Optimizing database performance
Question and Answer
Agenda
Taming SQL Performance issuesSlide5
Advanced Query TechniquesSlide6
Three sets of scenario based query solutions
Discuss the methodology of locating information
How to enhance queries
Advanced Query Techniques
How to solve common query questionsSlide7
SQL Query Scenario #1
How do I get a list of all
collections?
How do I determine how many have incremental update enabled?Core conceptsSelectCount
Case
Reference
ConfigMgr 2007
Reporting
Views (
msi
install)
http://
www.microsoft.com/en-us/download/details.aspx?id=22052
Slide8
SQL Query Scenario #1
Demo - CollectionsSlide9
SQL Query Scenario #
2
How do I get a list of all servers that have any version of SQL Server installed
?How do I limit by collection?Core concepts
TOP n (Data profiling)Inner Join
Alias
Where
Distinct/Grouping
BatchSlide10
Joins
System
(Computer)
Workstation
Status
Inner Join = Intersection
Show me all computers that have
a
Last Hardware Scan
date
SELECT sys.Netbios_Name0 AS
MachineName
,
ws.LastHWScan
FROM
v_R_System
sys
INNER JOIN
v_GS_WORKSTATION_STATUS
ws
ON
sys.ResourceID = ws.ResourceID
Inner JoinSlide11
SQL Query Scenario #2
Demo – Qualified Server ListSlide12
SQL Query Scenario #
3
How do I get a list of all computers that
have not reported Hardware Inventory?How do I get a list of all computers that have not installed Adobe Shockwave Player?Core concepts
Advanced Joins (LEFT)Subquery (NOT IN)
Schema
ViewSlide13
Joins – Left Join
System
(Computer)
Workstation
Status
Left Join = Intersection + Left
Show me all computers that have and do NOT have a Last Hardware Scan date.
SELECT sys.Netbios_Name0 AS
MachineName
,
ws.LastHWScan
FROM
v_R_System
sys
LEFT OUTER JOIN
v_GS_WORKSTATION_STATUS
ws
ON
sys.ResourceID
= ws.ResourceID
Left Outer JoinSlide14
SQL Query Scenario #3
Demo – Advanced JoinsSlide15
Query Process Overview
Understand the
query process
Identify the underlying issue(s)
RemediateSlide16
Query Process
2) Parsing
&
3) Binding
4) Query
Optimization
5) Query
Execution
6) Query
Results
1) SQL Statement
Relational Engine
Storage Engine
Generates “cost”
based query plan
Execution Plan created & cached
Indexes
StatisticsSlide17
Statistics
Used by the Query Processor
May
improve query optimization planMany plans (estimates) may be evaluated
Plan with the least cost, wins!
How
are they created?Slide18
Statistics – Auto createdSlide19
Statistics - Optimizing
How are they maintained?
Somewhat “auto magically”, however, Statistics
may become stale…How do you know if they are stale?
DBCC SHOW_STATISTICS
STATS_DATE
DMV -
sys.stats
How to update?
UPDATE STATISTICS
EXEC
sp_updatestats
;
When to update?Slide20
Indexes
Used by the Storage Engine
Index Types
ClusteredNon ClusteredHow is Index data stored
?Pages and Extents
Page SplitsSlide21
IndexesSlide22
Indexes – Page SplitsSlide23
Indexes –
optimizing
Index reorganization
Index rebuilds
Fill FactorSlide24
Indexes –
optimizing
How
to update?
ALTER INDEX
What’s
best?
Depends on index size
Depends on amount of fragmentation
Depends on the application
Measure, choose and monitorSlide25
SQL Scripts & PowerShell
SQLAudit
Document
ImplementationIdentify
(potential) Issues Statistics
&
Indexes
Database recovery mode
Database file(s)
Sequence
1) Collection
Information
2) Combine
in an Excel Workbook
Extensible!Slide26
Indexes & Statistics
SQL & PowerShellDemoSlide27
Taming SQL Performance issuesSlide28
Tools to help identify Performance Issues
Profiler
Show Query Plan
Why would you use these tools?When would you use these tools?Slide29
Optimizing database performance
Custom database solution:
CMMonitor
What does it do?
Why is it needed?
Key Advantages
No changes made to CM database
Configurable
how statistics and indexes are updated
Logs activity
“Open source
”
solution based on
Ola
Hallengren’s
SQL Server maintenance solution
http://
ola.hallengren.com/sql-server-index-and-statistics-maintenance.htmlSlide30
CMMonitor
How do we implement?
Steps
involved1) Create Database
2) Create objects in database3) Schedule SQL Agent task(s)Slide31
SQL Performance Tools
CMMonitorDemoSlide32
Recommendations/Summary
Query Scenarios
Query Process Summary
Document
implementation: SQLAudit
Implement database solution:
CMMonitor
The ask – report back!Slide33
Questions
?Slide34
More information
Review Brian
Mason / Kent
Augerland
session
UD-B308
Advanced Infrastructure for System Center 2012 Configuration Manager
SP1
Blog (all demo material posted here)
http
://SteveThompsonMVP.wordpress.com
Email
Steve_r_Thompson@dell.com
Slide35
Evaluation
Complete your session evaluations today and enter to win prizes daily.
Provide your feedback at a CommNet kiosk or log on at
www.2013mms.com
.
Upon submission you will receive instant notification if you have won a prize.
Prize pickup is at the Information Desk located in Attendee Services in the Mandalay Bay Foyer.
Entry details can be found on the MMS website.
We want to hear from you!Slide36
Resources
http://channel9.msdn.com/Events
Access MMS Online to view session recordings after the event.Slide37
© 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.Slide38
AppendixSlide39
References
Statistics
http://msdn.microsoft.com/en-us/library/ms190397(v=sql.110).
aspx Indexeshttp://msdn.microsoft.com/en-us/library/ms175049.aspx
SQL Server Maintenance Solutionhttp://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
How to read graphical query plans
http://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans
/