-- You can verify what edition you are running with the following query:
SELECT SERVERPROPERTY('Edition');
-- There is also a server property called EngineEdition that you can inspect, as follows:
SELECT SERVERPROPERTY('EngineEdition');
-- For example, the following query returns 58 rows of output on my SQL Server 2008 instance:
USE master;
SELECT name FROM sys.objects
WHERE type_desc = 'SYSTEM_TABLE';
-- For example, there is a catalog view called objects in the sys schema,
-- so to reference the view, the following can be executed:
SELECT * FROM sys.objects;
-- Because the output from these types of queries is too wide to reproduce,
-- let me just suggest that you run these two queries yourself and observe the difference:
SELECT * FROM sys.databases;
SELECT * FROM sysdatabases;
-- For example, if you want to know which databases are in simple recovery mode,
-- you can run the following:
SELECT name FROM sys.databases
WHERE recovery_model_desc = 'SIMPLE';
-- So to see the definition of sys.tables, you can execute the following:
SELECT object_definition (object_id('sys.tables'));
-- To view the recovery model of a single database, you can use the property function as follows:
SELECT DATABASEPROPERTYEX('msdb', 'Recovery');
-- To view the recovery models of all our databases, you can use the sys.databases view:
SELECT name, recovery_model, recovery_model_desc
FROM sys.databases;
-- Both of the following SELECT statements should return the same result:
SELECT database_id
FROM sys.databases
WHERE name = 'AdventureWorks';
SELECT DB_ID('AdventureWorks');
-- Using the catalog views, these queries are straightforward:
SELECT name FROM sys.databases
WHERE suser_sname(owner_sid) ='sue';
SELECT name FROM sys.databases
WHERE compatibility_level < 90;
-- Each Resource Monitor has its own SPID, which you can see by querying the
-- sys.dm_exec_requests and sys.dm_os_workers DMVs, as shown here:
SELECT session_id,
CONVERT (varchar(10), t1.status) AS status,
CONVERT (varchar(20), t1.command) AS command,
CONVERT (varchar(15), t2.state) AS worker_state
FROM sys.dm_exec_requests AS t1 JOIN sys.dm_os_workers AS t2
ON t2.task_address = t1.task_address
WHERE command = 'RESOURCE MONITOR';
-- If there is an active DAC, the query will return the SPID for the DAC;
-- otherwise, it will return no rows.
SELECT s.session_id
FROM sys.tcp_endpoints as e JOIN sys.dm_exec_sessions as s
ON e.endpoint_id = s.endpoint_id
WHERE e.name='Dedicated Admin Connection';
-- You can monitor Memory Broker behavior by querying the Memory Broker ring buffer as follows:
SELECT * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type =
'RING_BUFFER_MEMORY_BROKER';
-- The DMV called sys.dm_os_memory_clerks has a column called multi_pages_kb that shows
-- how much space is used by a memory component outside the buffer pool:
SELECT type, sum(multi_pages_kb)
FROM sys.dm_os_memory_clerks
WHERE multi_pages_kb != 0
GROUP BY type;
-- Example
--- Create a resource pool for production processing
--- and set limits.
USE master;
GO
CREATE RESOURCE POOL pProductionProcessing
WITH
(
MAX_CPU_PERCENT = 100,
MIN_CPU_PERCENT = 50
);
GO
--- Create a workload group for production processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP gProductionProcessing
WITH
(
IMPORTANCE = MEDIUM
)
--- Assign the workload group to the production processing
--- resource pool.
USING pProductionProcessing;
GO
--- Create a resource pool for off-hours processing
--- and set limits.
CREATE RESOURCE POOL pOffHoursProcessing
WITH
(
MAX_CPU_PERCENT = 50,
MIN_CPU_PERCENT = 0
);
GO
--- Create a workload group for off-hours processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP gOffHoursProcessing
WITH
(
IMPORTANCE = LOW
)
--- Assign the workload group to the off-hours processing
--- resource pool.
USING pOffHoursProcessing;
GO
--- Any changes to workload groups or resource pools require that the
--- resource governor be reconfigured
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
USE master;
GO
CREATE TABLE tblClassifierTimeTable (
strGroupName sysname not null,
tStartTime time not null,
tEndTime time not null
);
GO
--- Add time values that the classifier will use to
--- determine the workload group for a session.
INSERT into tblClassifierTimeTable
VALUES('gProductionProcessing', '6:35 AM', '6:15 PM');
GO
--- Create the classifier function
CREATE FUNCTION fnTimeClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @strGroup sysname
DECLARE @loginTime time
SET @loginTime = CONVERT(time,GETDATE())
SELECT TOP 1 @strGroup = strGroupName
FROM dbo.tblClassifierTimeTable
WHERE tStartTime <= @loginTime and tEndTime >= @loginTime
IF(@strGroup is not null)
BEGIN
RETURN @strGroup
END
--- Use the default workload group if there is no match
--- on the lookup.
RETURN N'gOffHoursProcessing'
END;
GO
--- Reconfigure the Resource Governor to use the new function
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
-- The following query returns the protocol used for the current connection,
-- using the DMV sys.dm_exec_connections:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
-- You’ll first need to change the ‘show advanced options’ option to be 1, as shown here:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
-- To see which options are advanced, you can again query the sys.configurations view and
-- examine a column called is_advanced, which lets you see which options are considered advanced:
SELECT * FROM sys.configurations
WHERE is_advanced = 1;
GO
-- You can use the sys.configurations view to determine which options are dynamic:
SELECT * FROM sys.configurations
WHERE is_dynamic = 1;
GO
-- After the backup has been performed, you can inspect the backupset table in the msdb database
-- to determine the compression ratio, using a statement like the following:
SELECT backup_size/compressed_backup_size FROM msdb..backupset;
-- Here’s an example:
SELECT AVG(charge_amt), category
FROM charge
GROUP BY category
-- A query that is running on multiple CPUs has one row for each thread, as follows:
SELECT
task_address,
task_state,
context_switches_count,
pending_io_count,
pending_io_byte_count,
pending_io_byte_average,
scheduler_id,
session_id,
exec_context_id,
request_id,
worker_address,
host_address
FROM sys.dm_os_tasks
ORDER BY session_id, request_id;
