Download - Back2 Basic Tools
![Page 2: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/2.jpg)
Michelle Gutzait
[email protected]@gmail.com
Blog: http://michelle-gutzait.spaces.live.com/default.aspx
![Page 3: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/3.jpg)
out of scope…
T-SQL enhancements –
The FILESTREAM datatypes
The new DATE and TIME datatypes
The new Merge command
Table Valued Parameters
Large UDTs
User-Defined Aggregates
Hierarchical data
New Dependency Management
Filtered Indexes
![Page 4: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/4.jpg)
More topics – out of scope…
Integration with Microsoft Office 2007
Transparent Data Encryption
Auditing data access
Data Compression
Security related improvements
Database mirroring, replication, Service Broker
Resource Governor
Dynamic Development with New ADO , Visual Studio options and
Dot Net 3
Entity Data Services (LOB and eSQL)
Development of frequently disconnected applications
![Page 5: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/5.jpg)
Introduction
Enhancements in Management Studio 2008
The query editor and debugger
Performance Studio (?)
PowerShell integration (?)
![Page 6: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/6.jpg)
Introduction
Enhancements in Management Studio 2008
The query editor and debugger
Performance Studio (?)
PowerShell integration (?)
![Page 7: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/7.jpg)
Nice to meet you…..
![Page 8: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/8.jpg)
Introduction
Enhancements in Management Studio 2008
The query editor and debugger
Performance Studio (?)
PowerShell integration (?)
![Page 9: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/9.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 10: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/10.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 11: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/11.jpg)
Provides high-level perspective on what might be ailing your SQL Server., in
real time
To run:
Right click on SQL Server instance (Object Explorer) Activity monitor
Refresh interval
Pause
Sort and filter data
![Page 12: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/12.jpg)
Processes
Shows all running processes
Previous versions:
Sp_who2
Current Activity
![Page 13: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/13.jpg)
Right click on Process:
Details – shows last running batch
Kill Process
Trace Process in SQL Server Profiler – opens SQL Profiler, tracing
only the specific SPID.
![Page 14: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/14.jpg)
Resource waits
Provides a snapshot of key resource waits occurring on the server
Measures the amount of time a worker thread has to wait until it can
gain access to a resource
Helping identify potential resource bottleneck
![Page 15: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/15.jpg)
Buffer IO – waits for IO subsystem
CPU – Waiting for CPU resource
Buffer Latch – Disk to memory contention
Latch – possible contention in internal caches
Lock – waiting to acquire a lock on an object
Logging – transaction logs
Memory - waiting for memory resource
Network IO – waiting for Network resource
![Page 16: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/16.jpg)
Data File IO
Provides information about IO usage of database files
Helps identify database/database files bottlenecks
![Page 17: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/17.jpg)
Recent Expensive Queries
Provides information about most recent expensive queries
Those currently in cache
![Page 18: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/18.jpg)
Right click on query:
Edit Query Text - displaying the entire query (not just the small part
of the query you see in the window)
Show Execution Plan - displaying a graphical execution plan of the
query
![Page 19: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/19.jpg)
Tool Tips throughout all the screens of the Activity Monitor
Most of the data displayed in the Activity Monitor is from DMVs.
Many of the Tool Tips even tell you the name of the DMV used to
return the data you are viewing
![Page 20: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/20.jpg)
Querying DMVs
Top files under load
Process information SELECT fn.text,p.* from sysprocesses as pCROSS APPLY fn_get_sql(sql_handle) as fnORDER BY spid
Top wait statistics SELECT TOP 10 [Wait type] = wait_type, [Wait time (s)] = wait_time_ms / 1000, [% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER()) FROM sys.dm_os_wait_statsWHERE wait_type NOT LIKE '%SLEEP%' ORDER BY wait_time_ms DESC
Top files under load SELECT db_name(mf.database_id),physical_name,num_of_readsFROM sys.master_files mf Inner join sys.dm_io_virtual_file_stats(NULL,NULL) as vfon mf.database_id = vf.database_id and mf.file_id=vf.file_idorder by num_of_reads desc
Top query by Avg:
Top by CPU Top by IOTop by Duration
SELECT TOP 10 [Average CPU used] = total_worker_time / qs.execution_count, [Total CPU used] = total_worker_time ,[Execution count] = qs.execution_count ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,[Parent Query] = qt.text ,DatabaseName = DB_NAME(qt.dbid)FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Average CPU used] DESC
![Page 21: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/21.jpg)
Permissions
To view the Activity Monitor :
VIEW SERVER STATE permission on the server
Permission to KILL a process
sysadmin and processadmin fixed database roles
is not transferable.
![Page 22: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/22.jpg)
Demo…
![Page 23: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/23.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 24: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/24.jpg)
Object Explorer Details
In SQL Server 2005 - displayed the same information as in Object Explorer
View Object Explorer Details or F7
SQL 2008 – screen includes more information. Examples:
![Page 25: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/25.jpg)
Object Explorer Details
Right click on titles in “Object Explorer View” – there are more
details that can be shown in the window:
Any changes you make are automatically remembered for next time you come back to this screen
![Page 26: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/26.jpg)
Demo…
![Page 27: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/27.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 28: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/28.jpg)
Object Search
The scope of the search depends on what object has been selected in the Object
Explorer
Can’t “jump” to the right location, only view properties
![Page 29: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/29.jpg)
Demo…
![Page 30: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/30.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
Security related improvements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 31: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/31.jpg)
Custom colors for connection
Handy for those who work often with multiple SQL Server instances
Connect Options
Enable “use custom color” option and then “Select” a color
![Page 32: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/32.jpg)
Custom colors for connection
Example
Development
Production
![Page 33: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/33.jpg)
Demo…
![Page 34: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/34.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 35: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/35.jpg)
Error when trying to modify table definition
For example –modifying column’s datatype
Might be a good thing for Production, but ….
![Page 36: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/36.jpg)
Error when trying to modify table definition
Solution:
In SSMS 2008 - Tools Options Designers
![Page 37: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/37.jpg)
Demo…
![Page 38: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/38.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 39: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/39.jpg)
Speed Enhancements for SSMS
Faster communications between SSMS and the SQL Server engine
![Page 40: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/40.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 41: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/41.jpg)
UI Framework Enhancements
Many little things are configurable: Tools Options
SQL 2005: SQL 2008:
![Page 42: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/42.jpg)
Speaking of enhancements… Have you ever used these in SQL 2005…?
![Page 43: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/43.jpg)
Demo…
![Page 44: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/44.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 45: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/45.jpg)
"Open Table" enhancements
A limited amount of rows is returned to the "Open Table"
dialog to avoid locks and load on server.
Configurable
![Page 46: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/46.jpg)
Demo…
![Page 47: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/47.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
Security related improvements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 48: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/48.jpg)
Register servers at a central location
View Registered Servers
There are two kinds of registered servers:
Local
Unique to a user on the local machine
Stored on file system
SQL Server Authentication allowed (stores passwords)
Supporting previous versions of SQL Server
Central
Stored in the Central Management Server
Only Windows Authentication can be used
Can be stored only in SQL 2008 database servers
No special permissions required
![Page 49: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/49.jpg)
Exists in SSMS 2005 as well – Import/Export servers registration
Go to the Registered Servers window
Right click on Group
![Page 50: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/50.jpg)
Import/Export servers registration
You can include User Names and Passwords in export file (encrypted!)
![Page 51: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/51.jpg)
Demo…
![Page 52: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/52.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 53: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/53.jpg)
GUI for Table Partitioning Setup
Wizard
Wizard
![Page 54: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/54.jpg)
Wizard for Table Partitioning Setup
Run immediately / script
![Page 56: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/56.jpg)
Activity Monitor
Object Explorer Details
Object Search
Custom colors for connection
Error when trying to modify table definition
Speed Enhancements for SSMS
UI Framework Enhancements
Security related improvements
"Open Table" enhancements
Register servers at a central location
Wizard for Table Partitioning Setup
Service Broker UI enhancements
![Page 57: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/57.jpg)
Service Broker UI enhancements
Easier setup of Service Broker. Provided Templates and
context menu in the object explorer
SQL 2005:
SQL 2008:
SQL 2008:
![Page 58: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/58.jpg)
Introduction
T-SQL enhancments
Enhancements in Management Studio 2008
The query editor and debugger
Performance Studio (?)
PowerShell integration
![Page 59: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/59.jpg)
Multi-Server Queries
Intellisense added to the Query Editor
Collapsible window
Customizable tabs
Error list
New Right-Click options on Results Grid
Showplan - new options
The debugger
![Page 60: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/60.jpg)
Multi-Server Queries
Intellisense added to the Query Editor
Collapsible window
Customizable tabs
Error list
New Right-Click options on Results Grid
Showplan - new options
The debugger
![Page 61: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/61.jpg)
Multi-Server Queries
Query multiple servers at the same time, returning the results
to a single window
![Page 62: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/62.jpg)
Multi-Server Queries
Create a Server Group from the Registered Servers window
Add SQL Server registrations to the group
Supports previous versions
Right-click on the Server Group and select "New Query"
![Page 63: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/63.jpg)
Multi-Server Queries
Disadvantage
Need to create separate Server Groups for each subset, if not
all server are required in a query
![Page 64: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/64.jpg)
Multi-Server Queries – Example #1
![Page 65: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/65.jpg)
Multi-Server Queries Configuration options
Tools Options
![Page 66: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/66.jpg)
Multi-Server Queries – Example #1 (merged results = false)
![Page 67: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/67.jpg)
Multi-Server Queries – Example #2 (merged results = true)
![Page 68: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/68.jpg)
Multi-Server Queries – Example #3…
Database DemoDatabase does not exist on (local) server:
Database DemoDatabase exists on all servers:
![Page 69: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/69.jpg)
Demo…
![Page 70: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/70.jpg)
Multi-Server Queries
Intellisense added to the Query Editor
Collapsible window
Customizable tabs
Error list
New Right-Click options on Results Grid
Showplan - new options
The debugger
![Page 71: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/71.jpg)
Intellisense added to the Query Editor
![Page 72: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/72.jpg)
Collapsible window
![Page 73: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/73.jpg)
Multi-Server Queries
Intellisense added to the Query Editor
Collapsible window
Customizable tabs
Error list
New Right-Click options on Results Grid
Showplan - new options
The debugger
![Page 74: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/74.jpg)
Customizable tabs
The query editor’s results can now have their tabs
set with customized names
Switch to full screen view of SSMS
Shift+Alt+Enter key combination
![Page 75: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/75.jpg)
To switch between different tabs (Editor, Results, Messages) use F6.
![Page 76: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/76.jpg)
![Page 77: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/77.jpg)
Multi-Server Queries
Intellisense added to the Query Editor
Collapsible window
Customizable tabs
Error list
New Right-Click options on Results Grid
Showplan - new options
The debugger
![Page 78: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/78.jpg)
Error list
View list of syntax errors as you type
View the error list form:
![Page 79: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/79.jpg)
Error list
From all active Windows
Double click on error will set you on the code line
![Page 80: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/80.jpg)
Demo…
![Page 81: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/81.jpg)
Multi-Server Queries
Intellisense added to the Query Editor
Collapsible window
Customizable tabs
Error list
New Right-Click options on Results Grid
Showplan - new options
The debugger
![Page 82: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/82.jpg)
New Right-Click options on Results Grid
SQL 2005 SQL 2008
![Page 83: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/83.jpg)
Demo…
![Page 84: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/84.jpg)
Multi-Server Queries
Intellisense added to the Query Editor
Collapsible window
Customizable tabs
Error list
New Right-Click options on Results Grid
Showplan - new options
The debugger
![Page 85: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/85.jpg)
Showplan – new options
You can right-click a showplan graphic and see the XML version of the plan
The XML created by the showplan is now formatted instead of showing as a large string
You can right-click a graphical showplan and extract the SQL query that generated it
![Page 86: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/86.jpg)
Showplan – new options
Show as XML – nicely formatted
• Easier to compare
• Easier to analyze
![Page 87: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/87.jpg)
Showplan – new options
Extract query of a saved Showplan
Open saved file
![Page 88: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/88.jpg)
Demo…
![Page 89: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/89.jpg)
Multi-Server Queries
Intellisense added to the Query Editor
Collapsible window
Customizable tabs
Error list
New Right-Click options on Results Grid
Showplan - new options
The debugger
![Page 90: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/90.jpg)
The debugger
Set breakpoints
Step through code
Step into code
Set watches up to monitor variable values, locals
and the call stack
Woohoo!!!!
![Page 91: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/91.jpg)
Demo…
![Page 92: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/92.jpg)
SQL Server 2005 includes a Performance Dashboard (reports – standard and custom)
Displays real-time performance data
SQL 2008 Performance Studio
Collect performance data from multiple databases and store them in a central repository
Compare current and past performance
Troubleshoot performance problems
Track your custom performance metrics
Provides a set of prebuilt reports
Add your Custom reports
![Page 93: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/93.jpg)
Data Collection UI (Object Explorer)
Data Collection Configuration
Collection Set Reports
TargetData
CollectorCollection
Sets
TargetData
CollectorCollection
Sets
Management Data Warehouse
Architecture
![Page 94: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/94.jpg)
Two types of Collection Sets:
System
Mostly DMVs
User
User Collection
![Page 95: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/95.jpg)
Data Collection UI
Permissions: sysadmin permissions are requiredSystem DC
![Page 96: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/96.jpg)
Example report
Also:
Disk usage
Query statistics history
Custom reports
![Page 97: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/97.jpg)
SQL Server Agent jobs created automatically
(more can be added):
Job types:
Data Collection
Data purges
![Page 98: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/98.jpg)
Data Collector Set
Create custom data collections
Use Performance Studio as repository
Permissions:
Permissions to run Profiler
Sysadmin on server to add the set
![Page 99: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/99.jpg)
Data Collector Set
Follow BOL
How to: Use SQL Server Profiler to Create a SQL Trace Collection Set
![Page 100: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/100.jpg)
Data Collector Set
Open saved filein SSMS and execute
2 occurrencesin code
![Page 101: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/101.jpg)
Data Collector Set
Manageable from Object Explorer
![Page 102: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/102.jpg)
Demo…
![Page 103: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/103.jpg)
PowerShell is the .NET based automation engine that Microsoft shipped in November 2006. It can: Have a MMC layered over the top as in Exchange 2007
Be embedded into .NET applications
Be used as a command line shell and scripting language.
Available in 32 bit and 64 bit versions for Windows 2003, Windows XP and Windows Vista. Also installable feature in Windows Server 2008
It is now part of Microsoft’s Common Engineering Criteria and will be incorporated into all major products
![Page 104: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/104.jpg)
Supports more complex logic than Transact-SQL scripts
Implementations:
Provides simple navigation mechanism similar to file system paths
Set of cmdlets, which are commands used in PowerShell scripts to specify a SQL Server action
A verb-noun syntax, i.e. Get-Help.
![Page 105: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/105.jpg)
Usage:
In command prompt
powershell.exe
In SQL Server Management Studio
Right click an Object Start Powershell
In a SQL Server Agent job
Step type can be Powershell
![Page 106: Back2 Basic Tools](https://reader034.vdocuments.us/reader034/viewer/2022052506/557ddb80d8b42a4e358b4853/html5/thumbnails/106.jpg)
Questions?
Feel free to contact me: